Splunk Search

adding percentage of SLA breach

avoelk
Path Finder

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
Path Finder

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
Path Finder

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
Path Finder

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
Path Finder

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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...