Uw Change Management Data Template

This method uses the ServiceNow UI for filtering and exporting, not a direct script. The configuration below outlines the filters and columns for each required export.

**Export 1: Change Request Data**
*   **Navigate to Table**: `change_request.list`
*   **Filter Conditions**:
    *   `Created` `on` `Last 3 months` (or your desired date range)
    *   AND `Type` `is one of` `Normal, Standard, Emergency` (as needed)
*   **Columns to Select/Export**:
    *   `number` (maps to ChangeRequestNumber)
    *   `sys_created_on` (used for 'Change Request Created' EventTime)
    *   `sys_updated_on` (used as EventTime for most state changes)
    *   `state` (maps to ChangeState)
    *   `assignment_group` (maps to AssignmentGroup)
    *   `type` (maps to ChangeType)
    *   `priority` (maps to Priority)
    *   `risk` (maps to RiskLevel)
    *   `cmdb_ci` (maps to ConfigurationItem)

**Export 2: Approval Data**
*   **Navigate to Table**: `sysapproval_approver.list`
*   **Filter Conditions**:
    *   `Source table` `is` `change_request`
    *   AND `Created` `on` `Last 3 months` (or your desired date range)
*   **Columns to Select/Export**:
    *   `document_id` (used to link to the Change Request Number)
    *   `state` (approval state: requested, approved, rejected)
    *   `sys_created_on` (used for 'Approval Requested' EventTime)
    *   `sys_updated_on` (used for 'Change Approved'/'Change Rejected' EventTime)

**Post-Export Transformation Logic (to be performed in a spreadsheet or script)**:

For each unique Change Request Number, create the following rows based on the data from the exported files. Timestamps should be from `sys_updated_on` unless specified otherwise.

1.  `ActivityName`: 'Change Request Created', `EventTime`: `sys_created_on` from change_request export.
2.  `ActivityName`: 'Change Awaiting Assessment', `EventTime`: Timestamp when `state` becomes 'Assess'.
3.  `ActivityName`: 'Risk And Impact Assessed', `EventTime`: Timestamp when `state` changes from 'Assess' to 'Authorize'.
4.  `ActivityName`: 'Approval Requested', `EventTime`: `sys_created_on` from sysapproval_approver export where approval `state` is 'requested'.
5.  `ActivityName`: 'Change Approved', `EventTime`: `sys_updated_on` from sysapproval_approver export when final approval `state` becomes 'approved'.
6.  `ActivityName`: 'Change Rejected', `EventTime`: `sys_updated_on` from sysapproval_approver export when approval `state` becomes 'rejected'.
7.  `ActivityName`: 'Change Scheduled', `EventTime`: Timestamp when `state` becomes 'Scheduled'.
8.  `ActivityName`: 'Implementation Started', `EventTime`: Timestamp when `state` becomes 'Implement'.
9.  `ActivityName`: 'Change Implemented', `EventTime`: Timestamp when `state` changes from 'Implement' to 'Review'.
10. `ActivityName`: 'Review In Progress', `EventTime`: Timestamp when `state` becomes 'Review'.
11. `ActivityName`: 'Change Reopened', `EventTime`: Inferred from a non-linear state change (e.g., 'Review' back to 'Implement'). This is difficult to capture with this method and often requires manual inspection or analysis of audit data.
12. `ActivityName`: 'Change Closed', `EventTime`: Timestamp when `state` becomes 'Closed'.
13. `ActivityName`: 'Change Canceled', `EventTime`: Timestamp when `state` becomes 'Canceled'.

const axios = require('axios');
const fs = require('fs');

// --- Configuration ---
const SN_INSTANCE = '[your-instance].service-now.com';
const SN_USER = '[your_username]';
const SN_PASSWORD = '[your_password]';
const START_DATE = '2023-01-01 00:00:00'; // Start date for changes to extract
const END_DATE = '2023-12-31 23:59:59';   // End date for changes to extract
const OUTPUT_FILE = 'servicenow_change_management_log.csv';

const auth = { username: SN_USER, password: SN_PASSWORD };

