All Apps and Add-ons

compare | stats count by x y z over different days

Aufex
Explorer

Hi,
i audit my firewall with

action=blocked earliest=-1d@d latest=@d | stats count by host src_zone dest_zone | sort - count

that work fine and gives me a good overview. Now i try to compare each resulting count with the counts given 8 days ago.

when i try to | append []

splunk gives an error.

can you help me? thanks

0 Karma
1 Solution

DalJeanis
Legend

There's dozens of ways, here's one...

action=blocked earliest=-8d@d latest=@d 
| bin _time span=1d
| stats count as daycount by host src_zone dest_zone _time 
| eventstats min(_time) as mintime max(_time) as maxtime
| eventstats avg(daycount) as avgcount by host src_zone dest_zone
| where _time = mintime OR _time=maxtime 
| stats max(_time) as time, min(_time) as priortime, earliest(avgcount) as avgcount,
    earliest(daycount) as priorcount, latest(daycount) as daycount by host src_zone dest_zone
| sort - avgcount

Here's another, which should be a little more efficient...

| multisearch 
   [search action=blocked earliest=-1d@d latest=@d | fields host src_zone dest_zone     | bin _time span=1d]
   [search action=blocked earliest=-8d@d latest=-7d@d | fields host src_zone dest_zone     | bin _time span=1d]

| stats count as daycount by host src_zone dest_zone _time 
| stats max(_time) as time, min(_time) as priortime, avg(daycount) as avgcount,
    earliest(daycount) as priorcount, latest(daycount) as daycount by host src_zone dest_zone
| sort - avgcount

View solution in original post

0 Karma

niketn
Legend

@Aufex, which version of Splunk are you on? Also if you want to append the result to compare value over time, you should ideally use timechart along with timewrap (Splunk 6.5 onward) command as suggested by @woodcock.

If you are on Splunk 6.6 you can use the newly introduced union command which should perform better than append. Refer to documentation for details: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Union#Optimized_syntax_for_stream...

Following is the run anywhere search based on Splunk's _internal index where I have picked only 5 series to be plotted by each timechart using limit=5.

| union 
      [search index=_internal sourcetype=splunkd log_level="INFO" component=* group=* earliest=-1d@d latest=-0d@d
      | eval key=component."-".group 
      | timechart count by key useother=f limit=5] 
      [search index=_internal sourcetype=splunkd log_level="INFO" component=* group=* earliest=-7d@d latest=-6d@d
      | eval key=component."-".group 
      | timechart count by key useother=f limit=5]
      | timewrap 1day series="exact" time_format=" on %d/%m"

There might be other ways if you are not on Splunk 6.6 for union command and running older version than 6.5 for timewrap command.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

DalJeanis
Legend

There's dozens of ways, here's one...

action=blocked earliest=-8d@d latest=@d 
| bin _time span=1d
| stats count as daycount by host src_zone dest_zone _time 
| eventstats min(_time) as mintime max(_time) as maxtime
| eventstats avg(daycount) as avgcount by host src_zone dest_zone
| where _time = mintime OR _time=maxtime 
| stats max(_time) as time, min(_time) as priortime, earliest(avgcount) as avgcount,
    earliest(daycount) as priorcount, latest(daycount) as daycount by host src_zone dest_zone
| sort - avgcount

Here's another, which should be a little more efficient...

| multisearch 
   [search action=blocked earliest=-1d@d latest=@d | fields host src_zone dest_zone     | bin _time span=1d]
   [search action=blocked earliest=-8d@d latest=-7d@d | fields host src_zone dest_zone     | bin _time span=1d]

| stats count as daycount by host src_zone dest_zone _time 
| stats max(_time) as time, min(_time) as priortime, avg(daycount) as avgcount,
    earliest(daycount) as priorcount, latest(daycount) as daycount by host src_zone dest_zone
| sort - avgcount
0 Karma

woodcock
Esteemed Legend

You need the timewrap command.

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...