How to list and plot total and average events by hour?

[ejohn]

I'm trying to do something pretty straightforward, and have looked at  practically every "average" answer on Splunk Community, but no dice.  I want to compare total and average webpage hits on a line chart.  I calculated and confirmed the standard (fillnull value=0) and cumulative (fillnull value=null) averages with the following:

host....
| bin _time span=1h
| eval date_hour=strftime(_time, "%H")
| stats count as hits by date, date_hour
| xyseries date, date_hour, hits
| fillnull value=0
|appendpipe
    [| untable date, date_hour, hits
     | eventstats avg(hits) as avg_events by date_hour
     | eval "Average Events"= avg_events
     | xyseries date date_hour avg_events
     | head 1
     | eval date="Average Events"]

How do I plot hits and avg_events on a line chart by date_hour?  Also,  if there is less convoluted SPL to get the same results, I'd love to know that as well—because I think I found where Google ends.

Thanks! 

[gcusello]

Hi @ejohn,

if you grouped timestamps for hours using the bin command, you dont need the following commands, please try something like this:

<your_search>
| eventstats count AS total
| bin _time span=1h
| stats values(total) AS total count BY _time
| eval average=count/total

If I didn't center your requirement, please share a sample of the desidered output

Ciao.

Giuseppe

[ejohn]

Ciao @gcusello ,

I've played around with the commands a bit, but still not getting the desired results.  I may not have explained well in my initial post, but I'm trying to get event counts and averages for a specific time range.  For example, if I have the following in my data:

date	01	12	17
08-01-2022	1
08-02-2022	1		2
08-03-2022		1	7
08-04-2022	1

I'd like to calculate the averages as follows:

standard avg = Σ events/# of events

cumulative avg = Σ events/# of days

date_hour	Σ events	# events	# days	standard avg	cumulative avg
01	3	3	6	1	0.50
12	1	1	6	1	0.16667
17	9	2	6	4.5	1.5

The calculations would be executed separately so I can plot one line chart with Σ events vs standard avg and another with Σ events vs. cumulative avg.   The table for standard average would look like this: 

date_hour	Σ events	# events	standard avg
01	3	3	1
12	1	1	1
17	9	2	4.5

Thanks for the help! 

[gcusello]

Hi @ejohn,

ok, your first table could be created using this search:

<your_search>
| eventstats count AS total
| bin _time span=1h
| chart count OVER _time BY hits

 Instead to arrive to the final table, you could try something like this example:

index=_internal 
| eventstats count AS total
| stats values(total) AS total dc(date_hour) AS date_hour count BY sourcetype
| eval average=round(count/total*100,2), cumulative=count/date_hour

in your case, you should try something like this.

<your_search>
| eventstats count AS total
| eval day=strftime(_time,"%m/%d/%Y")
| stats values(total) AS total dc(day) AS days count BY sourcetype
| eval average=round(count/total*100,2), cumulative=round(count/days,2)

Ciao.

Giuseppe

[ejohn]

Hi @gcusello ,

You put me on the right track!  I modified what you provided to calculate the average:

| eventstats count AS total by date_hour
| eval day=strftime(_time,"%m/%d/%Y")
| stats dc(day) AS days count BY date_hour
| eval average=round(count/days,2)
| sort by date_hour
| rename count as SumOfEvents, days as NumOfEvents

I used the syntax below separately to calculate the number of days in my selected date range.  So for the month of August, TotalDays will have a value of 31.

| eventstats dc(date_mday) as daysNmonth
| timechart sum(daysNmonth)
| stats count(_time) as TotalDays

 
I'm having trouble incorporating TotalDays with the first block of syntax to calculate:  

cumulative=round(SumOfEvents/TotalDays,2)

[gcusello]

Hi @ejohn,

I cannot test your search, but it should run:

| eventstats count AS total by date_hour
| eval day=strftime(_time,"%m/%d/%Y")
| eventstats dc(date_mday) as daysNmonth
| eventstats count(_time) as TotalDays
| stats 
   dc(day) AS days 
   values(daysNmonth) AS daysNmonth 
   values(TotalDays) AS TotalDays 
   count 
   BY date_hour
| eval average=round(count/days,2)
| sort by date_hour
| rename count as SumOfEvents, days as NumOfEvents
| eval cumulative=round(SumOfEvents/TotalDays,2)

Ciao.

Giuseppe