How to generate a report to analysis the RITM stage, approver and approval duration

[major li]

Hi community, 

I have request to generate a report to analysis the duration of each stage in RITM level, together with the approvers in each stages. I could find the metric_instance to calculate the duration of each stage, however, i could not find a table to calculate the approvers under specific stage, the sysapproval_approver table will only log the current stage.

Could you please help me? thank you. 

[MaxMixali]

You're correct that this is a common challenge in ServiceNow. The sysapproval_approver table doesn't inherently track which approval stage each approver belongs to. Here are some approaches to solve this you can try do do this: 

Use this code Javascript to usese the wf_history table (Workflow History)

// Query example var gr = new GlideRecord('wf_history'); gr.addQuery('id', ritm_sys_id); // Your RITM sys_idgr.addQuery('activity_definition_name', 'CONTAINS', 'Approval'); // Filter for approval activities gr.query();while(gr.next()) { // gr.name = Stage/Activity name // gr.actual_duration = Duration of that activity // gr.result = Approval result (approved/rejected) }

Additonal notes:

The wf_history table captures:

• Each workflow activity (including approval stages)
• Duration of each activity
• Who acted on it
• The result

2. Cross-reference sysapproval_approver with wf_history

You can correlate the two tables:

• Use wf_history to identify the stage names and durations
• Match sysapproval_approver records to stages based on timestamps
• Link approvers to stages by comparing sys_created_on dates

3. Custom solution: Create a tracking table

If you need ongoing tracking, consider:

• Creating a custom table to capture stage + approver relationships
• Using a Business Rule on sysapproval_approver to log the current stage context when an approval record is created
• Store the stage information (perhaps from sc_req_item.stage or workflow context)

4. Check for Stage field customization

Some implementations add a custom field to sysapproval_approver:

• Check if there's a field like u_approval_stage or u_stage
• This would be a custom enhancement
•