how to get tthe average of a count value ?

[abilis]

hi,

can someone help me to complete the search to get the average of a count ??

we have a file that has the logins of the users, we would like to create a graph that give us the average of login per hour for a month.

so far we are able to get the sum of all logins per hour with the following search:

index="login" (users=*) | stats count by date_hour | sort date_hour

but we dont khow to get the average that should be the sum of all logins per hour divided by the number of days where there was a login

thanks a lot for your help

[somesoni2 · Accepted solution]

It'll be this

index="login" (users=) | stats count by date_hour | stats avg(count) as AveragePerAvailableHour

OR

 index="login" (users=) | timechart span=1h count | where count>0 | eval day=strftime(_time,"%m/%d/%Y") | stats sum(count) as TotalLogins dc(day) as totaldays | eval AverageLogins=TotalLogins/totaldays

Updated based on latest comment

index="login" (users=) | timechart span=1h count | eval date_hour=strftime(_time,"%H") | stats avg(count) as AverageLoginCount by date_hour

[abilis]

hi,

thanks again for your help... i am getting some average. the search is dividing each of the counts by the total number of date is the file, when it should only divide by the number of date when there was a login

from the table below, the average for hour "9" should be 10 (number of logins) / 5 (distinct day where the login occurred ) = 2 .... all the other days in between should be ignored in the calculation because login at 9am only happen during 5 days

date hour minute
2/27/2018 9 25
2/27/2018 9 26
2/27/2018 9 26
3/2/2018 9 11
3/5/2018 9 4
3/5/2018 9 57
3/6/2018 9 51
3/6/2018 9 53
4/5/2018 9 18
4/5/2018 9 44

thanks a lot for your help....

[abilis]

thanks for your help, we decided to use your last suggestion for the search.

this worked for us

index="login" (users=) | timechart span=1h count | eval date_hour=strftime(_time,"%H") | stats avg(count) as AverageLoginCount by date_hour

[kmaron]

Are you looking for a single number? The hourly average for the month? the daily average for the month? I'm not quite sure what you're looking for as a result.

[kmaron]

Are you looking for a single number? The hourly average for the month? the daily average for the month? I'm not quite sure what you're looking for as a result.

[abilis]

first, thanks for your help.

i'm looking for the average value per hour, meaning in the X-axes i will have from 0 - 23 (representing the hour of the day from the file) and in the Y-axes i have the total count of logins for each hour for the entire month

i.e on the day 14,15,16 of the month i had 3,5,6 logins (for the 14,15,16 of the month) at 10 am so the total in the Y-axes with "count" will give me a value of 14..this is what i get with the search i posted, but i dont need the total, i need the average per hour, so in my example the y-axes should be 14 (total of logins) / 3 (# days) = 4.6...and this for each of from 0-23h

thanks for you help