> Continuous Process Improvement > Problem Management > ServiceNow Problem Management > Data Template

Your Problem Management Data Template

ServiceNow Problem Management

Problem Management Attributes (22) Problem Management Activities (14) Extraction Guides (4)

Your Problem Management Data Template

This template provides a comprehensive blueprint for analyzing your ITIL problem management lifecycle within ServiceNow. You will find the necessary attributes to collect, the critical activities to track, and step by step extraction guidance for your data project. This structure ensures you have the visibility needed to reduce mean time to resolution and eliminate recurring incidents.

Recommended attributes for root cause analysis
Key process milestones and activities
Step by step ServiceNow extraction guidance

New to event logs? Learn how to create a process mining event log.

Download Template

Problem Management Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your ITIL problem management process.

5 Required 7 Recommended 10 Optional

	Name	Description
	Activity Activity	The specific event or action performed on the problem record.
Description Represents the distinct steps or status changes that occur during the lifecycle of a problem record. Examples include 'Problem Record Created', 'Root Cause Identified', or 'Assigned to Support Group'. This attribute is crucial for constructing the process map and visualizing the sequence of events. Why it matters It defines the nodes in the process map, enabling the visualization of workflow and process variants. Where to get Derived from 'sys_audit', 'sys_history_line' tables, or state changes in the 'problem' table Examples Problem Record CreatedAnalysis CompletedState Changed to Closed
	Event Timestamp EventTime	The exact date and time when an activity occurred.
Description Records the specific timestamp when a change or action was logged in the system. This data point is fundamental for ordering activities chronologically and calculating duration metrics such as cycle times and lead times between process steps. Why it matters Essential for ordering events and calculating all time-based KPIs. Where to get ServiceNow 'sys_created_on' field in audit/history tables Examples 2023-10-12T08:30:00Z2023-10-12T14:45:12Z
	Problem Record ProblemNumber	The unique identifier for the problem record.
Description The unique alphanumeric key assigned to a specific problem record within ServiceNow (e.g., PRB000123). This identifier serves as the central thread linking all process activities, from initial logging through root cause analysis to final closure. In process mining analysis, this attribute functions as the Case ID, allowing the reconstruction of the end-to-end journey of the problem resolution. Why it matters It is the primary key for differentiating unique cases and grouping related events in the process graph. Where to get ServiceNow 'problem' table, field 'number' Examples PRB004512PRB009823PRB001122
	Last Data Update LastDataUpdate	The timestamp when the data was extracted or last refreshed.
Description Indicates the currency of the dataset used for analysis. It helps analysts understand if they are looking at real-time data or a historical snapshot, which is critical for interpreting open case statuses correctly. Why it matters Ensures users know how fresh the data is for accurate operational reporting. Where to get System time at moment of ETL execution Examples 2023-11-01T12:00:00Z
	Source System SourceSystem	The name of the system where the data originated.
Description Identifies the specific ServiceNow instance or environment from which the problem management data was extracted. This is particularly useful in multi-system environments to track data lineage and handle system-specific nuances in the analysis. Why it matters Provides context for data origin, especially when merging data from multiple ITSM tools. Where to get Hardcoded during extraction (e.g., 'ServiceNow Production') Examples ServiceNow ProdServiceNow EMEA
	Assigned User AssignedTo	The specific individual assigned to work on the problem.
Description Identifies the user currently responsible for the problem record. Analyzing this attribute helps in understanding workload distribution, individual performance, and potential bottlenecks at the resource level. Why it matters Key for analyzing resource efficiency and individual workload. Where to get ServiceNow 'problem' table, field 'assigned_to' Examples Alice SmithBob JonesSystem Administrator
	Priority Priority	The priority level assigned to the problem record.
Description Indicates the importance and urgency of the problem, typically calculated from impact and urgency. This attribute allows for the segmentation of analysis by criticality, supporting the 'SLA Breach and Priority Monitoring' dashboard. Why it matters Allows segmentation of process performance by business criticality. Where to get ServiceNow 'problem' table, field 'priority' Examples 1 - Critical2 - High3 - Moderate
	Problem State ProblemState	The lifecycle status of the problem record.
