> Continuous Process Improvement > Incident Management > BMC Helix ITSM > Data Template

Data Template: Incident Management

BMC Helix ITSM

Incident Management Attributes (22) Incident Management Activities (14) Extraction Guides (3)

Your Incident Management Data Template

This template provides a comprehensive guide to collecting the essential data needed for optimizing your Incident Management process. It outlines the crucial attributes and activities to track, along with practical guidance on how to extract this data. Use this resource to streamline your data preparation and accelerate your process analysis journey.

Recommended attributes to collect
Key activities to track for your process
Extraction guidance for your data system

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

Download Template

Incident Management Attributes

These are the recommended data fields to include in your event log for a thorough analysis of your Incident Management process.

3 Required 9 Recommended 10 Optional

	Name	Description
	Incident ID IncidentId	The unique identifier for each incident record.
Description The Incident ID serves as the primary key for each incident, uniquely identifying it from creation to closure. It links all related activities, logs, and changes, allowing for a complete, end-to-end view of the incident's lifecycle. In process mining, this attribute is fundamental as it defines the case. Every event with the same Incident ID is considered part of the same process instance, enabling the reconstruction and analysis of how individual incidents are handled. Why it matters This is the essential case identifier that connects all events in an incident's lifecycle, making end-to-end process analysis possible. Where to get This is the 'Incident Number' field (Field ID: 1000000161) in the 'HPD:Help Desk' form. Examples INC000001234567INC000002345678INC000003456789
	Activity Name ActivityName	The name of the specific event or task that occurred within the incident lifecycle.
Description This attribute describes the activity that was performed at a specific point in time for an incident, such as 'Incident Reported', 'Group Assigned', or 'Incident Resolved'. These activities are the building blocks of the process map. Analyzing the sequence and frequency of these activities reveals the actual process flow, identifies common paths, and highlights deviations from the standard procedure. It is crucial for understanding what actions are taken to resolve an incident. Why it matters Activities define the steps in the process map, allowing for visualization and analysis of the incident management workflow. Where to get Typically derived from changes in the 'Status' (Field ID: 7) and 'Status_Reason' fields in the 'HPD:Help Desk' form, or from the 'HPD:Help Desk Audit Log' form. Examples Incident ReportedGroup AssignedResolution ImplementedIncident Closed
	Event Timestamp EventTimestamp	The exact date and time when the activity occurred.
Description This timestamp records when a specific event in the incident lifecycle happened. It provides the chronological order needed to reconstruct the process flow from raw data. The Event Timestamp is essential for all time-based analysis, including calculating cycle times between activities, identifying bottlenecks where incidents wait for long periods, and measuring overall resolution times. It forms the temporal backbone of the process analysis. Why it matters This timestamp provides the chronological order of events, which is critical for calculating durations, identifying bottlenecks, and understanding the process timeline. Where to get The 'Last Modified Date' (Field ID: 6) or specific date fields from the 'HPD:Help Desk' form. For historical events, this is sourced from the audit log's timestamp field. Examples 2023-10-26T10:00:00Z2023-10-26T10:15:32Z2023-10-27T14:22:05Z
	Assigned Group AssignedGroup	The support group responsible for working on the incident.
Description This attribute identifies the team or department assigned to the incident, such as 'Service Desk', 'Network Team', or 'Database Administrators'. Tracking assignments is key to understanding the flow of work between teams. This attribute is used to analyze inter-team handoffs, identify bottlenecks caused by specific groups, and measure the Incident Reassignment Rate. It helps visualize the path an incident takes across the organization and highlights areas with inefficient routing or knowledge gaps. Why it matters Tracking which group is assigned helps analyze handoffs, identify reassignment loops, and pinpoint bottlenecks within specific teams. Where to get This is the 'Assigned Group' field (Field ID: 1000000217) in the 'HPD:Help Desk' form. Examples Service DeskNetwork OperationsApplication Support Tier 2Infrastructure Services
	Assignee Assignee	The individual user assigned to work on the incident.
Description The Assignee is the specific support agent or technician responsible for the incident at a given time. This provides a more granular level of detail than the Assigned Group. Analyzing performance by assignee can help identify top performers, agents who may need additional training, and workload distribution imbalances. It is also used to trace the exact sequence of actions taken by individuals working on a complex incident. Why it matters Provides a granular view of workload distribution and individual performance, helping to identify top performers or agents needing support. Where to get This is the 'Assignee' field (Field ID: 1000000218) in the 'HPD:Help Desk' form. Examples Bob SmithAlice JohnsonCharlie Brown
	Incident Category IncidentCategory	The classification of the incident, often in a tiered structure.
Description Incident categorization provides a structured way to classify incidents, typically using a multi-level hierarchy (e.g., Tier 1: Hardware, Tier 2: Laptop, Tier 3: Battery). This data is essential for routing, reporting, and trend analysis. In process mining, categorization is used to analyze different types of incidents separately. It supports the Incident Categorization Accuracy dashboard by comparing initial and final categories and helps in identifying trends for the Root Cause Trends dashboard. Why it matters Categorization enables accurate routing, trend analysis, and performance comparison across different types of incidents. Where to get These are the 'Operational Categorization Tier 1/2/3' fields in the 'HPD:Help Desk' form. Examples Hardware > Laptop > BatterySoftware > Enterprise App > Login ErrorNetwork > Connectivity > Wi-Fi
	Incident Resolution Time IncidentResolutionTime	The total time elapsed from when an incident was first reported until it was resolved.
Description This is a calculated metric representing the duration between the 'Incident Reported' and 'Incident Resolved' activities. It is one of the most common and important KPIs in incident management. This metric directly measures the efficiency of the resolution process. In process mining, it is used as a primary performance indicator, analyzed across different incident categories, priorities, and teams to identify areas for improvement and to track the impact of process changes over time. Why it matters This is a critical KPI that measures the end-to-end efficiency of the incident management process, directly impacting user satisfaction. Where to get Calculated by subtracting the timestamp of the first event from the timestamp of the 'Incident Resolved' event for each Incident ID. Examples 2592003600864000
	Incident Status IncidentStatus	The current or historical status of the incident at the time of the event.
Description This attribute indicates the state of the incident, such as 'New', 'In Progress', 'Pending', 'Resolved', or 'Closed'. It provides a snapshot of where the incident is in its lifecycle. Analyzing status changes is fundamental to understanding the incident management process. It is used to define activities, measure time spent in different states (e.g., how long incidents are 'Pending'), and identify incidents that might be stuck or inactive. Why it matters Tracking status changes is key to understanding the incident's progress and measuring how long it spends in specific states like 'Pending' or 'In Progress'. Where to get This is the 'Status' field (Field ID: 7) in the 'HPD:Help Desk' form. Examples NewAssignedIn ProgressPendingResolvedClosed
	Is Reopened IsReopened	A flag indicating if an incident was reopened after being set to a 'Resolved' status.
