day of the week - average

[loyslegrand]

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

[loyslegrand]

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

[martin_mueller · Accepted solution]

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]

Great Job - It works - Thanks a lot

Loys

[deepanram211219]

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

[martin_mueller]

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?

[loyslegrand]

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.

[martin_mueller]

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.

[loyslegrand]

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

[Ayn]

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.

[bsizemore]

That should do the trick!

[martin_mueller]

What's wrong with this?

base search | stats avg(measure) by date_wday