""" Phase 3E: Ontology and Knowledge Graph Subtopics: 1. ITSM multi-entity graph construction 2. Semantic CMDB schema extraction from CI naming conventions 3. Graph-augmented root cause propagation analysis 4. Duplicate and orphaned ticket detection using graph connectivity """ import pandas as pd import numpy as np import plotly.express as px import plotly.graph_objects as go from collections import defaultdict import warnings warnings.filterwarnings('ignore') # Load cleaned data inc = pd.read_parquet("temp_files/incidents_cleaned.parquet") cr = pd.read_parquet("temp_files/cr_cleaned.parquet") sr = pd.read_parquet("temp_files/sr_cleaned.parquet") print("=" * 80) print("PHASE 3E: ONTOLOGY AND KNOWLEDGE GRAPH") print("=" * 80) # ============================================================ # 1. ITSM MULTI-ENTITY GRAPH CONSTRUCTION # ============================================================ print("\n--- 1. ITSM Multi-Entity Graph Construction ---") nodes = {} edges = [] # Add CI nodes ci_nodes = set(inc['cmdb_ci'].dropna()) | set(cr['cmdb_ci'].dropna()) | set(sr['cmdb_ci'].dropna()) for ci in ci_nodes: if ci and ci != 'Unknown': nodes[ci] = {'type': 'CI', 'label': ci} # Add incident nodes and edges for _, row in inc.iterrows(): inc_id = row['number'] nodes[inc_id] = {'type': 'Incident', 'label': inc_id, 'priority': row.get('priority', 'Unknown')} if pd.notna(row['cmdb_ci']) and row['cmdb_ci'] != 'Unknown': edges.append((inc_id, row['cmdb_ci'], 'AFFECTS')) if pd.notna(row['assigned_to']): assignee = row['assigned_to'] if assignee not in nodes: nodes[assignee] = {'type': 'Person', 'label': assignee} edges.append((inc_id, assignee, 'ASSIGNED_TO')) # Add change nodes and edges for _, row in cr.iterrows(): cr_id = row['number'] nodes[cr_id] = {'type': 'Change', 'label': cr_id, 'category': row.get('Category', 'Unknown')} if pd.notna(row['cmdb_ci']) and row['cmdb_ci'] != 'Unknown': edges.append((cr_id, row['cmdb_ci'], 'MODIFIES')) if pd.notna(row['assigned_to']): assignee = row['assigned_to'] if assignee not in nodes: nodes[assignee] = {'type': 'Person', 'label': assignee} edges.append((cr_id, assignee, 'ASSIGNED_TO')) # Add SR nodes and edges for _, row in sr.iterrows(): sr_id = row['number'] nodes[sr_id] = {'type': 'ServiceRequest', 'label': sr_id} if pd.notna(row['cmdb_ci']) and row['cmdb_ci'] != 'Unknown': edges.append((sr_id, row['cmdb_ci'], 'REQUESTS')) if pd.notna(row['parent']): parent = row['parent'] if parent not in nodes: nodes[parent] = {'type': 'RITM', 'label': parent} edges.append((sr_id, parent, 'CHILD_OF')) if pd.notna(row['request']): req = row['request'] if req not in nodes: nodes[req] = {'type': 'REQ', 'label': req} edges.append((sr_id, req, 'PART_OF')) print(f" Total nodes: {len(nodes)}") print(f" Total edges: {len(edges)}") node_types = pd.Series([n['type'] for n in nodes.values()]).value_counts() print(f" Node types: {node_types.to_dict()}") edge_types = pd.Series([e[2] for e in edges]).value_counts() print(f" Edge types: {edge_types.to_dict()}") # Plot: Node type distribution node_types_df = node_types.reset_index() node_types_df.columns = ['node_type', 'count'] fig_nt = px.pie(node_types_df, names='node_type', values='count', title='Knowledge Graph Node Type Distribution') fig_nt.update_traces(textinfo='percent+label') fig_nt.update_layout(template='plotly_white') fig_nt.write_html("assets/images/html/kg_node_types.html") fig_nt.write_image("assets/images/png/kg_node_types.png", width=900, height=600, scale=2) # Plot: Edge type distribution edge_types_df = edge_types.reset_index() edge_types_df.columns = ['edge_type', 'count'] fig_et = px.bar(edge_types_df, x='edge_type', y='count', title='Knowledge Graph Edge Type Distribution', labels={'edge_type': 'Edge Type', 'count': 'Count'}, color='count', color_continuous_scale='Blues') fig_et.update_layout(template='plotly_white', xaxis_tickangle=45) fig_et.write_html("assets/images/html/kg_edge_types.html") fig_et.write_image("assets/images/png/kg_edge_types.png", width=1000, height=500, scale=2) # ============================================================ # 2. SEMANTIC CMDB SCHEMA EXTRACTION # ============================================================ print("\n--- 2. Semantic CMDB Schema Extraction ---") ontology = defaultdict(lambda: {'systems': set(), 'components': set(), 'environments': set(), 'ids': set()}) for ci in ci_nodes: if pd.isna(ci) or ci == 'Unknown': continue parts = str(ci).split('-') if len(parts) >= 5: system = parts[0] component = parts[2] env = parts[3] cid = parts[4] ontology['all']['systems'].add(system) ontology['all']['components'].add(component) ontology['all']['environments'].add(env) ontology['all']['ids'].add(cid) ontology[system]['components'].add(component) ontology[system]['environments'].add(env) print(f" Systems: {len(ontology['all']['systems'])}") print(f" Components: {len(ontology['all']['components'])}") print(f" Environments: {len(ontology['all']['environments'])}") system_component_counts = {} for system in list(ontology['all']['systems'])[:15]: system_component_counts[system] = len(ontology[system]['components']) sc_df = pd.DataFrame(list(system_component_counts.items()), columns=['system', 'component_count']) sc_df = sc_df.sort_values('component_count', ascending=False) fig_ont = px.bar(sc_df, x='system', y='component_count', title='CMDB Ontology: System-to-Component Diversity', labels={'system': 'System', 'component_count': 'Unique Components'}, color='component_count', color_continuous_scale='Viridis') fig_ont.update_layout(template='plotly_white', xaxis_tickangle=45) fig_ont.write_html("assets/images/html/kg_cmdb_ontology.html") fig_ont.write_image("assets/images/png/kg_cmdb_ontology.png", width=1200, height=600, scale=2) env_dist = pd.Series(list(ontology['all']['environments'])).value_counts().reset_index() env_dist.columns = ['environment', 'count'] fig_env = px.bar(env_dist, x='environment', y='count', title='CMDB Environment Class Distribution', labels={'environment': 'Environment', 'count': 'CI Count'}, color='count', color_continuous_scale='Plasma') fig_env.update_layout(template='plotly_white') fig_env.write_html("assets/images/html/kg_environment_distribution.html") fig_env.write_image("assets/images/png/kg_environment_distribution.png", width=900, height=500, scale=2) # ============================================================ # 3. GRAPH-AUGMENTED ROOT CAUSE PROPAGATION # ============================================================ print("\n--- 3. Root Cause Propagation Analysis ---") propagation = [] for _, change in cr.iterrows(): if pd.isna(change['work_end']) or pd.isna(change['cmdb_ci']): continue window_end = change['work_end'] + pd.Timedelta(hours=72) mask = ( (inc['cmdb_ci'] == change['cmdb_ci']) & (inc['opened_at'] >= change['work_end']) & (inc['opened_at'] <= window_end) ) related_inc = inc[mask] if len(related_inc) > 0: propagation.append({ 'change': change['number'], 'change_cat': change['Category'], 'ci': change['cmdb_ci'], 'incidents': len(related_inc), 'inc_cats': ';'.join(related_inc['Category'].astype(str).unique()), 'hours_to_first': (related_inc['opened_at'].min() - change['work_end']).total_seconds() / 3600 }) prop_df = pd.DataFrame(propagation) print(f" Changes with related incidents (72hr): {len(prop_df)}") if len(prop_df) > 0: print(f" Avg hours to first incident: {prop_df['hours_to_first'].mean():.1f}h") fig_prop = px.histogram(prop_df, x='hours_to_first', nbins=30, title='Root Cause Propagation: Hours from Change to First Incident', labels={'hours_to_first': 'Hours to First Incident', 'count': 'Change Count'}, color_discrete_sequence=['crimson']) fig_prop.update_layout(template='plotly_white') fig_prop.write_html("assets/images/html/kg_propagation_timeline.html") fig_prop.write_image("assets/images/png/kg_propagation_timeline.png", width=900, height=500, scale=2) prop_cat = prop_df.groupby('change_cat').agg({ 'change': 'count', 'incidents': 'sum', 'hours_to_first': 'mean' }).sort_values('change', ascending=False).head(10) print(f" Top propagating categories:\n{prop_cat}") fig_propcat = px.bar(prop_cat.reset_index(), x='change_cat', y='change', title='Changes Causing Incidents by Category', labels={'change_cat': 'Change Category', 'change': 'Change Count'}, color='change', color_continuous_scale='Reds') fig_propcat.update_layout(template='plotly_white', xaxis_tickangle=45) fig_propcat.write_html("assets/images/html/kg_propagation_by_category.html") fig_propcat.write_image("assets/images/png/kg_propagation_by_category.png", width=1100, height=600, scale=2) # ============================================================ # 4. DUPLICATE AND ORPHANED TICKET DETECTION # ============================================================ print("\n--- 4. Duplicate & Orphaned Ticket Detection ---") dup_phrases = ['duplicate', 'same as', 'dup ', 'already created', 'same issue'] inc['is_duplicate'] = inc['close_notes'].str.lower().str.contains('|'.join(dup_phrases), na=False) dup_count = inc['is_duplicate'].sum() print(f" Duplicate-flagged incidents: {dup_count} ({dup_count/len(inc)*100:.1f}%)") sr['has_parent'] = sr['parent'].notna() & (sr['parent'] != '') orphaned_sr = len(sr[~sr['has_parent']]) print(f" Orphaned SRs (no parent RITM): {orphaned_sr} ({orphaned_sr/len(sr)*100:.1f}%)") orphaned_inc = len(inc[inc['cmdb_ci'].isna() | (inc['cmdb_ci'] == 'Unknown')]) print(f" Orphaned incidents (no CI): {orphaned_inc} ({orphaned_inc/len(inc)*100:.1f}%)") sr_graph = defaultdict(list) for _, row in sr.iterrows(): if pd.notna(row['parent']) and row['parent'] != '': sr_graph[row['parent']].append(row['number']) components = [] visited = set() def dfs(node, component): if node in visited: return visited.add(node) component.append(node) for child in sr_graph.get(node, []): dfs(child, component) for node in list(sr_graph.keys()): if node not in visited: comp = [] dfs(node, comp) if len(comp) > 1: components.append(comp) component_sizes = [len(c) for c in components] print(f" Connected SR components: {len(components)}") if component_sizes: print(f" Component size stats: min={min(component_sizes)}, max={max(component_sizes)}, avg={np.mean(component_sizes):.1f}") fig_dup = go.Figure() fig_dup.add_trace(go.Bar(name='Duplicates', x=['Incidents'], y=[dup_count])) fig_dup.add_trace(go.Bar(name='Orphaned SRs', x=['Service Requests'], y=[orphaned_sr])) fig_dup.add_trace(go.Bar(name='Orphaned Incidents', x=['Incidents'], y=[orphaned_inc])) fig_dup.update_layout(title='Duplicate & Orphaned Ticket Detection', template='plotly_white', barmode='group') fig_dup.write_html("assets/images/html/kg_duplicate_orphan_detection.html") fig_dup.write_image("assets/images/png/kg_duplicate_orphan_detection.png", width=900, height=500, scale=2) if component_sizes: fig_comp = px.histogram(x=component_sizes, nbins=20, title='SR Hierarchy Connected Component Size Distribution', labels={'x': 'Component Size (tasks)', 'count': 'Frequency'}, color_discrete_sequence=['teal']) fig_comp.update_layout(template='plotly_white') fig_comp.write_html("assets/images/html/kg_component_sizes.html") fig_comp.write_image("assets/images/png/kg_component_sizes.png", width=900, height=500, scale=2) kg_results = { 'total_nodes': len(nodes), 'total_edges': len(edges), 'unique_systems': len(ontology['all']['systems']), 'unique_components': len(ontology['all']['components']), 'propagation_events': len(prop_df) if 'prop_df' in locals() else 0, 'duplicate_incidents': int(dup_count), 'orphaned_srs': int(orphaned_sr), 'orphaned_incs': int(orphaned_inc), 'connected_components': len(components), } pd.Series(kg_results).to_json("temp_files/kg_results.json") print("\nPhase 3E complete. All knowledge graph plots saved.")