Ihr Daten-Template für den Kundenservice

// This configuration outlines how to build a series of reports in the ServiceNow UI.
// Each block represents a separate report that must be created and exported.
// The final event log is created by combining the outputs of these reports.

// Activity: Service Request Created
// Source Table: sn_customerservice_case
// Report Filter Conditions:
// 1. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: number
// - ActivityName: "Service Request Created" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: assigned_to
// - AssignmentGroup: assignment_group
// - Priority: priority
// - State: state
// - Category: category
// - IsSlaBreached: made_sla

// Activity: Request Categorized and Prioritized
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name [is one of] category, priority
// 3. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Request Categorized and Prioritized" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: documentkey.state
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Request Assigned to Agent
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = assigned_to
// 3. New value [is not empty]
// 4. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Request Assigned to Agent" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: newvalue (This field contains the sys_id, so select documentkey.assigned_to to get the name)
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: documentkey.state
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Assignment Group Changed
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = assignment_group
// 3. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Assignment Group Changed" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: newvalue (This field contains the sys_id, so select documentkey.assignment_group to get the name)
// - Priority: documentkey.priority
// - State: documentkey.state
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Agent Starts Investigation
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = state
// 3. New value = [Your 'Work in Progress' State Value]
// 4. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Agent Starts Investigation" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: newvalue
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Information Requested from Customer
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = state
// 3. New value = [Your 'Awaiting Info' State Value]
// 4. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Information Requested from Customer" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: newvalue
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Information Received from Customer
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = state
// 3. Old value = [Your 'Awaiting Info' State Value]
// 4. New value [is not] [Your 'Awaiting Info' State Value]
// 5. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Information Received from Customer" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: newvalue
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Internal Escalation Triggered
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = escalation
// 3. New value [is not] [Your 'Normal' Escalation Value]
// 4. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Internal Escalation Triggered" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: documentkey.state
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Solution Proposed
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = state
// 3. New value = [Your 'Awaiting Acceptance' State Value]
// 4. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Solution Proposed" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: newvalue
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Service Request Reopened
// Source Table: sys_audit
// Report Filter Conditions:
// 1. Table name = sn_customerservice_case
// 2. Field name = state
// 3. Old value [is one of] [Your 'Resolved' State Value], [Your 'Closed' State Value]
// 4. New value [is not one of] [Your 'Resolved' State Value], [Your 'Closed' State Value]
// 5. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: documentkey.number
// - ActivityName: "Service Request Reopened" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: documentkey.assigned_to
// - AssignmentGroup: documentkey.assignment_group
// - Priority: documentkey.priority
// - State: newvalue
// - Category: documentkey.category
// - IsSlaBreached: documentkey.made_sla

// Activity: Service Request Resolved
// Source Table: sn_customerservice_case
// Report Filter Conditions:
// 1. Resolved [is not empty]
// 2. Resolved [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: number
// - ActivityName: "Service Request Resolved" (Use a formula or add manually after export)
// - EventTime: resolved_at
// - AssignedAgent: assigned_to
// - AssignmentGroup: assignment_group
// - Priority: priority
// - State: state
// - Category: category
// - IsSlaBreached: made_sla

// Activity: SLA Breached
// Source Table: task_sla
// Report Filter Conditions:
// 1. Has breached = true
// 2. Task.Task type = Case (or sn_customerservice_case)
// 3. Planned end time [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: task.number
// - ActivityName: "SLA Breached" (Use a formula or add manually after export)
// - EventTime: planned_end_time
// - AssignedAgent: task.assigned_to
// - AssignmentGroup: task.assignment_group
// - Priority: task.priority
// - State: task.state
// - Category: task.category
// - IsSlaBreached: has_breached

// Activity: Service Request Closed
// Source Table: sn_customerservice_case
// Report Filter Conditions:
// 1. Closed [is not empty]
// 2. Closed [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: number
// - ActivityName: "Service Request Closed" (Use a formula or add manually after export)
// - EventTime: closed_at
// - AssignedAgent: assigned_to
// - AssignmentGroup: assignment_group
// - Priority: priority
// - State: state
// - Category: category
// - IsSlaBreached: made_sla

