Splunk Search

Count items satisfying a condition

feridamana
Engager

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:

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

Thanks for the help!!

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

elliotproebstel
Champion

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!

Get Updates on the Splunk Community!

Get Schooled with Splunk Education: Explore Our Latest Courses

At Splunk Education, we’re dedicated to providing incredible learning experiences that cater to every skill ...

Splunk AI Assistant for SPL | Key Use Cases to Unlock the Power of SPL

Splunk AI Assistant for SPL | Key Use Cases to Unlock the Power of SPL  The Splunk AI Assistant for SPL ...

Buttercup Games: Further Dashboarding Techniques (Part 5)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...