// Maps ServiceNow integer state values to human-readable names. Configure based on your system.
const STATE_MAP = {
    '-5': 'Draft',
    '-4': 'Assess',
    '-3': 'Authorize',
    '-2': 'Scheduled',
    '-1': 'Implement',
    '0': 'Review',
    '3': 'Closed',
    '4': 'Canceled',
    // Add any custom state values here
};

// Defines the standard process flow for detecting 'Reopened' events
const STATE_SEQUENCE = { 'Assess': 1, 'Authorize': 2, 'Scheduled': 3, 'Implement': 4, 'Review': 5, 'Closed': 6 };

// Function to fetch data with pagination
async function fetchAllRecords(table, query, fields) {
    let offset = 0;
    const limit = 1000;
    let allRecords = [];
    let hasMore = true;

    while (hasMore) {
        console.log(`Fetching ${table} records, offset: ${offset}`);
        const url = `https://${SN_INSTANCE}/api/now/table/${table}`;
        try {
            const response = await axios.get(url, {
                auth,
                params: {
                    sysparm_query: `${query}^sysparm_offset=${offset}^sysparm_limit=${limit}`,
                    sysparm_display_value: 'false', // Get raw values for mapping
                    sysparm_exclude_reference_link: true,
                    sysparm_fields: fields
                }
            });
            const records = response.data.result;
            if (records.length > 0) {
                allRecords = allRecords.concat(records);
                offset += limit;
            } else {
                hasMore = false;
            }
        } catch (error) {
            console.error(`Error fetching from ${table}:`, error.response ? error.response.data : error.message);
            hasMore = false;
        }
    }
    return allRecords;
}

// Main extraction function
async function extractChangeLog() {
    const eventLog = [];

    // 1. Get all change requests in the date range
    const changeQuery = `sys_created_on>=${START_DATE}^sys_created_on<=${END_DATE}`;
    const changeFields = 'sys_id,number,sys_created_on,assignment_group,type,priority,risk,cmdb_ci,state';
    const changes = await fetchAllRecords('change_request', changeQuery, changeFields);

    for (const change of changes) {
        // Activity: Change Request Created
        eventLog.push({
            ChangeRequestNumber: change.number,
            ActivityName: 'Change Request Created',
            EventTime: change.sys_created_on,
            ChangeState: STATE_MAP[change.state] || 'Unknown',
            AssignmentGroup: change.assignment_group,
            ChangeType: change.type,
            Priority: change.priority,
            RiskLevel: change.risk,
            ConfigurationItem: change.cmdb_ci
        });

        // 2. Get audit history for this change
        const auditQuery = `documentkey=${change.sys_id}^tablename=change_request^fieldnameINstate,approval`;
        const auditFields = 'sys_created_on,fieldname,oldvalue,newvalue';
        const auditRecords = await fetchAllRecords('sys_audit', auditQuery, auditFields);

        for (const audit of auditRecords) {
            let activityName = null;

            if (audit.fieldname === 'state') {
                const oldStateName = STATE_MAP[audit.oldvalue] || audit.oldvalue;
                const newStateName = STATE_MAP[audit.newvalue] || audit.newvalue;

                switch (newStateName) {
                    case 'Assess': activityName = 'Change Awaiting Assessment'; break;
                    case 'Authorize': activityName = 'Risk And Impact Assessed'; break;
                    case 'Scheduled': activityName = 'Change Scheduled'; break;
                    case 'Implement': activityName = 'Implementation Started'; break;
                    case 'Review':
                        // Per requirements, create two distinct events for the same timestamp
                        eventLog.push({ ...change, ActivityName: 'Change Implemented', EventTime: audit.sys_created_on, ChangeState: newStateName });
                        eventLog.push({ ...change, ActivityName: 'Review In Progress', EventTime: audit.sys_created_on, ChangeState: newStateName });
                        continue; // Skip the generic push at the end
                    case 'Closed': activityName = 'Change Closed'; break;
                    case 'Canceled': activityName = 'Change Canceled'; break;
                }
                // Check for 'Reopened' event
                if (STATE_SEQUENCE[oldStateName] && STATE_SEQUENCE[newStateName] && STATE_SEQUENCE[newStateName] < STATE_SEQUENCE[oldStateName]) {
                    eventLog.push({ ...change, ActivityName: 'Change Reopened', EventTime: audit.sys_created_on, ChangeState: newStateName });
                }

            } else if (audit.fieldname === 'approval') {
                if (audit.newvalue === 'requested') activityName = 'Approval Requested';
                if (audit.newvalue === 'approved') activityName = 'Change Approved';
                if (audit.newvalue === 'rejected') activityName = 'Change Rejected';
            }

            if (activityName) {
                eventLog.push({
                    ChangeRequestNumber: change.number,
                    ActivityName: activityName,
                    EventTime: audit.sys_created_on,
                    ChangeState: STATE_MAP[change.state] || 'Unknown', // Use the final state of the change for context
                    AssignmentGroup: change.assignment_group,
                    ChangeType: change.type,
                    Priority: change.priority,
                    RiskLevel: change.risk,
                    ConfigurationItem: change.cmdb_ci
                });
            }
        }
    }

    // 3. Convert to CSV and save
    if (eventLog.length > 0) {
        const header = Object.keys(eventLog[0]).join(',');
        const rows = eventLog.map(row => Object.values(row).map(val => `"${val || ''}"`).join(','));
        fs.writeFileSync(OUTPUT_FILE, `${header}\n${rows.join('\n')}`);
        console.log(`Extraction complete. Saved ${eventLog.length} events to ${OUTPUT_FILE}`);
    } else {
        console.log('No events found for the specified criteria.');
    }
}