Description This boolean attribute is true if an incident's status changed back to an active state (like 'In Progress') after it had already been marked as 'Resolved'. This indicates that the initial fix was not effective or complete. This is a direct measure of rework and is used to calculate the 'Incident Rework Rate' KPI. Analyzing reopened incidents helps identify poor-quality fixes, insufficient testing, or recurring problems that were not properly addressed, supporting the Rework and Escalation Paths dashboard. Why it matters Directly measures rework and the quality of resolutions. A high reopen rate points to ineffective fixes and process weaknesses. Where to get Calculated by analyzing the sequence of activities for an incident. If an 'Incident Reopened' or similar activity appears after a 'Resolution Implemented' activity, this flag is set to true. Examples truefalse
	Is SLA Breached IsSlaBreached	A flag indicating whether the incident was resolved after its SLA target date.
Description This boolean attribute is true if the incident's resolution time exceeded its defined SLA. It provides a clear, binary outcome for SLA performance on each incident. This flag is essential for creating the Incident SLA Performance Overview dashboard and calculating the 'Incident SLA Compliance Rate' KPI. It simplifies analysis by allowing direct filtering and aggregation of all incidents that failed to meet their service level targets, making it easy to investigate the reasons for breaches. Why it matters This flag simplifies SLA compliance analysis, making it easy to filter for all breached incidents and investigate the root causes. Where to get Calculated by comparing the 'Incident Resolved' event timestamp to the 'SlaTargetDate'. If the resolution timestamp is after the target date, this flag is true. Examples truefalse
	Priority Priority	The priority level assigned to the incident, determining its handling urgency.
Description Priority is typically derived from the combination of impact and urgency and dictates the order and speed of incident resolution. Common values range from 'Critical' to 'Low'. In process mining, analyzing incidents by priority is crucial for performance analysis. It helps answer questions like, 'Are we meeting SLAs for high-priority incidents?' and 'Do low-priority incidents experience longer delays?'. Filtering by priority allows for focused analysis on the most critical business issues. Why it matters This attribute is essential for segmenting analysis to ensure high-priority incidents are handled faster and meet their specific service level targets. Where to get This is the 'Priority' field (Field ID: 1000000164) in the 'HPD:Help Desk' form. Examples CriticalHighMediumLow
	Service Service	The business or technical service affected by the incident.
Description This attribute links an incident to a specific service defined in the Configuration Management Database (CMDB), such as 'Email Service', 'VPN Access', or 'SAP Financials'. This linkage is critical for understanding the business impact of incidents. Analyzing incidents by service helps identify problematic services that generate a high volume of incidents, highlights recurring issues tied to specific technologies, and supports trend analysis for problem management. Why it matters Connecting incidents to business services is crucial for impact analysis and identifying which services are most prone to issues. Where to get This is the 'ServiceCI' or a similar field representing the affected Configuration Item (CI) in the 'HPD:Help Desk' form. Examples Corporate EmailSAP ERPRemote Access VPNHuman Resources Portal
	Channel Channel	The method used to report the incident.
Description This attribute specifies how the incident was submitted, for instance, via phone call, email, self-service portal, or direct walk-up. It indicates the entry point of the incident into the support process. Analyzing incidents by channel can reveal which channels are most effective or which generate incidents that are easier or harder to resolve. It can also inform decisions about where to invest in automation or user training, for example, by promoting the use of a self-service portal that captures better initial data. Why it matters Understanding the submission channel helps analyze the efficiency of different intake methods and can guide investments in self-service or automation. Where to get This is the 'Reported Source' field (Field ID: 1000000215) in the 'HPD:Help Desk' form. Examples EmailPhoneSelf ServiceDirect Input
	Close Code CloseCode	The code selected upon closing an incident, indicating the resolution outcome.
Description The Close Code provides a structured summary of how an incident was resolved. Examples include 'Resolved by User', 'No Fault Found', 'Duplicate Incident', or 'Permanent Fix Applied'. This attribute is valuable for analyzing resolution effectiveness and outcomes. It can help identify incidents that were closed without a real fix or highlight categories where users frequently resolve their own issues, which could indicate opportunities for better knowledge base articles or self-service tools. Why it matters Provides structured data on resolution outcomes, helping to analyze the effectiveness of fixes and identify trends in how incidents are closed. Where to get This is typically part of the resolution or closure information on the 'HPD:Help Desk' form. Examples Resolved RemotelyDuplicate IssueUser ErrorNo Action Required
	Impact Impact	The measure of the incident's effect on business processes.
Description Impact assesses the extent of an incident's adverse effect on the business. It is often defined on a scale, such as 'Extensive/Widespread', 'Significant/Large', 'Moderate/Limited', or 'Minor/Localized'. Combined with Urgency, Impact determines the incident's Priority. Analyzing by impact helps to understand which incidents cause the most business disruption, regardless of their technical complexity. This is key for prioritizing process improvement efforts. Why it matters Helps quantify the business severity of an incident, which is a key component in determining priority and focusing analysis on high-impact issues. Where to get This is the 'Impact' field (Field ID: 1000000163) in the 'HPD:Help Desk' form. Examples 1-Extensive/Widespread2-Significant/Large3-Moderate/Limited4-Minor/Localized
	Last Data Update LastDataUpdate	The timestamp indicating when the data for this event was last refreshed from the source system.
Description This attribute records the date and time of the most recent data extraction. It provides context on the freshness of the data being analyzed, which is important for understanding how current the process insights are. Knowing the last update time is vital for reporting and dashboarding, as it informs users about the data's timeliness and helps manage expectations about the inclusion of the very latest incident activities. Why it matters Indicates the freshness of the data, ensuring users understand how current the process analysis and any resulting insights are. Where to get This value is typically generated and stamped on the dataset during the data extraction (ETL) process. Examples 2023-11-01T02:00:00Z2023-11-02T02:00:00Z2023-11-03T02:00:00Z
	Reassignment Count ReassignmentCount	The total number of times an incident was reassigned to a different group.
Description This metric counts how many times the 'AssignedGroup' field changed during the incident's lifecycle. A high count suggests issues with initial routing, a lack of first-contact resolution, or complex issues requiring input from multiple teams. This attribute directly supports the 'Incident Reassignment Rate' KPI and the 'Incident Reassignment Cycle Analysis' dashboard. It helps quantify the 'ping-pong' effect where tickets are passed back and forth between teams, leading to significant delays and process inefficiency. Why it matters Quantifies inefficient routing and handoffs, helping to identify incidents that get stuck in reassignment loops between teams. Where to get Calculated by counting the number of times the 'AssignedGroup' value changes for a given Incident ID in the event log. Examples 0135
	Resolution Description Resolution	A free-text description of the steps taken to resolve the incident.
Description This field contains the detailed, human-written summary of the final resolution. It explains what was done to fix the issue and restore service to the user. While unstructured, this text data can be analyzed using text mining techniques to identify common resolution patterns, extract keywords related to specific problems, or supplement root cause analysis. It provides qualitative context that structured data fields often lack. Why it matters Offers qualitative details about the resolution, which can be used in text mining to find patterns not visible in structured data. Where to get This is the 'Resolution' field (Field ID: 1000000156) in the 'HPD:Help Desk' form. Examples User's password was reset via Active Directory.Cleared cache and cookies in browser, which resolved the login issue.Rebooted network switch in IDF closet 3B.
	Root Cause RootCause	The underlying reason or ultimate cause of the incident.
