How to create a bar chart that compares values over two time periods?

[swedishmike]

I've created a search that displays the top 10 blocked destination ports over the last 4 hours. I've also managed to create a search that not only does that but also shows the top 10 blocked destination ports for the same time period for the previous day.

This works, albeit a bit slowly, for me:

index=netfw sourcetype="firewall" action!="allowed" earliest=-4h@d latest=now |
 stats count by dest_port |
 sort -count | 
 head 10| 
 multikv| 
 eval ReportKey="Last 4 Hours" | 
 append [search index=netfw sourcetype="firewall" action!="allowed" earliest=-2d@d latest=-1d@d |
 stats count by dest_port |  
 sort -count |  
 head 10 |  
 multikv|  
 eval ReportKey="Yesterday - same timeframe" | 
 eval  _time=_time+86400]

As I'd (sort of) expect this generates three columns: dest_port, count, ReportKey. If I select Bar Chart in the Visualization tab I actually get two bar charts, 'count' and 'ReportKey'. Only the 'count' one have any data in it.

What I'm trying to achieve now is to get a bar chart to work which displays the top 10 for both time periods, if that makes sense? Basically you should be able to look at the chart and see today's data in say yellow and yesterdays in say blue. It might be that port 80 the top blocker today and yesterday but there's a different port (and count) in second.

Is this something that is possible to achieve or am I over-reaching again? Also - not sure if this is the most efficient way to do this search so any input on making this quicker as well would be really appreciated.

Thank in advance

[somesoni2]

Give this a try. Simple conversion from your search (also updated the time range of subsearch, yesterday would from -1d@d to @d)

index=netfw sourcetype="firewall" action!="allowed" earliest=-4h@d latest=now 
| stats count as "Last 4 Hours" by dest_port 
| sort 10 -"Last 4 Hours" 
| append [search index=netfw sourcetype="firewall" action!="allowed" earliest=-1d@d latest=@d 
| stats count as "Yesterday - same timeframe" by dest_port 
| sort 10 -"Yesterday - same timeframe" 
| stats values(*) as * by dest_port

[swedishmike]

Many thanks for your reply - but I think something is going wrong somewhere.

I only get the "Yesterday - same timeframe" output, with the portnumbers but the count is stuck at 1 for each.

Also, just so I get don't misunderstand - the time-range change you made for 'Yesterday - same timeframe" makes that cover the whole day, doesn't it? What I'm after is the same four hours but for the day before if you see what I mean?

Cheers!

[somesoni2]

Give this a try. Fixed the time-range part.

 index=netfw sourcetype="firewall" action!="allowed" earliest=-4h@h latest=now 
 | stats count as "Last 4 Hours" by dest_port 
 | sort 10 -"Last 4 Hours" 
 | append [search index=netfw sourcetype="firewall" action!="allowed" earliest=-1d latest=-4h@h-1d 
 | stats count as "Yesterday - same timeframe" by dest_port 
 | sort 10 -"Yesterday - same timeframe" ]
 | stats values(*) as * by dest_port

[lguinn2]

Try this:

 index=netfw sourcetype="firewall" action!="allowed" earliest=-28h@h latest=@h |
  eval date=case(_time < relative_time(now(),"-24h@h"),"Yesterday",
                 _time < relative_time(now(),"-4h@h"),"Drop",
                 1==1,"Today") |
  where date!="Drop" |
  top dest_port by  date
  xyseries dest_port date count

It may be more efficient, too. Note that I cut off any partial hour, so that if you run this at 8:18, it will be looking at the time period between 4:00 and 8:00.

[swedishmike]

Thanks for your reply but I'm not getting the output I (and probably you) would be expecting.

Initially I got the error: "Error in 'top' command: The split by field 'dest_port' cannot be repeated." when I tried your suggestion, after adding a | after "top dest_port by date" it runs through.

However, when I run it all I get is values for Yesterday, nothing for today. I'm trying to read up on the logic/functions you've used here to see if I can spot something but I thought I'd let you know about what I see in my end.

Cheers!

[swedishmike]

Actually - it's now showing both Today and Yesterday but I need to work on the sorting/tabulation I think.

The idea was to get the bar chart to show the #1 for today with port number and count and in another colour yesterday's #1 with port number and count, then #2 etc etc.

Some of them might be the same port but with a different count or they could be different ports altogether.

At the moment it sorts on port number so the chart looks a bit weird.

It has of course dawned on me that what I'm wanting to do might be impossible. 😉

[niketn]

Nevertheless, the query that you are trying to use to append results of one days count with another day is actually meant for timechart with single series per day, that is why you have _time adjusted in the end with 24*60*60=86400. (PS: Splunk has introduced timewrap command 6.5 SPL syntax recently to do something similar with ease).

Since you are building generating multiple series above might not fit in perfectly as you might have already seen. Following is what you can try.
1) Instead of stats, sort, head... I have used top command
2) Instead of static ReportKey, I have added the same to Series being plotted
4) I have converted latest time to now and @s which snaps to current second
5) Corrected Yesterday to -1d@d in your query and similarly changed earliest to -0d@d (Today from beginning of the day)

Finally, append might not be able to retain all the events from multiple days so it may drop out older event. In other words, your previous day stats might not be correct so ensure that you run subsearch for yesterday in a separate window and test the counts. If it does not, explore the option of using date_mday as the group by field. Second option would be to summarize the data first and use following query on your summary index.

index=netfw sourcetype="firewall" dest_port=* action!="allowed" earliest=-0d@d latest=now |
 eval ReportKey = dest_port  + "-" + "Today" |
 top 10 ReportKey showperc=f |
 append [search index=netfw sourcetype="firewall" dest_port=* action!="allowed" earliest=-1d@d latest=-1d@s |
 eval ReportKey= dest_port + "-" + "Yesterday" |
 top 10 ReportKey showperc=f] |
 sort ReportKey

On a completely unrelated note, you have really reversed the SPL pipe syntax on me ;),Nevertheless, the query that you are trying to use to append results of one days count with another day is actually meant for timechart with single series per day, that is why you have _time adjusted in the end with 24*60*60=86400. (PS: Splunk has introduced timewrap command 6.5 SPL syntax recently to do something similar with ease).

Since you are building generating multiple series above might not fit in perfectly as you might have already seen. Following is what you can try.
1) Instead of stats, sort, head... I have used top command
2) Instead of static ReportKey, I have added the same to Series being plotted
4) I have converted latest time to now and @s which snaps to current second
5) Corrected Yesterday to -1d@d in your query and similarly changed earliest to -0d@d (Today from beginning of the day)

Finally, append might not be able to retain all the events from multiple days so it may drop out older event. In other words, your previous day stats might not be correct so ensure that you run subsearch for yesterday in a separate window and test the counts.

index=netfw sourcetype="firewall" dest_port=* action!="allowed" earliest=-0d@d latest=now |
 eval ReportKey = dest_port  + "-" + "Today" |
 top 10 ReportKey showperc=f |
 append [search index=netfw sourcetype="firewall" dest_port=* action!="allowed" earliest=-1d@d latest=-1d@s |
 eval ReportKey= dest_port + "-" + "Yesterday" |
 top 10 ReportKey showperc=f] |
 sort ReportKey

On a completely unrelated note, you have really reversed the SPL pipe syntax on me 😉