Splunk Search

day of the week - average

loyslegrand
Path Finder

Hello

I would like to get the average of a measure depending on the day of the week (monday, tuesday,...) and this for a dedicated period.

I am able to retrieve the day of the week corresponding to an event (date_wday) , but I am not able to know the number of Mondays , Tuesdays,.... in the period (e.g month) and thus the results I get are not relevant.

Does anyone have an idea ?

Thanks in advance

Loys

Tags (3)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Based on the assumption in my latest comment you can do this:

base search | bin span=1d _time | stats count dc(_time) as days by date_wday | eval average_count = count / days

View solution in original post

loyslegrand
Path Finder

in May 2012 from which my data are from, there are 4 mondays for 17780 incidents => an average of 17780/4 = 4445 incidents on Mondays, and 5 Tuesdays for 15488 => an average of 15488/5 = 3097 incidents on Tuesdays

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Based on the assumption in my latest comment you can do this:

base search | bin span=1d _time | stats count dc(_time) as days by date_wday | eval average_count = count / days

loyslegrand
Path Finder

Great Job - It works - Thanks a lot

Loys

0 Karma

deepanram211219
New Member

Hi loyslegrand,

My issue is that I have created a successful search for 1 category but need to know how to count for different category over week days and show them on one chart.

base search category=* | bin span=1d _time | stats count dc(_time) as days by date_wday | eval average_count = count / days

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

What should the result of avg(count(Incident)) be for each week day? The average daily count, so if run over four weeks then a quarter of the total count?

0 Karma

loyslegrand
Path Finder

I can enter also | stats count(Incident) by date_wday which gives me the same result as I need the number of events and if I enter : | stats avg(count(Incident) by date_wday, I have still a null result for each day.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Do your events have a numeric field called count? If not then computing its average would be pointless.

Your first search counts the number of events without looking at any fields or their average.

0 Karma

loyslegrand
Path Finder

Thanks for your answer , but I'm still not able to get the right result

When I enter :

base search | stats count by date_wday

(with date range = "May 2012")

I have :

friday : 13772

monday : 17780

saturday : 16389

sunday : 20548

thursday : 18187

tuesday : 15488

wednesday :21458

When I enter :

base search  | stats avg(count) by date_wday

I have 0 for all the days of the week

BRgds

Loys

0 Karma

Ayn
Legend

Note that the date_* are only available for events where the timestamp processor has been invoked, which is not the case with for instance WinEventLog:* events.

0 Karma

bsizemore
Path Finder

That should do the trick!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

What's wrong with this?

base search | stats avg(measure) by date_wday
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...