> Continuous Process Improvement > Service Request Management > BMC Helix ITSM > Data Template

Your Service Request Management Data Template

BMC Helix ITSM

Service Request Management Attributes (21) Service Request Management Activities (14) Extraction Guides (3)

Your Service Request Management Data Template

This template provides a clear overview of the essential data attributes to collect and the key activities to track for your service request management process. It also includes practical guidance on how to extract this data efficiently from BMC Helix ITSM. Use this resource to streamline your data preparation and kickstart your process mining initiatives with confidence.

Recommended attributes to collect
Key activities to track
Extraction guidance for BMC Helix ITSM

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

Download Template

Service Request Management Attributes

These data fields are essential for creating a comprehensive event log, enabling a deep analysis of your service request management process.

5 Required 6 Recommended 10 Optional

	Name	Description
	Activity ActivityName	The name of the specific event or task that occurred at a point in time within the service request lifecycle.
Description This attribute describes a specific step or status change in the service request process, such as 'Request In Review', 'Fulfillment In Progress', or 'Service Request Resolved'. Each activity represents a single event in the end-to-end journey of a service request. Analyzing the sequence and frequency of activities is the core of process mining. It allows for the discovery of process maps, identification of bottlenecks, and analysis of process variants. Understanding which activities occur, in what order, and how often is crucial for process optimization. Why it matters Activities form the building blocks of the process map. Tracking them allows for the visualization and analysis of the process flow, revealing how work is actually done. Where to get This is typically derived from changes in the 'Status' and 'Status Reason' fields on the 'SRM:Request' form or related fulfillment application logs (e.g., Incident, Work Order). Examples Request Waiting for ApprovalFulfillment In ProgressService Request ResolvedService Request Closed
	Service Request ID ServiceRequestId	The unique identifier for each service request, serving as the primary key for tracking the entire lifecycle.
Description The Service Request ID uniquely identifies each individual service request submitted by a user or system. It serves as the central thread linking all subsequent events, from initial logging to final closure, allowing for a complete, end-to-end analysis of each service request's journey. In process mining, this ID is essential for reconstructing the sequence of activities for each case. It allows the tool to group all related events, such as 'Request Submitted', 'Request Assigned', and 'Service Request Closed', into a single process instance, forming the basis for all process analysis. Why it matters This is the fundamental case identifier. Without it, it's impossible to trace the end-to-end journey of a service request, making process discovery and analysis impossible. Where to get This is typically the 'InstanceId' or 'Request Number' field in the 'SRM:Request' form in BMC Helix ITSM. Examples SR000010572931SR000010572932SR000010572933
	Start Time EventStartTime	The timestamp indicating when a specific activity or event began.
Description This attribute records the precise date and time that an activity started. Every event in the log, from the initial submission to the final closure, must have a start time to establish the chronological sequence of the process. This timestamp is critical for all time-based process mining analysis. It is used to calculate cycle times, durations of activities, waiting times between steps, and to check for SLA compliance. It enables the discovery of bottlenecks and the analysis of process performance over time. Why it matters The start time provides the chronological order of events, which is essential for calculating process durations, identifying delays, and understanding the process timeline. Where to get This corresponds to the timestamp fields in the audit logs or status history tables associated with the 'SRM:Request' form, such as 'Submit Date' for the initial event. Examples 2023-10-26T10:00:00Z2023-10-26T11:30:00Z2023-10-27T14:22:00Z
	Last Data Update LastDataUpdate	The timestamp when the data for this process was last refreshed from the source system.
Description This attribute indicates the date and time of the most recent data extraction from BMC Helix ITSM. It provides users with context on the freshness of the data they are analyzing, ensuring they are aware of the time period covered by the analysis. This is a critical metadata attribute for any process mining dashboard or analysis. It allows users to understand if the insights are based on near real-time data or a historical snapshot, which affects the validity and relevance of the conclusions drawn. Why it matters It informs users about the timeliness of the data, which is essential for making decisions based on the most current process performance information available. Where to get This timestamp is generated and added during the data extraction and loading process. Examples 2024-05-21T08:00:00Z
	Source System SourceSystem	Identifies the system from which the data was extracted.
Description This attribute specifies the origin of the process data. For this view, it would be statically set to 'BMC Helix ITSM' to indicate that all events related to the service requests were sourced from this system. In environments with multiple integrated systems, this field is crucial for understanding data lineage and partitioning data based on its source. It ensures clarity and traceability, especially when merging data from different platforms. Why it matters It provides context about data origin, which is important for data governance, traceability, and troubleshooting in multi-system environments. Where to get This is a static value added during the data extraction and transformation process, not a field within BMC Helix ITSM itself. Examples BMC Helix ITSM
	Assigned Agent AssignedAgent	The individual user currently assigned to work on the service request.
Description This attribute identifies the specific IT agent or support staff member responsible for the request at a given point in time. Changes in this field over the lifecycle of a single request indicate a handoff or reassignment. This attribute is crucial for agent performance and workload analysis. It allows for tracking how many requests each agent handles, their average resolution time, and the frequency of reassignments. This data supports resource management and identification of training opportunities. Why it matters Tracking the assigned agent is essential for analyzing handoffs, measuring individual performance, and understanding workload distribution across the support team. Where to get This corresponds to the 'Assignee' or 'Assigned To' field on the fulfillment record (e.g., Work Order, Incident) associated with the service request. Examples Bob SmithAlice JohnsonCharlie Brown
	Assigned Team AssignedTeam	The support group or team currently assigned to the service request.
Description This attribute identifies the functional group responsible for handling the request, such as 'Help Desk', 'Network Team', or 'Database Administration'. A change in this field signifies a transfer of responsibility between teams. Analysis based on the assigned team helps identify bottlenecks at a team level, analyze inter-team handoffs, and evaluate the efficiency of different support groups. It is key for the Request Rework and Reassignment and Triage Efficiency dashboards, revealing patterns in how work is routed through the organization. Why it matters It enables analysis of process flow between different functional groups, helping to identify routing inefficiencies and measure team-level performance. Where to get This corresponds to the 'Assigned Group' field on the fulfillment record (e.g., Work Order, Incident) associated with the service request. Examples Service DeskInfrastructure SupportApplication Support Tier 2
	End Time EventEndTime	The timestamp indicating when a specific activity or event was completed.
