เทมเพลตข้อมูลการจัดการปัญหา

var writer = sn_export_sets.CSVWriter(output, gs.getCSVHeader());

// Define the headers for the output CSV file
var headers = 'ProblemNumber,Activity,EventTime,SourceSystem,LastDataUpdate,AssignedTo,AssignmentGroup,ProblemState,Priority,RootCauseCategory,ReassignmentCount,RelatedIncidentCount';
writer.writeHeader(headers);

var lastUpdate = new GlideDateTime().getDisplayValue();

// Query the Problem table based on the filter defined in the Export Set
var problemGr = new GlideRecord('problem');
problemGr.addEncodedQuery(source.getEncodedQuery()); // Applies filters from the Export Set
problemGr.query();

while (problemGr.next()) {
    var problemNumber = problemGr.getValue('number');
    var problemSysId = problemGr.getUniqueValue();
    var sourceSystem = 'ServiceNow';

    // Helper function to write a row to the CSV
    function writeRow(activity, eventTime) {
        if (!eventTime || eventTime == '') return;

        var assignedTo = problemGr.getDisplayValue('assigned_to') || '';
        var assignmentGroup = problemGr.getDisplayValue('assignment_group') || '';
        var problemState = problemGr.getDisplayValue('state') || '';
        var priority = problemGr.getDisplayValue('priority') || '';
        var rootCauseCategory = problemGr.getDisplayValue('u_root_cause_category') || ''; // Adjust field name if custom
        var reassignmentCount = problemGr.getValue('reassignment_count') || '0';
        var relatedIncidentCount = problemGr.getValue('related_incidents') || '0';

        var row = [
            problemNumber,
            activity,
            eventTime,
            sourceSystem,
            lastUpdate,
            assignedTo,
            assignmentGroup,
            problemState,
            priority,
            rootCauseCategory,
            reassignmentCount,
            relatedIncidentCount
        ].join(',');
        writer.writeRow(row);
    }

    // 1. Problem Record Created
    writeRow('Problem Record Created', problemGr.getValue('sys_created_on'));

    // Query Metric Instance for events related to this problem
    var metricGr = new GlideRecord('metric_instance');
    metricGr.addQuery('id', problemSysId);
    metricGr.orderBy('sys_created_on');
    metricGr.query();

    while (metricGr.next()) {
        var definition = metricGr.definition.getDisplayValue();
        var timestamp = metricGr.getValue('sys_created_on');

        // 2. Assigned to Support Group
        if (definition == 'Assignment Group') {
            writeRow('Assigned to Support Group', timestamp);
        }

        // 3, 9, 10, 11, 13, 14. State Changes
        if (definition == 'Problem State Duration' || definition == 'State') { // Check for both common definitions
            var stateValue = metricGr.getDisplayValue('value');
            if (stateValue == 'Assess' || stateValue == 'Root Cause Analysis') {
                writeRow('Investigation Commenced', timestamp);
            }
            if (stateValue == 'Fix in Progress') {
                writeRow('State Changed to Fix in Progress', timestamp);
            }
            if (stateValue == 'Resolved') {
                writeRow('Permanent Fix Applied', timestamp);
            }
            if (stateValue == 'Closed') {
                // Check previous state to distinguish between regular close and rejected
                var previousState = metricGr.start_value.getDisplayValue();
                if (previousState == 'Assess' || previousState == 'New'){
                    writeRow('Assessment Rejected', timestamp);
                }
                writeRow('Resolution Verified', timestamp); // Assumption: moving to Closed verifies the resolution
                writeRow('Problem Record Closed', problemGr.getValue('closed_at'));
            }
        }

        // 4. Workaround Identified
        if (metricGr.field_value_chronological_index == 1 && metricGr.field.element == 'workaround') {
             writeRow('Workaround Identified', timestamp);
        }
        
        // 5. Workaround Published (approximated by Known Error flag)
        if (metricGr.field.element == 'known_error' && metricGr.getValue('value') == 'true') {
            writeRow('Workaround Published', timestamp);
        }

        // 6. Root Cause Identified
        if (metricGr.field_value_chronological_index == 1 && metricGr.field.element == 'root_cause') {
            writeRow('Root Cause Identified', timestamp);
        }

        // 7. Proposed Solution Drafted
        if (metricGr.field_value_chronological_index == 1 && metricGr.field.element == 'fix_notes') {
            writeRow('Proposed Solution Drafted', timestamp);
        }

        // 8. Change Request Initiated
        if (metricGr.field_value_chronological_index == 1 && metricGr.field.element == 'rfc') {
             writeRow('Change Request Initiated', timestamp);
        }
    }

    // 12. Post-Implementation Review Completed (check related task)
    var pirTask = new GlideRecord('problem_task');
    pirTask.addQuery('problem', problemSysId);
    pirTask.addQuery('short_description', 'CONTAINS', 'PIR'); // Or use a specific task type
    pirTask.addQuery('state', '3'); // Closed Complete
    pirTask.orderByDesc('closed_at');
    pirTask.setLimit(1);
    pirTask.query();
    if (pirTask.next()) {
        writeRow('Post-Implementation Review Completed', pirTask.getValue('closed_at'));
    }
}

