topic Re: Count items satisfying a condition in Splunk Search

Count items satisfying a condition

feridamana — Tue, 29 Sep 2020 17:35:48 GMT

I have a event created each time a user does an action in my system (e.g. login, open_page, close_page).
I need to do statistics based on the user regularity: a regular user logins more than 5 times or more over a period, the others are occasional.

I use the query:

... event=login| stats count by user

which returns the following :
User A: 10
User B: 7
User C: 3
User 😧 5

I am trying to obtain the following:

Number of regular users (login >=5 times)
Number of open_page events done by regular users
Proportion of close_page per user type (regular vs occasional)

Thanks for the help!!

Re: Count items satisfying a condition

elliotproebstel — Tue, 09 Jan 2018 18:21:40 GMT

How about this:

... event="login" OR event="open_page" OR event="close_page"
| stats count(eval(event="login")) AS login_count 
  count(eval(event="open_page")) AS open_page_count 
  count(eval(event="close_page")) AS close_page_count BY user 
| eval user_type=if(login_count>=5, "regular", "occasional")

That should give you a table with the necessary stats and categorizations to calculate all three!

Re: Count items satisfying a condition

somesoni2 — Tue, 09 Jan 2018 19:19:23 GMT

Try this

Number of regular users (login >=5 times)

... event=login| stats count by user | where count>=5 | stats count as "Number of regular users"

Number of open_page events done by regular users
To list regular users with corresponding open_page events

... event=login OR event=open_page | chart count by user event | where login>=5

To list total count of oepn_page events by regular users

... event=login OR event=open_page | chart count by user event | where login>=5 | stats sum(open_page) as "Total open_page events by regular users"

Proportion of close_page per user type (regular vs occasional)

... event=login OR event=close_page | chart count by user event | eval user_type=if(count>=5, "regular", "occasional") | eval temp=1 | chart sum(close_page) over temp by user_type | eval "Close_page Ratio(regular/occational)"=regular/occasional