Description The End Time marks the conclusion of an activity. While many activities in ITSM systems are instantaneous status changes, some have a measurable duration. Having an End Time allows for precise calculation of the duration of such activities. In analysis, the End Time is used in conjunction with the Start Time to calculate the processing time for individual activities. This helps pinpoint which specific tasks, not just the waiting time between them, are consuming the most time in the process. Why it matters It enables the calculation of activity processing times, which is crucial for identifying inefficient steps and understanding where resources spend their time. Where to get This can be derived. The End Time of one activity is often the Start Time of the next sequential activity for the same case. For the final activity, it would be the resolution or closed timestamp. Examples 2023-10-26T10:05:15Z2023-10-26T11:45:10Z2023-10-28T09:00:00Z
	Priority Priority	The priority level assigned to the service request, indicating its business impact and urgency.
Description Priority determines the order and speed at which requests should be handled. Common values include 'Critical', 'High', 'Medium', and 'Low'. This assignment is often based on a combination of the request's impact on the business and its urgency. Analyzing by priority is essential for evaluating if high-priority requests are being processed faster than low-priority ones. It is a key dimension in dashboards for resolution time and SLA compliance, helping to ensure that resources are being allocated appropriately to the most critical business needs. Why it matters It helps assess whether the process correctly prioritizes work and meets the expected service levels for requests with different levels of business impact. Where to get This is the 'Priority' field on the 'SRM:Request' form. Examples CriticalHighMediumLow
	Request Status RequestStatus	The status of the service request at the time of the event, such as 'In Progress', 'Pending', or 'Closed'.
Description This attribute captures the state of the service request at different points in its lifecycle. The status provides context for each activity and is often the source from which the 'Activity' attribute itself is derived. Analyzing by status helps to understand how much time requests spend in certain states, such as 'Pending Customer' or 'Waiting for Approval'. This is essential for identifying bottlenecks and delays caused by external dependencies or internal queues. It directly supports the Bottleneck Identification dashboard. Why it matters It provides a snapshot of the request's state, enabling analysis of time spent in waiting states versus active states, which is key to identifying bottlenecks. Where to get This is the 'Status' field on the 'SRM:Request' form. Historical values can be found in the audit log. Examples PlanningIn ProgressPendingResolvedClosed
	Service Type ServiceType	The category or type of service being requested by the user.
Description The Service Type classifies the nature of the request, for example, 'Request New Software', 'Password Reset', or 'Onboard New Employee'. It is a fundamental dimension for filtering and segmenting the process data. In process analysis, this attribute is used to compare the performance of different types of requests. It helps answer questions like, 'Which service types take the longest to resolve?' or 'Which service types have the most rework?'. This is critical for the Resolution Time and SLA Compliance dashboards. Why it matters It allows for the segmentation of service requests to compare process flows, identify type-specific issues, and tailor optimization efforts effectively. Where to get This data is often found in the 'Title' or a categorization field on the 'SRM:Request' form, derived from the selected service in the catalog. Examples New Hardware RequestSoftware Access RequestVPN Access Setup
	Close Code CloseCode	A code indicating the final outcome or reason for closing the service request.
Description The Close Code provides a standardized way to classify the resolution of a service request. Examples include 'Resolved by Service Desk', 'Canceled by User', or 'Duplicate Request'. Analyzing close codes helps in understanding the common outcomes of requests. It can highlight issues such as a high number of requests being canceled by users, which might indicate a lengthy process, or many duplicates, which could point to a system or communication issue. This attribute supports the Resolution Category Accuracy dashboard. Why it matters It provides structured data on request outcomes, enabling analysis of resolution effectiveness and reasons for non-completion or cancellation. Where to get This information is typically found in a 'Resolution' or 'Closure Code' field on the fulfillment ticket associated with the service request. Examples SuccessfulCanceled by UserNo Longer RequiredAutomated Resolution
	Cycle Time CycleTime	The total time elapsed from the creation of the service request to its final resolution.
Description Cycle Time, also known as end-to-end duration, measures the total lifespan of a service request. It is calculated as the difference between the timestamp of the first event (e.g., 'Service Request Submitted') and the last resolution event (e.g., 'Service Request Resolved'). This is one of the most fundamental KPIs in process mining, directly supporting the Service Request Resolution Time dashboard. Analyzing average cycle time, and slicing it by dimensions like service type or priority, reveals the overall efficiency of the process and highlights areas that are taking too long. Why it matters This is a primary measure of process efficiency. Understanding and reducing cycle time is often a key goal of process improvement initiatives. Where to get This is a calculated metric. It is derived by subtracting the 'Submit Date' from the 'Closed Date' or 'Resolved Date' for each unique Service Request ID. Examples 2 days 4 hours8 hours 30 minutes15 days
	Handoff Count HandoffCount	The total number of times a service request was reassigned between different agents or teams.
Description This calculated metric counts the number of times the 'AssignedAgent' or 'AssignedTeam' changes for a single service request. A high handoff count can indicate process fragmentation, lack of first-call resolution, or inefficient routing. This attribute is the basis for the Average Agent Handoffs per Request KPI and is used in the Request Rework and Reassignment dashboard. Analyzing cases with high handoff counts can reveal opportunities to improve triage, provide better training, or streamline the resolution process to reduce delays and improve customer satisfaction. Why it matters It measures process fragmentation and communication overhead. High handoff counts are often correlated with longer resolution times and lower process efficiency. Where to get This is a calculated metric derived by counting the number of distinct values in the 'AssignedAgent' or 'AssignedTeam' attribute for each unique Service Request ID. Examples 0135
	Is Escalated IsEscalated	A boolean flag that indicates whether the service request has been escalated.
Description This flag is set to true if a service request has undergone a functional or hierarchical escalation. An escalation typically occurs when a request is not progressing as expected, is about to breach an SLA, or requires higher authority for approval or action. This attribute is key for the Request Escalation Efficiency Analysis dashboard. It allows for filtering and analyzing the process paths of escalated requests to understand what triggers them, how long they take to resolve post-escalation, and how effective the escalation process is. Why it matters It allows for isolating and analyzing the subset of requests that required escalation, helping to identify weaknesses in the standard process or triggers for complex issues. Where to get This is typically not a single field. It's derived by checking for specific escalation-related activities in the audit log or changes in priority or assignment that follow an escalation protocol. Examples truefalse
	Is Rework IsRework	A boolean flag indicating if a service request has undergone rework, such as returning to a previous stage.
Description This flag identifies service requests that have experienced a loop or rework in their process flow. For example, a request moving from 'Fulfillment in Progress' back to 'Request in Review' would be considered rework. The exact definition depends on the business process logic. This attribute directly supports the Request Rework and Reassignment Analysis dashboard and the Request Rework Rate KPI. It allows for quantifying the frequency of rework and analyzing the common causes, such as incorrect initial assessment or incomplete information, leading to process inefficiencies. Why it matters It quantifies process inefficiency by flagging cases that deviate from the 'happy path', helping to identify the root causes of loops and repeated work. Where to get This is a calculated attribute derived from the sequence of activities in the event log. Logic is needed to detect backward movements in the process flow. Examples truefalse
	Is SLA Breached IsSlaBreached	A boolean flag indicating if the service request was resolved after its SLA target date.
