topic Re: Listing and plotting total and average events by hour in Splunk Search

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

ejohn — Fri, 14 Oct 2022 17:48:55 GMT

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:

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!

Re: Listing and plotting total and average events by hour

gcusello — Fri, 14 Oct 2022 06:28:47 GMT

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

Re: Listing and plotting total and average events by hour

ejohn — Mon, 17 Oct 2022 16:21:43 GMT

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!

Re: Listing and plotting total and average events by hour

gcusello — Sat, 22 Oct 2022 10:16:27 GMT

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

Re: Listing and plotting total and average events by hour

ejohn — Wed, 02 Nov 2022 00:26:54 GMT

Hi @gcusello ,

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

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)

Re: Listing and plotting total and average events by hour

gcusello — Wed, 02 Nov 2022 07:31:35 GMT

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