// Activity: Customer Survey Sent
// Source Table: asmt_assessment_instance
// Report Filter Conditions:
// 1. Trigger table = sn_customerservice_case
// 2. Created [on] [Your Desired Date Range]
// Columns to Export:
// - ServiceRequest: trigger_id.number
// - ActivityName: "Customer Survey Sent" (Use a formula or add manually after export)
// - EventTime: sys_created_on
// - AssignedAgent: trigger_id.assigned_to
// - AssignmentGroup: trigger_id.assignment_group
// - Priority: trigger_id.priority
// - State: trigger_id.state
// - Category: trigger_id.category
// - IsSlaBreached: trigger_id.made_sla

/*
 This is a conceptual JavaScript script for a Node.js environment.
 It demonstrates the logic for making a series of API calls to ServiceNow 
 to build a complete event log. Dependencies like 'axios' for HTTP requests
 and 'papaparse' for CSV conversion would be required.
*/

const axios = require('axios');

// --- Configuration ---
const INSTANCE_URL = 'https://[your-instance].service-now.com';
const USERNAME = '[your_username]';
const PASSWORD = '[your_password]';
const START_DATE = '2023-01-01 00:00:00'; // Inclusive
const END_DATE = '2023-06-30 23:59:59';   // Inclusive

const auth = { username: USERNAME, password: PASSWORD };
const eventLog = [];

// Helper function for paginated GET requests
async function queryTable(tableName, params) {
    let records = [];
    let offset = 0;
    const limit = 1000;
    let hasMore = true;

    while (hasMore) {
        try {
            const response = await axios.get(`${INSTANCE_URL}/api/now/table/${tableName}`, {
                auth,
                params: { ...params, sysparm_limit: limit, sysparm_offset: offset }
            });
            const newRecords = response.data.result;
            if (newRecords.length > 0) {
                records = records.concat(newRecords);
                offset += newRecords.length;
            } else {
                hasMore = false;
            }
        } catch (error) {
            console.error(`Error querying ${tableName}:`, error.message);
            hasMore = false;
        }
    }
    return records;
}

