Splunk Enterprise Security

SPL to output Time To Resolution for Incidents by Urgency

pdgill314
Path Finder

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 IDEvent OpenedTriage process startedEvent ResolvedDaysInNewStatusDaysToResolution
4160DC1A-7DF2-4F18-A229-2BA45F1ED9FA@@notable@@e90ff7db7d8ff92bbe8aa4566c1bab372025-07-05 02:02:132025-07-07 09:39:072025-07-21 13:26:262.3216.48
7C412294-C46A-448A-8170-466CE301D56A@@notable@@0feff824336394dbe4dcbedcbf9802382025-07-05 02:02:082025-07-07 09:39:072025-07-21 13:26:262.3216.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

 

 

startTimerule_idsourcecommenturgencyreviewerstatus_descriptionowner_realname status_label
2025-07-29 09:30:164160DC1A-7DF2-4F18-A229-2BA45F1ED9FA@@notable@@5ebbdf0e0821b477785b018e29d44973Endpoint - ADFS Smart Lockout Events - Rule critical Event has not been reviewed.unassignedNew
2025-07-29 09:30:12AD72F249-8457-4D5E-9557-9621E2F5D3FF@@notable@@3043a1f3a2fbc3f92f67800a066ada66Endpoint - ADFS Smart Lockout Events - Rule critical Event has not been reviewed.unassignedNew
2025-07-29 07:15:187C412294-C46A-448A-8170-466CE301D56A@@notable@@54a0ffabacbf083cb7f2e370937fc2bfEndpoint - ADFS Smart Lockout Events - RuleThe event has been triagedcriticalabcde00Initial analysis of threatJohn DoeTriage

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. 

0 Karma

livehybrid
Ultra Champion

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!

:glowing_star: 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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...