topic Re: SLA reporting in SPL in Splunk Search

SLA reporting in SPL

dm2 — Wed, 31 Jan 2024 11:12:31 GMT

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 ?

Re: SLA reporting in SPL

ITWhisperer — Wed, 31 Jan 2024 12:55:38 GMT

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")

Re: SLA reporting in SPL

dm2 — Wed, 31 Jan 2024 14:33:11 GMT

that worked for 2 results but not for the last one

Re: SLA reporting in SPL

ITWhisperer — Wed, 31 Jan 2024 15:20:48 GMT

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+"

Re: SLA reporting in SPL

dm2 — Thu, 01 Feb 2024 13:02:36 GMT

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

Re: SLA reporting in SPL

ITWhisperer — Thu, 01 Feb 2024 13:46:01 GMT

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

Re: SLA reporting in SPL

dm2 — Thu, 01 Feb 2024 15:49:32 GMT

WORKED! And this is my final query. TY

Re: SLA reporting in SPL

dm2 — Thu, 01 Feb 2024 15:52:09 GMT

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

Re: SLA reporting in SPL

ITWhisperer — Thu, 01 Feb 2024 16:29:27 GMT

Not quite - your fieldformat is using strftime rather than tostring

Re: SLA reporting in SPL

dm2 — Mon, 05 Feb 2024 09:02:16 GMT

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

Re: SLA reporting in SPL

ITWhisperer — Mon, 05 Feb 2024 09:15:15 GMT

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

Re: SLA reporting in SPL

dm2 — Mon, 05 Feb 2024 09:39:15 GMT

WORKS! thank you

Re: SLA reporting in SPL

dm2 — Mon, 05 Feb 2024 13:24:45 GMT

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

Re: SLA reporting in SPL

ITWhisperer — Mon, 05 Feb 2024 13:29:08 GMT

Try combining the two lines

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

Re: SLA reporting in SPL

dm2 — Mon, 05 Feb 2024 14:19:38 GMT

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.

Re: SLA reporting in SPL

ITWhisperer — Mon, 05 Feb 2024 15:13:21 GMT

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

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