Description Reflects the current stage of the problem record, such as Open, Root Cause Analysis, Fix in Progress, or Closed. This is a primary driver for filtering and understanding the backlog composition in the 'Aged Problem Record Aging Analysis'. Why it matters The primary status indicator used to filter open vs closed cases. Where to get ServiceNow 'problem' table, field 'state' Examples NewAssessRoot Cause AnalysisResolved
	Reassignment Count ReassignmentCount	The number of times the problem was reassigned between groups.
Description A counter tracking how frequently the assignment group has changed. This is the direct source for the 'Problem Record Reassignment Count' KPI and helps identify 'ping-pong' effects where tickets bounce between teams. Why it matters Direct indicator of process friction and lack of clear ownership. Where to get ServiceNow 'problem' table, field 'reassignment_count' Examples 0312
	Related Incident Count RelatedIncidentCount	The number of incidents linked to this problem record.
Description Quantifies the impact of the problem by counting the number of incidents associated with it. High counts in this field, particularly for Known Errors, feed the 'Known Error and Incident Recurrence' dashboard. Why it matters Measures the magnitude of user impact caused by the problem. Where to get ServiceNow 'problem' table, field 'related_incidents' (field name varies) or count of linked records Examples 1501200
	Root Cause Category RootCauseCategory	The classification of the identified root cause.
Description Categorizes the underlying reason for the problem, such as Software Bug, Human Error, or Hardware Failure. This attribute powers the 'Root Cause Categorization Accuracy' dashboard and helps in identifying systemic failure patterns. Why it matters Enables analysis of failure patterns to drive strategic improvements. Where to get ServiceNow 'problem' table, field 'rca_category' or 'u_root_cause_category' Examples Software DefectConfiguration ErrorVendor Issue
	Support Group AssignmentGroup	The technical team responsible for the problem resolution.
Description Designates the specific support group or team currently assigned to the problem. This attribute is vital for the 'Support Group Reassignment Analysis' dashboard to track handoffs between teams and identify organizational silos. Why it matters Crucial for identifying bottlenecks between departments and analyzing handover efficiency. Where to get ServiceNow 'problem' table, field 'assignment_group' Examples Network OpsDatabase AdminsService Desk
	Business Service BusinessService	The high-level business service impacted by the problem.
Description Represents the business-facing service (e.g., 'Payroll Service', 'Customer Portal') rather than the technical component. This provides a business-centric view for reporting to stakeholders. Why it matters Connects technical problems to business value streams. Where to get ServiceNow 'problem' table, field 'business_service' Examples Online BankingInternal Email
	Change Request Number ChangeRequestNumber	The identifier of the change request initiated to fix the problem.
Description Links the problem record to a Change Management record (RFC). This connection is vital for the 'Change Request Transition Efficiency' dashboard to measure the handover speed between problem diagnosis and infrastructure change execution. Why it matters Tracks the transition from problem management to change management. Where to get ServiceNow 'problem' table, field 'rfc' Examples CHG003001CHG004552
	Configuration Item ConfigurationItem	The specific asset or service affected by the problem.
Description Identifies the Configuration Item (CI) linked to the problem record. Analyzing this helps correlate problems with specific hardware, software, or services, supporting the 'Root Cause Categorization Accuracy' dashboard. Why it matters Links process issues to specific physical or logical assets. Where to get ServiceNow 'problem' table, field 'cmdb_ci' Examples SAP ERP Server 01Exchange Email ServiceOracle DB Prod
	Is Known Error IsKnownError	Flag indicating if the problem is classified as a Known Error.
