Search to view day by day count of events by host name

[hannanp]

New to Splunk and am working with the search tool. I can pull the total counts by host no problem but am trying to figure out the most efficient way to accomplish the following:

I want to compare the last 7 days data for a host and also the trending data as far as day 1 had 1k events, day 2 had 1200 and chart this change accordingly. Would this be accomplished through the date_mday function?

I found this which I think shows the average for the last 3 days compared to today but don't think it is going to give me what I am looking for:

earliest=-3d@d latest=@d | stats count as dailyCount by date_mday | stats avg(dailyCount) as Weekly_Average |join [search earliest=-24h@h latest=@h | stats count as Todays_Count ] | fieldformat Weekly_Average=round(Weekly_Average,0)

Thanks for helping a newbie!

[emechler_splunk · Accepted solution]

Give this search a try:

.... earliest=-2h@h latest=@h | bucket _time span=1h |  eventstats count AS Count by _time | timechart span=1h Count | delta Count AS Delta p=1 | eval percDelta = (Delta/Count)*100

This will give you a table looking like this:

_time Count Delta percDelta
5/9/13 8:00:00.00 AM 199
5/9/13 9:00:00.00 AM 18939 16946 89.476741

If you want to do a day-by-day comparison, just change the earliest & latest terms at the beginning, and change the spans in the bucket and timechart commands as well. bmacias84 also brings up a good point that if you're going to be running this type of search on big data sets and/or on a frequent basis, Summary Indexing or Report Acceleration might prove useful too.

[hannanp]

okay i have something close. this for example I think pulls the last 2 hours and compares the counts. The question I have is if I run it with a specific host and use the built in "last 60 min" I seem to get a different response. Did I miss something?

earliest=-2h@h latest=@h | stats count by date_hour,host | stats first(count) as Last_Hour last(count) as Current_Hour by host | eval Percent_Change=((Current_Hour-Last_Hour)/Current_Hour)*100 | where Percent_Change<100|sort -Percent_Change | fieldformat Percent_Change=round(Percent_Change,2)

[bmacias84]

I would recommend using summary indexing at the hour level the rolling up to the day.