Splunk Search

Rolling Distinct Counts

timrcase
Explorer

We have a table with the following columns:

SESSION_ID      USER_ID          CONNECT_TS
--------------  ---------------  ---------------
1               99               2013-01-01 2:23:33
2               101              2013-01-01 2:23:55
3               104              2013-01-01 2:24:41
4               101              2013-01-01 2:24:43
5               233              2013-01-01 2:25:01

We need to get a distinct count of users for each day and a distinct count of users that have used the application within 45 days of each day. Is this possible using Splunk?

Tags (2)
0 Karma

kristian_kolb
Ultra Champion

Assuming this is already indexed in Splunk as events, and that the timestamp is parsed correctly.

your_base_search earliest=-45d@d 
| bucket span=1d _time 
| stats dc(USER_ID) by _time 
| append [search your_base_search = -45d@d 
| stats dc(USER_ID) as Total 
| fields + Total]

UPDATE:

Assuming you want the report to cover a 30-day period, you'll need to search through 75 days of data;

your_base_search earliest=-75d@d latest=@d 
| bucket span=1d _time 
| stats values(USER_ID) as dv_daily dc(USER_ID) as dc_daily by _time 
| streamstats window=45 dc(dv_daily) as dc_45d 
| fields - dv_daily 
| tail 30 
| sort _time 

The last three lines are just for getting tidier results (remove the disticnt values, trim off the first 45 days from the presentation, and resort the results)

/K

0 Karma

kristian_kolb
Ultra Champion

Yes that makes sense. Didn't read carefully enough. See above for solution. /k

0 Karma

timrcase
Explorer

This isn't quite what I'm looking for. It's providing a count of distinct users per day for 45 days, and a distinct user count for the entire period, but I need a count of distinct users within 45 days of each date. For example, the row for 1/1/2013 would have a count of users the app on 1/1 and a count of users that used the app in the 45 days prior (11/17/2013 to 1/1/2013). The row 1/2/2013 would have a count of users that used the app on 1/2 and a count of users that used the app in the 45 days prior (11/18/2012 to 1/2/2013), and so on. Does that make sense?

0 Karma

timrcase
Explorer

At this point I'll take anything I can get, but ideally it would be a 3 column table with Day, Daily Users, Active Users in the columns and a row for each day. I could make a single search that returns the rolling count of active users for each day work though.

0 Karma

cpeteman
Contributor

Do you want two separate searches or one row with the distinct count of users in the past day and another row with the dc of users in the last 45 days?

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...