SPL to output Time To Resolution for Incidents by Urgency

[pdgill314]

So, I have been struggling with this for a few days. I have thrown it against generative AI and not getting exactly what I want. 

We have a requirement to ensure a percentage of timely critical event completion investigation per month for Critical and High notable events in Splunk ES. 

I have this query which gives me the numerator and denominator for the events, but does not break it out by Urgency/Severity: 

| inputlookup incident_review_workflow_audit  
| where notable_time > relative_time(now(), "-1mon@mon") 
  AND notable_time < relative_time(now(), "@mon") 
| eval
    EventOpenedEpoch      = notable_time,
    TriageStartedEpoch    = triage_time,
    ResolutionEpoch       = notable_time + new_to_resolution_duration,
    DaysInNewStatus       = round(new_duration/86400,2),
    DaysToResolution      = round(new_to_resolution_duration/86400,2)
| where new_to_resolution_duration>0
| eval
    "Event Opened"           = strftime(EventOpenedEpoch,      "%Y-%m-%d %H:%M:%S"),
    "Triage process started" = strftime(TriageStartedEpoch,    "%Y-%m-%d %H:%M:%S"),
    "Event Resolved"         = strftime(ResolutionEpoch,       "%Y-%m-%d %H:%M:%S")
| rename rule_id AS "Event ID"
| table
    "Event ID",
    "Event Opened",
    "Triage process started",
    "Event Resolved",
    DaysInNewStatus,
    DaysToResolution
| sort - DaysToResolution 

Event ID	Event Opened	Triage process started	Event Resolved	DaysInNewStatus	DaysToResolution
4160DC1A-7DF2-4F18-A229-2BA45F1ED9FA@@notable@@e90ff7db7d8ff92bbe8aa4566c1bab37	2025-07-05 02:02:13	2025-07-07 09:39:07	2025-07-21 13:26:26	2.32	16.48
7C412294-C46A-448A-8170-466CE301D56A@@notable@@0feff824336394dbe4dcbedcbf980238	2025-07-05 02:02:08	2025-07-07 09:39:07	2025-07-21 13:26:26	2.32	16.48

This query does give me the Urgency for events, but does not give me time to resolution:

`notable` | search (urgency=critical) | eval startTime=strftime (_time, "%Y-%m-%d %H:%M:%S") | table startTime, rule_id source comment urgency reviewer status_description owner_realname status_label

startTime	rule_id	source	comment	urgency	reviewer	status_description	owner_realname	status_label
2025-07-29 09:30:16	4160DC1A-7DF2-4F18-A229-2BA45F1ED9FA@@notable@@5ebbdf0e0821b477785b018e29d44973	Endpoint - ADFS Smart Lockout Events - Rule		critical		Event has not been reviewed.	unassigned	New
2025-07-29 09:30:12	AD72F249-8457-4D5E-9557-9621E2F5D3FF@@notable@@3043a1f3a2fbc3f92f67800a066ada66	Endpoint - ADFS Smart Lockout Events - Rule		critical		Event has not been reviewed.	unassigned	New
2025-07-29 07:15:18	7C412294-C46A-448A-8170-466CE301D56A@@notable@@54a0ffabacbf083cb7f2e370937fc2bf	Endpoint - ADFS Smart Lockout Events - Rule	The event has been triaged	critical	abcde00	Initial analysis of threat	John Doe	Triage

Trying to combine them to get time to resolution plus urgency (so I can filter on urgency) has been a complete mess. If I do manage to combine them by trimming around the Event ID / rule_id, it doesn't give me the expected number or half the time it is missing the urgency. 

Is there something I am missing, or is this even possible?

Thanks in advance. 

[livehybrid]

Hi @pdgill314 

You could start with the `notable` search and then to a lookup on Rule_ID/event_id, however there might be an easier way. I believe the KV Store incident_review_lookup has an urgency field...

Try:

| inputlookup incident_review_workflow_audit 
| lookup incident_review_lookup rule_id OUTPUT urgency 
| where urgency="critical"
``` then the rest as before ```
| where notable_time > relative_time(now(), "-1mon@mon") 
  AND notable_time < relative_time(now(), "@mon") 
| eval
    EventOpenedEpoch      = notable_time,
    TriageStartedEpoch    = triage_time,
    ResolutionEpoch       = notable_time + new_to_resolution_duration,
    DaysInNewStatus       = round(new_duration/86400,2),
    DaysToResolution      = round(new_to_resolution_duration/86400,2)
| where new_to_resolution_duration>0
| eval
    "Event Opened"           = strftime(EventOpenedEpoch,      "%Y-%m-%d %H:%M:%S"),
    "Triage process started" = strftime(TriageStartedEpoch,    "%Y-%m-%d %H:%M:%S"),
    "Event Resolved"         = strftime(ResolutionEpoch,       "%Y-%m-%d %H:%M:%S")
| rename rule_id AS "Event ID"
| table
    "Event ID",
    "Event Opened",
    "Triage process started",
    "Event Resolved",
    DaysInNewStatus,
    DaysToResolution
    urgency
| sort - DaysToResolution 

Im not infront of an ES deployment at the minute so sorry I cant test completely!

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[pdgill314]

Sorry for the late reply. 

What that ends up giving me is:

Unable to parse event_time_field='_time', check whether it is in epoch format.

0 results (7/1/25 12:00:00.000 AM to 8/1/25 12:00:00.000 AM)

I tried manipulating it with generative AI some more and continued to hit roadblocks.