Description Identifies if the problem record has been converted to or marked as a Known Error. This is essential for the 'Known Error and Incident Recurrence' analysis to see if the knowledge management process is effective. Why it matters Distinguishes between active investigations and accepted defects with workarounds. Where to get ServiceNow 'problem' table, field 'known_error' Examples truefalse
	Pending Duration PendingDuration	Total time the problem spent in a suspended or pending state.
Description Aggregates the time spent in statuses like 'Pending Vendor' or 'On Hold'. This metric is used for 'Pending State and Wait Time Analysis' to separate internal processing time from external delays. Why it matters Differentiates between team inefficiency and external dependencies. Where to get Calculated: Sum of duration of intervals where State = Pending/On Hold Examples 5 days0 minutes
	PIR Result PostImplementationReviewResult	The outcome or completion status of the Post-Implementation Review.
Description Stores the result or status of the PIR (e.g., Completed, Not Required, Pending). This attribute is necessary for the 'Post-Implementation Review Compliance' dashboard to ensure major problems are reviewed. Why it matters Quality control metric ensuring learning from major incidents. Where to get ServiceNow 'problem' table, field 'pir_state' or similar custom field Examples CompletedWaivedPending
	RCA Duration RootCauseAnalysisDuration	Time taken from problem creation to root cause identification.
Description A calculated duration measuring the efficiency of the investigation phase. This directly supports the 'Mean Time to Root Cause' KPI and helps identify technical skill gaps or complexity issues. Why it matters Key efficiency metric for the investigation phase. Where to get Calculated: Timestamp of 'Root Cause Identified' activity minus 'Problem Record Created' time Examples 3 days 4 hours12 hours
	SLA Due Date SlaDueDate	The target date/time for problem resolution per the SLA.
Description The timestamp indicating when the problem must be resolved to meet service level agreements. This is the baseline for the 'SLA Breach and Priority Monitoring' dashboard and calculating compliance rates. Why it matters Baseline for calculating SLA breach status. Where to get ServiceNow 'task_sla' table linked to the problem Examples 2023-12-31T17:00:00Z
	Solution Drafting Time SolutionDraftingTime	Time between identifying root cause and proposing a solution.
Description Tracks the duration of the design phase where a fix is developed after the cause is known. This supports the 'Solution Drafting and Fix Application' dashboard. Why it matters Isolates the 'fix design' phase performance. Where to get Calculated: Timestamp of 'Proposed Solution Drafted' minus 'Root Cause Identified' Examples 2 days4 hours
	Workaround Published WorkaroundPublished	Indicates if a workaround has been documented and shared.
Description A boolean or status flag indicating whether a temporary fix has been identified and published to the Knowledge Base or Known Error Database. This supports the 'Workaround Publication Performance' dashboard. Why it matters Critical for measuring how quickly business impact is mitigated before a final fix. Where to get ServiceNow 'problem' table, field 'work_around' (presence of text) or specific status Examples truefalse

Required Recommended Optional

Problem Management Activities

These are the key process steps and lifecycle milestones to capture in your event log for accurate discovery of your problem workflows.

8 Recommended 6 Optional

	Activity	Description
	Assigned to Support Group	The routing of the problem record to a specific technical team for investigation. This activity tracks the flow of ownership and is critical for analyzing handoffs.
Why it matters Essential for the Support Group Reassignment Analysis dashboard to identify ping-pong effects and bottlenecks between teams. Where to get ServiceNow 'sys_audit' or 'sys_history_line' table tracking changes to the 'assignment_group' field. Capture Compare status field before/after Event type inferred
	Change Request Initiated	The association of a Change Request (RFC) to the Problem Record. This signifies the handoff from Problem Management to Change Management for implementation.
Why it matters Vital for the Change Request Transition Efficiency dashboard. Identifies delays between finding a fix and starting the change process. Where to get ServiceNow 'sys_audit' table tracking the population of the 'rfc' reference field on the 'problem' table. Capture Logged when transaction Create Normal Change executed Event type explicit
	Investigation Commenced	The transition of the problem record state from 'New' to 'Assess' or 'Root Cause Analysis'. It signifies that an analyst has actively begun working on the issue.
