Splunk Search

display cumulative total and specific group summations on chart

DEAD_BEEF
Builder

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
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

View solution in original post

DEAD_BEEF
Builder

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.

0 Karma

somesoni2
Revered Legend

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
0 Karma

DEAD_BEEF
Builder

This works, thank you!

0 Karma

woodcock
Esteemed Legend

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

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!