async function extractServiceRequestEvents() {
    console.log('Starting extraction...');

    // 1. Get all relevant cases within the date range
    const cases = await queryTable('sn_customerservice_case', {
        sysparm_query: `sys_created_on>=${START_DATE}^sys_created_on<=${END_DATE}`,
        sysparm_fields: 'sys_id,number,sys_created_on,resolved_at,closed_at,assigned_to,assignment_group,priority,state,category'
    });
    const caseSysIds = cases.map(c => c.sys_id);

    console.log(`Found ${cases.length} cases to process.`);

    // 2. Extract activities for each case
    for (const aCase of cases) {
        // Activity: Service Request Created
        eventLog.push({
            ServiceRequest: aCase.number,
            ActivityName: 'Service Request Created',
            EventTime: aCase.sys_created_on,
            AssignedAgent: aCase.assigned_to.display_value || null,
            AssignmentGroup: aCase.assignment_group.display_value || null,
            Priority: aCase.priority,
            State: aCase.state,
            Category: aCase.category,
            IsSlaBreached: false // Default, will be updated later if a breach is found
        });

        // Activity: Service Request Resolved
        if (aCase.resolved_at) {
            eventLog.push({ ...eventLog[eventLog.length - 1], ActivityName: 'Service Request Resolved', EventTime: aCase.resolved_at });
        }

        // Activity: Service Request Closed
        if (aCase.closed_at) {
            eventLog.push({ ...eventLog[eventLog.length - 1], ActivityName: 'Service Request Closed', EventTime: aCase.closed_at });
        }
    }

    // 3. Query audit table for change-based events
    if (caseSysIds.length > 0) {
        const auditRecords = await queryTable('sys_audit', {
            sysparm_query: `documentkeyIN${caseSysIds.join(',')}^tablename=sn_customerservice_case`,
            sysparm_fields: 'documentkey,sys_created_on,fieldname,oldvalue,newvalue'
        });
        
        const caseMap = cases.reduce((map, obj) => (map[obj.sys_id] = obj, map), {});

        for (const audit of auditRecords) {
            const relatedCase = caseMap[audit.documentkey];
            if (!relatedCase) continue;

            let activity = null;
            // Request Categorized and Prioritized
            if (['category', 'subcategory', 'priority'].includes(audit.fieldname) && audit.newvalue) {
                activity = { ActivityName: 'Request Categorized and Prioritized' };
            }
            // Request Assigned to Agent
            else if (audit.fieldname === 'assigned_to' && audit.newvalue) {
                activity = { ActivityName: 'Request Assigned to Agent' };
            }
            // Assignment Group Changed
            else if (audit.fieldname === 'assignment_group' && audit.newvalue) {
                activity = { ActivityName: 'Assignment Group Changed' };
            }
            // State changes
            else if (audit.fieldname === 'state') {
                if (['2', '-5'].includes(audit.oldvalue) && audit.newvalue === '6') { // New/Assigned -> Work in Progress
                     activity = { ActivityName: 'Agent Starts Investigation' };
                }
                else if (audit.newvalue === '3') { // -> Awaiting User Info
                    activity = { ActivityName: 'Information Requested from Customer' };
                }
                else if (audit.oldvalue === '3' && audit.newvalue !== '3') { // Awaiting User Info -> Other
                    activity = { ActivityName: 'Information Received from Customer' };
                }
                else if (audit.oldvalue === '7' && audit.newvalue === '6') { // Resolved -> Work in Progress
                    activity = { ActivityName: 'Service Request Reopened' };
                }
                else if (audit.newvalue === '5') { // -> Awaiting Acceptance (example for Solution Proposed)
                    activity = { ActivityName: 'Solution Proposed' };
                }
            }
            // Internal Escalation (example: based on priority increase)
            else if (audit.fieldname === 'priority' && audit.newvalue < audit.oldvalue) {
                 activity = { ActivityName: 'Internal Escalation Triggered' };
            }

            if (activity) {
                eventLog.push({ 
                    ServiceRequest: relatedCase.number,
                    ActivityName: activity.ActivityName, 
                    EventTime: audit.sys_created_on,
                    AssignedAgent: relatedCase.assigned_to.display_value || null,
                    AssignmentGroup: relatedCase.assignment_group.display_value || null,
                    Priority: relatedCase.priority,
                    State: relatedCase.state,
                    Category: relatedCase.category,
                    IsSlaBreached: false
                });
            }
        }
    }

    // 4. Query for SLA Breaches
    if (caseSysIds.length > 0) {
        const slaRecords = await queryTable('task_sla', {
            sysparm_query: `taskIN${caseSysIds.join(',')}^has_breached=true`,
            sysparm_fields: 'task,planned_end_time'
        });

        for (const sla of slaRecords) {
            const relatedCase = cases.find(c => c.sys_id === sla.task.value);
            if(relatedCase) {
                eventLog.push({ 
                    ServiceRequest: relatedCase.number,
                    ActivityName: 'SLA Breached',
                    EventTime: sla.planned_end_time,
                    AssignedAgent: relatedCase.assigned_to.display_value || null,
                    AssignmentGroup: relatedCase.assignment_group.display_value || null,
                    Priority: relatedCase.priority,
                    State: relatedCase.state,
                    Category: relatedCase.category,
                    IsSlaBreached: true
                });
            }
        }
    }

    // 5. Query for Customer Surveys Sent
    if (caseSysIds.length > 0) {
        const surveyRecords = await queryTable('asmt_assessment_instance', {
             sysparm_query: `trigger_idIN${caseSysIds.join(',')}`,
             sysparm_fields: 'trigger_id,sys_created_on'
        });

        for (const survey of surveyRecords) {
             const relatedCase = cases.find(c => c.sys_id === survey.trigger_id.value);
             if(relatedCase) {
                eventLog.push({ 
                    ServiceRequest: relatedCase.number,
                    ActivityName: 'Customer Survey Sent',
                    EventTime: survey.sys_created_on,
                    AssignedAgent: relatedCase.assigned_to.display_value || null,
                    AssignmentGroup: relatedCase.assignment_group.display_value || null,
                    Priority: relatedCase.priority,
                    State: relatedCase.state,
                    Category: relatedCase.category,
                    IsSlaBreached: false
                });
             }
        }
    }

    console.log(`Total events extracted: ${eventLog.length}`);
    // At this point, the 'eventLog' array can be sorted by time and written to a CSV file.
    // Example: console.log(JSON.stringify(eventLog, null, 2));
}