Why it matters Marks the end of the initial queue wait time and the start of the active investigation phase, supporting Pending State analysis. Where to get ServiceNow 'sys_audit' table tracking changes to the 'state' field (e.g., value moving to 102 or 103 depending on configuration). Capture Compare status field before/after Event type inferred
	Permanent Fix Applied	The moment the problem is marked as Resolved, often triggered by the closure of the associated Change Request. This indicates the technical work is complete.
Why it matters Determines the end of the active fix cycle. Used to calculate the total resolution time relative to the SLA. Where to get ServiceNow 'sys_audit' table, 'state' field transition to 'Resolved' (value 106 typical). Capture Compare status field before/after Event type inferred
	Problem Record Closed	The final lifecycle event where the record becomes inactive. No further work is expected.
Why it matters The definitive end point for the process instance. Required for calculating total cycle time. Where to get ServiceNow 'problem' table, 'closed_at' field or 'state' transition to 'Closed'. Capture Logged when transaction Close Problem executed Event type explicit
	Problem Record Created	The initial creation of a problem record in the ServiceNow system. This marks the beginning of the problem management lifecycle and sets the baseline timestamp for aging metrics.
Why it matters Establishes the start time for all cycle time calculations and SLA measurements. It is the primary anchor for identifying the volume of incoming problem investigations. Where to get ServiceNow 'problem' table, 'sys_created_on' field. Capture Logged when transaction New Record executed Event type explicit
	Root Cause Identified	The point where the 'Root Cause' codes are populated or the state moves to 'Fix in Progress'. This represents the successful diagnosis of the problem.
Why it matters Calculates Mean Time to Root Cause and supports the Root Cause Investigation Cycle Time dashboard. It is a major process milestone. Where to get ServiceNow 'sys_audit' table tracking changes to 'root_cause' category field or transition to 'Fix in Progress' state. Capture Compare status field before/after Event type inferred
	Workaround Identified	The act of entering text into the 'Workaround' field on the problem record. This captures the moment a temporary solution is documented by the analyst.
Why it matters Supports the Workaround Publication Performance dashboard by establishing when the technical solution was first known. Where to get ServiceNow 'sys_audit' table where 'fieldname' is 'workaround'. Capture Compare status field before/after Event type inferred
	Assessment Rejected	The problem record is returned to a previous state or cancelled during the assessment phase because it was not a valid problem.
Why it matters Identifies waste in the process where incidents are incorrectly promoted to problems. Where to get ServiceNow 'sys_audit' table, 'state' field transition to 'Closed/Cancelled' or 'New' from 'Assess'. Capture Compare status field before/after Event type inferred
	Post-Implementation Review Completed	The completion of the PIR task or the setting of a PIR flag. This confirms that a retrospective analysis was performed for major problems.
Why it matters Directly supports the Post-Implementation Review Compliance dashboard. Missing this activity on high-priority problems indicates compliance failure. Where to get ServiceNow 'problem_task' table (type=PIR) closure, or 'problem' table 'pir_state' field change. Capture Derive from comparing field X to Y Event type inferred
	Proposed Solution Drafted	The entry of data into the 'Fix Notes' or 'Resolution Code' fields. Indicates the analyst has moved from understanding the cause to designing the permanent fix.
Why it matters Supports the Solution Drafting and Fix Application dashboard by isolating the design phase duration. Where to get ServiceNow 'sys_audit' table tracking updates to the 'fix_notes' field. Capture Compare status field before/after Event type inferred
	Resolution Verified	A validation step where the resolution is confirmed to be effective. This may be a specific state or a checkbox depending on process maturity.
Why it matters Ensures quality control before closure. Bypassing this step allows for Process Deviation analysis. Where to get ServiceNow 'sys_audit' table. Can be a state change (Resolved -> Closed) or a specific field 'u_resolution_verified' if configured. Capture Compare status field before/after Event type inferred
	State Changed to Fix in Progress	The record transitions to a state indicating a fix is being built or deployed (often waiting on Change Management).
