How to display Avg of Users per Time Picker?

[Madmax]

Hello! 

I'm trying to figure out a way to display a single value that calculates users who have disconnected divided by the time range based on the time picker.   The original number comes from the avg of total disconnects divided by the distinct user count.  I need to divide that number by the number of days which is based on the time picker.  The goal is to get the avg user disconnects per day based on time frame selected in time picker.  For example if there are 100 disconnects and 10 distinct users =10, then divided by the number of days selected in picker(7) should equal 1.42 disconnects per day.  I hope that makes sense.  Here is my search:

Index=... Host=HostName 
earliest=$time_tok.earliest$ latest=$time_tok.latest$
| stats count by "User ID"
|search "User ID"=*
|stats avg(count)

That will only give me the Total disconnects divided by Distinct users, but I need that number divided by the time picker number of days and I can't get it to work.  Thank you!!!  

[Madmax]

Sorry for the late reply.  I tried your command and it's receiving an error.  

[yuanliu]

Are you looking for something like this?

index=... Host=HostName "User ID"=*
  earliest=$time_tok.earliest$ latest=$time_tok.latest$
| stats count by "User ID"
|stats avg(eval(count*86400/($time_tok.latest$ - $time_tok.earliest$))

This is really just translating what you described into math formula.  If you need to truncate number of decimals, round is your friend.  Note: "User ID"=* is not needed after group by "User ID".  Any null value will disappear from groupby.  Meanwhile, if you have events that miss "User ID", it is beneficial to remove them before hand.

Hope this helps.

[fredclown]

Also, if you want to do this in an ad hoc search you can use | addinfo to  add the info_max_time, and info_min_time fields to your data to get the ad hoc search time range from the time picker. Editing the above answer would look like this.

index=... Host=HostName "User ID"=*
| addinfo
| stats count by "User ID"
| stats avg(eval(count*86400/(info_max_time - info_min_time)))

[Madmax]

Hello Thank you for the reply!  I tried it but I'm getting an error stating " Error in "search processor" Mismatched quotes and/or parentheses."

[fredclown]

Add another paren at the end of the stats line.

|stats avg(eval(count*86400/($time_tok.latest$ - $time_tok.earliest$)))

[Madmax]

That fixed the Parenthesis error but now I'm receiving this error:

Thank you for your time! 

[yuanliu · Accepted solution]

I see that the time tokens are symbolic, not actual time values.  In that case, use addinfo as @fredclown suggested.  Just a couple tweaks, one to make search faster if there are lots of events, the other is syntactic.

index=... Host=HostName "User ID"=*
| stats count by "User ID"
| addinfo ``` magical command goes anywhere, but less work in fewer rows ```
| stats avg(eval(count*86400/(info_max_time - info_min_time))) as average

[Madmax]

Sorry for the late reply.  I tried your command and it's receiving an error.  

[yuanliu]

You didn't run addinfo in the chain search as suggested.  Of course that will cause error because that's the same as  division by zero.

[fredclown]

Your second stats will not work because after the first stats you only have the User ID and count fields. The info_max_time and info_min_time fields no longer exist.

[Madmax]

Wow finally got this to work.  I'm not sure why but I had to refresh and it started working.  All the other numbers populated.  Thank you so much for your help!!!!  Here is a screen shot that could help someone else with the final code that was successful.  

[Madmax]

I see what you mean about missing the | addinfo, my mistake.  I tried 3 different ways.  With time picker token and without. I also tried with No token and with | addinfo but I'm still getting an error.