WITH ProblemData AS (
  SELECT
    pbm.sys_id,
    pbm.number AS ProblemNumber,
    pbm.sys_created_on,
    pbm.assigned_to,
    pbm.assignment_group,
    pbm.state AS ProblemState,
    pbm.priority AS Priority,
    pbm.close_code AS RootCauseCategory,
    pbm.reassignment_count AS ReassignmentCount,
    pbm.related_incidents AS RelatedIncidentCount
  FROM problem pbm
  WHERE pbm.sys_created_on BETWEEN '[Your Start Date]' AND '[Your End Date]'
)
SELECT
  pd.ProblemNumber,
  'Problem Record Created' AS Activity,
  pd.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM ProblemData pd
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
UNION ALL
SELECT
  pd.ProblemNumber,
  'Assigned to Support Group' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'assignment_group' AND aud.newvalue IS NOT NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'Investigation Commenced' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'state' AND aud.oldvalue = '[Your State Value for New]' AND aud.newvalue IN ('[Your State Value for Assess]', '[Your State Value for Root Cause Analysis]')
UNION ALL
SELECT
  pd.ProblemNumber,
  'Workaround Identified' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'work_around' AND aud.newvalue IS NOT NULL AND aud.oldvalue IS NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'Workaround Published' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'known_error' AND aud.newvalue = '1'
UNION ALL
SELECT
  pd.ProblemNumber,
  'Root Cause Identified' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'root_cause' AND aud.newvalue IS NOT NULL AND aud.oldvalue IS NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'Proposed Solution Drafted' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'close_notes' AND aud.newvalue IS NOT NULL AND aud.oldvalue IS NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'Change Request Initiated' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'rfc' AND aud.newvalue IS NOT NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'State Changed to Fix in Progress' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'state' AND aud.newvalue = '[Your State Value for Fix in Progress]'
UNION ALL
SELECT
  pd.ProblemNumber,
  'Permanent Fix Applied' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'state' AND aud.newvalue = '[Your State Value for Resolved]'
UNION ALL
SELECT
  pd.ProblemNumber,
  'Resolution Verified' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'close_notes' AND aud.newvalue IS NOT NULL AND aud.oldvalue IS NOT NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'Post-Implementation Review Completed' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'review_date' AND aud.newvalue IS NOT NULL
UNION ALL
SELECT
  pd.ProblemNumber,
  'Assessment Rejected' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'state' AND aud.newvalue = '[Your State Value for Cancelled]' AND aud.oldvalue IN ('[Your State Value for New]', '[Your State Value for Assess]')
UNION ALL
SELECT
  pd.ProblemNumber,
  'Problem Record Closed' AS Activity,
  aud.sys_created_on AS EventTime,
  'ServiceNow' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  usr.name AS AssignedTo,
  grp.name AS AssignmentGroup,
  pd.ProblemState,
  pd.Priority,
  pd.RootCauseCategory,
  pd.ReassignmentCount,
  pd.RelatedIncidentCount
FROM sys_audit aud
JOIN ProblemData pd ON aud.documentkey = pd.sys_id
LEFT JOIN sys_user usr ON pd.assigned_to = usr.sys_id
LEFT JOIN sys_user_group grp ON pd.assignment_group = grp.sys_id
WHERE aud.tablename = 'problem' AND aud.fieldname = 'state' AND aud.newvalue = '[Your State Value for Closed]'

import requests
import pandas as pd
from datetime import datetime, timedelta
import getpass
import json