Description This calculated flag is set to true if the final resolution timestamp of the service request is later than its 'SLA Target Date'. It provides a simple, binary outcome for SLA performance on a per-request basis. This attribute is essential for the SLA Compliance Overview dashboard and the SLA Adherence Rate KPI. It allows for easy aggregation to calculate overall compliance rates and enables filtering to analyze the process characteristics of breached requests versus compliant ones, helping to identify root causes of SLA failures. Why it matters It simplifies SLA performance analysis by converting a timestamp comparison into a simple boolean flag, making it easy to measure and visualize compliance rates. Where to get This is a calculated field. The logic is: IF 'Resolution Timestamp' > 'SlaTargetDate' THEN true ELSE false. Examples truefalse
	Requestor Department RequestorDepartment	The business department or unit of the user who submitted the request.
Description This attribute identifies the organizational department of the person requesting the service, such as 'Finance', 'Human Resources', or 'IT'. This information is typically sourced from the user's profile in the system. Segmenting the process analysis by department allows for identifying department-specific needs, request patterns, and potential areas for targeted training or service improvements. It can help answer questions like, 'Does the Finance department experience longer wait times for their requests?'. Why it matters It enables analysis of service consumption and process performance by business unit, which can highlight department-specific issues or trends. Where to get This information is usually retrieved from the user's profile associated with the 'Requested For' user on the 'SRM:Request' form. Examples FinanceSalesHuman ResourcesInformation Technology
	Resolution Category ResolutionCategory	The classification of the solution provided to resolve the request.
Description This attribute provides a structured categorization of how a request was resolved, such as 'Software Fix', 'User Training', or 'Data Correction'. It goes beyond a simple close code to describe the nature of the resolution. This is essential for the Resolution Category Accuracy dashboard, where it can be compared against the initial service type to check for consistency. Analyzing resolution categories helps identify trends in issues and informs proactive problem management, for example, if many requests are resolved by user training. Why it matters It offers insights into the nature of solutions, helping to identify trends in recurring issues and opportunities for proactive problem management or user training. Where to get This information is part of the operational and product categorization fields on the fulfillment ticket, often labeled 'Resolution Category'. Examples Account AdministrationHardware FailureSoftware UpgradeInformation Provided
	SLA Target Date SlaTargetDate	The date and time by which the service request is expected to be resolved according to its Service Level Agreement (SLA).
Description The SLA Target Date is a calculated timestamp that represents the deadline for completing the service request. It is determined by the service agreement rules, which often take into account factors like the request's priority and type. This attribute is fundamental for the SLA Compliance Overview dashboard. It serves as the benchmark against which the actual resolution time is measured. By comparing the 'EventEndTime' of the final resolution activity with this target date, we can determine whether the service commitment was met. Why it matters This is the primary benchmark for measuring service performance against commitments, making it essential for SLA compliance monitoring and reporting. Where to get This date is calculated and stored by the Service Level Management (SLM) module and can be found on related SLM forms linked to the service request. Examples 2023-10-28T17:00:00Z2023-11-01T09:00:00Z2023-10-27T12:00:00Z
	Submission Channel SubmissionChannel	The method or channel through which the service request was submitted.
Description This attribute records how the service request was initiated, for example, via a self-service portal, email, phone call to the service desk, or automated system alert. Different channels can lead to different process variants and resolution times. Analyzing the process by submission channel can reveal inefficiencies or best practices associated with specific intake methods. For instance, requests submitted via the self-service portal may be resolved faster due to better initial data quality, while those from email may require more manual triage. Why it matters It helps in understanding how the intake method affects process efficiency, data quality, and overall cycle time, allowing for targeted improvements in specific channels. Where to get This can often be inferred from fields like 'Client Type' or 'Reported Source' on the 'SRM:Request' form or associated fulfillment tickets. Examples Self-Service PortalEmailPhoneSystem Generated

Required Recommended Optional

Service Request Management Activities

These key process steps and milestones should be captured in your event log for accurate process discovery and visualization.

6 Recommended 8 Optional

	Activity	Description
	Fulfillment In Progress	The assigned agent or team has started actively working on fulfilling the service request. This indicates that the request has moved from a queue into an active work state.
Why it matters Marks the beginning of the value-add fulfillment work. Analyzing the time spent in this phase helps understand resource productivity and fulfillment complexity. Where to get Inferred from a status change in the SRM:Request form to 'In Progress'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'In Progress'. Event type inferred
	Request Assigned	The service request has been assigned to a specific fulfillment agent or team responsible for completing the work. This marks the end of the triage phase.
Why it matters This milestone is critical for measuring triage time and analyzing agent workload. Frequent re-assignments can indicate routing issues or skill gaps. Where to get This event can be captured explicitly from the audit log of the 'Assigned Group' or 'Assignee' fields on the SRM:Request or related fulfillment application forms (e.g., WOI:WorkOrder). Capture Timestamp from the audit log showing a non-null value being set in the 'Assignee' field for the first time. Event type explicit
	Service Request Canceled	The service request has been withdrawn either by the requestor or the service desk before fulfillment was completed. This is a terminal state for the request.
Why it matters Tracking cancellations helps identify patterns, such as users submitting incorrect requests or services no longer being needed, which can inform service catalog improvements. Where to get Inferred from a status change in the SRM:Request form to 'Canceled'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'Canceled'. Event type inferred
	Service Request Closed	The service request is formally closed and moved to a read-only, archived state. This occurs after resolution and any confirmation period has passed.
Why it matters This activity represents the definitive end of the process. The time between 'Resolved' and 'Closed' can highlight inefficiencies in the closing procedure. Where to get Inferred from the final status change in the SRM:Request form to 'Closed'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'Closed'. Event type inferred
	Service Request Resolved	The fulfillment of the service request is complete, and the resolution has been communicated to the requestor. The request awaits final confirmation or will auto-close after a set period.
Why it matters A critical milestone that marks the end of the service delivery cycle. It is the primary endpoint for measuring resolution time and SLA adherence. Where to get Inferred from a status change in the SRM:Request form to 'Resolved' or 'Completed'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'Resolved' or 'Completed'. Event type inferred
	Service Request Submitted	This activity marks the creation and submission of a new service request by a user. It is captured when a new entry is created in the SRM:Request form with an initial status, typically 'Submitted'.