extractChangeLog();

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"># Enter your ServiceNow instance URL, for example, https://your-instance.service-now.com
SERVICENOW_INSTANCE = class="hl-string">"[Your ServiceNow Instance URL]"
SERVICENOW_USER = class="hl-string">"[Your ServiceNow API Username]"
class=class="hl-string">"hl-comment"># It is recommended to use a secure method for password management, such as environment variables or a secrets manager.
SERVICENOW_PASSWORD = getpass.getpass(class="hl-string">"Enter ServiceNow Password: ")

class=class="hl-string">"hl-comment"># --- State and Approval Mapping ---
class=class="hl-string">"hl-comment"># Maps ServiceNow integer state values to human-readable activity names
STATE_MAP = {
    class="hl-string">'-4': class="hl-string">'Change Awaiting Assessment',
    class="hl-string">'-3': class="hl-string">'Risk And Impact Assessed', class=class="hl-string">"hl-comment"># Mapped from leaving Assess state
    class="hl-string">'-2': class="hl-string">'Change Scheduled',
    class="hl-string">'-1': class="hl-string">'Implementation Started',
    class="hl-string">'0': class="hl-string">'Review In Progress',
    class="hl-string">'3': class="hl-string">'Change Closed',
    class="hl-string">'4': class="hl-string">'Change Canceled'
}

class=class="hl-string">"hl-comment"># Function to handle pagination and fetch all records from an endpoint
def get_all_records(endpoint, query_params):
    records = []
    offset = 0
    limit = 1000
    while True:
        query_params[class="hl-string">'sysparm_offset'] = str(offset)
        query_params[class="hl-string">'sysparm_limit'] = str(limit)
        response = requests.get(endpoint, auth=(SERVICENOW_USER, SERVICENOW_PASSWORD), params=query_params)
        response.raise_for_status()
        data = response.json().get(class="hl-string">'result', [])
        if not data:
            break
        records.extend(data)
        offset += limit
        print(fclass="hl-string">"Fetched {len(records)} records from {endpoint.split('/')[-1]}...")
    return records

