Splunk Search

Get event count for last hour, event count for yesterday, and use the two counts to get change % via tstats

SausagePizzza
Engager

Hello, 

I'm trying to get a few things from my tstats search:

  • count for last hour
  • count for yesterday
  • Use the two counts to get % change

Typically I'd do a nested eval statement to get the info but it does not work with tstats:

 

| eval lastHours = relative_time(now(),"-h@h")
| eval yesterday = relative_time("-1d@d","-2d@d")
| stats count(yesterday) as yesterday count(lastHours) as lastHours by user src_ip
| eval ChangePercent = (lastHours - yesterday) / 100

 

 

How would I get the info above with tstats?

 

| tstats `summariesonly` values(All_Traffic.src_zone) AS src_zone, values(All_Traffic.dest_ip) AS dest_ip, values(All_Traffic.dest_zone) AS dest_zone, values(All_Traffic.dest_port) AS dest_port, values(All_Traffic.rule) AS rule, values(All_Traffic.app) AS app, values(sourcetype) as event_source
    from datamodel=Network_Traffic.All_Traffic 
    where All_Traffic.action="allowed" AND (earliest=-2d@d latest=now) by All_Traffic.user, All_Traffic.src_ip 
| `drop_dm_object_name("All_Traffic")`

 

 

Labels (2)
Tags (3)

somesoni2
Revered Legend

Add this to your current search (adjust percent calculation per your need)

...| eval lastHour=if(_time>=relative_time(now(),"-1h@h") AND _time<relative_time(now(),"@h"), eventCount,0)
| eval yesterday=if(_time>=relative_time(now(),"-1d@d") AND _time<relative_time(now(),"@d"), eventCount,0)
| stats sum(lastHour) as lastHour sum(yesterday) as yesterday
| eval Perc_Change=round(lastHour*100/yesterday,2)