Daily Average Event Count by Field

[dan_growler]

Let's say I have a field called "host" and it can take the following values: host1, host2, host3.

I'm having trouble writing a query which displays the number of hits each host had today and the average number of hits it has had over all time. A hit is defined as the host appearing in the field so if I had an event where host=host1 - that would count as a hit for host1 (essentially a count).

The output would look something like this:

        Hits_Today    Average_Hits_over_all_time
host1        5              10
host2       12               3
host3       23              16

Any advice?

[sideview · Accepted solution]

Well this will give you a table that's close to what you're looking for:

<your search> | eval hit_today = if (_time < relative_time(now(), "@d"), 0, 1) | stats sum(hit_today) as Hits_Today count as Total by host

Except that the last column is just the total count over the given timerange, not the average per day.

If you run the search specifically over 'last 7 days', and you divide 'Total' by 7 with another eval command, you'll have the average count for those 7 days.

If you need to calculate that daily average generically you can use the addinfo command to do it.

see this question and answer which deals with similar issues.

http://answers.splunk.com/questions/2712/line-chart-comparing-yesterdays-result-with-todays-result-i...