def main():
    print(class="hl-string">"Starting ServiceNow Change Management data extraction...")
    event_log = []

    class=class="hl-string">"hl-comment"># 1. Fetch Change Request base data
    print(class="hl-string">"\nStep 1: Fetching core Change Request data...")
    cr_endpoint = fclass="hl-string">"{SERVICENOW_INSTANCE}/api/now/table/change_request"
    cr_params = {
        class="hl-string">'sysparm_display_value': class="hl-string">'true',
        class="hl-string">'sysparm_fields': class="hl-string">'number,sys_id,sys_created_on,sys_updated_on,state,assignment_group,type,priority,risk,cmdb_ci',
        class="hl-string">'sysparm_query': class="hl-string">'sys_updated_on>=javascript:gs.daysAgo(180)' class=class="hl-string">"hl-comment"># Adjust date range as needed
    }
    change_requests = get_all_records(cr_endpoint, cr_params)
    
    if not change_requests:
        print(class="hl-string">"No change requests found for the specified period. Exiting.")
        return

    cr_sys_ids = [cr[class="hl-string">'sys_id'] for cr in change_requests]
    cr_map = {cr[class="hl-string">'sys_id']: cr for cr in change_requests}
    cr_id_query_part = class="hl-string">'^documentkeyIN' + class="hl-string">','.join(cr_sys_ids)

    class=class="hl-string">"hl-comment"># 2. Fetch all relevant Audit (sys_audit) records for state changes
    print(class="hl-string">"\nStep 2: Fetching audit history for state changes...")
    audit_endpoint = fclass="hl-string">"{SERVICENOW_INSTANCE}/api/now/table/sys_audit"
    audit_params = {
        class="hl-string">'sysparm_display_value': class="hl-string">'false', class=class="hl-string">"hl-comment"># We need the integer state values
        class="hl-string">'sysparm_fields': class="hl-string">'documentkey,sys_created_on,oldvalue,newvalue',
        class="hl-string">'sysparm_query': fclass="hl-string">'tablename=change_request^fieldname=state^{cr_id_query_part}'
    }
    audit_records = get_all_records(audit_endpoint, audit_params)

    class=class="hl-string">"hl-comment"># 3. Fetch all relevant Approval (sysapproval_approver) records
    print(class="hl-string">"\nStep 3: Fetching approval history...")
    approval_endpoint = fclass="hl-string">"{SERVICENOW_INSTANCE}/api/now/table/sysapproval_approver"
    approval_params = {
        class="hl-string">'sysparm_display_value': class="hl-string">'true',
        class="hl-string">'sysparm_fields': class="hl-string">'sysapproval,sys_created_on,sys_updated_on,state',
        class="hl-string">'sysparm_query': fclass="hl-string">'sysapprovalIN' + class="hl-string">','.join(cr_sys_ids)
    }
    approval_records = get_all_records(approval_endpoint, approval_params)

    print(class="hl-string">"\nStep 4: Transforming data into event log format...")
    class=class="hl-string">"hl-comment"># Process each change request to build the event log
    for cr_id, cr_data in cr_map.items():
        base_attributes = {
            class="hl-string">'ChangeRequestNumber': cr_data.get(class="hl-string">'number'),
            class="hl-string">'ChangeState': cr_data.get(class="hl-string">'state'),
            class="hl-string">'AssignmentGroup': cr_data.get(class="hl-string">'assignment_group'),
            class="hl-string">'ChangeType': cr_data.get(class="hl-string">'type'),
            class="hl-string">'Priority': cr_data.get(class="hl-string">'priority'),
            class="hl-string">'RiskLevel': cr_data.get(class="hl-string">'risk'),
            class="hl-string">'ConfigurationItem': cr_data.get(class="hl-string">'cmdb_ci')
        }

        class=class="hl-string">"hl-comment"># Event: Change Request Created
        event_log.append({
            **base_attributes,
            class="hl-string">'ActivityName': class="hl-string">'Change Request Created',
            class="hl-string">'EventTime': cr_data.get(class="hl-string">'sys_created_on'),
            class="hl-string">'EndTime': None
        })
        
        class=class="hl-string">"hl-comment"># Events from Audit History (State Changes)
        cr_audit_records = sorted([r for r in audit_records if r[class="hl-string">'documentkey'] == cr_id], key=lambda x: x[class="hl-string">'sys_created_on'])
        
        last_state = None
        for audit in cr_audit_records:
            new_state = audit[class="hl-string">'newvalue']
            old_state = audit[class="hl-string">'oldvalue']
            event_time = audit[class="hl-string">'sys_created_on']

            class=class="hl-string">"hl-comment"># Activity: Risk and Impact Assessed (when moving out of Assess state)
            if old_state == class="hl-string">'-4': class=class="hl-string">"hl-comment"># Assess
                 event_log.append({
                    **base_attributes,
                    class="hl-string">'ActivityName': class="hl-string">'Risk And Impact Assessed',
                    class="hl-string">'EventTime': event_time,
                    class="hl-string">'EndTime': None
                })

            class=class="hl-string">"hl-comment"># Map new state to activity
            if new_state in STATE_MAP:
                event_log.append({
                    **base_attributes,
                    class="hl-string">'ActivityName': STATE_MAP[new_state],
                    class="hl-string">'EventTime': event_time,
                    class="hl-string">'EndTime': None
                })
            
            class=class="hl-string">"hl-comment"># Activity: Change Reopened (non-linear transition, e.g., Review (0) -> Implement (-1))
            if old_state and new_state and int(old_state) > int(new_state):
                class=class="hl-string">"hl-comment"># Basic check for moving to a numerically lower state, indicating rework
                if not (old_state == class="hl-string">'3' and new_state == class="hl-string">'0'): class=class="hl-string">"hl-comment"># Exclude closed to review which might be valid
                    event_log.append({
                        **base_attributes,
                        class="hl-string">'ActivityName': class="hl-string">'Change Reopened',
                        class="hl-string">'EventTime': event_time,
                        class="hl-string">'EndTime': None
                    })
            last_state = new_state

        class=class="hl-string">"hl-comment"># Events from Approval History
        cr_approval_records = sorted([r for r in approval_records if r[class="hl-string">'sysapproval'][class="hl-string">'value'] == cr_id], key=lambda x: x[class="hl-string">'sys_created_on'])
        for approval in cr_approval_records:
            class=class="hl-string">"hl-comment"># Activity: Approval Requested
            event_log.append({
                **base_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Approval Requested',
                class="hl-string">'EventTime': approval[class="hl-string">'sys_created_on'],
                class="hl-string">'EndTime': None
            })

            class=class="hl-string">"hl-comment"># Activity: Change Approved or Rejected
            if approval[class="hl-string">'state'] == class="hl-string">'Approved':
                event_log.append({
                    **base_attributes,
                    class="hl-string">'ActivityName': class="hl-string">'Change Approved',
                    class="hl-string">'EventTime': approval[class="hl-string">'sys_updated_on'],
                    class="hl-string">'EndTime': None
                })
            elif approval[class="hl-string">'state'] == class="hl-string">'Rejected':
                event_log.append({
                    **base_attributes,
                    class="hl-string">'ActivityName': class="hl-string">'Change Rejected',
                    class="hl-string">'EventTime': approval[class="hl-string">'sys_updated_on'],
                    class="hl-string">'EndTime': None
                })

    print(fclass="hl-string">"Transformation complete. Total events created: {len(event_log)}")

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

    print(class="hl-string">"\nStep 5: Saving event log to CSV file...")
    df = pd.DataFrame(event_log)
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'ChangeRequestNumber', class="hl-string">'EventTime'])

    class=class="hl-string">"hl-comment"># Calculate EndTime as the start of the next event for the same case
    df[class="hl-string">'EndTime'] = df.groupby(class="hl-string">'ChangeRequestNumber')[class="hl-string">'EventTime'].shift(-1)
    
    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'ChangeRequestNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'EndTime', class="hl-string">'ChangeState',
        class="hl-string">'AssignmentGroup', class="hl-string">'ChangeType', class="hl-string">'Priority', class="hl-string">'RiskLevel', class="hl-string">'ConfigurationItem'
    ]
    df = df[column_order]

    output_filename = class="hl-string">'servicenow_change_management_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%d %H:%M:%S')
    print(fclass="hl-string">"Successfully saved event log to {output_filename}")

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