topic Re: adding percentage of SLA breach in Splunk Search

adding percentage of SLA breach

avoelk — Fri, 29 Oct 2021 12:11:10 GMT

I'd like to add a percentage into the following panel:

I've added severity since I just want to see it for critical and high severity. now I'd like to define an sla value of , let's say 2 hours, and then want a percentage of each rules percentage of it's count breached.

so in other words: in this statistic I want to have an additional field that tells me the percentage of how many of the counted events for those rules have a longer max time to triage than 2h.

rule 1 count 20 (10 breached over 2h sla) -> a field that tells me 50%

I can't seem to find a good way to get a percentage in. here is the whole SPL (from ES mostly):

| tstats summariesonly=true allow_old_summaries=false earliest(_time) as _time FROM datamodel=Incident_Management BY source, "Notable_Events_Meta.rule_id" | rename "Notable_Events_Meta.*" as "*" | lookup update=true correlationsearches_lookup _key as source OUTPUTNEW annotations, security_domain, severity, rule_name, description as savedsearch_description, rule_title, rule_description, drilldown_name, drilldown_search, drilldown_earliest_offset, drilldown_latest_offset, default_status, default_owner, next_steps, investigation_profiles, extract_artifacts, recommended_actions | eval rule_name=if(isnull(rule_name),source,rule_name), rule_title=if(isnull(rule_title),rule_name,rule_title), drilldown_earliest=case(isint(drilldown_earliest_offset),('_time' - drilldown_earliest_offset),(drilldown_earliest_offset == "$info_min_time$"),info_min_time,true(),null()), drilldown_latest=case(isint(drilldown_latest_offset),('_time' + drilldown_latest_offset),(drilldown_latest_offset == "$info_max_time$"),info_max_time,true(),null()), security_domain=if(isnull(security_domain),"threat",lower(security_domain)), rule_description=case(isnotnull(rule_description),rule_description,isnotnull(savedsearch_description),savedsearch_description,true(),"unknown") | eval governance_lookup_type="default" | lookup update=true governance_lookup savedsearch as source, lookup_type as governance_lookup_type OUTPUT governance, control | eval governance_lookup_type="tag" | lookup update=true governance_lookup savedsearch as source, tag, lookup_type as governance_lookup_type OUTPUT governance as governance_tag, control as control_tag | eval governance=mvappend(governance,NULL,governance_tag), control=mvappend(control,NULL,control_tag) | fields - governance_lookup_type, governance_tag, control_tag | join rule_id [| inputlookup incident_review_lookup | eval _time=time | stats earliest(_time) as review_time by rule_id] | eval ttt=(review_time - '_time') | stats count,values(severity) as severity avg(ttt) as avg_ttt,min(ttt) as min_ttt,max(ttt) as max_ttt by rule_name | search severity=high OR severity=critical | `uptime2string(avg_ttt, avg_ttt)` | `uptime2string(max_ttt, max_ttt)` | `uptime2string(min_ttt, min_ttt)` | sort severity -avg_ttt | rename "*_ttt*" as "*(time_to_triage)*" | fields - "*_dec"

Re: adding percentage of SLA breach

ITWhisperer — Fri, 29 Oct 2021 12:21:44 GMT

Try something like this

| eval ttt=(review_time - '_time') | eval breach=if(ttt>60*60*2,1,0) | stats count,values(severity) as severity avg(ttt) as avg_ttt,min(ttt) as min_ttt,max(ttt) as max_ttt sum(breach) as breach by rule_name | eval percent=100*breach/count

Re: adding percentage of SLA breach

avoelk — Fri, 29 Oct 2021 13:21:31 GMT

thanks so much for that, it worked. when I now add a percent of not breached field, what would I need to do to highlight this one for example as red when it goes below 95%?

edited: change of question

Re: adding percentage of SLA breach

ITWhisperer — Fri, 29 Oct 2021 12:59:09 GMT

Then visualise as a stacked column chart?

Re: adding percentage of SLA breach

avoelk — Fri, 29 Oct 2021 13:31:15 GMT

hello! I thought about it, a visual isn't necessary 🙂 so additionally I try to do the following:

| eval OK=100-percent | eval slastatus=case(OK<95,"NOT OK",OK==95,"IN NEED OF ADJUSTMENT",OK>95,"OK",1=1,0)

this is how it looks

what I further try to do is
a) highlight the percentage in "OK" where it falls bellow 95
b) maybe a drilldown in which I click on the rule and it shows me the underlying events that breached

is it possible to change the sla based on the severity?

Re: adding percentage of SLA breach

avoelk — Fri, 29 Oct 2021 13:43:02 GMT

I changed the SLA based on severity like that:

| eval breaches=case(ttt>7200 AND severity=="critical",1,ttt>14400 AND severity=="high",1,ttt>32400 AND severity=="medium",1,ttt>86400 AND severity=="informational",1,1==1,0)

now I have different slas for different severity levels. now, if the sla was breached more than 5% of total events (bellow 95% ok) then it should be highlighted red.

afterwards I'd generate another panel in which all breached events are shown. might be easier than a drilldown 🙂 what do you think?

Re: adding percentage of SLA breach

ITWhisperer — Fri, 29 Oct 2021 13:43:22 GMT

Highlight based on value is possibly - there are numerous answers about doing this

Drilldown is possible - see splunk documentation

For sla based on severity, you could do a further lookup to get another field with the corresponding sla value against which to compare (that's the approach I use).

Re: adding percentage of SLA breach

ITWhisperer — Fri, 29 Oct 2021 13:45:29 GMT

Or do both - i.e. have a panel with all breach events, but also have a drilldown to allow the user the hone in on a subset of the events (by rule name?)

Re: adding percentage of SLA breach

avoelk — Tue, 02 Nov 2021 08:39:59 GMT

So I highlighted everything accordingly too but it doesn't seem to work to show the underlying events that are causing those breaches 😕

the spl is in tstats (mostly copied from ES) and within ES upon clicking a rule it forwards me to a different dashboard in incident review and shows me all the single events/incidents. I can't seem to mimik this behavior with my own query/dashboard. I guess partially because I don't use rules but only want to see those events that caused the breach.

do you have any input on this? I'm not sure how I can use my own field ES doesn't know about to show me the underlying incidents that caused those breaches.

Re: adding percentage of SLA breach

ITWhisperer — Tue, 02 Nov 2021 11:48:21 GMT

Perhaps you could look at it the other way around - construct a dashboard/panel which has the results you want and then look at how the parameters to the search used by this dashboard can be set as tokens by the drilldown from the first panel.