Why it matters This is the starting point for every service request case, essential for measuring the total lifecycle duration and analyzing request intake volumes. Where to get This event is inferred from the creation timestamp and the initial status (e.g., 'Submitted') of a record in the SRM:Request form. Capture Identify the creation timestamp for a new Service Request ID in the SRM:Request form when the status is 'Submitted'. Event type inferred
	Information Requested from User	The fulfillment agent requires additional information from the requestor to proceed with the work. The request is typically placed in a 'Pending' state.
Why it matters This activity is crucial for calculating 'External Information Wait Time', identifying how often requests are stalled due to incomplete information. Where to get Inferred from a status change in the SRM:Request form to 'Pending' with a status reason like 'Customer Hold' or 'Awaiting Information'. Capture Timestamp of the status change to 'Pending' combined with a specific status reason. Event type inferred
	Request Approved	The service request has been formally approved by the required party, allowing the fulfillment process to proceed. This event typically follows a 'Waiting for Approval' status.
Why it matters Marks the end of the approval sub-process and is a key milestone for tracking how long approvals take and their impact on overall resolution time. Where to get Inferred from a status change in the SRM:Request form from 'Waiting Approval' to a subsequent status like 'Planning' or 'In Progress'. The approval decision itself is logged in related approval forms. Capture Timestamp of the status change out of 'Waiting Approval' after a positive approval decision. Event type inferred
	Request In Review	The service request is undergoing initial review and triage by the service desk to determine its nature, priority, and the appropriate fulfillment team. This is typically represented by a status change in the request record.
Why it matters Tracking this activity helps measure triage efficiency and identifies delays between submission and assignment, which is crucial for the 'Average Triage Time' KPI. Where to get Inferred from a status change in the SRM:Request form to a value like 'In Review' or 'Planning'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'In Review'. Event type inferred
	Request Rejected	The service request was denied during an approval phase. This is a terminal state that stops the process before fulfillment begins.
Why it matters Analyzing rejected requests can highlight issues with request justifications, eligibility criteria, or approval policies. Where to get Inferred from a status change in the SRM:Request form to 'Rejected'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'Rejected'. Event type inferred
	Request Resumed	The service request has been taken out of a pending or waiting state, typically after the required information was provided by the user. Work on the request is resumed by the fulfillment agent.
Why it matters Marks the end of a wait period, allowing for precise measurement of external wait times and their impact on SLA compliance. Where to get Inferred when the status of the SRM:Request changes from 'Pending' back to 'In Progress'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes from 'Pending' to 'In Progress'. Event type inferred
	Request Waiting for Approval	The service request has been submitted to a designated approver or approval group and is awaiting a decision before fulfillment can begin. This is a common step for requests involving cost or access rights.
Why it matters This activity isolates approval-related delays, allowing analysis of approval cycle times and identification of bottlenecks in the approval chain. Where to get Inferred from a status change in the SRM:Request form to a value like 'Waiting Approval'. Capture Timestamp of the update event where the SRM:Request 'Status' field changes to 'Waiting Approval'. Event type inferred
	Resolution Confirmed by User	The requestor has actively confirmed that the service has been delivered satisfactorily and the request is resolved. This often triggers the final closure of the request.
Why it matters Provides a clear indicator of customer satisfaction and formally concludes the service interaction. It separates process resolution from customer acceptance. Where to get This event might be captured in the SRM:Request work logs or activity notes when a user confirms via the portal or email. It is not always a discrete status. Capture Scan work logs (SRM:WorkInfo) for specific entries indicating user confirmation or survey completion. Event type explicit
	Solution Implemented	The technical work required to fulfill the service request has been completed by the agent. The request is now ready for confirmation with the user before being formally resolved.
Why it matters This activity separates the technical completion from the formal resolution, helping to identify any delays between work completion and user confirmation. Where to get This may be inferred from a status change on a backend fulfillment ticket (e.g., Work Order status to 'Completed') before the parent SRM:Request is marked as 'Resolved'. Capture Timestamp when a backend ticket (Work Order, Incident, etc.) linked to the SRM:Request is marked as complete. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from BMC Helix ITSM

Steps

Access Smart Reporting: Log into your BMC Helix ITSM environment. Navigate to the reporting section and launch Smart Reporting. Depending on your version, this may be labeled as 'Reports' or 'Smart Reporting'.
Create a New Report: Once in the Smart Reporting interface, select the option to create a new report. Choose 'Report' as the type.
Select Data Source and View: You will be prompted to select a data source. Choose your primary AR System data source. For the view, search for and select the Service Request view, which is typically based on the SRM:Request form.
Add Additional Forms (Joins): To capture the full lifecycle, you must join the main SRM:Request form with related forms. Use the 'Data' tab and join the following:
- SRM:WorkInfo (on SR ID+ = Request Number) to get details about fulfillment activities and worklogs.
- AP:Signature (on Request = SRInstanceID) to get approval-related events like approvals and rejections. You may need a custom join for this.
Define Report Columns: Drag and drop the required fields from the joined forms onto the 'Columns' area. This will include fields like Request Number, Submit Date, Status, Description, Priority, Assigned To, and Assigned Group from SRM:Request, and related fields from the joined forms.
Create Calculated Fields for Activities: The core of this method is creating a series of calculated fields to represent each activity's timestamp. Go to the 'Data' tab and create a new calculated field for each activity. For example:
- Submitted Timestamp: Use the Submit Date field directly.
- Approval Timestamp: Use the Signature Date from the AP:Signature form.
- Work In Progress Timestamp: Use the MIN(Submit Date) from SRM:WorkInfo grouped by Request Number.
Structure the Report for Union: Smart Reporting does not have a simple 'UNION ALL' button like SQL. To achieve this, you will create a single report and use advanced functions or subqueries to pivot the data. A common technique is to create multiple calculated columns, one for each activity's timestamp. You will then need to format the final export to transform these columns into separate rows for each activity.
Apply Filters: To limit the data scope and improve performance, add filters to your report. At a minimum, filter by Submit Date from the SRM:Request form to select a specific date range. Also, add filters for 'Company' if you operate in a multi-tenant environment.
Run and Preview Data: Run the report to ensure the data is being pulled correctly. Check for correct joins, populated columns, and that your calculated fields are returning the expected timestamps.
Export the Report: Once the report is validated, export the data. Choose CSV or Microsoft Excel as the export format, as these are easily processed for import into process mining tools.
Final Data Transformation: After exporting, you must transform the data from a wide format (one row per service request with many timestamp columns) to a long format (one row per activity). Use a script (e.g., Python, PowerShell) or a tool like Excel Power Query to unpivot the timestamp columns into two columns: ActivityName and EventStartTime. This step is critical to create the required event log structure.

Configuration

