All Apps and Add-ons

compare | stats count by x y z over different days

Aufex
New Member

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
SplunkTrust
SplunkTrust

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

niketnilay
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
SplunkTrust
SplunkTrust

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

woodcock
Esteemed Legend

You need the timewrap command.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.