Why it matters Differentiates active investigation time from 'waiting for change' time, refining bottleneck analysis. Where to get ServiceNow 'sys_audit' table, 'state' field transition to 'Fix in Progress' (value 104 typical). Capture Compare status field before/after Event type inferred
	Workaround Published	The execution of the 'Communicate Workaround' action, which pushes the workaround to related incidents or creates a Known Error article. This is distinct from simply typing the workaround.
Why it matters Critical for measuring the speed of knowledge sharing. Delays here directly impact the recurring incident volume. Where to get ServiceNow 'sys_journal_field' or specific UI Action logs; can also be inferred if a 'kb_knowledge' record of type 'Known Error' is created. Capture Logged when transaction Communicate Workaround executed Event type explicit

Recommended Optional

Extraction Guides

How to get your data from ServiceNow Problem Management

Steps

Prerequisites: Ensure you have a functional MID Server configured with appropriate access to a target file share. You will need the export_set_admin or admin role in ServiceNow.
Navigate to Export Sets: In the ServiceNow application navigator, go to System Export Sets > Create an Export Set. This is where you define the overall export job.
Configure the Export Set:
- Name: Give it a descriptive name, such as ProcessMind Problem Management Log.
- Table: Select the Problem [problem] table.
- File format: Choose CSV.
- MID Server: Select the pre-configured MID Server that will receive the file.
Define Export Target and File Path: In the 'Export Target' related list at the bottom of the form, click New.
- Name: Give the target a name, for example, Problem Log CSV Target.
- File path: Specify the full path on the MID server where the file should be saved, for example, /opt/servicenow/mid/exports/problem_log.csv.
Create a Transform Map: Navigate to System Import Sets > Administration > Transform Maps and click New. This map will control the script-based generation of the event log.
- Name: Name it ProcessMind Problem Event Log Transform.
- Source table: Leave this blank.
- Target table: Select Problem [problem].
- Run business rules: Uncheck this box.
- Enforce mandatory fields: Select No.
- Copy empty fields: Check this box.
- Save the record.
Configure the Transform Script: In the Transform Map you just created, go to the 'Transform Scripts' related list and click New.
- When: Select OnBefore.
- Script: Paste the complete script provided in the 'query' section of this document. This script transforms each Problem record into multiple event log rows. Save the script.
Associate the Transform Map: Return to your Export Set record. In the Post-export script field, add the script to link your transform map. The specific script to call a transform can vary, but typically this is handled by the Export Definition's script field. For this approach, we will place the logic directly into the Export Definition's script context.
Configure the Export Definition: In your Export Set record, go to the 'Export Definitions' related list and click New. This defines what data is selected and transformed.
- Name: Problem Base and Events.
- Export set: Should be pre-filled with your set.
- Use Script field: Check this box.
- Script: Paste the complete script from the 'query' section below into this field. This script will query the problem table, generate all activities, and output them to the CSV file.
Set Export Filters (Optional but Recommended): On the Export Set record, define filters to limit the data volume. For example, add a filter for Created on at or after [Start Date] and Created on at or before [End Date] to select problems from the last 6 months.
Schedule the Export: On the Export Set record, fill in the scheduling fields (Run as, Run, Time, etc.) to automate the export. A weekly or monthly run is common. Or, you can execute it manually by clicking the Export UI Action.
Retrieve the File: After the export runs successfully, the generated CSV file will be located on the specified MID Server path. Retrieve this file for upload into your process mining tool.
Prepare for Upload: Ensure the CSV file has the headers: ProblemNumber, Activity, EventTime, SourceSystem, LastDataUpdate, AssignedTo, AssignmentGroup, ProblemState, Priority, RootCauseCategory, ReassignmentCount, RelatedIncidentCount. Upload the final file to ProcessMind.

Configuration

