How to use the average of a field as a search parameter to filter results?

[Shark2112]

Hey guys.

I want to search not standard hosts from witch users login at server.

For example:
user1 - 20 logins on host1, 20 logins on host2, and 5 logins on host3
user2 - 50 logins on host1, 5 logins on host2

user="testuser" | stats count by Workstations| stats avg(count) - this is average count of logins of one user (15 and 25.5 in my case)

And now I want to find all users which have logins on hosts with less than the average count (user 1 on host3 and user2 on host2 in my case).

[somesoni2 · Accepted solution]

Try something like this

index=foo sourcetype=bar user=* | stats count by user Workstations | eventstats avg(count) as avg by user | where count<avg 

[Shark2112]

seems bad =(
workstation dont grouped, avg is average from count, but not count of fields

[somesoni2]

Not sure I get it. What's problem with the result?

[Shark2112]

hostname | user | count | avg
host1 user1 3 797.368421
host2 user2 2 3010.000000
host2 user3 4 1.500000
e.t.c.

[somesoni2]

So, it giving you list of all user and hostname combinations where count is less than avg count of user sessions for the user. Isn't that expectation?

[Shark2112]

yes, all work fine, i got wrong because of low\up case, thank for help!