Description The Root Cause is the fundamental issue that, if addressed, would prevent the incident from recurring. This is often identified as part of a related problem investigation process. While not all incidents will have a documented root cause, analyzing this attribute is critical for proactive problem management. It supports the 'Root Cause Identification Rate' KPI and helps in creating dashboards that track trends in recurring issues, guiding efforts to implement permanent solutions. Why it matters Identifying the root cause is essential for problem management and for analyses aimed at reducing recurring incident volume. Where to get This information might be in a dedicated 'Root Cause' field on the incident or, more commonly, on a linked 'PBI:Problem Investigation' form. Examples Insufficient disk space on serverNetwork configuration errorSoftware bug in version 2.1Expired security certificate
	SLA Target Date SlaTargetDate	The date and time by which the incident is expected to be resolved according to its SLA.
Description This attribute stores the deadline for incident resolution as defined by the applicable Service Level Agreement (SLA). It is calculated based on the incident's priority and the defined service hours. This timestamp is the benchmark against which actual resolution time is measured. It is essential for calculating the 'Incident SLA Compliance Rate' KPI and for building dashboards that visualize SLA performance. It allows for proactive monitoring of incidents approaching their deadline. Why it matters This is the benchmark for measuring SLA compliance. It enables the calculation of whether an incident was resolved on time or breached its agreement. Where to get This data is typically stored in the 'SLM:Measurement' form and linked to the incident. It is not a direct field on 'HPD:Help Desk'. Examples 2023-10-26T14:00:00Z2023-10-27T09:00:00Z2023-11-01T17:00:00Z
	Source System SourceSystem	The system from which the incident data was extracted.
Description This attribute identifies the origin of the data, which is especially useful in environments with multiple ITSM tools or integrated systems. It confirms that the data comes from the expected source, such as a specific BMC Helix ITSM instance. In analysis, it helps differentiate processes or data characteristics that might vary between production, development, or legacy systems, ensuring data lineage is clear and trustworthy. Why it matters Identifies the data's origin, which is crucial for data validation and for managing analyses across multiple integrated systems. Where to get This is usually a static value added during the data extraction, transformation, and loading (ETL) process. Examples BMCHelixITSM_ProdITSM-EU-InstanceServiceManagement-APAC
	Submitter Submitter	The person who initially reported the incident.
Description The Submitter is the end-user or customer who experienced the issue and reported it. This is distinct from the assignee, who works on the incident. Analyzing data by submitter or their department can help identify specific user groups that may require more training or groups that are impacted by a particular type of issue. It provides a customer-centric view of the incident management process. Why it matters Identifies the user who reported the issue, allowing for analysis based on user department, location, or role to find user-specific trends. Where to get This information is captured in the 'Submitter' field or fields related to the customer's information on the 'HPD:Help Desk' form. Examples John DoeJane SmithPeter Jones

Required Recommended Optional

Incident Management Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and flow visualization.

5 Recommended 9 Optional

	Activity	Description
	Group Assigned	This activity marks the initial assignment of the incident to a specific support group for investigation. It is inferred from the first time the 'Assigned Group' field is populated after the incident's creation.
Why it matters This is a key milestone that signifies the start of active work. Tracking the time to first assignment is critical for evaluating response times and initial routing efficiency. Where to get Inferred from the audit log ('HPD:HelpDesk_AuditLogSystem') for the 'HPD:Help Desk' form, capturing the first population of the 'Assigned Group' field. Capture From the timestamp when the 'Assigned Group' field is first populated. Event type inferred
	Incident Closed	This is the final activity, marking the formal closure of the incident record after resolution has been confirmed or a confirmation period has passed. It is captured when the status is set to 'Closed'.
Why it matters This is the definitive end event for the incident lifecycle. The time between 'Resolved' and 'Closed' represents the user confirmation and administrative wrap-up period. Where to get This event corresponds to the 'Status' field on the 'HPD:Help Desk' form being set to 'Closed'. The timestamp is recorded in the 'Closed Date' field and the audit log. Capture From the 'Closed Date' timestamp or the status change to 'Closed'. Event type inferred
	Incident Reported	This activity marks the initial creation of the incident record in the system. It is captured explicitly from the creation timestamp of the incident in the core incident management form.
Why it matters This is the primary start event for the incident lifecycle. It is essential for calculating overall resolution times and understanding incident arrival rates. Where to get This event corresponds to the record creation in the 'HPD:Help Desk' form. The timestamp is typically taken from the 'Submit Date' or 'Reported Date' field. Capture From the 'Submit Date' timestamp on the HPD:Help Desk form. Event type explicit
	Incident Resolved	This activity marks the official resolution of the incident from the service desk's perspective, before final closure. It is captured when the incident's status is set to 'Resolved'.
Why it matters This is the most important milestone for measuring SLA compliance and resolution time. It signifies that the service has been restored for the user. Where to get This event corresponds to the 'Status' field on the 'HPD:Help Desk' form being set to 'Resolved'. The timestamp is captured in the 'Last Resolved Date' field and the audit log. Capture From the timestamp of the status change to 'Resolved' in the audit log. Event type inferred
	Investigation Initiated	Indicates that a support agent has begun actively working on the incident. This is typically inferred by a status change from 'Assigned' to 'In Progress'.
Why it matters This milestone marks the transition from waiting in a queue to active diagnosis. Analyzing the time spent waiting for investigation to start helps identify resource bottlenecks and supports the 'Diagnosis & Investigation Bottlenecks' dashboard. Where to get Inferred from a status change on the 'HPD:Help Desk' form. The event is triggered when the 'Status' field changes to 'In Progress', with the timestamp captured from the audit log. Capture Inferred from status change to 'In Progress' in the HPD:HelpDesk_AuditLogSystem. Event type inferred
	Awaiting User Confirmation	Occurs after a resolution has been implemented and the support team is waiting for the user to confirm the fix is successful. This is often represented by the 'Resolved' status itself, where the clock for auto-closure starts.
Why it matters This activity is crucial for the 'User Confirmation & Verification Delays' dashboard. It helps quantify delays between providing a fix and getting user validation, which can artificially extend the incident lifecycle. Where to get This state begins when the 'Status' on the 'HPD:Help Desk' form moves to 'Resolved'. The duration is measured from the 'Last Resolved Date' until the incident is either 'Closed' or 'Reopened'. Capture Begins upon status change to 'Resolved', ends upon status change to 'Closed' or 'In Progress'. Event type inferred
	Incident Cancelled	This activity represents the termination of an incident that was created in error or is no longer relevant. It is captured when the incident's status is set to 'Cancelled'.
Why it matters This is a terminal end state for an incident, distinct from a successful resolution. Analyzing cancelled incidents can highlight issues with incident creation channels or duplicate reporting. Where to get Inferred from the 'HPD:Help Desk' form when the 'Status' field is updated to 'Cancelled'. The timestamp is captured in the audit log. Capture Inferred from status change to 'Cancelled' in the audit log. Event type inferred
	Incident Categorized	Represents the point where the incident has been classified with operational and product categorizations, and a priority has been set. This is typically inferred from the population or last modification of categorization fields.