MID Server: A MID Server is required. The server must have network access to a file share or directory where it can write the output CSV file.
ServiceNow Roles: The user configuring the export set needs the export_set_admin or admin role.
Date Range Filtering: It is critical to apply a filter on the problem table, typically on the sys_created_on field. Extracting all historical data can be extremely slow and resource-intensive. A rolling 6-12 month window is recommended for initial analysis.
Table Selection: The primary table for the export set is Problem [problem]. The provided script then queries the Metric Instance [metric_instance] table to derive the specific activities and timestamps.
State Model Customization: The script relies on standard Problem Management state values like 'Assess', 'Root Cause Analysis', 'Fix in Progress', 'Resolved', and 'Closed'. If your organization uses custom states, you must update the script to reflect your specific state model.
Performance: The metric_instance table can be very large. The script is optimized to perform targeted queries for each problem record, but exporting a large number of problems can still impact instance performance. Schedule exports during off-peak hours.

a Sample Query config

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'));
    }
}

Steps

Install ServiceNow ODBC Driver: Download the appropriate 32-bit or 64-bit ServiceNow ODBC driver from the ServiceNow developer portal or support site. Follow the installation wizard to install it on the client machine where you will run the SQL queries.
Configure ODBC DSN: Open the ODBC Data Source Administrator on your client machine. Create a new System DSN (Data Source Name) using the ServiceNow driver. You will need to provide your ServiceNow instance URL (e.g., https://yourinstance.service-now.com), a dedicated username, and password.
Test Connection: Use the 'Test connection' feature in the DSN configuration window to ensure your credentials and instance URL are correct. A successful connection is required before proceeding.
Prepare SQL Client: Open your preferred SQL client tool, such as DBeaver, SQL Server Management Studio, or a scripting environment like Python with an ODBC library. Connect to the DSN you configured in the previous step.
Review and Customize SQL Query: Copy the provided SQL query. Before executing, you must replace the placeholder values. Pay close attention to the date range filters ([Your Start Date], [Your End Date]) and any system specific state values (e.g., [Your State Value for New]). Consult your ServiceNow administrator to get the correct integer or string values for your instance's problem states.
Execute Query: Run the customized SQL query in your client tool. The execution time will vary depending on the date range and the size of your ServiceNow instance's problem and audit tables.
Initial Validation: Once the query completes, inspect the first few hundred rows of the result set. Check that columns like ProblemNumber, Activity, and EventTime are populated as expected and that different activities are present.
Export Data to CSV: Export the entire result set from your SQL client to a CSV file. Ensure the file is UTF-8 encoded to prevent character issues.
Format for ProcessMind: Open the CSV file and verify that the column headers match the required attributes (ProblemNumber, Activity, EventTime, etc.). Confirm that the EventTime column is in a consistent date and time format (e.g., YYYY-MM-DD HH:MI:SS). The file is now ready for upload.

Configuration

ServiceNow User Permissions: The user account configured in the ODBC DSN must have read access (ACLs) to the problem, sys_audit, and related task tables. A user with the itil or a dedicated problem_reader role is typically sufficient.
Date Range Filtering: It is critical to apply a date range filter in the query's WHERE clause to limit the amount of data processed. We recommend extracting 3 to 6 months of data for an initial analysis to ensure manageable performance.
State Value Configuration: ServiceNow uses internal integer values to represent different states. The provided query uses placeholders like [Your State Value for New]. You must replace these with the correct values from your instance's sys_choice table where name = 'problem' and element = 'state'.
Performance Considerations: Executing queries against the sys_audit table can be resource intensive on your ServiceNow instance. Schedule large extractions during off-peak business hours to minimize performance impact. Avoid extracting excessively long time periods (e.g., multiple years) in a single query.
ODBC Driver Version: Ensure the version of the ODBC driver you install is compatible with your ServiceNow instance version (e.g., Rome, San Diego, Tokyo). Check the ServiceNow product documentation for compatibility information.

a Sample Query sql

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]'

Steps

