topic Re: Average event count using only business days (M-F) in Splunk Search

Average event count using only business days (M-F)?

JoshSaunders — Wed, 15 Mar 2023 16:18:30 GMT

I have a specific event that I'm looking to do an average count for the past 5 business days.

Right now, I'm able to get the weekly average with the following search, but want to restrict that count to only business days, so that the average is more reflective of a normal workday. Including weekends significantly lowers the running average, so the information isn't helpful.

source="wineventlog:application" EventCode=9999 | timechart span=7d count as Avg | eval Avg=round(Avg/7,2)

Thanks in advance for any assistance.

Re: Average event count using only business days (M-F)

gcusello — Tue, 14 Mar 2023 11:09:22 GMT

Hi @JoshSaunders,

if you want to exclude only Saturday and Sunday, you can add this filter to the main search and you easily solve your need:

source="wineventlog:application" EventCode=9999 NOT (date_wday="saturday" OR date_wday="sunday") | bin span=1d _time | stats avg(count) AS Avg BY _time | eval Avg=round(Avg,2)

if you haven't the date_wday field, you have to extract adding the following eval expression

if insteads you want to consider also holydays, you have to create a lookup containing the holidays and filter your search for this lookup.

In other words, you create a lookup containing all the holydays and the weekends in a column called e.g. holyday, then you have to run something like this:

Put attention that the date format in the lookup will be the same of the date field (%Y-%m-%d" or another) .

Ciao.

Giuseppe

Re: Average event count using only business days (M-F)

JoshSaunders — Tue, 14 Mar 2023 11:35:36 GMT

Thank you for the reply. When I use the following:

It displays a "0" with an arrow to a smaller zero instead of the average number per day.

Doing a search adding each part, it does appear to remove Sat and Sun data, though.

Re: Average event count using only business days (M-F)

gcusello — Tue, 14 Mar 2023 11:55:37 GMT

Hi @JoshSaunders,

which visualization are you using?

check in a table if results are correct, then you can define visualization.

Ciao.

Giuseppe

Re: Average event count using only business days (M-F)

JoshSaunders — Tue, 14 Mar 2023 11:56:11 GMT

I should've mentioned that this is for a <single> dashboard value. So I have it as follows:

<single> <title>Daily Average</title> <search> <query> source="wineventlog:application" EventCode=9999 | eval date_wday=strftime(_time,"%A") | search NOT (date_wday="saturday" OR date_wday="sunday") | bin span=1d _time | stats avg(count) AS Avg BY _time | eval Avg=round(Avg,2) </query> </search> </single>

Re: Average event count using only business days (M-F)

JoshSaunders — Tue, 14 Mar 2023 11:58:17 GMT

When I put the exact code into search field, I get a table with dates (which are M-F dates, which is good) on the left under "_time" and an "Avg" column on the right that is blank.

Re: Average event count using only business days (M-F)

gcusello — Tue, 14 Mar 2023 12:04:33 GMT

Hi @JoshSaunders,

in this case results shuld be only two values: the acxtual value and the previous one, I forgot this requisite, so please try this:

Ciao.

Giuseppe

Re: Average event count using only business days (M-F)

JoshSaunders — Tue, 14 Mar 2023 12:37:55 GMT

I was able to get it to work with this (I THINK):

That search displays a number that looks to be about the average amount I expected per day.

Thank you for all your help!

Re: Average event count using only business days (M-F)

gcusello — Tue, 14 Mar 2023 13:15:22 GMT

Hi @JoshSaunders,

if one answer solves your need, please accept one answer for the other people of Community or tell me how I can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

Re: Average event count using only business days (M-F)

PickleRick — Tue, 14 Mar 2023 15:16:24 GMT

Don't rely on the date_* fields unless you are absolutely sure they will be in the events and they will have proper values. They might not be generated if the original event had no timestamp in them. And they might be reflecting the event's timezone instead of your own (remember that even if all your sources and you are in the same timezone, the source might be misconfigured or might be reporting time in a predefined timezone).

To be honest, I have no idea what these are good for.

Re: Average event count using only business days (M-F)

JoshSaunders — Tue, 14 Mar 2023 16:15:27 GMT

We have Horizon VDI VMs that people login to with zeroclients. I have a scheduled task on the golden image set to run a script on login to create Event ID 9999 that logs the username, zeroclient name, zeroclient IP, VM name, and VM IP. That allows us to see what zeroclient each user is using to login to what VM.

Since we already had that event, we also wanted to be able to see a running daily average number of VDI logins that occur to determine how many VMs are in use per day in the average workday. So as VDI usage increases, we can make sure we have enough resources.