SLA reporting in SPL

[dm2]

Hi, 

I have this query that calulates how much time the alerts are open, so far so good, but unfortunatelly if the rule name repeats (duplicate rule name) in a new event, then now() function does not know how to calculate the correct time for the first rule that triggered. 

How can I calculate SLA time without deleting duplicates and keeping the same structure as showed in the picture ? 

[dm2]

How can I sum all the time together ? stats sum did not work for me, and in addition, I need to add also 
| stats count(event_id) and get the count of critical alerts in order to do Event Count / Total Time and get an average of how much time takes to close alert by severity.

[ITWhisperer]

As I said before, you can't do calculations on strings! Try this

| stats avg(eval(incident_review_time-notable_time)) as average

[dm2]

Hi, Can you help with this one? time_difference remains empty after the calculation

[ITWhisperer · Accepted solution]

Why are you formatting the two times before performing your calculation? Subtracting one string from another doesn't give you a number!

[dm2]

WORKS! thank you

[dm2]

I tried the same concept for a different query and did not run:
This one calculates how much time took the alert to be closed on the incident manager

[ITWhisperer]

Not quite - your fieldformat is using strftime rather than tostring

[dm2]

WORKED! And this is my final query. TY

`notable_by_id("*")`
| search status_end="false"
| where severity IN ("high", "critical")
| eval timenow=now()
| eval nowstring=strftime(now(), "%H:%M:%S %p")
| eval diff=now()-_time
| eval diff=tostring(diff, "duration")
| table _time, diff, rule_name, owner, status_label, "Audit Category", urgency
| rename status_label as Status
| rename diff as time_difference 

[dm2]

Exactly, This is my search

`notable_by_id("*")`
| search status_end="false"
| where severity IN ("high", "critical")
| eval time_difference=tostring(now() - _time)
| eval time_difference = strftime(time_difference, "%H:%M:%S")
| table _time, time_difference, rule_name, owner, status_label, "Audit Category", urgency
| rename status_label as Status

[ITWhisperer]

So, why not use tostring with duration as I suggested?

[dm2]

that worked for 2 results but not for the last one 

[ITWhisperer]

For the last one, it should be 1+01:02:10 to signify 1 day + 1 hour, 2 minutes and 10 seconds, but since you haven't shown your complete search, it is difficult to know why you are missing the "1+"

[dm2]

How can I round/get rid off the decimals after the seconds?

[ITWhisperer]

Try combining the two lines

| eval time_difference=tostring(round(incident_review_time - notable_time, 0), "duration")

[ITWhisperer]

It is not clear what you are trying to achieve when _time is from the previous day.

Also, note that you could consider using

| eval time_difference=tostring(now() - _time, "duration")