Why it matters Accurate and timely categorization is crucial for efficient routing and reporting. Analyzing this activity helps identify delays or inaccuracies in the initial triage process, supporting the 'Incident Categorization Accuracy' dashboard. Where to get Inferred from the audit log ('HPD:HelpDesk_AuditLogSystem') tracking changes to fields like 'Operational Categorization Tier 1-3', 'Product Categorization Tier 1-3', and 'Priority' on the 'HPD:Help Desk' form. Capture From the timestamp of the last update to categorization or priority fields after creation. Event type inferred
	Incident Reopened	Represents an incident that was previously marked as resolved but has been reactivated because the issue persists. This is inferred from a status change from 'Resolved' back to an active state like 'In Progress' or 'Assigned'.
Why it matters This activity directly measures rework and the effectiveness of initial solutions. A high volume of reopened incidents is a key indicator of poor fix quality and supports the 'Incident Rework Rate' KPI. Where to get Inferred from the audit log ('HPD:HelpDesk_AuditLogSystem') by detecting a 'Status' change from 'Resolved' to 'In Progress' or 'Assigned' for a given Incident ID. Capture Inferred from status transition from 'Resolved' to an active state. Event type inferred
	Pending Customer Input	Represents a point where the incident progression is paused while awaiting information or action from the user. This is inferred from a status change to a 'Pending' state.
Why it matters This activity helps separate agent work time from customer wait time. Analyzing time spent in this state is crucial for understanding how user response delays impact overall resolution times. Where to get Inferred from the 'HPD:Help Desk' form when the 'Status' field is updated to 'Pending'. The specific reason is often found in the 'Status_Reason' field. Capture Inferred from status change to 'Pending' in the HPD:HelpDesk_AuditLogSystem. Event type inferred
	Resolution Implemented	This activity signifies that a fix or workaround has been applied by the support team. This is often inferred when the incident status moves to 'Resolved'.
Why it matters This is a critical milestone indicating a solution has been delivered. It is a key data point for measuring the time to apply a fix after investigation is complete. Where to get This is typically inferred when the 'Status' on the 'HPD:Help Desk' form is changed to 'Resolved'. The timestamp is captured in the 'Last Resolved Date' field and the audit log. Capture From the 'Last Resolved Date' timestamp or status change to 'Resolved'. Event type inferred
	SLA Breach	This is a calculated event that occurs when the time to resolve an incident exceeds its defined Service Level Agreement target. It is derived by comparing the resolution timestamp against the SLA due date.
Why it matters This activity is fundamental for the 'Incident SLA Performance Overview' dashboard and 'Incident SLA Compliance Rate' KPI. It directly flags incidents that failed to meet service commitments. Where to get Calculated by comparing the 'Last Resolved Date' to the 'Target Date' (SLA Due Date) on the 'HPD:Help Desk' form. If the incident is not yet resolved, it can be calculated against the current time. Capture Calculated event: occurs if 'Last Resolved Date' > 'Target Date'. Event type calculated
	Transferred To Another Group	This activity occurs when an incident is reassigned from one support group to another. It is inferred by detecting a change in the 'Assigned Group' field after the initial assignment.
Why it matters Frequent transfers indicate incorrect initial routing or knowledge gaps. Tracking this activity is essential for the 'Incident Reassignment Cycle Analysis' dashboard and the 'Incident Reassignment Rate' KPI. Where to get Inferred from the audit log ('HPD:HelpDesk_AuditLogSystem') by identifying subsequent changes to the 'Assigned Group' field on the 'HPD:Help Desk' form after it was first populated. Capture Identify changes to the 'Assigned Group' field after the initial assignment. Event type inferred
	User Confirmation Received	Represents the user actively confirming that the provided solution has resolved their issue. This may be an explicit event or inferred from notes or a related system action before closure.
Why it matters Tracking this provides a more accurate picture of the user validation process than just waiting for auto-closure. It helps measure the 'Average User Confirmation Time' KPI and identify communication gaps. Where to get This can be difficult to capture reliably. It might be inferred from a work log entry or a specific status reason update just before the incident is moved to 'Closed'. It may require analysis of the 'HPD:WorkLog' form. Capture Inferred from specific work log entries or status reason updates prior to closure. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from BMC Helix ITSM

Steps

Access Smart Reporting: Log in to your BMC Helix ITSM environment. From the main application menu, navigate to Reports to launch Smart Reporting.
Create a New Report: Once in Smart Reporting, click the Create button and select Report. Choose the Advanced Report option to gain access to more complex features like Freehand SQL.
Select Data Source: When prompted, choose your primary ITSM data source. Select the Incident Management view, which is the semantic layer built on top of the core incident forms.
Use Freehand SQL: In the report builder, instead of dragging and dropping fields, navigate to the data source options and select Freehand SQL. This will open an editor where you can write a custom SQL query.
Enter the SQL Query: Copy the complete SQL query provided in the query section of this document and paste it into the Freehand SQL editor. You must replace the placeholder values for [START_DATE], [END_DATE], and [YOUR_COMPANY_NAME] with your specific requirements.
Validate and Preview Data: Use the validation or preview feature in the SQL editor to check for syntax errors and see a sample of the output. This ensures the query runs correctly against your environment before you build the full report.
Format the Report: Once the query is validated, proceed to the report design step. The columns defined in the SQL query will be available as fields. Ensure all columns (IncidentId, ActivityName, EventTimestamp, etc.) are included in your report table.
Apply Report Filters (Optional): While the main date filter is in the SQL query, you can add further interactive filters at the report level if needed, such as filtering by Priority or AssignedGroup.
Run and Save the Report: Execute the report to generate the full dataset. Once you are satisfied with the results, save the report with a descriptive name like "Incident Management Event Log".
Export to CSV: With the report results on screen, use the Export function. Select CSV as the export format. This will download the complete event log file to your local machine.
Schedule the Export (Optional): For ongoing analysis, you can schedule the report to run automatically. In the report's distribution settings, configure a schedule (e.g., daily, weekly) and set the destination to a file share or email, ensuring the format is CSV.

Configuration

Prerequisites: You must have a BMC Helix account with permissions to access Smart Reporting, create reports, use Freehand SQL, and query the Incident Management data source.
Report Type: An Advanced Report using the Freehand SQL feature is required to construct the event log structure using UNION ALL statements.
Data Source View: Use the standard Incident Management view, which is based on the HPD:Help Desk form and its related joins.
Date Range Filtering: It is critical to set a date range to manage performance. The provided query includes [START_DATE] and [END_DATE] placeholders. We recommend extracting 3 to 6 months of data for a meaningful analysis without overloading the system.
Company Filter: The query includes a placeholder for [YOUR_COMPANY_NAME] to scope the data to a specific operating company. If you operate in a multi-tenant environment, this is essential for accurate reporting.
Performance Considerations: The event log query is complex and joins multiple tables. Running it on large datasets can consume significant system resources. Always schedule large exports during off-peak hours.

a Sample Query config

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Reported' AS "ActivityName",
  hpd."Submit Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Categorized' AS "ActivityName",
  hpd."Last Modified Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."First Assigned Date" IS NOT NULL AND hpd."Operational Categ Tier 1" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT 
  hpd."Incident Number" AS "IncidentId",
  'Group Assigned' AS "ActivityName",
  hpd."First Assigned Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."First Assigned Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  aud."New_Value" AS "ActivityName",
  aud."Audit_Date" AS "EventTimestamp",
  aud."New_Value" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