extractServiceRequestEvents();

import requests
import pandas as pd
import getpass
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your ServiceNow instance URL, e.g., class="hl-string">"https://your-instance.service-now.com"
INSTANCE = class="hl-string">"[Your ServiceNow Instance URL]"
class=class="hl-string">"hl-comment"># ServiceNow username. Leave PASSWORD empty to be prompted securely.
USER = class="hl-string">"[Your ServiceNow Username]"
PASSWORD = class="hl-string">""

class=class="hl-string">"hl-comment"># Date range for the extraction (YYYY-MM-DD)
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-06-30"

class=class="hl-string">"hl-comment"># API pagination size
PAGE_SIZE = 1000

class=class="hl-string">"hl-comment"># --- Helper Functions ---
def get_credentials():
    class="hl-string">"""Get password securely if not provided."""
    if not PASSWORD:
        return USER, getpass.getpass(fclass="hl-string">"Enter password for user '{USER}': ")
    return USER, PASSWORD

def fetch_all_records(table, query, fields):
    class="hl-string">"""Fetch all records from a table with pagination."""
    user, password = get_credentials()
    records = []
    offset = 0
    while True:
        url = fclass="hl-string">"{INSTANCE}/api/now/table/{table}"
        headers = {class="hl-string">"Accept": class="hl-string">"application/json"}
        params = {
            class="hl-string">'sysparm_query': query,
            class="hl-string">'sysparm_fields': class="hl-string">','.join(fields),
            class="hl-string">'sysparm_limit': PAGE_SIZE,
            class="hl-string">'sysparm_offset': offset
        }
        
        print(fclass="hl-string">"Fetching {table} records... Offset: {offset}")
        response = requests.get(url, auth=(user, password), headers=headers, params=params)
        response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
        data = response.json().get(class="hl-string">'result', [])
        
        if not data:
            break
        
        records.extend(data)
        offset += PAGE_SIZE
        
    print(fclass="hl-string">"Fetched a total of {len(records)} records from {table}.")
    return records

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    class=class="hl-string">"hl-comment"># 1. Fetch Cases
    case_query = fclass="hl-string">"sys_created_on>={START_DATE} 00:00:00^sys_created_on<={END_DATE} 23:59:59"
    case_fields = [
        class="hl-string">'sys_id', class="hl-string">'number', class="hl-string">'sys_created_on', class="hl-string">'resolved_at', class="hl-string">'closed_at', class="hl-string">'assigned_to',
        class="hl-string">'assignment_group', class="hl-string">'priority', class="hl-string">'state', class="hl-string">'category'
    ]
    cases = fetch_all_records(class="hl-string">'sn_customerservice_case', case_query, case_fields)
    if not cases:
        print(class="hl-string">"No cases found for the given date range.")
        return
    
    cases_df = pd.DataFrame(cases)
    case_sys_ids = cases_df[class="hl-string">'sys_id'].tolist()

    class=class="hl-string">"hl-comment"># 2. Fetch related data in bulk
    class=class="hl-string">"hl-comment"># Fetch Audit records
    audit_query = fclass="hl-string">"documentkeyIN{','.join(case_sys_ids)}"
    audit_fields = [class="hl-string">'documentkey', class="hl-string">'sys_created_on', class="hl-string">'fieldname', class="hl-string">'newvalue', class="hl-string">'oldvalue']
    audit_records = fetch_all_records(class="hl-string">'sys_audit', audit_query, audit_fields)
    audit_df = pd.DataFrame(audit_records)
    
    class=class="hl-string">"hl-comment"># Fetch SLA records
    sla_query = fclass="hl-string">"taskIN{','.join(case_sys_ids)}"
    sla_fields = [class="hl-string">'task', class="hl-string">'planned_end_time', class="hl-string">'has_breached']
    sla_records = fetch_all_records(class="hl-string">'task_sla', sla_query, sla_fields)
    sla_df = pd.DataFrame(sla_records)

    class=class="hl-string">"hl-comment"># Fetch Survey records
    survey_query = fclass="hl-string">"trigger_idIN{','.join(case_sys_ids)}"
    survey_fields = [class="hl-string">'trigger_id', class="hl-string">'sys_created_on']
    survey_records = fetch_all_records(class="hl-string">'asmt_assessment_instance', survey_query, survey_fields)
    survey_df = pd.DataFrame(survey_records)

    class=class="hl-string">"hl-comment"># 3. Create helper mappings for sys_ids to names
    user_sys_ids = cases_df[class="hl-string">'assigned_to'].dropna().apply(lambda x: x[class="hl-string">'value']).unique().tolist()
    group_sys_ids = cases_df[class="hl-string">'assignment_group'].dropna().apply(lambda x: x[class="hl-string">'value']).unique().tolist()

    user_map = {u[class="hl-string">'sys_id']: u[class="hl-string">'name'] for u in fetch_all_records(class="hl-string">'sys_user', fclass="hl-string">"sys_idIN{','.join(user_sys_ids)}", [class="hl-string">'sys_id', class="hl-string">'name'])} if user_sys_ids else {}
    group_map = {g[class="hl-string">'sys_id']: g[class="hl-string">'name'] for g in fetch_all_records(class="hl-string">'sys_user_group', fclass="hl-string">"sys_idIN{','.join(group_sys_ids)}", [class="hl-string">'sys_id', class="hl-string">'name'])} if group_sys_ids else {}

    class=class="hl-string">"hl-comment"># 4. Generate Event Log
    event_log_records = []
    for _, case in cases_df.iterrows():
        case_id = case[class="hl-string">'number']
        case_sys_id = case[class="hl-string">'sys_id']

        class=class="hl-string">"hl-comment"># Get current attributes
        assigned_agent = user_map.get(case.get(class="hl-string">'assigned_to', {}).get(class="hl-string">'value'))
        assignment_group = group_map.get(case.get(class="hl-string">'assignment_group', {}).get(class="hl-string">'value'))
        priority = case.get(class="hl-string">'priority')
        state = case.get(class="hl-string">'state')
        category = case.get(class="hl-string">'category')

        base_event = {
            class="hl-string">'ServiceRequest': case_id, class="hl-string">'AssignedAgent': assigned_agent,
            class="hl-string">'AssignmentGroup': assignment_group, class="hl-string">'Priority': priority,
            class="hl-string">'State': state, class="hl-string">'Category': category, class="hl-string">'IsSlaBreached': False
        }

        class=class="hl-string">"hl-comment"># Event: Service Request Created
        event_log_records.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Service Request Created', class="hl-string">'EventTime': case[class="hl-string">'sys_created_on']})

        class=class="hl-string">"hl-comment"># Filter related data for this case
        case_audit_df = audit_df[audit_df[class="hl-string">'documentkey'] == case_sys_id].sort_values(by=class="hl-string">'sys_created_on')
        case_sla_df = sla_df[sla_df[class="hl-string">'task'].apply(lambda x: x.get(class="hl-string">'value') == case_sys_id)]
        case_survey_df = survey_df[survey_df[class="hl-string">'trigger_id'] == case_sys_id]

        class=class="hl-string">"hl-comment"># Process Audit Log for events
        categorized_flag = False
        for _, audit_entry in case_audit_df.iterrows():
            event_time = audit_entry[class="hl-string">'sys_created_on']
            field = audit_entry[class="hl-string">'fieldname']
            new_val = audit_entry[class="hl-string">'newvalue']
            activity = None

            class=class="hl-string">"hl-comment"># Map state changes to activities (adjust state values for your instance)
            if field == class="hl-string">'state':
                if new_val == class="hl-string">'6': activity = class="hl-string">'Agent Starts Investigation' class=class="hl-string">"hl-comment"># Work in Progress
                elif new_val == class="hl-string">'3': activity = class="hl-string">'Information Requested from Customer' class=class="hl-string">"hl-comment"># Awaiting User Info
                elif audit_entry[class="hl-string">'oldvalue'] == class="hl-string">'3': activity = class="hl-string">'Information Received from Customer'
                elif new_val == class="hl-string">'-5': activity = class="hl-string">'Solution Proposed' class=class="hl-string">"hl-comment"># Awaiting Acceptance
                elif audit_entry[class="hl-string">'oldvalue'] == class="hl-string">'10' and new_val == class="hl-string">'6': activity = class="hl-string">'Service Request Reopened' class=class="hl-string">"hl-comment"># Resolved -> Work in Progress
            
            class=class="hl-string">"hl-comment"># Other field changes
            elif field == class="hl-string">'assignment_group': activity = class="hl-string">'Assignment Group Changed'
            elif field == class="hl-string">'assigned_to': activity = class="hl-string">'Request Assigned to Agent'
            elif field in [class="hl-string">'category', class="hl-string">'subcategory', class="hl-string">'priority'] and not categorized_flag:
                activity = class="hl-string">'Request Categorized and Prioritized'
                categorized_flag = True class=class="hl-string">"hl-comment"># Create this event only once
            
            class=class="hl-string">"hl-comment"># Handle Escalation (example: assignment to a tier 2 group)
            if field == class="hl-string">'assignment_group' and class="hl-string">'Tier 2' in group_map.get(new_val, class="hl-string">''):
                activity = class="hl-string">'Internal Escalation Triggered'
            
            if activity:
                event_log_records.append({**base_event, class="hl-string">'ActivityName': activity, class="hl-string">'EventTime': event_time})
        
        class=class="hl-string">"hl-comment"># Event: Service Request Resolved
        if pd.notna(case[class="hl-string">'resolved_at']):
            event_log_records.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Service Request Resolved', class="hl-string">'EventTime': case[class="hl-string">'resolved_at']})

        class=class="hl-string">"hl-comment"># Event: SLA Breached
        for _, sla in case_sla_df.iterrows():
            if sla.get(class="hl-string">'has_breached') == class="hl-string">'true':
                breached_event = {**base_event, class="hl-string">'ActivityName': class="hl-string">'SLA Breached', class="hl-string">'EventTime': sla[class="hl-string">'planned_end_time'], class="hl-string">'IsSlaBreached': True}
                event_log_records.append(breached_event)
                class=class="hl-string">"hl-comment"># Update subsequent events for this case to reflect the breach
                for rec in event_log_records:
                    if rec[class="hl-string">'ServiceRequest'] == case_id:
                        rec[class="hl-string">'IsSlaBreached'] = True
                break class=class="hl-string">"hl-comment"># Only record first breach

        class=class="hl-string">"hl-comment"># Event: Customer Survey Sent
        if not case_survey_df.empty:
            survey_time = case_survey_df.iloc[0][class="hl-string">'sys_created_on']
            event_log_records.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Customer Survey Sent', class="hl-string">'EventTime': survey_time})

        class=class="hl-string">"hl-comment"># Event: Service Request Closed
        if pd.notna(case[class="hl-string">'closed_at']):
            event_log_records.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Service Request Closed', class="hl-string">'EventTime': case[class="hl-string">'closed_at']})

    class=class="hl-string">"hl-comment"># 5. Create and Save Final DataFrame
    if not event_log_records:
        print(class="hl-string">"No events were generated.")
        return
        
    final_df = pd.DataFrame(event_log_records)
    final_df[class="hl-string">'EventTime'] = pd.to_datetime(final_df[class="hl-string">'EventTime'])
    final_df = final_df.sort_values(by=[class="hl-string">'ServiceRequest', class="hl-string">'EventTime'], ascending=[True, True])
    
    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'ServiceRequest', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'AssignedAgent', 
        class="hl-string">'AssignmentGroup', class="hl-string">'Priority', class="hl-string">'State', class="hl-string">'Category', class="hl-string">'IsSlaBreached'
    ]
    final_df = final_df[column_order]
    
    final_df.to_csv(class="hl-string">'servicenow_csm_event_log.csv', index=False)
    print(class="hl-string">"\nExtraction complete. Event log saved to 'servicenow_csm_event_log.csv'.")

if __name__ == class="hl-string">'__main__':
    main()