Prerequisites: You must have a user account with reporting permissions in BMC Helix ITSM, specifically access to create and run reports in Smart Reporting. You also need permissions to read data from the SRM:Request, SRM:WorkInfo, and AP:Signature forms.
Key Forms: The report is built by joining these core forms:
- SRM:Request: The primary form containing the main details of the service request.
- SRM:WorkInfo: Contains worklog entries, which are used to derive fulfillment activities.
- AP:Signature: Contains approval records, which are essential for tracking approval-related activities.
Date Range Filtering: Always apply a date range filter on the SRM:Request.Submit Date field. A range of 3 to 6 months of data is recommended to balance getting a representative sample with report performance.
Important Filters: In addition to a date range, filter by Company to isolate data for a specific business entity. You may also want to filter out specific request types or statuses, such as 'Draft', that are not relevant to the process analysis.
Performance Considerations: Reports that join multiple forms across a wide date range can be slow to generate. It is highly recommended to schedule the report to run during off-peak hours for large data volumes. Consider breaking up the extraction into smaller, monthly chunks if timeouts occur.

a Sample Query config

/*
This is a conceptual SQL query representing the logic you would build in the BMC Helix ITSM Smart Reporting UI.
Smart Reporting uses a graphical interface, so you will create this structure by joining forms and creating calculated fields that correspond to each SELECT block below.
The final step of unpivoting data from columns to rows must be done after exporting the report.
*/

-- 1. Service Request Submitted
SELECT
  'Service Request Submitted' AS "ActivityName",
  req."Request Number" AS "ServiceRequestId",
  req."Submit Date" AS "EventStartTime",
  NULL AS "EventEndTime",
  req."Status" AS "RequestStatus",
  req."Title" AS "ServiceType", -- Or another field representing the service
  req."Priority" AS "Priority",
  req."ASG_Assignee" AS "AssignedAgent",
  req."Assigned Group" AS "AssignedTeam",
  'BMC Helix ITSM' AS "SourceSystem",
  GETDATE() AS "LastDataUpdate"
FROM "SRM:Request" AS req
WHERE req."Submit Date" BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

-- 2, 3, 4, 14: Approval Steps (Waiting, Approved, Rejected)
SELECT
  CASE sig."Outcome" WHEN 'Approved' THEN 'Request Approved' WHEN 'Rejected' THEN 'Request Rejected' ELSE 'Request Waiting for Approval' END AS "ActivityName",
  req."Request Number" AS "ServiceRequestId",
  sig."Signature Date" AS "EventStartTime",
  NULL AS "EventEndTime",
  req."Status" AS "RequestStatus",
  req."Title" AS "ServiceType",
  req."Priority" AS "Priority",
  req."ASG_Assignee" AS "AssignedAgent",
  req."Assigned Group" AS "AssignedTeam",
  'BMC Helix ITSM' AS "SourceSystem",
  GETDATE() AS "LastDataUpdate"
FROM "SRM:Request" AS req
JOIN "AP:Signature" AS sig ON req."InstanceId" = sig."Request"
WHERE req."Submit Date" BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

-- 5. Request Assigned (based on first Work Info entry)
SELECT
  'Request Assigned' AS "ActivityName",
  wi."Request Number" AS "ServiceRequestId",
  MIN(wi."Submit Date") AS "EventStartTime",
  NULL AS "EventEndTime",
  req."Status" AS "RequestStatus",
  req."Title" AS "ServiceType",
  req."Priority" AS "Priority",
  req."ASG_Assignee" AS "AssignedAgent",
  req."Assigned Group" AS "AssignedTeam",
  'BMC Helix ITSM' AS "SourceSystem",
  GETDATE() AS "LastDataUpdate"
FROM "SRM:WorkInfo" AS wi
JOIN "SRM:Request" AS req ON wi."Request Number" = req."Request Number"
WHERE req."Submit Date" BETWEEN '[START_DATE]' AND '[END_DATE]'
GROUP BY wi."Request Number", req."Status", req."Title", req."Priority", req."ASG_Assignee", req."Assigned Group"

UNION ALL

-- 6, 7, 8, 9: Fulfillment Steps from Work Logs
SELECT
  CASE 
    WHEN wi."Work Log Type" = 'General Information' AND wi.Description LIKE '%Fulfillment In Progress%' THEN 'Fulfillment In Progress'
    WHEN wi."Work Log Type" = 'Customer Communication' AND wi.Description LIKE '%Information Requested%' THEN 'Information Requested from User'
    WHEN wi."Work Log Type" = 'General Information' AND wi.Description LIKE '%Request Resumed%' THEN 'Request Resumed'
    WHEN wi."Work Log Type" = 'Resolution Communications' THEN 'Solution Implemented'
    ELSE NULL 
  END AS "ActivityName",
  wi."Request Number" AS "ServiceRequestId",
  wi."Submit Date" AS "EventStartTime",
  NULL AS "EventEndTime",
  req."Status" AS "RequestStatus",
  req."Title" AS "ServiceType",
  req."Priority" AS "Priority",
  req."ASG_Assignee" AS "AssignedAgent",
  req."Assigned Group" AS "AssignedTeam",
  'BMC Helix ITSM' AS "SourceSystem",
  GETDATE() AS "LastDataUpdate"
FROM "SRM:WorkInfo" AS wi
JOIN "SRM:Request" AS req ON wi."Request Number" = req."Request Number"
WHERE req."Submit Date" BETWEEN '[START_DATE]' AND '[END_DATE]'
  AND wi."Work Log Type" IN ('General Information', 'Customer Communication', 'Resolution Communications')

UNION ALL

-- 10, 11, 12, 13: Terminal Statuses (Resolved, Confirmed, Closed, Canceled)
SELECT
  CASE req."Status"
    WHEN 'Resolved' THEN 'Service Request Resolved'
    WHEN 'Closed' AND req."Status_Reason" = 'Confirmed' THEN 'Resolution Confirmed by User'
    WHEN 'Closed' THEN 'Service Request Closed'
    WHEN 'Cancelled' THEN 'Service Request Canceled'
    ELSE NULL
  END AS "ActivityName",
  req."Request Number" AS "ServiceRequestId",
  req."Last Modified Date" AS "EventStartTime", -- This is an approximation of when the status change occurred
  NULL AS "EventEndTime",
  req."Status" AS "RequestStatus",
  req."Title" AS "ServiceType",
  req."Priority" AS "Priority",
  req."ASG_Assignee" AS "AssignedAgent",
  req."Assigned Group" AS "AssignedTeam",
  'BMC Helix ITSM' AS "SourceSystem",
  GETDATE() AS "LastDataUpdate"
