compare | stats count by x y z over different days

[Aufex]

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

[niketn]

@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.

[DalJeanis · Accepted solution]

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

[woodcock]

You need the timewrap command.