JOIN HPD_HelpDesk_AuditLogSystem aud ON hpd."Incident Number" = aud."Original_Request_ID"
WHERE aud."Fields_Changed" LIKE '%Status%' AND aud.New_Value IN ('In Progress', 'Pending')
  AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'
  AND aud.New_Value = 'In Progress' AND aud."Audit_Date" = (SELECT MIN(a2."Audit_Date") FROM HPD_HelpDesk_AuditLogSystem a2 WHERE a2."Original_Request_ID" = hpd."Incident Number" AND a2."New_Value" = 'In Progress' AND a2."Fields_Changed" LIKE '%Status%')

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Transferred To Another Group' AS "ActivityName",
  aud."Audit_Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  aud."New_Value" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
JOIN HPD_HelpDesk_AuditLogSystem aud ON hpd."Incident Number" = aud."Original_Request_ID"
WHERE aud."Fields_Changed" LIKE '%Assigned Group%' AND aud."Audit_Date" > hpd."First Assigned Date"
  AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Pending Customer Input' AS "ActivityName",
  aud."Audit_Date" AS "EventTimestamp",
  'Pending' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
JOIN HPD_HelpDesk_AuditLogSystem aud ON hpd."Incident Number" = aud."Original_Request_ID"
WHERE aud."Fields_Changed" LIKE '%Status%' AND aud."New_Value" = 'Pending'
  AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Resolution Implemented' AS "ActivityName",
  hpd."Last Resolved Date" AS "EventTimestamp",
  'Resolved' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Last Resolved Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Awaiting User Confirmation' AS "ActivityName",
  hpd."Last Resolved Date" AS "EventTimestamp",
  'Resolved' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Resolved' AND hpd."Last Resolved Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Reopened' AS "ActivityName",
  hpd."Re-Opened Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  'Yes' AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Re-Opened Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Resolved' AS "ActivityName",
  hpd."Last Resolved Date" AS "EventTimestamp",
  'Resolved' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" >= 'Resolved' AND hpd."Last Resolved Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'User Confirmation Received' AS "ActivityName",
  hpd."Closed Date" AS "EventTimestamp",
  'Closed' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Closed' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'SLA Breach' AS "ActivityName",
  hpd."Estimated Resolution Date" AS "EventTimestamp", -- Represents the SLA due date
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  'Yes' AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."SLM Status" = 'Service Target Breached' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Cancelled' AS "ActivityName",
  hpd."Last Modified Date" AS "EventTimestamp",
  'Cancelled' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Cancelled' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Closed' AS "ActivityName",
  hpd."Closed Date" AS "EventTimestamp",
  'Closed' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Closed' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

Steps

Identify Database Credentials and Schema: Obtain read-only credentials for the underlying BMC Helix ITSM AR System database (e.g., SQL Server, Oracle). You must also identify the specific schema and table names for the incident management form (HPD:Help Desk) and its audit log. The main table is often named like T<form_id> (e.g., T1234), and the audit log is typically named H<form_id> or [form_name]_AuditLogSystem.
Connect to the Database: Use a standard SQL client, such as DBeaver, SQL Server Management Studio (SSMS), or Oracle SQL Developer, to connect to the database using the credentials from the previous step.
Review and Customize the SQL Query: Copy the provided SQL query into your client. Carefully review the placeholders and replace them with the correct values for your environment:
- [Your_HPD_Help_Desk_Table]: Replace with the actual table name for the HPD:Help Desk form.
- [Your_HPD_Help_Desk_Audit_Table]: Replace with the actual table name for the audit log associated with the HPD:Help Desk form.
- 'YYYY-MM-DD' and 'YYYY-MM-DD': Set the start and end dates for the extraction period.
Handle Timestamp Conversion: The provided query uses DATEADD(s, [TimestampColumn], '1970-01-01'), which is specific to SQL Server for converting epoch timestamps. If you are using Oracle or another database, you must adjust this function accordingly. For Oracle, the equivalent is TO_DATE('1970-01-01', 'YYYY-MM-DD') + ( [TimestampColumn] / 86400 ).
Execute the Query: Run the modified query against the database. Depending on the date range and the size of your incident data, this may take several minutes to complete.
Inspect the Results: Once the query finishes, perform a quick review of the output. Check that the columns IncidentId, ActivityName, and EventTimestamp are populated as expected. Look for a variety of activities to ensure the UNION ALL statements are working correctly.
Export Data to CSV: Export the complete result set from your SQL client to a CSV file. Ensure the file uses UTF-8 encoding and includes a header row with the column names.
Prepare for Upload: Before uploading to a process mining tool, ensure the EventTimestamp column is formatted consistently (e.g., YYYY-MM-DD HH:MI:SS). No further data transformation should be necessary if the SQL query ran correctly.

Configuration

Prerequisites: You need read-only user access to the BMC Helix ITSM application database. You also need to know the specific table names for the HPD:Help Desk form and its audit log, which can vary between system installations.
Date Range Filtering: The query includes a WHERE clause in the BaseIncidents CTE to filter by Submit_Date. It is highly recommended to start with a limited date range, such as 3 to 6 months, to manage query performance and data volume. For example: WHERE hpd.Submit_Date >= '2023-01-01' AND hpd.Submit_Date < '2023-07-01'.
Company Filtering: If your ITSM instance is multi-tenant, add a filter for the specific company you want to analyze in the WHERE clause of the BaseIncidents CTE (e.g., AND hpd.Company = '[Your Company Name]').
Performance Considerations: Direct database queries, especially those involving joins on large audit log tables and multiple UNION ALL operations, can be resource-intensive. It is best practice to run large extractions during off-peak hours to avoid impacting system performance.
Timestamp Conversion: The query assumes timestamps are stored in Unix epoch format (seconds since 1970-01-01). Verify this is the case for your system and that the conversion function used in the query is correct for your specific database technology (e.g., SQL Server vs. Oracle).

a Sample Query sql

