Rolling Distinct Counts

[timrcase]

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?

[kristian_kolb]

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

[kristian_kolb]

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

[timrcase]

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?

[timrcase]

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.

[cpeteman]

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?