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

[JoshSaunders]

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.

[gcusello · Accepted solution]

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

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)

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:

source="wineventlog:application" EventCode=9999 
| eval date=strftime(_time, "%Y-%m-%d")
| search NOT [ | inputlookup holydays.csv | rename holyday AS date | fields date ]
| bin span=1d _time 
| stats avg(count) AS Avg BY _time
| eval Avg=round(Avg,2)

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

Ciao.

Giuseppe

[PickleRick]

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.

[JoshSaunders]

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.

[JoshSaunders]

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

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)

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.

[JoshSaunders]

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>

[gcusello]

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:

source="wineventlog:application" EventCode=9999 earliest=-14d latest=now
| eval date_wday=strftime(_time,"%A")
| search NOT (date_wday="saturday" OR date_wday="sunday")
| bin span=7d _time
| stats avg(count) AS Avg BY _time
| eval Avg=round(Avg,2)

Ciao.

Giuseppe

[gcusello]

Hi @JoshSaunders,

which visualization are you using?

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

Ciao.

Giuseppe

[JoshSaunders]

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

source="wineventlog:application" EventCode=9999 earliest=-14d latest=now
| eval date_wday=strftime(_time,"%A")
| search NOT (date_wday="saturday" OR date_wday="sunday")
| bin span=7d _time
| stats avg(count) AS Avg
| eval Avg=round(Avg/14,2)

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

Thank you for all your help!

[gcusello]

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 😉

[JoshSaunders]

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.