FROM "SRM:Request" AS req
WHERE req."Submit Date" BETWEEN '[START_DATE]' AND '[END_DATE]'
  AND req."Status" IN ('Resolved', 'Closed', 'Cancelled')

Steps

Prerequisites: Ensure you have a database client (like DBeaver, SQL Server Management Studio, or Oracle SQL Developer) and read-only credentials for the BMC Helix AR System database.
Connect to Database: Use your database client to establish a connection to the AR System database server.
Prepare the Query: Copy the complete SQL query provided in the 'Query' section of this document into your database client's query editor.
Set Parameters: Locate the placeholder values within the query. Replace :start_date and :end_date with the desired date range for the extraction, for example, '2023-01-01' and '2023-12-31'. Replace :company_name with the specific company name you wish to filter on, if applicable.
Execute the Query: Run the modified SQL query. Depending on the date range and system size, this may take several minutes to complete.
Review Results: Once the query finishes, a result set containing the event log will be displayed. Briefly review the data to ensure it has populated correctly and contains the expected columns and activities.
Handle Timestamps: Verify that all timestamp columns, specifically EventStartTime, EventEndTime, and LastDataUpdate, are formatted in a consistent manner, such as 'YYYY-MM-DD HH:MI:SS'. If your database returns them in a different format, adjust the TO_CHAR or CONVERT functions in the query accordingly.
Export Data: Use your database client's export functionality to save the full result set as a CSV file. Choose UTF-8 encoding to ensure compatibility.
Prepare for Upload: Name the file appropriately, for example, servicerequest_eventlog.csv. This file is now ready to be uploaded to the ProcessMind platform.

Configuration

Database Credentials: A read-only user account for the AR System database is required. This prevents accidental data modification and adheres to security best practices.
Date Range Filtering: The query includes :start_date and :end_date placeholders. It is crucial to set a specific and reasonable date range, typically 3 to 12 months of data, to manage query performance and data volume. Filtering is applied to the Submit_Date of the service request.
Company Filter: A placeholder :company_name is included to filter requests for a specific operating company. If you need to extract data for all companies, you can remove the line AND req.Company = :company_name or set it to a wildcard if supported.
Status Values: The query uses standard BMC Helix ITSM status integer values (e.g., Submitted = 0, In Review = 1, etc.). Your system may have custom statuses or different integer mappings. Verify these values against your system's SYS:Status form or developer studio and adjust the WHERE clauses in the query if necessary.
Performance: Direct database queries on large tables like SRM_Request and its audit logs can be resource-intensive. Schedule the extraction during off-peak hours to minimize impact on system performance.

a Sample Query sql

WITH ServiceRequests AS (
  SELECT
    req.Request_Number AS ServiceRequestId,
    req.Submit_Date AS SubmitDate,
    req.Status AS CurrentStatus,
    req.Description AS ServiceType,
    req.Priority AS Priority,
    req.Assignee AS AssignedAgent,
    req.Assigned_Group AS AssignedTeam,
    req.Company,
    req.InstanceId
  FROM
    SRM_Request req
  WHERE
    req.Submit_Date BETWEEN TO_TIMESTAMP(:start_date, 'YYYY-MM-DD') AND TO_TIMESTAMP(:end_date, 'YYYY-MM-DD')
    AND req.Company = :company_name
)
-- 1. Service Request Submitted
SELECT
  sr.ServiceRequestId AS "ServiceRequestId",
  'Service Request Submitted' AS "ActivityName",
  sr.SubmitDate AS "EventStartTime",
  sr.SubmitDate AS "EventEndTime",
  'Submitted' AS "RequestStatus",
  sr.ServiceType AS "ServiceType",
  sr.Priority AS "Priority",
  sr.AssignedAgent AS "AssignedAgent",
  sr.AssignedTeam AS "AssignedTeam",
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
UNION ALL
-- 2. Request In Review
SELECT
  sr.ServiceRequestId,
  'Request In Review' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'In Review' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%In Review%'
UNION ALL
-- 3. Request Waiting for Approval
SELECT
  sr.ServiceRequestId,
  'Request Waiting for Approval' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Waiting Approval' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Waiting Approval%'
UNION ALL
-- 4. Request Approved
SELECT
  sr.ServiceRequestId,
  'Request Approved' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Approved' AS "RequestStatus", -- This status might vary, often transitions to Planning or Assigned
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Planning%' AND audit.Old_Values LIKE '%Waiting Approval%'
UNION ALL
-- 5. Request Assigned
SELECT
  sr.ServiceRequestId,
  'Request Assigned' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Assigned' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Assigned_Group%' AND audit.New_Values IS NOT NULL
UNION ALL
-- 6. Fulfillment In Progress
SELECT
  sr.ServiceRequestId,
  'Fulfillment In Progress' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'In Progress' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%In Progress%'
UNION ALL
-- 7. Information Requested from User
SELECT
  sr.ServiceRequestId,
  'Information Requested from User' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Pending' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Pending%'
UNION ALL
-- 8. Request Resumed
SELECT
  sr.ServiceRequestId,
  'Request Resumed' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'In Progress' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%In Progress%' AND audit.Old_Values LIKE '%Pending%'
UNION ALL
-- 9. Solution Implemented
SELECT
  sr.ServiceRequestId,
  'Solution Implemented' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Completed' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Completed%'
UNION ALL
-- 10. Service Request Resolved
SELECT
  sr.ServiceRequestId,
  'Service Request Resolved' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Resolved' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Resolved%'
UNION ALL
-- 11. Resolution Confirmed by User
SELECT
  sr.ServiceRequestId,
  'Resolution Confirmed by User' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Closed' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Closed%' AND audit.Old_Values LIKE '%Resolved%'
UNION ALL
-- 12. Service Request Closed
SELECT
  sr.ServiceRequestId,
  'Service Request Closed' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Closed' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Closed%'
UNION ALL
-- 13. Service Request Canceled
SELECT
  sr.ServiceRequestId,
  'Service Request Canceled' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Cancelled' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Cancelled%'
UNION ALL
-- 14. Request Rejected
SELECT
  sr.ServiceRequestId,
  'Request Rejected' AS "ActivityName",
  audit.Audit_Date AS "EventStartTime",
  audit.Audit_Date AS "EventEndTime",
  'Rejected' AS "RequestStatus",
  sr.ServiceType,
  sr.Priority,
  sr.AssignedAgent,
  sr.AssignedTeam,
  'BMC Helix ITSM' AS "SourceSystem",
  CURRENT_TIMESTAMP AS "LastDataUpdate"
FROM
  ServiceRequests sr
  JOIN SRM_Request_AuditLog audit ON sr.InstanceId = audit.Original_Request_ID