Prepare Python Environment: Ensure you have Python 3.6 or newer installed. Install the necessary libraries using pip: pip install requests pandas.
Configure ServiceNow Access: Create a dedicated service account in your ServiceNow instance. Grant this account, at a minimum, the itil role and ensure it has read access (ACLs) to the problem table and the sys_audit table. This user will be used for API authentication.
Set Up the Script: Save the provided Python code as a .py file, for example, extract_problems.py. Open the file and modify the configuration section at the top with your ServiceNow instance URL, the username, and the password for the service account. It is recommended to use environment variables or a secrets manager for credentials rather than hardcoding them.
Define Extraction Scope: Adjust the START_DATE and END_DATE variables in the script to define the time period for the extraction. This filters problems based on their creation date.
Execute the Extraction Script: Run the script from your terminal using the command: python extract_problems.py. The script will connect to the ServiceNow API, fetch problem data, and then query the audit logs for each problem.
Monitor API Calls: The script will print progress updates to the console, indicating which batch of problems and audit records it is currently fetching. This can take a significant amount of time for large data sets.
Process Raw Data: The script automatically processes the JSON responses from the API. It identifies specific field changes in the audit log (like a change in the state or assignment_group field) and maps them to the predefined activity names.
Generate Event Log: Once all data is fetched and processed, the script constructs a pandas DataFrame. It creates a distinct row for each identified activity, including the initial 'Problem Record Created' event.
Review the Output: The script saves the final event log as a CSV file named servicenow_problem_event_log.csv in the same directory. Open this file to review the structure and data before uploading.
Prepare for Upload: The generated CSV file is structured with the required columns (ProblemNumber, Activity, EventTime, etc.) and is ready for upload into the ProcessMind platform. No further formatting should be necessary.

Configuration

ServiceNow Instance URL: The full URL of your ServiceNow instance, for example, https://[your-instance].service-now.com.
ServiceNow Credentials: A username and password for a service account with read permissions on the problem and sys_audit tables.
Date Range: The START_DATE and END_DATE variables in the script control the extraction window based on the problem creation date (sys_created_on). For initial analysis, a range of 3 to 6 months is recommended.
API Batch Size: The SYSparm_LIMIT variable sets the number of records to fetch per API call. The default is set to 1000, which is a safe value to avoid timeouts. This can be increased for performance but should not exceed ServiceNow's recommended limits.
Table and Field Mappings: The script is pre-configured for the standard ServiceNow Problem Management application. If you have customized table names or field names, you will need to update the PROBLEM_TABLE, AUDIT_TABLE, and the field name strings within the processing loop of the script.
State Model Customization: The script maps standard ServiceNow problem states ('New', 'Assess', 'Root Cause Analysis', 'Fix in Progress', 'Resolved', 'Closed') to process activities. If your organization uses custom states, you must update the logic in the process_audit_records function to recognize them.

a Sample Query python

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()

Steps

