topic Re: How to display Avg of Users per Time Picker? in Splunk Search

How to display Avg of Users per Time Picker?

Madmax — Tue, 03 Oct 2023 21:23:42 GMT

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!!!

Re: How to display Avg of Users per Time Picker?

yuanliu — Wed, 04 Oct 2023 03:24:23 GMT

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.

Re: How to display Avg of Users per Time Picker?

Madmax — Wed, 04 Oct 2023 14:27:04 GMT

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."

Re: How to display Avg of Users per Time Picker?

fredclown — Wed, 04 Oct 2023 19:13:11 GMT

Add another paren at the end of the stats line.

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

Re: How to display Avg of Users per Time Picker?

fredclown — Wed, 04 Oct 2023 19:20:50 GMT

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)))

Re: How to display Avg of Users per Time Picker?

Madmax — Thu, 05 Oct 2023 18:09:00 GMT

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

Thank you for your time!

Re: How to display Avg of Users per Time Picker?

yuanliu — Thu, 05 Oct 2023 19:38:26 GMT

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

Re: How to display Avg of Users per Time Picker?

Madmax — Tue, 24 Oct 2023 18:11:50 GMT

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

Re: How to display Avg of Users per Time Picker?

Madmax — Tue, 24 Oct 2023 18:15:12 GMT

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

Re: How to display Avg of Users per Time Picker?

fredclown — Tue, 24 Oct 2023 20:26:59 GMT

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.

Re: How to display Avg of Users per Time Picker?

yuanliu — Tue, 24 Oct 2023 22:17:22 GMT

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.

Re: How to display Avg of Users per Time Picker?

Madmax — Wed, 25 Oct 2023 14:03:41 GMT

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.

Re: How to display Avg of Users per Time Picker?

Madmax — Wed, 25 Oct 2023 15:36:33 GMT

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.