""" Phase 3: SLA Performance Analysis MTTR trend analysis by Priority, SLA compliance by Group/Region """ import pandas as pd import numpy as np import plotly.express as px import plotly.graph_objects as go from pathlib import Path from datetime import timedelta import warnings warnings.filterwarnings('ignore') # Set display options pd.set_option('display.max_columns', 50) pd.set_option('display.width', 200) # Define paths OUTPUT_PATH = Path('/app/workspace/temp_files') IMAGES_PATH = Path('/app/workspace/assets/images') # SLA targets (hours) SLA_TARGETS = { '1 - Critical': 4, '2 - High': 12, '3 - Moderate': 24, '4 - Low': 36 } print("=" * 80) print("PHASE 3: SLA PERFORMANCE ANALYSIS") print("=" * 80) # Load cleaned data df = pd.read_csv(OUTPUT_PATH / 'cleaned_incidents_2025.csv') print(f"\nLoaded {len(df):,} records from cleaned dataset") # Convert datetime columns df['Opened'] = pd.to_datetime(df['Opened'], errors='coerce') df['Resolved'] = pd.to_datetime(df['Resolved'], errors='coerce') # ============================================================================= # MTTR TREND ANALYSIS BY PRIORITY # ============================================================================= print("\n--- MTTR TREND ANALYSIS BY PRIORITY ---") # Create monthly aggregations by priority df['YearMonth'] = df['Opened'].dt.to_period('M').astype(str) df['Priority_Order'] = df['Priority'].str.extract(r'(\d)').astype(float) # Monthly MTTR by Priority monthly_priority_mttr = df.groupby(['YearMonth', 'Priority']).agg({ 'MTTR_hours': ['mean', 'median', 'count'] }).reset_index() monthly_priority_mttr.columns = ['YearMonth', 'Priority', 'Avg_MTTR', 'Median_MTTR', 'Count'] # SLA Compliance by Priority monthly_priority_sla = df.groupby(['YearMonth', 'Priority']).apply( lambda x: (x['SLA_Status'] == 'Met').mean() * 100 ).reset_index(name='SLA_Compliance_%') # ============================================================================ # VISUALIZATION 1: MTTR Trend by Priority (Line Chart) # ============================================================================ fig1 = px.line(monthly_priority_mttr, x='YearMonth', y='Avg_MTTR', color='Priority', markers=True, title='Mean Time to Resolution (MTTR) Trend by Priority Level', labels={'YearMonth': 'Month', 'Avg_MTTR': 'Average MTTR (Hours)', 'Priority': 'Priority'}) fig1.update_traces(line_width=3) fig1.add_hline(y=24, line_dash="dash", line_color="orange", annotation_text="P3 SLA Target (24h)") fig1.add_hline(y=12, line_dash="dash", line_color="red", annotation_text="P2 SLA Target (12h)") fig1.update_layout(legend_title_text='Priority Level', xaxis_title='Month (2025)', yaxis_title='Average MTTR (Hours)') fig1.write_html(IMAGES_PATH / 'html/11_mttr_trend_by_priority.html') fig1.write_image(IMAGES_PATH / 'png/11_mttr_trend_by_priority.png', scale=2) print("Saved: 11_mttr_trend_by_priority") # ============================================================================ # VISUALIZATION 2: SLA Compliance Trend by Priority # ============================================================================ fig2 = px.line(monthly_priority_sla, x='YearMonth', y='SLA_Compliance_%', color='Priority', markers=True, title='SLA Compliance Rate Trend by Priority Level', labels={'YearMonth': 'Month', 'SLA_Compliance_%': 'SLA Compliance (%)', 'Priority': 'Priority'}) fig2.update_traces(line_width=3) fig2.add_hline(y=90, line_dash="dash", line_color="green", annotation_text="Target: 90%") fig2.update_layout(legend_title_text='Priority Level', xaxis_title='Month (2025)', yaxis_title='SLA Compliance Rate (%)') fig2.update_yaxes(range=[0, 105]) fig2.write_html(IMAGES_PATH / 'html/12_sla_compliance_trend.html') fig2.write_image(IMAGES_PATH / 'png/12_sla_compliance_trend.png', scale=2) print("Saved: 12_sla_compliance_trend") # ============================================================================ # SLA COMPLIANCE BY ASSIGNMENT GROUP # ============================================================================= print("\n--- SLA COMPLIANCE BY ASSIGNMENT GROUP ---") # Calculate MTTR stats per group group_stats = df.groupby('Assignment_group').agg({ 'MTTR_hours': ['count', 'mean', 'median'] }).round(2) group_stats.columns = ['Incident_Count', 'Avg_MTTR', 'Median_MTTR'] # Calculate SLA compliance separately sla_met = df.groupby('Assignment_group')['SLA_Status'].apply(lambda x: (x == 'Met').sum()) sla_total = df.groupby('Assignment_group')['SLA_Status'].apply(lambda x: x.notna().sum()) # Combine group_sla = group_stats.copy() group_sla['Met'] = sla_met group_sla['Total'] = sla_total group_sla['SLA_Compliance_Rate'] = (group_sla['Met'] / group_sla['Total'] * 100).round(1) group_sla['Breach_Rate'] = 100 - group_sla['SLA_Compliance_Rate'] print(group_sla[['Incident_Count', 'Avg_MTTR', 'SLA_Compliance_Rate']].to_string()) # ============================================================================ # VISUALIZATION 3: SLA Compliance by Assignment Group # ============================================================================ fig3 = px.bar(group_sla.reset_index(), x='Assignment_group', y='SLA_Compliance_Rate', color='SLA_Compliance_Rate', title='SLA Compliance Rate by Assignment Group', labels={'Assignment_group': 'Assignment Group', 'SLA_Compliance_Rate': 'SLA Compliance (%)'}, color_continuous_scale='RdYlGn') fig3.add_hline(y=90, line_dash="dash", line_color="green", annotation_text="90% Target") fig3.update_layout(xaxis_title='Assignment Group', yaxis_title='SLA Compliance Rate (%)', yaxis_range=[0, 100]) fig3.write_html(IMAGES_PATH / 'html/13_sla_by_assignment_group.html') fig3.write_image(IMAGES_PATH / 'png/13_sla_by_assignment_group.png', scale=2) print("Saved: 13_sla_by_assignment_group") # ============================================================================ # VISUALIZATION 4: Assignment Group Performance Comparison (Dual Axis) # ============================================================================ fig4 = go.Figure() fig4.add_trace(go.Bar(x=group_sla.index, y=group_sla['Incident_Count'], name='Incident Volume', marker_color='lightblue', yaxis='y1')) fig4.add_trace(go.Scatter(x=group_sla.index, y=group_sla['Avg_MTTR'], name='Avg MTTR (hours)', line=dict(color='red', width=3), yaxis='y2')) fig4.add_trace(go.Scatter(x=group_sla.index, y=group_sla['SLA_Compliance_Rate'], name='SLA Compliance (%)', line=dict(color='green', width=3, dash='dash'), yaxis='y2')) fig4.update_layout(title='Assignment Group: Workload vs Performance', xaxis_title='Assignment Group', yaxis=dict(title='Incident Volume', side='left'), yaxis2=dict(title='MTTR (hrs) / SLA (%)', side='right', overlaying='y'), legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center')) fig4.write_html(IMAGES_PATH / 'html/14_assignment_group_performance.html') fig4.write_image(IMAGES_PATH / 'png/14_assignment_group_performance.png', scale=2) print("Saved: 14_assignment_group_performance") # ============================================================================ # SLA COMPLIANCE BY REGION # ============================================================================= print("\n--- SLA COMPLIANCE BY REGION ---") region_sla = df.groupby('Region').agg({ 'MTTR_hours': ['count', 'mean', 'median'], 'SLA_Status': lambda x: (x == 'Met').mean() * 100 }).round(2) region_sla.columns = ['Incident_Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_Rate'] region_sla = region_sla.sort_values('SLA_Compliance_Rate', ascending=False) print(region_sla.to_string()) # ============================================================================ # VISUALIZATION 5: SLA Compliance by Region # ============================================================================ fig5 = px.bar(region_sla.reset_index(), x='Region', y='SLA_Compliance_Rate', color='Region', title='SLA Compliance Rate by Region', labels={'Region': 'Region', 'SLA_Compliance_Rate': 'SLA Compliance (%)'}, color_discrete_sequence=px.colors.qualitative.Set2) fig5.add_hline(y=90, line_dash="dash", line_color="green", annotation_text="90% Target") fig5.update_layout(xaxis_title='Region', yaxis_title='SLA Compliance Rate (%)', yaxis_range=[0, 100]) fig5.write_html(IMAGES_PATH / 'html/15_sla_by_region.html') fig5.write_image(IMAGES_PATH / 'png/15_sla_by_region.png', scale=2) print("Saved: 15_sla_by_region") # ============================================================================ # VISUALIZATION 6: MTTR Comparison by Region (Box Plot) # ============================================================================ fig6 = px.box(df, x='Region', y='MTTR_hours', color='Region', title='MTTR Distribution by Region', labels={'Region': 'Region', 'MTTR_hours': 'MTTR (Hours)'}) fig6.update_layout(showlegend=False) fig6.write_html(IMAGES_PATH / 'html/16_mttr_by_region_box.html') fig6.write_image(IMAGES_PATH / 'png/16_mttr_by_region_box.png', scale=2) print("Saved: 16_mttr_by_region_box") # ============================================================================ # MONTHLY SLA BREACH ANALYSIS # ============================================================================= print("\n--- MONTHLY SLA BREACH ANALYSIS ---") monthly_total = df.groupby('YearMonth')['SLA_Status'].apply(lambda x: x.notna().sum()) monthly_met = df.groupby('YearMonth')['SLA_Status'].apply(lambda x: (x == 'Met').sum()) monthly_breach = df.groupby('YearMonth')['SLA_Status'].apply(lambda x: (x == 'Breach').sum()) monthly_sla = pd.DataFrame({ 'Total': monthly_total, 'Met': monthly_met, 'Breach': monthly_breach }) monthly_sla['SLA_Rate'] = (monthly_sla['Met'] / monthly_sla['Total'] * 100).round(1) # Filter to months with complete data monthly_sla = monthly_sla[monthly_sla['Total'] > 100] print(monthly_sla.to_string()) # ============================================================================ # VISUALIZATION 7: Monthly SLA Breach Count & Rate # ============================================================================ fig7 = go.Figure() fig7.add_trace(go.Bar(x=monthly_sla.index, y=monthly_sla['Breach'], name='SLA Breaches', marker_color='#FF6B6B', yaxis='y1')) fig7.add_trace(go.Scatter(x=monthly_sla.index, y=monthly_sla['SLA_Rate'], name='SLA Compliance %', line=dict(color='#4ECDC4', width=3), yaxis='y2')) fig7.update_layout(title='Monthly SLA Breaches vs Compliance Rate', xaxis_title='Month', yaxis=dict(title='Number of Breaches', side='left'), yaxis2=dict(title='SLA Compliance (%)', side='right', overlaying='y', range=[0, 100]), legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center')) fig7.add_hline(y=90, line_dash="dash", line_color="green", annotation_text="90% Target", yref='y2') fig7.write_html(IMAGES_PATH / 'html/17_monthly_sla_breach.html') fig7.write_image(IMAGES_PATH / 'png/17_monthly_sla_breach.png', scale=2) print("Saved: 17_monthly_sla_breach") # ============================================================================ # HIGH PRIORITY (P1+P2) ANALYSIS # ============================================================================= print("\n--- HIGH PRIORITY (P1 + P2) ANALYSIS ---") # Note: Data shows only P2, P3, P4 - no P1 in dataset high_priority = df[df['Priority_Num'] <= 2] print(f"High Priority Incidents: {len(high_priority)}") if len(high_priority) > 0: print(f"High Priority SLA Compliance: {(high_priority['SLA_Status'] == 'Met').mean() * 100:.1f}%") print(f"High Priority Avg MTTR: {high_priority['MTTR_hours'].mean():.2f} hours") # ============================================================================ # ASSIGNMENT GROUP HEATMAP - SLA & MTTR # ============================================================================= print("\n--- ASSIGNMENT GROUP HEATMAP DATA ---") # Create heatmap data: Region x Assignment Group heatmap_data = df.pivot_table( values='MTTR_hours', index='Assignment_group', columns='Region', aggfunc='mean' ).round(1) fig8 = px.imshow(heatmap_data, labels=dict(x="Region", y="Assignment Group", color="MTTR (Hours)"), title='Average MTTR Heatmap: Assignment Group × Region', color_continuous_scale='RdYlGn_r') fig8.write_html(IMAGES_PATH / 'html/18_mttr_heatmap.html') fig8.write_image(IMAGES_PATH / 'png/18_mttr_heatmap.png', scale=2) print("Saved: 18_mttr_heatmap") # ============================================================================ # SUMMARY METRICS # ============================================================================= print("\n" + "=" * 80) print("PHASE 3 COMPLETE: SLA Performance Analysis") print("=" * 80) overall_sla = (df['SLA_Status'] == 'Met').mean() * 100 print(f"\nKey SLA Metrics:") print(f" - Overall SLA Compliance: {overall_sla:.1f}%") print(f" - Best Performing Region: {region_sla['SLA_Compliance_Rate'].idxmax()} ({region_sla['SLA_Compliance_Rate'].max():.1f}%)") print(f" - Worst Performing Region: {region_sla['SLA_Compliance_Rate'].idxmin()} ({region_sla['SLA_Compliance_Rate'].min():.1f}%)") print(f" - Best Performing Group: {group_sla['SLA_Compliance_Rate'].idxmax()} ({group_sla['SLA_Compliance_Rate'].max():.1f}%)") print(f" - Worst Performing Group: {group_sla['SLA_Compliance_Rate'].idxmin()} ({group_sla['SLA_Compliance_Rate'].min():.1f}%)") # Save detailed SLA metrics to CSV sla_summary = pd.DataFrame({ 'Priority': list(SLA_TARGETS.keys()), 'SLA_Target_Hours': list(SLA_TARGETS.values()), 'Incident_Count': [len(df[df['Priority'] == p]) for p in SLA_TARGETS.keys()], 'Avg_MTTR': [df[df['Priority'] == p]['MTTR_hours'].mean() for p in SLA_TARGETS.keys()], 'SLA_Compliance': [(df[df['Priority'] == p]['SLA_Status'] == 'Met').mean() * 100 for p in SLA_TARGETS.keys()] }).round(2) sla_summary.to_csv(OUTPUT_PATH / 'sla_metrics_summary.csv', index=False) print(f"\nSLA summary saved to {OUTPUT_PATH / 'sla_metrics_summary.csv'}")