Splunk Search

## Daily Average Event Count by Field

Engager

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

Tags (2)
1 Solution
SplunkTrust

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.

SplunkTrust

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.