WHERE
  audit.Fields_Changed LIKE '%Status%' AND audit.New_Values LIKE '%Rejected%';

Steps

Prerequisites: Ensure you have Python 3.6 or newer installed on your system. Install the requests library, which is necessary for making HTTP API calls, by running pip install requests in your terminal or command prompt.
Obtain API Credentials: Create or obtain credentials for a dedicated service account in BMC Helix ITSM. This account requires at least read-only permissions for the SRM:Request form and the corresponding audit log form, which is often named SRM:Request_AuditLog or similar. Note your username, password, and the base URL for your BMC Helix instance (e.g., https://[your-company].bmc.com).
Configure the Python Script: Copy the provided Python script into a file, for example, extract_servicerequests.py. Edit the script and populate the placeholder variables at the top: BASE_URL, USERNAME, PASSWORD, and AUDIT_FORM_NAME. Adjust the STATUS_ACTIVITY_MAP dictionary to match the specific status values used in your organization's service request workflow.
Set Extraction Period: Modify the START_DATE and END_DATE variables in the script. This defines the time window for the extraction based on the service request submission date. For initial testing, use a small date range, such as a single day.
Execute the Script: Run the script from your terminal using the command python extract_servicerequests.py. The script will first authenticate with the API to get a token.
Initial Data Fetch: The script queries the SRM:Request form to retrieve a list of all service requests created within the specified date range. This step gathers the case ID and associated static attributes like priority and service type.
Fetch Event History: The script iterates through each service request ID obtained in the previous step. For each ID, it makes a second API call to the configured audit log form to pull the detailed history of status changes for that specific request.
Transform Data into Event Log: For each record from the audit log, the script maps the status change to a standard activity name using the STATUS_ACTIVITY_MAP. It creates a row for each mapped activity, including the Service Request ID, Activity Name, and the timestamp of the change.
Generate Submitted Event: The script also creates the initial 'Service Request Submitted' activity for each request, using the Submit Date from the main SRM:Request form.
Export to CSV: After processing all service requests and their histories, the script consolidates the generated rows into a single list and writes the data to a CSV file named servicerequest_event_log.csv. This file is formatted and ready for upload to a process mining tool.

Configuration

API Endpoints: The script primarily uses two API endpoints. Authentication is handled via api/jwt/login. Data is queried from arsys/v1/entry/SRM:Request for the list of cases and arsys/v1/entry/[AUDIT_FORM_NAME] for the event history.
Service Account Permissions: A dedicated service account with read access to the SRM:Request form and the system's audit log form is required. Using a service account is critical for security and traceability.
Date Range Filtering: The script is configured to filter service requests based on their Submit Date. It is recommended to extract data in manageable batches, suchas 3-6 months at a time, to ensure reliable performance.
Status to Activity Mapping: The STATUS_ACTIVITY_MAP dictionary in the script is the core of the transformation logic. It must be carefully configured to reflect your organization's custom statuses and map them to the desired activity names. Any status in your workflow not present in the map will be ignored.
Audit Log Form: The script's ability to capture detailed activities depends entirely on a queryable audit log. You must identify the correct form name in your environment that stores status change history for service requests and update the AUDIT_FORM_NAME variable accordingly.
Pagination: The script handles API pagination automatically to retrieve all records. The limit parameter in the API call is set to retrieve 1000 records per request to balance performance and memory usage.

a Sample Query python

import requests
import json
import csv
import datetime

class=class="hl-string">"hl-comment"># --- Configuration Settings --- #
class=class="hl-string">"hl-comment"># Replace with your BMC Helix environment details
BASE_URL = class="hl-string">"https://[your-company].bmc.com"
USERNAME = class="hl-string">"[your-service-account-username]"
PASSWORD = class="hl-string">"[your-service-account-password]"
class=class="hl-string">"hl-comment"># This is a placeholder; you must identify the correct audit form for SRM:Request in your system
AUDIT_FORM_NAME = class="hl-string">"SRM:Request_AuditLog"

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

class=class="hl-string">"hl-comment"># --- Status to Activity Mapping --- #
class=class="hl-string">"hl-comment"># CRITICAL: Customize this map to match your system's status values
STATUS_ACTIVITY_MAP = {
    class="hl-string">"In Review": class="hl-string">"Request In Review",
    class="hl-string">"Waiting for Approval": class="hl-string">"Request Waiting for Approval",
    class="hl-string">"Approved": class="hl-string">"Request Approved",
    class="hl-string">"Assigned": class="hl-string">"Request Assigned",
    class="hl-string">"In Progress": class="hl-string">"Fulfillment In Progress",
    class="hl-string">"Pending": class="hl-string">"Information Requested from User", class=class="hl-string">"hl-comment"># This is a simplification; may need refinement
    class="hl-string">"Completed": class="hl-string">"Service Request Resolved",
    class="hl-string">"Closed": class="hl-string">"Service Request Closed",
    class="hl-string">"Cancelled": class="hl-string">"Service Request Canceled",
    class="hl-string">"Rejected": class="hl-string">"Request Rejected"
    class=class="hl-string">"hl-comment"># Add other custom statuses as needed
}

class=class="hl-string">"hl-comment"># Map specific status transitions for more granular activities
TRANSITION_ACTIVITY_MAP = {
    (class="hl-string">'Pending', class="hl-string">'In Progress'): class="hl-string">'Request Resumed'
    class=class="hl-string">"hl-comment"># You can add other specific transitions here
}

def get_jwt_token(base_url, username, password):
    class="hl-string">"""Authenticates with the REST API and returns a JWT token."""
    url = fclass="hl-string">"{base_url}/api/jwt/login"
    payload = {
        class="hl-string">'username': username,
        class="hl-string">'password': password
    }
    headers = {class="hl-string">'Content-Type': class="hl-string">'application/x-www-form-urlencoded'}
    try:
        response = requests.post(url, data=payload, headers=headers)
        response.raise_for_status()
        return response.text
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error getting JWT token: {e}")
        return None

def get_paginated_results(base_url, token, form_name, query, fields):
    class="hl-string">"""Retrieves all entries from a form, handling pagination."""
    all_entries = []
    url = fclass="hl-string">"{base_url}/api/arsys/v1/entry/{form_name}"
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'AR-JWT {token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    params = {
        class="hl-string">'q': query,
        class="hl-string">'fields': fclass="hl-string">'values({fields})',
        class="hl-string">'limit': 1000
    }
    while True:
        try:
            response = requests.get(url, headers=headers, params=params)
            response.raise_for_status()
            data = response.json()
            all_entries.extend(data[class="hl-string">'entries'])
            class=class="hl-string">"hl-comment"># Check for next page link
            if class="hl-string">'next' in data[class="hl-string">'_links']:
                url = data[class="hl-string">'_links'][class="hl-string">'next'][class="hl-string">'href']
                params = {}
            else:
                break
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {form_name}: {e}")
            break
    return all_entries