class=class="hl-string">"hl-comment"># --- CONFIGURATION ---
class=class="hl-string">"hl-comment"># ServiceNow Instance Details (use placeholders or environment variables in production)
SN_INSTANCE = class="hl-string">'https://[your-instance].service-now.com'
SN_USER = class="hl-string">'[your_service_account_user]'
class=class="hl-string">"hl-comment"># It is strongly recommended to use environment variables or a secrets vault for passwords
SN_PASSWORD = getpass.getpass(class="hl-string">'Enter ServiceNow Password: ')

class=class="hl-string">"hl-comment"># Date Range for Extraction (ISO 8601 format)
START_DATE = (datetime.now() - timedelta(days=180)).strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
END_DATE = datetime.now().strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

class=class="hl-string">"hl-comment"># API Parameters
SYSparm_LIMIT = 1000
PROBLEM_TABLE = class="hl-string">'problem'
AUDIT_TABLE = class="hl-string">'sys_audit'

class=class="hl-string">"hl-comment"># --- SCRIPT START ---

class=class="hl-string">"hl-comment"># Set up a session for connection pooling
session = requests.Session()
session.auth = (SN_USER, SN_PASSWORD)
headers = {class="hl-string">"Accept": class="hl-string">"application/json", class="hl-string">"Content-Type": class="hl-string">"application/json"}
session.headers.update(headers)

