topic Re: display cumulative total and specific group summations on chart in Splunk Search

display cumulative total and specific group summations on chart

DEAD_BEEF — Fri, 01 Dec 2017 21:07:12 GMT

I have anti-virus data and I want to plot the the types of alerts on a chart over time. I want to plot the data such that I can graphically see all virus related alerts compared to all AV alerts (to include the virus count).

Using my current query, I am getting two lines on my chart, Virus and NULL.

Does the 1=1 condition count all cases as true? Or only all that didn't meet the previous cases? I need a count of all alerts to include the Virus.

current query

index=av alert=*
| eval alert_type = case(alert="virus-adware","Virus", alert="pup-virus","Virus", alert="banking-malware","Virus", 1=1,All)
| timechart count by alert_type span=1d

final working query

index=av alert=*
| eval Virus= if(alert="virus-adware" OR alert="pup-virus" OR alert="banking-malware", 1,0) 
| timechart span=1d sum(Virus) as Virus count as All

Re: display cumulative total and specific group summations on chart

somesoni2 — Fri, 01 Dec 2017 21:14:44 GMT

You need to include default case value All in double quotes. Without it, it's trying to assign value of field All which probably doesn't exist in your data (hence NULL).

index=av alert=*
 | eval alert_type = case(alert="virus-adware","Virus", alert="pup-virus","Virus", alert="banking-malware","Virus", 1=1,"All")
 | timechart count by alert_type span=1d

Re: display cumulative total and specific group summations on chart

DEAD_BEEF — Fri, 01 Dec 2017 21:22:15 GMT

Doh! I totally missed the quotes. That fixed it. Do you know if the 1=1 case is an aggregate of all or only all which do not meet previous case= statements? Reason is that I want to plot virus vs all (to include the virus count). e.g.: if it were 20 out of 100 total alerts rather than 20 and 80 other alerts.

Re: display cumulative total and specific group summations on chart

woodcock — Fri, 01 Dec 2017 21:29:53 GMT

I use true() instead of 1==1 because it is more clear.

Re: display cumulative total and specific group summations on chart

somesoni2 — Fri, 01 Dec 2017 21:30:59 GMT

It'll be for all non-matching events. If you want cumulative, try this variation.

index=av alert=*
  | eval Virus= if(alert="virus-adware" OR alert="pup-virus" OR alert="banking-malware", 1,0) 
  | timechart span=1d sum(Virus) as Virus count as All

Re: display cumulative total and specific group summations on chart

DEAD_BEEF — Fri, 01 Dec 2017 21:52:55 GMT

This works, thank you!