Splunk Search

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

ejohn
Path Finder
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! 

Labels (6)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

ejohn
Path Finder
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:
 
date011217
08-01-20221  
08-02-20221 2
08-03-2022 17
08-04-20221  
 
I'd like to calculate the averages as follows:
 
standard avg = Σ events/# of events
cumulative avg = Σ events/# of days
 
date_hourΣ events# events# daysstandard avg cumulative avg
0133610.50
1211610.16667
179264.51.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# eventsstandard avg 
01331
12111
17924.5

 

Thanks for the help! 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

ejohn
Path Finder

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)


0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Updated Team Landing Page in Splunk Observability

We’re making some changes to the team landing page in Splunk Observability, based on your feedback. The ...

New! Splunk Observability Search Enhancements for Splunk APM Services/Traces and ...

Regardless of where you are in Splunk Observability, you can search for relevant APM targets including service ...

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...