def fetch_paginated_data(table, query):
    class="hl-string">"""Fetches all records from a table matching a query, handling pagination."""
    records = []
    offset = 0
    while True:
        params = {
            class="hl-string">'sysparm_query': query,
            class="hl-string">'sysparm_limit': SYSparm_LIMIT,
            class="hl-string">'sysparm_offset': offset,
            class="hl-string">'sysparm_display_value': class="hl-string">'true' class=class="hl-string">"hl-comment"># Get display values for reference fields
        }
        endpoint = fclass="hl-string">'{SN_INSTANCE}/api/now/table/{table}'
        try:
            response = session.get(endpoint, 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 += len(data)
            print(fclass="hl-string">'Fetched {len(records)} records from {table}...')
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">'Error fetching data from {table}: {e}')
            break
    return records

def main():
    class="hl-string">"""Main function to orchestrate the extraction and transformation."""
    print(fclass="hl-string">'Starting extraction for problems created between {START_DATE} and {END_DATE}.')
    
    class=class="hl-string">"hl-comment"># 1. Fetch all problem records within the date range
    problem_query = fclass="hl-string">'sys_created_on>={START_DATE}^sys_created_on<={END_DATE}'
    problems = fetch_paginated_data(PROBLEM_TABLE, problem_query)
    if not problems:
        print(class="hl-string">'No problems found in the specified date range. Exiting.')
        return

    problem_sys_ids = [p[class="hl-string">'sys_id'] for p in problems]
    print(fclass="hl-string">'Found {len(problem_sys_ids)} problems. Now fetching audit history.')

    class=class="hl-string">"hl-comment"># 2. Fetch all audit records for these problems in batches to avoid long query strings
    all_audit_records = []
    batch_size = 100
    for i in range(0, len(problem_sys_ids), batch_size):
        batch_ids = problem_sys_ids[i:i + batch_size]
        audit_query = fclass="hl-string">'documentkeyIN{',class="hl-string">'.join(batch_ids)}'
        batch_audit_records = fetch_paginated_data(AUDIT_TABLE, audit_query)
        all_audit_records.extend(batch_audit_records)

    class=class="hl-string">"hl-comment"># Group audit records by problem sys_id for easier processing
    audit_records_by_problem = {}
    for record in all_audit_records:
        doc_key = record[class="hl-string">'documentkey']
        if doc_key not in audit_records_by_problem:
            audit_records_by_problem[doc_key] = []
        audit_records_by_problem[doc_key].append(record)

    class=class="hl-string">"hl-comment"># 3. Process records into an event log format
    event_log = []
    last_data_update = datetime.now().isoformat()

    for prob in problems:
        problem_sys_id = prob[class="hl-string">'sys_id']

        class=class="hl-string">"hl-comment"># Base attributes for all events for this problem
        base_event = {
            class="hl-string">'ProblemNumber': prob.get(class="hl-string">'number'),
            class="hl-string">'SourceSystem': class="hl-string">'ServiceNow',
            class="hl-string">'LastDataUpdate': last_data_update,
            class="hl-string">'Priority': prob.get(class="hl-string">'priority'),
            class="hl-string">'RootCauseCategory': prob.get(class="hl-string">'u_root_cause_category'), class=class="hl-string">"hl-comment"># Example custom field
            class="hl-string">'ReassignmentCount': prob.get(class="hl-string">'reassignment_count'),
            class="hl-string">'RelatedIncidentCount': prob.get(class="hl-string">'related_incidents')
        }

        class=class="hl-string">"hl-comment"># Event: Problem Record Created
        event_log.append({
            **base_event,
            class="hl-string">'Activity': class="hl-string">'Problem Record Created',
            class="hl-string">'EventTime': prob[class="hl-string">'sys_created_on'],
            class="hl-string">'AssignedTo': prob.get(class="hl-string">'assigned_to'),
            class="hl-string">'AssignmentGroup': prob.get(class="hl-string">'assignment_group'),
            class="hl-string">'ProblemState': prob.get(class="hl-string">'state')
        })

        class=class="hl-string">"hl-comment"># Process audit records for other events
        problem_audits = sorted(audit_records_by_problem.get(problem_sys_id, []), key=lambda x: x[class="hl-string">'sys_created_on'])

        for audit in problem_audits:
            field = audit[class="hl-string">'fieldname']
            new_value = audit[class="hl-string">'newvalue']
            old_value = audit[class="hl-string">'oldvalue']
            event_time = audit[class="hl-string">'sys_created_on']
            
            activity_event = {
                **base_event,
                class="hl-string">'EventTime': event_time,
                class="hl-string">'AssignedTo': prob.get(class="hl-string">'assigned_to'), class=class="hl-string">"hl-comment"># Use final state for simplicity, can be improved
                class="hl-string">'AssignmentGroup': prob.get(class="hl-string">'assignment_group'),
                class="hl-string">'ProblemState': prob.get(class="hl-string">'state')
            }
            
            activity_found = False

            class=class="hl-string">"hl-comment"># Map audit entries to activities
            if field == class="hl-string">'assignment_group' and new_value:
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Assigned to Support Group'
                activity_event[class="hl-string">'AssignmentGroup'] = new_value
                activity_found = True
            elif field == class="hl-string">'state':
                if new_value in [class="hl-string">'Assess', class="hl-string">'Root Cause Analysis'] and old_value == class="hl-string">'New':
                    activity_event[class="hl-string">'Activity'] = class="hl-string">'Investigation Commenced'
                    activity_found = True
                elif new_value == class="hl-string">'Fix in Progress':
                    activity_event[class="hl-string">'Activity'] = class="hl-string">'State Changed to Fix in Progress'
                    activity_found = True
                elif new_value == class="hl-string">'Resolved':
                    activity_event[class="hl-string">'Activity'] = class="hl-string">'Permanent Fix Applied'
                    activity_found = True
                elif new_value == class="hl-string">'Closed':
                    activity_event[class="hl-string">'Activity'] = class="hl-string">'Problem Record Closed'
                    activity_found = True
                elif new_value == class="hl-string">'[Your Resolution Verified State]': class=class="hl-string">"hl-comment"># Customize as needed
                    activity_event[class="hl-string">'Activity'] = class="hl-string">'Resolution Verified'
                    activity_found = True
                elif new_value == class="hl-string">'Canceled' and old_value == class="hl-string">'Assess':
                    activity_event[class="hl-string">'Activity'] = class="hl-string">'Assessment Rejected'
                    activity_found = True
            elif field == class="hl-string">'work_around' and new_value and not old_value:
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Workaround Identified'
                activity_found = True
            elif field == class="hl-string">'known_error' and new_value == class="hl-string">'true': class=class="hl-string">"hl-comment"># Common proxy for communicating workaround
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Workaround Published'
                activity_found = True
            elif field == class="hl-string">'root_cause' and new_value and not old_value:
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Root Cause Identified'
                activity_found = True
            elif field == class="hl-string">'fix_notes' and new_value and not old_value:
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Proposed Solution Drafted'
                activity_found = True
            elif field == class="hl-string">'rfc' and new_value and not old_value:
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Change Request Initiated'
                activity_found = True
            elif field == class="hl-string">'[Your PIR Completed Field]' and new_value: class=class="hl-string">"hl-comment"># Customize to your PIR field
                activity_event[class="hl-string">'Activity'] = class="hl-string">'Post-Implementation Review Completed'
                activity_found = True
                
            if activity_found:
                activity_event[class="hl-string">'ProblemState'] = new_value if field == class="hl-string">'state' else prob.get(class="hl-string">'state')
                event_log.append(activity_event)

    class=class="hl-string">"hl-comment"># 4. Create DataFrame and save to CSV
    if not event_log:
        print(class="hl-string">'No events were generated. The output file will be empty.')
        return

    df = pd.DataFrame(event_log)

    class=class="hl-string">"hl-comment"># Order columns as specified
    column_order = [
        class="hl-string">'ProblemNumber', class="hl-string">'Activity', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'AssignedTo', class="hl-string">'AssignmentGroup', class="hl-string">'ProblemState', class="hl-string">'Priority', class="hl-string">'RootCauseCategory',
        class="hl-string">'ReassignmentCount', class="hl-string">'RelatedIncidentCount'
    ]
    df = df[column_order]
    df = df.sort_values(by=[class="hl-string">'ProblemNumber', class="hl-string">'EventTime']).reset_index(drop=True)

    output_filename = class="hl-string">'servicenow_problem_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">'Successfully created event log: {output_filename}')

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

(function () {
    var START_DATE = '[Start date]';
    var END_DATE = '[End date]';
    var SOURCE_SYSTEM = 'ServiceNow Problem Management';
    var LAST_DATA_UPDATE = new GlideDateTime().getValue();
    var TABLE_NAME = '[Your table name]';

    var FIELD_NUMBER = '[Problem number field]';
    var FIELD_CREATED = '[Created date and time field]';
    var FIELD_ASSIGNED_TO = '[Assigned to field]';
    var FIELD_ASSIGNMENT_GROUP = '[Assignment group field]';
    var FIELD_STATE = '[Problem state field]';
    var FIELD_PRIORITY = '[Priority field]';
    var FIELD_WORKAROUND = '[Workaround field]';
    var FIELD_ROOT_CAUSE = '[Root cause field or code field]';
    var FIELD_FIX_NOTES = '[Fix notes field]';
    var FIELD_RESOLUTION_CODE = '[Resolution code field]';
    var FIELD_RELATED_CHANGE = '[Related change field or relationship indicator]';
    var FIELD_VERIFICATION = '[Resolution verification field]';
    var FIELD_PIR = '[PIR completion field or flag]';
    var FIELD_ACTIVE = '[Active field]';
    var FIELD_UPDATED = '[Updated date and time field]';
    var FIELD_REASSIGNMENT_COUNT = '[Reassignment count field or calculation]';
    var FIELD_RELATED_INCIDENT_COUNT = '[Related incident count field or calculation]';

    var ACTIVITY_CREATED = 'Problem Record Created';
    var ACTIVITY_ASSIGNED = 'Assigned to Support Group';
    var ACTIVITY_INVESTIGATION = 'Investigation Commenced';
    var ACTIVITY_WORKAROUND_IDENTIFIED = 'Workaround Identified';
    var ACTIVITY_WORKAROUND_PUBLISHED = 'Workaround Published';
    var ACTIVITY_ROOT_CAUSE = 'Root Cause Identified';
    var ACTIVITY_SOLUTION = 'Proposed Solution Drafted';
    var ACTIVITY_CHANGE = 'Change Request Initiated';
    var ACTIVITY_FIX_PROGRESS = 'State Changed to Fix in Progress';
    var ACTIVITY_FIX_APPLIED = 'Permanent Fix Applied';
    var ACTIVITY_VERIFIED = 'Resolution Verified';
    var ACTIVITY_PIR = 'Post-Implementation Review Completed';
    var ACTIVITY_REJECTED = 'Assessment Rejected';
    var ACTIVITY_CLOSED = 'Problem Record Closed';

    var result = [];
    var gr = new GlideRecord(TABLE_NAME);
    gr.addQuery(FIELD_UPDATED, '>=', START_DATE);
    gr.addQuery(FIELD_UPDATED, '<=', END_DATE);
    gr.query();

    while (gr.next()) {
        var number = gr.getValue(FIELD_NUMBER);
        var assignedTo = gr.getDisplayValue(FIELD_ASSIGNED_TO);
        var assignmentGroup = gr.getDisplayValue(FIELD_ASSIGNMENT_GROUP);
        var state = gr.getDisplayValue(FIELD_STATE);
        var priority = gr.getDisplayValue(FIELD_PRIORITY);
        var rootCause = gr.getDisplayValue(FIELD_ROOT_CAUSE);
        var reassignmentCount = gr.getValue(FIELD_REASSIGNMENT_COUNT);
        var relatedIncidentCount = gr.getValue(FIELD_RELATED_INCIDENT_COUNT);
        var createdTime = gr.getValue(FIELD_CREATED);
        var updatedTime = gr.getValue(FIELD_UPDATED);
        var workaround = gr.getValue(FIELD_WORKAROUND);
        var fixNotes = gr.getValue(FIELD_FIX_NOTES);
        var resolutionCode = gr.getValue(FIELD_RESOLUTION_CODE);
        var relatedChange = gr.getValue(FIELD_RELATED_CHANGE);
        var verification = gr.getValue(FIELD_VERIFICATION);
        var pir = gr.getValue(FIELD_PIR);
        var active = gr.getValue(FIELD_ACTIVE);

        function addEvent(activity, eventTime) {
            if (!number || !eventTime) {
                return;
            }
            result.push({
                ProblemNumber: number,
                Activity: activity,
                EventTime: eventTime,
                SourceSystem: SOURCE_SYSTEM,
                LastDataUpdate: LAST_DATA_UPDATE,
                AssignedTo: assignedTo,
                AssignmentGroup: assignmentGroup,
                ProblemState: state,
                Priority: priority,
                RootCauseCategory: rootCause,
                ReassignmentCount: reassignmentCount,
                RelatedIncidentCount: relatedIncidentCount
            });
        }

        addEvent(ACTIVITY_CREATED, createdTime);

        if (assignmentGroup) {
            addEvent(ACTIVITY_ASSIGNED, updatedTime);
        }

        if (state == 'Assess' || state == 'Root Cause Analysis' || state == 'Assessment' || state == 'RCA') {
            addEvent(ACTIVITY_INVESTIGATION, updatedTime);
        }

        if (workaround) {
            addEvent(ACTIVITY_WORKAROUND_IDENTIFIED, updatedTime);
        }

        if (workaround && '[Communicate Workaround execution indicator]' == 'true') {
            addEvent(ACTIVITY_WORKAROUND_PUBLISHED, updatedTime);
        }

        if (rootCause) {
            addEvent(ACTIVITY_ROOT_CAUSE, updatedTime);
        }

        if (fixNotes || resolutionCode) {
            addEvent(ACTIVITY_SOLUTION, updatedTime);
        }

        if (relatedChange) {
            addEvent(ACTIVITY_CHANGE, updatedTime);
        }

        if (state == 'Fix in Progress' || state == 'Fix' || state == 'Implementation') {
            addEvent(ACTIVITY_FIX_PROGRESS, updatedTime);
        }

        if (state == 'Resolved' || state == 'Resolution') {
            addEvent(ACTIVITY_FIX_APPLIED, updatedTime);
        }

        if (verification == 'true' || verification == '1' || verification == 'Verified' || verification == 'Complete') {
            addEvent(ACTIVITY_VERIFIED, updatedTime);
        }

        if (pir == 'true' || pir == '1' || pir == 'Complete' || pir == 'Completed') {
            addEvent(ACTIVITY_PIR, updatedTime);
        }

        if (state == 'Rejected' || state == 'Cancelled' || state == 'Canceled' || active == 'false') {
            addEvent(ACTIVITY_REJECTED, updatedTime);
        }

        if (state == 'Closed' || active == 'false') {
            addEvent(ACTIVITY_CLOSED, updatedTime);
        }
    }

    result.sort(function (a, b) {
        if (a.ProblemNumber < b.ProblemNumber) return -1;
        if (a.ProblemNumber > b.ProblemNumber) return 1;
        if (a.EventTime < b.EventTime) return -1;
        if (a.EventTime > b.EventTime) return 1;
        if (a.Activity < b.Activity) return -1;
        if (a.Activity > b.Activity) return 1;
        return 0;
    });

    return JSON.stringify(result);
})();