def main():
    class="hl-string">"""Main function to orchestrate the data extraction."""
    print(class="hl-string">"Starting extraction...")
    token = get_jwt_token(BASE_URL, USERNAME, PASSWORD)
    if not token:
        return

    class=class="hl-string">"hl-comment"># 1. Get all Service Requests within the date range
    print(fclass="hl-string">"Fetching service requests from {START_DATE} to {END_DATE}...")
    srm_query = fclass="hl-string">"'Submit Date' >= \"{START_DATE}T00:00:00Z\class="hl-string">" AND 'Submit Date' <= \"{END_DATE}T23:59:59Z\class="hl-string">""
    srm_fields = class="hl-string">"Request Number,Submit Date,Priority,Assignee,Assigned Group,Title"
    srm_requests = get_paginated_results(BASE_URL, token, class="hl-string">"SRM:Request", srm_query, srm_fields)
    print(fclass="hl-string">"Found {len(srm_requests)} service requests.")

    event_log = []
    current_time = datetime.datetime.utcnow().isoformat() + class="hl-string">"Z"

    class=class="hl-string">"hl-comment"># 2. For each request, get its audit history
    for i, req in enumerate(srm_requests):
        req_values = req[class="hl-string">'values']
        request_id = req_values.get(class="hl-string">'Request Number')
        if not request_id:
            continue

        print(fclass="hl-string">"Processing request {i+1}/{len(srm_requests)}: {request_id}")

        class=class="hl-string">"hl-comment"># Add class="hl-string">'Service Request Submitted' event
        event_log.append({
            class="hl-string">'ServiceRequestId': request_id,
            class="hl-string">'ActivityName': class="hl-string">'Service Request Submitted',
            class="hl-string">'EventStartTime': req_values.get(class="hl-string">'Submit Date'),
            class="hl-string">'EventEndTime': req_values.get(class="hl-string">'Submit Date'),
            class="hl-string">'RequestStatus': class="hl-string">'Submitted',
            class="hl-string">'ServiceType': req_values.get(class="hl-string">'Title'),
            class="hl-string">'Priority': req_values.get(class="hl-string">'Priority'),
            class="hl-string">'AssignedAgent': req_values.get(class="hl-string">'Assignee'),
            class="hl-string">'AssignedTeam': req_values.get(class="hl-string">'Assigned Group'),
            class="hl-string">'SourceSystem': class="hl-string">'BMC Helix ITSM',
            class="hl-string">'LastDataUpdate': current_time
        })

        class=class="hl-string">"hl-comment"># 3. Fetch audit log for status changes
        audit_query = fclass="hl-string">"'Original Request ID' = \"{request_id}\class="hl-string">" AND 'Fields Changed' LIKE \"%Status%\class="hl-string">""
        audit_fields = class="hl-string">"New Value,Old Value,Log Key 1,Create Date"
        audit_entries = get_paginated_results(BASE_URL, token, AUDIT_FORM_NAME, audit_query, audit_fields)
        
        class=class="hl-string">"hl-comment"># Sort audit entries by date to ensure correct order
        audit_entries.sort(key=lambda x: x[class="hl-string">'values'][class="hl-string">'Create Date'])

        for audit_entry in audit_entries:
            audit_values = audit_entry[class="hl-string">'values']
            class=class="hl-string">"hl-comment"># Ensure we are only processing status changes
            if class="hl-string">'Status' not in audit_values.get(class="hl-string">'Log Key 1', class="hl-string">''):
                continue

            new_status = audit_values.get(class="hl-string">'New Value')
            old_status = audit_values.get(class="hl-string">'Old Value')
            timestamp = audit_values.get(class="hl-string">'Create Date')
            activity_name = None

            class=class="hl-string">"hl-comment"># Check for specific transitions first
            if (old_status, new_status) in TRANSITION_ACTIVITY_MAP:
                activity_name = TRANSITION_ACTIVITY_MAP.get((old_status, new_status))
            class=class="hl-string">"hl-comment"># Then check for general status mappings
            elif new_status in STATUS_ACTIVITY_MAP:
                activity_name = STATUS_ACTIVITY_MAP.get(new_status)

            if activity_name:
                event_log.append({
                    class="hl-string">'ServiceRequestId': request_id,
                    class="hl-string">'ActivityName': activity_name,
                    class="hl-string">'EventStartTime': timestamp,
                    class="hl-string">'EventEndTime': timestamp,
                    class="hl-string">'RequestStatus': new_status,
                    class="hl-string">'ServiceType': req_values.get(class="hl-string">'Title'),
                    class="hl-string">'Priority': req_values.get(class="hl-string">'Priority'),
                    class="hl-string">'AssignedAgent': req_values.get(class="hl-string">'Assignee'), class=class="hl-string">"hl-comment"># Note: Assignee is static from the main request
                    class="hl-string">'AssignedTeam': req_values.get(class="hl-string">'Assigned Group'), class=class="hl-string">"hl-comment"># Note: Team is static from the main request
                    class="hl-string">'SourceSystem': class="hl-string">'BMC Helix ITSM',
                    class="hl-string">'LastDataUpdate': current_time
                })
    
    class=class="hl-string">"hl-comment"># 4. Write to CSV
    output_file = class="hl-string">'servicerequest_event_log.csv'
    print(fclass="hl-string">"Writing event log to {output_file}...")
    if event_log:
        with open(output_file, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as f:
            writer = csv.DictWriter(f, fieldnames=event_log[0].keys())
            writer.writeheader()
            writer.writerows(event_log)
        print(class="hl-string">"Extraction complete.")
    else:
        print(class="hl-string">"No events to write.")

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

Your Service Request Management Data Template

Your Service Request Management Data Template

Service Request Management Attributes

Service Request Management Activities

Extraction Guides

BMC Helix ITSM Smart Reporting for Service Requests

Steps

Configuration

a Sample Query config

Direct SQL Query on AR System Database Tables

Steps

Configuration

a Sample Query sql

Python Script Extraction via the BMC Helix REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Unlock Efficiency: Optimize Service Request Management Today

Your Service Request Management Data Template

Your Service Request Management Data Template

Service Request Management Attributes

Service Request Management Activities

Extraction Guides

BMC Helix ITSM Smart Reporting for Service Requests

Steps

Configuration

a Sample Query config

Direct SQL Query on AR System Database Tables

Steps

Configuration

a Sample Query sql

Python Script Extraction via the BMC Helix REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Unlock Efficiency: Optimize Service Request Management Today