Splunk Search

adding percentage of SLA breach

avoelk
Communicator

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

avoelk_0-1635509142530.png

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"

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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

avoelk
Communicator

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

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eval ttt=(review_time - '_time')
| eval breach=if(ttt>60*60*2,1,0)
| stats count, sum(breach) as breach by rule_name
| eval OK=count-breach
| table rule_name OK breach

Then visualise as a stacked column chart?

avoelk
Communicator

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

avoelk_0-1635513777558.png

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? 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

avoelk
Communicator

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? 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

avoelk
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...