WITH BaseIncidents AS (
    -- Select the base set of incidents within the desired date range
    SELECT
        hpd.Incident_Number AS IncidentId,
        hpd.Status AS FinalStatus,
        hpd.Priority,
        hpd.Assigned_Group AS FinalAssignedGroup,
        hpd.Assignee AS FinalAssignee,
        hpd.Service_CI AS Service,
        hpd.Categorization_Tier_1 + ' | ' + hpd.Categorization_Tier_2 + ' | ' + hpd.Categorization_Tier_3 AS IncidentCategory,
        DATEDIFF(SECOND, hpd.Submit_Date, hpd.Last_Resolved_Date) AS IncidentResolutionTime,
        CASE WHEN hpd.Last_Resolved_Date > hpd.Target_Date THEN 'true' ELSE 'false' END AS IsSlaBreached,
        hpd.Submit_Date,
        hpd.Last_Resolved_Date,
        hpd.Closed_Date,
        hpd.Target_Date,
        hpd.Entry_ID -- Required for joining with the audit log
    FROM [Your_HPD_Help_Desk_Table] hpd
    WHERE hpd.Submit_Date BETWEEN '[START_DATE_YYYY-MM-DD]' AND '[END_DATE_YYYY-MM-DD]'
),
AuditChanges AS (
    -- Process the audit log to identify key changes in Status and Assigned Group
    SELECT 
        audit.Entry_ID,
        audit.Log_Key_Value,
        audit.Fields_Changed,
        DATEADD(s, audit.Log_Key_Value, '1970-01-01') AS EventTimestamp,
        LAG(audit.Fields_Changed, 1, '') OVER (PARTITION BY audit.Entry_ID, SUBSTRING(audit.Fields_Changed, 1, 8) ORDER BY audit.Log_Key_Value) AS Previous_Value
    FROM [Your_HPD_Help_Desk_Audit_Table] audit
    WHERE 
        -- Field ID 7 is for Status, Field ID 1000000217 is for Assigned Group
        (SUBSTRING(audit.Fields_Changed, 1, 8) = '7       ' OR SUBSTRING(audit.Fields_Changed, 1, 8) = '1000000217')
        AND audit.Entry_ID IN (SELECT Entry_ID FROM BaseIncidents)
),
ReopenedFlag AS (
    -- Identify incidents that have been reopened
    SELECT DISTINCT Entry_ID, 'true' AS IsReopened
    FROM AuditChanges
    WHERE 
        SUBSTRING(Fields_Changed, 1, 8) = '7       ' -- Status field
        AND CHARINDEX('Resolved', Previous_Value) > 0
        AND CHARINDEX('Resolved', Fields_Changed) = 0
)
-- Combine all activities into a single event log
SELECT
    b.IncidentId,
    'Incident Reported' AS ActivityName,
    b.Submit_Date AS EventTimestamp,
    'New' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Incident Categorized' AS ActivityName,
    a.EventTimestamp,
    'Assigned' AS IncidentStatus, -- Inferred status at time of categorization/assignment
    b.Priority,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN (
    -- Find the first assignment event, used as a proxy for categorization
    SELECT Entry_ID, MIN(EventTimestamp) AS FirstAssignmentTime
    FROM AuditChanges
    WHERE SUBSTRING(Fields_Changed, 1, 8) = '1000000217'
    GROUP BY Entry_ID
) first_assign ON b.Entry_ID = first_assign.Entry_ID
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID AND a.EventTimestamp = first_assign.FirstAssignmentTime AND SUBSTRING(a.Fields_Changed, 1, 8) = '1000000217'
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Group Assigned' AS ActivityName,
    a.EventTimestamp,
    'Assigned' AS IncidentStatus,
    b.Priority,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '1000000217' AND a.Previous_Value = ''
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Investigation Initiated' AS ActivityName,
    a.EventTimestamp,
    'In Progress' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE 
    SUBSTRING(a.Fields_Changed, 1, 8) = '7       '
    AND CHARINDEX('Assigned', a.Previous_Value) > 0
    AND CHARINDEX('In Progress', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Transferred To Another Group' AS ActivityName,
    a.EventTimestamp,
    'Assigned' AS IncidentStatus,
    b.Priority,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '1000000217' AND a.Previous_Value <> ''
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Pending Customer Input' AS ActivityName,
    a.EventTimestamp,
    'Pending' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Pending', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

-- Capturing this as 'Resolution Implemented' based on status change
SELECT
    b.IncidentId,
    'Resolution Implemented' AS ActivityName,
    a.EventTimestamp,
    'Resolved' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Resolved', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

-- Capturing 'Awaiting User Confirmation' from the same status change event
SELECT
    b.IncidentId,
    'Awaiting User Confirmation' AS ActivityName,
    a.EventTimestamp,
    'Resolved' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Resolved', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Incident Reopened' AS ActivityName,
    a.EventTimestamp,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    'true' AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE 
    SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' 
    AND CHARINDEX('Resolved', a.Previous_Value) > 0 
    AND CHARINDEX('Resolved', a.Fields_Changed) = 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

-- Using Last_Resolved_Date for the official 'Incident Resolved' activity
SELECT
    b.IncidentId,
    'Incident Resolved' AS ActivityName,
    b.Last_Resolved_Date AS EventTimestamp,
    'Resolved' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.Last_Resolved_Date IS NOT NULL

UNION ALL

-- Inferring 'User Confirmation Received' from the closure event
SELECT
    b.IncidentId,
    'User Confirmation Received' AS ActivityName,
    b.Closed_Date AS EventTimestamp,
    'Closed' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.Closed_Date IS NOT NULL

UNION ALL

SELECT
    b.IncidentId,
    'SLA Breach' AS ActivityName,
    b.Target_Date AS EventTimestamp,
    b.FinalStatus AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    'true' AS IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.IsSlaBreached = 'true' AND b.Target_Date IS NOT NULL

UNION ALL

SELECT
    b.IncidentId,
    'Incident Cancelled' AS ActivityName,
    a.EventTimestamp,
    'Cancelled' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Cancelled', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Incident Closed' AS ActivityName,
    b.Closed_Date AS EventTimestamp,
    'Closed' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.Closed_Date IS NOT NULL;

Steps

Prerequisites and Environment Setup: Ensure you have Python 3.6 or newer installed. Install the necessary requests library by running pip install requests in your command line or terminal. Obtain API credentials (username and password) for a BMC Helix user account that has at least read-only permissions to the 'HPD:Help Desk' and 'HPD:Help Desk Audit Log' forms.
Configure the Python Script: Copy the provided Python script into a file, for example extract_incidents.py. Open the file and modify the configuration variables at the top, including HELIX_HOSTNAME, HELIX_USERNAME, HELIX_PASSWORD, START_DATE, END_DATE, and COMPANY_NAME to match your environment and desired data scope.
Understand Authentication: The script first calls the /api/jwt/login endpoint to authenticate with your BMC Helix instance. It obtains a JSON Web Token (JWT) that is then used as an authorization header for all subsequent data requests.
Execute the Script: Run the script from your terminal using the command python extract_incidents.py. The script will begin executing, providing progress updates in the console.
Initial Incident Data Retrieval: The script queries the 'HPD:Help Desk' REST API endpoint to retrieve a list of all incidents created within the specified date range. It uses pagination (limit and offset parameters) to efficiently handle large numbers of incidents without overwhelming the API.
Audit Log Retrieval: For each incident retrieved in the previous step, the script makes a separate API call to the 'HPD:Help Desk Audit Log' form. This fetches the detailed change history for every incident, which is crucial for identifying activities like status changes and reassignments.
Event Derivation and Logic: The script processes the data in memory. It combines the main incident record with its corresponding audit log. It then iterates through the sorted audit log entries to logically derive the specified activities. For example, the first time the 'Assigned Group' field appears in the log, it creates a 'Group Assigned' event. A status change from 'Resolved' to 'In Progress' generates an 'Incident Reopened' event.
Handling Calculated Events: The script calculates events that are not directly logged. 'Incident Reported' is created from the 'Submit Date'. 'SLA Breach' is derived by comparing the 'Last Resolved Date' to the 'Target Date'.
Data Structuring: As each activity is identified, the script creates a dictionary containing the IncidentId, ActivityName, EventTimestamp, and all other required and recommended attributes. These dictionaries are collected into a single list.
CSV File Generation: Once all incidents and their corresponding activities have been processed, the script uses Python's built-in csv library to write the final event log to a file named bmc_helix_incident_log.csv. This file is created in the same directory where the script was executed.
Final Review and Upload: Open the generated CSV file in a spreadsheet program to review its contents. Verify that the columns and data appear correct. The file is now ready to be uploaded to ProcessMind for analysis.

Configuration

HELIX_HOSTNAME: The base URL of your BMC Helix ITSM environment, for example, "https://your-company.bmc.com".
HELIX_USERNAME: The username for the API account used for authentication.
HELIX_PASSWORD: The password for the API account. It is recommended to use environment variables or a secure secret management tool for credentials in a production environment.
START_DATE / END_DATE: The date range for the extraction, formatted as "YYYY-MM-DDTHH:MM:SS.sssZ". For an initial analysis, a period of 3 to 6 months is recommended to ensure a representative dataset without excessive volume.
COMPANY_NAME: The name of the company to filter incidents for. This is critical in multi-tenant environments to scope the data correctly. If not needed, you can set it to None.
PAGE_SIZE: The number of incidents to retrieve per API request. A value between 500 and 1000 is generally efficient, but may need adjustment based on your instance's performance and API rate limits.
Prerequisites: The script requires a BMC Helix user account with at least read permissions to the HPD:Help Desk and HPD:Help Desk Audit Log forms. Without access to both, the extraction will be incomplete.

a Sample Query python

import requests
import json
import csv
from datetime import datetime
import time

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
HELIX_HOSTNAME = class="hl-string">"https://[your-helix-instance].bmc.com"
HELIX_USERNAME = class="hl-string">"[your_api_username]"
HELIX_PASSWORD = class="hl-string">"[your_api_password]"

class=class="hl-string">"hl-comment"># Date range for the query (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00.000Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59.000Z"

class=class="hl-string">"hl-comment"># Optional: Filter by company for multi-tenant environments
COMPANY_NAME = class="hl-string">"[Your Company Name]"  class=class="hl-string">"hl-comment"># Set to None if not needed

PAGE_SIZE = 1000
OUTPUT_CSV_FILE = class="hl-string">'bmc_helix_incident_log.csv'

class=class="hl-string">"hl-comment"># --- End of Configuration ---

def get_jwt_token(session):
    class="hl-string">"""Authenticates with the BMC Helix API and returns a JWT token."""
    login_url = fclass="hl-string">"{HELIX_HOSTNAME}/api/jwt/login"
    auth_data = {class="hl-string">'username': HELIX_USERNAME, class="hl-string">'password': HELIX_PASSWORD}
    try:
        response = session.post(login_url, data=auth_data, headers={class="hl-string">'Content-Type': class="hl-string">'application/x-www-form-urlencoded'})
        response.raise_for_status()
        return response.text
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error during authentication: {e}")
        return None

def get_all_incidents(session, token):
    class="hl-string">"""Retrieves all incident records within the specified date range."""
    all_incidents = []
    offset = 0
    url = fclass="hl-string">"{HELIX_HOSTNAME}/api/arsys/v1/entry/HPD:Help Desk"
    headers = {class="hl-string">'Authorization': fclass="hl-string">'AR-JWT {token}', class="hl-string">'Content-Type': class="hl-string">'application/json'}
    
    qualification = fclass="hl-string">"'Submit Date' >= \"{START_DATE}\class="hl-string">" AND 'Submit Date' <= \"{END_DATE}\class="hl-string">""
    if COMPANY_NAME:
        qualification += fclass="hl-string">" AND 'Company' = \"{COMPANY_NAME}\class="hl-string">""

    fields = (
        class="hl-string">"Incident Number,Submit Date,Last Resolved Date,Closed Date,Status,Priority,Assigned Group,"
        class="hl-string">"Assignee,ServiceCI,Operational Categorization Tier 1,Operational Categorization Tier 2,"
        class="hl-string">"Product Categorization Tier 1,Product Categorization Tier 2,Target Date"
    )

    while True:
        params = {class="hl-string">'q': qualification, class="hl-string">'fields': fclass="hl-string">'values({fields})', class="hl-string">'limit': PAGE_SIZE, class="hl-string">'offset': offset}
        print(fclass="hl-string">"Fetching incidents, offset: {offset}...")
        try:
            response = session.get(url, headers=headers, params=params)
            response.raise_for_status()
            data = response.json()
            entries = data.get(class="hl-string">'entries', [])
            if not entries:
                break
            all_incidents.extend(entries)
            offset += len(entries)
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching incidents: {e}")
            break
    return all_incidents

def get_audit_log(session, token, incident_id):
    class="hl-string">"""Retrieves the audit log for a single incident."""
    url = fclass="hl-string">"{HELIX_HOSTNAME}/api/arsys/v1/entry/HPD:Help Desk Audit Log"
    headers = {class="hl-string">'Authorization': fclass="hl-string">'AR-JWT {token}', class="hl-string">'Content-Type': class="hl-string">'application/json'}
    qualification = fclass="hl-string">"'Incident Number' = \"{incident_id}\class="hl-string">""
    fields = class="hl-string">"Log,Fields Changed,Log Key,Audit Date"
    params = {class="hl-string">'q': qualification, class="hl-string">'fields': fclass="hl-string">'values({fields})'}
    try:
        response = session.get(url, headers=headers, params=params)
        response.raise_for_status()
        data = response.json()
        return sorted(data.get(class="hl-string">'entries', []), key=lambda x: x[class="hl-string">'values'][class="hl-string">'Audit Date'])
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error fetching audit log for {incident_id}: {e}")
        return []

def parse_timestamp(ts):
    if not ts: return None
    try:
        return datetime.strptime(ts, class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    except ValueError:
        try:
            return datetime.strptime(ts, class="hl-string">'%Y-%m-%dT%H:%M:%S%z').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
        except ValueError:
            return ts

def main():
    event_log = []
    with requests.Session() as session:
        token = get_jwt_token(session)
        if not token:
            return

        incidents = get_all_incidents(session, token)
        total = len(incidents)
        print(fclass="hl-string">"Found {total} incidents to process.")

        for index, incident_entry in enumerate(incidents):
            incident = incident_entry[class="hl-string">'values']
            incident_id = incident.get(class="hl-string">'Incident Number')
            print(fclass="hl-string">"Processing incident {index + 1}/{total}: {incident_id}")

            base_attributes = {
                class="hl-string">'IncidentId': incident_id,
                class="hl-string">'Priority': incident.get(class="hl-string">'Priority'),
                class="hl-string">'Service': incident.get(class="hl-string">'ServiceCI'),
                class="hl-string">'IncidentCategory': fclass="hl-string">"{incident.get('Product Categorization Tier 1', '')} / {incident.get('Product Categorization Tier 2', '')}",
            }
            
            class=class="hl-string">"hl-comment"># 1. Incident Reported
            event_log.append({
                **base_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Incident Reported',
                class="hl-string">'EventTimestamp': parse_timestamp(incident.get(class="hl-string">'Submit Date')),
                class="hl-string">'IncidentStatus': class="hl-string">'New', class=class="hl-string">"hl-comment"># Typically starts as New
                class="hl-string">'AssignedGroup': incident.get(class="hl-string">'Assigned Group'),
                class="hl-string">'Assignee': incident.get(class="hl-string">'Assignee'),
            })

            audit_entries = get_audit_log(session, token, incident_id)

            current_group = None
            last_status = class="hl-string">'New'
            is_reopened = False
            group_assigned = False

            for audit in audit_entries:
                audit_values = audit[class="hl-string">'values']
                event_ts = parse_timestamp(audit_values.get(class="hl-string">'Audit Date'))
                log_key = audit_values.get(class="hl-string">'Log Key')
                fields_changed = audit_values.get(class="hl-string">'Fields Changed', class="hl-string">'')

                class=class="hl-string">"hl-comment"># 2. Group Assigned & 3. Transferred To Another Group
                if class="hl-string">'Assigned Group' in fields_changed:
                    new_group = audit_values.get(class="hl-string">'Log').split(class="hl-string">'\n')[0]
                    if new_group and not group_assigned:
                        activity = class="hl-string">'Group Assigned'
                        group_assigned = True
                    elif new_group and new_group != current_group:
                        activity = class="hl-string">'Transferred To Another Group'
                    else:
                        continue
                    current_group = new_group
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': activity, class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': last_status, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

                class=class="hl-string">"hl-comment"># Status-based activities
                if class="hl-string">'Status' in fields_changed:
                    new_status_str = audit_values.get(class="hl-string">'Log').split(class="hl-string">'\n')[0]
                    new_status_map = {
                        class="hl-string">'In Progress': class="hl-string">'Investigation Initiated',
                        class="hl-string">'Pending': class="hl-string">'Pending Customer Input',
                        class="hl-string">'Resolved': class="hl-string">'Resolution Implemented', class=class="hl-string">"hl-comment"># First event is implementation
                        class="hl-string">'Cancelled': class="hl-string">'Incident Cancelled',
                        class="hl-string">'Closed': class="hl-string">'Incident Closed' class=class="hl-string">"hl-comment"># Final closure
                    }
                    
                    if last_status == class="hl-string">'Resolved' and new_status_str in [class="hl-string">'In Progress', class="hl-string">'Assigned']:
                        is_reopened = True
                        event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Incident Reopened', class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

                    if new_status_str in new_status_map:
                        activity = new_status_map[new_status_str]
                        event_log.append({**base_attributes, class="hl-string">'ActivityName': activity, class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})
                        class=class="hl-string">"hl-comment"># Add specific Resolution/Confirmation events
                        if new_status_str == class="hl-string">'Resolved':
                             event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Incident Resolved', class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})
                             event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Awaiting User Confirmation', class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})
                    
                    last_status = new_status_str

            class=class="hl-string">"hl-comment"># 4. Incident Categorized (approximated as last categorization change before resolution)
            cat_events = [a for a in audit_entries if class="hl-string">'Operational Categorization Tier 1' in a[class="hl-string">'values'].get(class="hl-string">'Fields Changed', class="hl-string">'') or class="hl-string">'Product Categorization Tier 1' in a[class="hl-string">'values'].get(class="hl-string">'Fields Changed', class="hl-string">'')]
            if cat_events:
                last_cat_event = cat_events[-1]
                event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Incident Categorized', class="hl-string">'EventTimestamp': parse_timestamp(last_cat_event[class="hl-string">'values'][class="hl-string">'Audit Date']), class="hl-string">'IncidentStatus': last_status, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

            class=class="hl-string">"hl-comment"># Add placeholder for User Confirmation Received - often not explicit
            if incident.get(class="hl-string">'Status') == class="hl-string">'Closed' and incident.get(class="hl-string">'Last Resolved Date'):
                class=class="hl-string">"hl-comment"># This is an approximation, real confirmation might be earlier
                event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'User Confirmation Received', class="hl-string">'EventTimestamp': parse_timestamp(incident.get(class="hl-string">'Closed Date')), class="hl-string">'IncidentStatus': class="hl-string">'Closed', class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

            class=class="hl-string">"hl-comment"># 5. SLA Breach (calculated)
            is_sla_breached = False
            target_date_str = incident.get(class="hl-string">'Target Date')
            resolved_date_str = incident.get(class="hl-string">'Last Resolved Date')
            if target_date_str and resolved_date_str:
                target_dt = datetime.strptime(target_date_str, class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                resolved_dt = datetime.strptime(resolved_date_str, class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                if resolved_dt > target_dt:
                    is_sla_breached = True
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'SLA Breach', class="hl-string">'EventTimestamp': parse_timestamp(target_date_str), class="hl-string">'IncidentStatus': incident.get(class="hl-string">'Status'), class="hl-string">'AssignedGroup': incident.get(class="hl-string">'Assigned Group'), class="hl-string">'Assignee': incident.get(class="hl-string">'Assignee')})

            class=class="hl-string">"hl-comment"># Final attributes update
            resolution_time = None
            if incident.get(class="hl-string">'Submit Date') and incident.get(class="hl-string">'Last Resolved Date'):
                submit_dt = datetime.strptime(incident.get(class="hl-string">'Submit Date'), class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                resolved_dt = datetime.strptime(incident.get(class="hl-string">'Last Resolved Date'), class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                resolution_time = (resolved_dt - submit_dt).total_seconds()
            
            for event in event_log:
                if event[class="hl-string">'IncidentId'] == incident_id:
                    event[class="hl-string">'IsSlaBreached'] = is_sla_breached
                    event[class="hl-string">'IsReopened'] = is_reopened
                    event[class="hl-string">'IncidentResolutionTime'] = resolution_time
            
            time.sleep(0.1) class=class="hl-string">"hl-comment"># Small delay to be kind to the API

    class=class="hl-string">"hl-comment"># Write to CSV
    if event_log:
        fieldnames = [class="hl-string">'IncidentId', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'IncidentStatus', class="hl-string">'Priority', class="hl-string">'AssignedGroup', class="hl-string">'Assignee', class="hl-string">'Service', class="hl-string">'IncidentCategory', class="hl-string">'IncidentResolutionTime', class="hl-string">'IsSlaBreached', class="hl-string">'IsReopened']
        with open(OUTPUT_CSV_FILE, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames, extrasaction=class="hl-string">'ignore')
            writer.writeheader()
            writer.writerows(sorted(event_log, key=lambda x: (x[class="hl-string">'IncidentId'], x[class="hl-string">'EventTimestamp'])))
        print(fclass="hl-string">"Successfully wrote {len(event_log)} events to {OUTPUT_CSV_FILE}")
    else:
        print(class="hl-string">"No events were generated.")

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

Data Template: Incident Management

Your Incident Management Data Template

Incident Management Attributes

Incident Management Activities

Extraction Guides

BMC Helix Smart Reporting Export to CSV

Steps

Configuration

a Sample Query config

Direct SQL Query on ITSM Database Incident Tables

Steps

Configuration

a Sample Query sql

Python Script Extraction via BMC Helix REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Fix Recurring Incidents in BMC Helix ITSM Today!