Prerequisites: Ensure you have a functional MID Server configured with appropriate access to a target file share. You will need the export_set_admin or admin role in ServiceNow.
Navigate to Export Sets: In the ServiceNow application navigator, go to System Export Sets > Create an Export Set. This is where you define the overall export job.
Configure the Export Set:
- Name: Give it a descriptive name, such as ProcessMind Problem Management Log.
- Table: Select the Problem [problem] table.
- File format: Choose CSV.
- MID Server: Select the pre-configured MID Server that will receive the file.
Define Export Target and File Path: In the 'Export Target' related list at the bottom of the form, click New.
- Name: Give the target a name, for example, Problem Log CSV Target.
- File path: Specify the full path on the MID server where the file should be saved, for example, /opt/servicenow/mid/exports/problem_log.csv.
Create a Transform Map: Navigate to System Import Sets > Administration > Transform Maps and click New. This map will control the script-based generation of the event log.
- Name: Name it ProcessMind Problem Event Log Transform.
- Source table: Leave this blank.
- Target table: Select Problem [problem].
- Run business rules: Uncheck this box.
- Enforce mandatory fields: Select No.
- Copy empty fields: Check this box.
- Save the record.
Configure the Transform Script: In the Transform Map you just created, go to the 'Transform Scripts' related list and click New.
- When: Select OnBefore.
- Script: Paste the complete script provided in the 'query' section of this document. This script transforms each Problem record into multiple event log rows. Save the script.
Associate the Transform Map: Return to your Export Set record. In the Post-export script field, add the script to link your transform map. The specific script to call a transform can vary, but typically this is handled by the Export Definition's script field. For this approach, we will place the logic directly into the Export Definition's script context.
Configure the Export Definition: In your Export Set record, go to the 'Export Definitions' related list and click New. This defines what data is selected and transformed.
- Name: Problem Base and Events.
- Export set: Should be pre-filled with your set.
- Use Script field: Check this box.
- Script: Paste the complete script from the 'query' section below into this field. This script will query the problem table, generate all activities, and output them to the CSV file.
Set Export Filters (Optional but Recommended): On the Export Set record, define filters to limit the data volume. For example, add a filter for Created on at or after [Start Date] and Created on at or before [End Date] to select problems from the last 6 months.
Schedule the Export: On the Export Set record, fill in the scheduling fields (Run as, Run, Time, etc.) to automate the export. A weekly or monthly run is common. Or, you can execute it manually by clicking the Export UI Action.
Retrieve the File: After the export runs successfully, the generated CSV file will be located on the specified MID Server path. Retrieve this file for upload into your process mining tool.
Prepare for Upload: Ensure the CSV file has the headers: ProblemNumber, Activity, EventTime, SourceSystem, LastDataUpdate, AssignedTo, AssignmentGroup, ProblemState, Priority, RootCauseCategory, ReassignmentCount, RelatedIncidentCount. Upload the final file to ProcessMind.

Configuration

MID Server: A MID Server is required. The server must have network access to a file share or directory where it can write the output CSV file.
ServiceNow Roles: The user configuring the export set needs the export_set_admin or admin role.
Date Range Filtering: It is critical to apply a filter on the problem table, typically on the sys_created_on field. Extracting all historical data can be extremely slow and resource-intensive. A rolling 6-12 month window is recommended for initial analysis.
Table Selection: The primary table for the export set is Problem [problem]. The provided script then queries the Metric Instance [metric_instance] table to derive the specific activities and timestamps.
State Model Customization: The script relies on standard Problem Management state values like 'Assess', 'Root Cause Analysis', 'Fix in Progress', 'Resolved', and 'Closed'. If your organization uses custom states, you must update the script to reflect your specific state model.
Performance: The metric_instance table can be very large. The script is optimized to perform targeted queries for each problem record, but exporting a large number of problems can still impact instance performance. Schedule exports during off-peak hours.

a Sample Query config

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'));
    }
}

Your Problem Management Data Template

Your Problem Management Data Template

Problem Management Attributes

Problem Management Activities

Extraction Guides

ServiceNow Scheduled Export Sets to MID Server

Steps

Configuration

a Sample Query config

ServiceNow ODBC Driver SQL Data Extraction

Steps

Configuration

a Sample Query sql

Python Extraction via ServiceNow Table REST API

Steps

Configuration

a Sample Query python

ServiceNow In-Platform Process Mining Configuration

Steps

Configuration

a Sample Query config

Ready to Get Started?

Optimize Problem Management for faster resolution now

Your Problem Management Data Template

Your Problem Management Data Template

Problem Management Attributes

Problem Management Activities

Extraction Guides

ServiceNow Scheduled Export Sets to MID Server

Steps

Configuration

a Sample Query config

ServiceNow ODBC Driver SQL Data Extraction

Steps

Configuration

a Sample Query sql

Python Extraction via ServiceNow Table REST API

Steps

Configuration

a Sample Query python

ServiceNow In-Platform Process Mining Configuration

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Optimize Problem Management for faster resolution now