""" Phase 3D: CMDB-Centric System Reliability & Correlation Subtopics: 1. CI reliability scoring via incident and change density 2. Environment-level vulnerability comparison 3. Regional operational variance analysis 4. Cross-process temporal correlation on shared CI """ import pandas as pd import numpy as np import plotly.express as px import plotly.graph_objects as go from plotly.subplots import make_subplots 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 3D: CMDB-CENTRIC SYSTEM RELIABILITY & CORRELATION") print("=" * 80) # ============================================================ # 1. CI RELIABILITY SCORING # ============================================================ print("\n--- 1. CI Reliability Scoring ---") # Incident density by CI inc_by_ci = inc.groupby('cmdb_ci').agg({ 'number': 'count', 'Category': 'nunique', 'priority': lambda x: (x == 'Priority 2').sum() }).rename(columns={'number': 'incident_count', 'Category': 'unique_categories', 'priority': 'p2_count'}) # Change density by CI cr_by_ci = cr.groupby('cmdb_ci').agg({ 'number': 'count', 'Category': 'nunique' }).rename(columns={'number': 'change_count', 'Category': 'change_categories'}) # Service request density by CI sr_by_ci = sr.groupby('cmdb_ci').agg({ 'number': 'count' }).rename(columns={'number': 'sr_count'}) # Merge all reliability = inc_by_ci.join(cr_by_ci, how='outer').join(sr_by_ci, how='outer').fillna(0) reliability['total_events'] = reliability['incident_count'] + reliability['change_count'] + reliability['sr_count'] # Composite health score (lower is better) for col in ['incident_count', 'change_count', 'sr_count', 'p2_count']: max_val = reliability[col].max() if max_val > 0: reliability[f'{col}_norm'] = reliability[col] / max_val else: reliability[f'{col}_norm'] = 0 # Weighted composite: incidents 50%, changes 30%, SRs 10%, P2s 10% reliability['health_score'] = ( reliability['incident_count_norm'] * 0.5 + reliability['change_count_norm'] * 0.3 + reliability['sr_count_norm'] * 0.1 + reliability['p2_count_norm'] * 0.1 ) reliability['health_score'] = 1 - reliability['health_score'] reliability = reliability.sort_values('health_score') print(f" Total unique CIs: {len(reliability)}") print(f" Top 10 least reliable CIs:") print(reliability.head(10)[['incident_count', 'change_count', 'sr_count', 'health_score']]) # Plot: Top 20 least reliable CIs fig_rel = px.bar( reliability.head(20).reset_index(), x='health_score', y='cmdb_ci', orientation='h', title='Top 20 Least Reliable CIs (Composite Health Score)', labels={'health_score': 'Health Score (0=Unstable, 1=Stable)', 'cmdb_ci': 'CMDB CI'}, color='health_score', color_continuous_scale='RdYlGn' ) fig_rel.update_layout(template='plotly_white', yaxis=dict(categoryorder='total ascending')) fig_rel.write_html("assets/images/html/cmdb_ci_reliability.html") fig_rel.write_image("assets/images/png/cmdb_ci_reliability.png", width=1100, height=800, scale=2) # Plot: Incident vs Change density scatter fig_scatter = px.scatter( reliability.reset_index(), x='incident_count', y='change_count', size='total_events', hover_data=['cmdb_ci', 'health_score'], title='CI Event Density: Incidents vs Changes', labels={'incident_count': 'Incident Count', 'change_count': 'Change Count'}, color='health_score', color_continuous_scale='RdYlGn' ) fig_scatter.update_layout(template='plotly_white') fig_scatter.write_html("assets/images/html/cmdb_incident_vs_change.html") fig_scatter.write_image("assets/images/png/cmdb_incident_vs_change.png", width=1000, height=700, scale=2) # ============================================================ # 2. ENVIRONMENT-LEVEL VULNERABILITY COMPARISON # ============================================================ print("\n--- 2. Environment-Level Vulnerability ---") # Incident metrics by environment env_inc = inc.groupby('ci_env').agg({ 'number': 'count', 'priority': lambda x: (x == 'Priority 2').sum(), 'Category': 'nunique' }).rename(columns={'number': 'incidents', 'priority': 'p2_incidents', 'Category': 'unique_cats'}) # Change metrics by environment env_cr = cr.groupby('ci_env').agg({ 'number': 'count', 'schedule_delta_hours': 'mean' }).rename(columns={'number': 'changes', 'schedule_delta_hours': 'avg_overrun'}) # SR metrics by environment env_sr = sr.groupby('ci_env').agg({ 'number': 'count', 'ola_breached': 'mean' }).rename(columns={'number': 'sr_tasks', 'ola_breached': 'ola_breach_rate'}) env_summary = env_inc.join(env_cr, how='outer').join(env_sr, how='outer').fillna(0) env_summary = env_summary[env_summary.index != 'Unknown'] env_summary = env_summary.sort_values('incidents', ascending=False) print(f" Environment summary:") print(env_summary) # Plot: Environment vulnerability radar env_radar = env_summary.reset_index() fig_env = go.Figure() for env in env_radar['ci_env']: row = env_radar[env_radar['ci_env'] == env].iloc[0] fig_env.add_trace(go.Scatterpolar( r=[row['incidents'], row['changes'], row['sr_tasks'], row['p2_incidents']], theta=['Incidents', 'Changes', 'SR Tasks', 'P2 Incidents'], fill='toself', name=env )) fig_env.update_layout( polar=dict(radialaxis=dict(visible=True)), title='Environment Vulnerability Profile', template='plotly_white' ) fig_env.write_html("assets/images/html/cmdb_env_vulnerability_radar.html") fig_env.write_image("assets/images/png/cmdb_env_vulnerability_radar.png", width=900, height=700, scale=2) # Plot: Category distribution by environment env_cat = inc.groupby(['ci_env', 'Category']).size().unstack(fill_value=0) env_cat = env_cat.loc[env_cat.sum(axis=1).sort_values(ascending=False).head(6).index] env_cat_top = env_cat.div(env_cat.sum(axis=1), axis=0) * 100 env_cat_top = env_cat_top[env_cat_top.sum().sort_values(ascending=False).head(10).index] fig_cat_env = go.Figure() for cat in env_cat_top.columns: fig_cat_env.add_trace(go.Bar(name=cat, x=env_cat_top.index, y=env_cat_top[cat])) fig_cat_env.update_layout( barmode='stack', title='Incident Category Distribution by Environment (%)', xaxis_title='Environment', yaxis_title='Percentage', template='plotly_white' ) fig_cat_env.write_html("assets/images/html/cmdb_category_by_env.html") fig_cat_env.write_image("assets/images/png/cmdb_category_by_env.png", width=1100, height=600, scale=2) # ============================================================ # 3. REGIONAL OPERATIONAL VARIANCE ANALYSIS # ============================================================ print("\n--- 3. Regional Operational Variance ---") # Regional incident analysis reg_inc = inc.groupby('region').agg({ 'number': 'count', 'Category': lambda x: x.mode()[0] if not x.mode().empty else 'Unknown', 'priority': lambda x: (x == 'Priority 2').sum() }).rename(columns={'number': 'incidents', 'Category': 'top_category', 'priority': 'p2_count'}) # Regional change analysis reg_cr = cr.groupby('region').agg({ 'number': 'count', 'schedule_delta_hours': 'mean' }).rename(columns={'number': 'changes', 'schedule_delta_hours': 'avg_overrun'}) reg_summary = reg_inc.join(reg_cr, how='outer').fillna(0) print(reg_summary) # Plot: Regional comparison fig_reg = make_subplots(rows=1, cols=3, subplot_titles=('Incidents', 'Changes', 'Avg Overrun (h)')) for i, col in enumerate(['incidents', 'changes', 'avg_overrun'], 1): fig_reg.add_trace(go.Bar(x=reg_summary.index, y=reg_summary[col], name=col), row=1, col=i) fig_reg.update_layout(title_text='Regional Operational Comparison', template='plotly_white', showlegend=False) fig_reg.write_html("assets/images/html/cmdb_regional_comparison.html") fig_reg.write_image("assets/images/png/cmdb_regional_comparison.png", width=1200, height=500, scale=2) # Regional category heatmap reg_cat = inc[inc['region'] != 'Unknown'].groupby(['region', 'Category']).size().unstack(fill_value=0) reg_cat_pct = reg_cat.div(reg_cat.sum(axis=1), axis=0) * 100 reg_cat_top = reg_cat_pct[reg_cat_pct.sum().sort_values(ascending=False).head(12).index] fig_hm = px.imshow( reg_cat_top, text_auto='.0f', aspect='auto', title='Incident Category Distribution by Region (%)', color_continuous_scale='Blues' ) fig_hm.update_layout(template='plotly_white') fig_hm.write_html("assets/images/html/cmdb_regional_heatmap.html") fig_hm.write_image("assets/images/png/cmdb_regional_heatmap.png", width=1200, height=600, scale=2) # ============================================================ # 4. CROSS-PROCESS TEMPORAL CORRELATION ON SHARED CI # ============================================================ print("\n--- 4. Cross-Process Temporal Correlation ---") # Select top 10 most active CIs for detailed timeline top_cis = reliability.sort_values('total_events', ascending=False).head(10).index.tolist() # Create monthly event counts by CI cross_data = [] # Incidents monthly inc_monthly = inc[inc['cmdb_ci'].isin(top_cis)].groupby(['cmdb_ci', 'Month']).size().reset_index(name='count') inc_monthly['process'] = 'Incident' cross_data.append(inc_monthly) # Changes monthly cr['Month'] = cr['work_start'].dt.to_period('M').astype(str) cr_monthly = cr[cr['cmdb_ci'].isin(top_cis)].groupby(['cmdb_ci', 'Month']).size().reset_index(name='count') cr_monthly['process'] = 'Change' cross_data.append(cr_monthly) # SR monthly sr['Month'] = sr['opened_at'].dt.to_period('M').astype(str) sr_monthly = sr[sr['cmdb_ci'].isin(top_cis)].groupby(['cmdb_ci', 'Month']).size().reset_index(name='count') sr_monthly['process'] = 'Service Request' cross_data.append(sr_monthly) cross_df = pd.concat(cross_data, ignore_index=True) # Plot: Cross-process timeline for top CIs fig_cross = px.bar( cross_df, x='Month', y='count', color='process', facet_col='cmdb_ci', facet_col_wrap=2, title='Cross-Process Event Timeline (Top 10 CIs)', labels={'count': 'Event Count', 'Month': 'Month'}, color_discrete_map={'Incident': 'red', 'Change': 'green', 'Service Request': 'blue'} ) fig_cross.update_layout(template='plotly_white', height=1200) fig_cross.write_html("assets/images/html/cmdb_crossprocess_timeline.html") fig_cross.write_image("assets/images/png/cmdb_crossprocess_timeline.png", width=1400, height=1200, scale=2) # Correlation matrix: incident count vs change count per CI per month corr_data = [] for ci in top_cis[:5]: inc_m = inc[(inc['cmdb_ci'] == ci)].groupby('Month').size().rename('incidents') cr_m = cr[(cr['cmdb_ci'] == ci)].groupby('Month').size().rename('changes') merged = pd.concat([inc_m, cr_m], axis=1).fillna(0) if len(merged) > 2: corr = merged['incidents'].corr(merged['changes']) corr_data.append({'ci': ci, 'inc_change_corr': corr}) corr_df = pd.DataFrame(corr_data) print(f" Incident-Change correlation per CI:") print(corr_df) # Plot correlation if len(corr_df) > 0: fig_corr = px.bar( corr_df, x='ci', y='inc_change_corr', title='Incident-Change Temporal Correlation by CI', labels={'ci': 'CMDB CI', 'inc_change_corr': 'Correlation Coefficient'}, color='inc_change_corr', color_continuous_scale='RdBu_r', range_color=[-1, 1] ) fig_corr.update_layout(template='plotly_white', xaxis_tickangle=45) fig_corr.write_html("assets/images/html/cmdb_incident_change_correlation.html") fig_corr.write_image("assets/images/png/cmdb_incident_change_correlation.png", width=1100, height=500, scale=2) # Save CMDB results cmdb_results = { 'total_cis': len(reliability), 'least_reliable_ci': reliability.index[0] if len(reliability) > 0 else 'N/A', 'most_vulnerable_env': env_summary.index[0] if len(env_summary) > 0 else 'N/A', 'na_incidents': int(reg_summary.loc['NA', 'incidents']) if 'NA' in reg_summary.index else 0, 'emea_incidents': int(reg_summary.loc['EMEA', 'incidents']) if 'EMEA' in reg_summary.index else 0, } pd.Series(cmdb_results).to_json("temp_files/cmdb_results.json") print("\nPhase 3D complete. All CMDB reliability plots saved.")