Reporting

Number of new users who have logged in per month as a running total to show a trend of new users over time.

Noah
Explorer

Hi, 

I am running a monthly report to show (unique users) logged in to the system (API) each month until the current time. 

. . . . .  earliest=@mon latest=now

| stats dc(CN) 

 

But I have difficulty in calculating the the number of new users who have logged in per month as a running total to show a trend of new users over time. 

The Query should run as following example: 

    • If 50 distinct users log in in October and none of the 50 has logged in before, the total is 50.
    • If 75 distinct users log in in November but 50 of them are the same that logged in in October, the number of new users is 25. Combined with the total for October, the total for November becomes 75.  

 

Tags (4)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| bin _time span=1mon
| stats values(CN) as CN by _time
| streamstats dc(CN) as unique
| streamstats latest(unique) as previous current=f
| fillnull value=0 previous
| eval new=unique-previous

Noah
Explorer

Thank you. I modified it a little and worked. 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

There are a number of ways to do this, but a simple approach is to do something like this

search earliest=-2mon@mon latest=@mon
| bin _time span=1mon
| stats count by _time CN
| stats dc(_time) as times values(_time) as _time by CN
| eventstats dc(eval(if(times=1 AND _time=relative_time(now(), "-1mon@mon"), CN, null()))) as "New" dc(eval(if(times=1 AND _time=relative_time(now(), "-2mon@mon"), CN, null()))) as "Old" dc(eval(if(times=2, CN, null()))) as "Returning"

but this will never class the first month users as new, it only compares last month with previous month, i.e. in this case October vs September - you can change the times to do October and partial November.

If you want a different approach you can keep a lookup of users who are "known" and then simply look at the current month and lookup the user against the lookup. If they do not exist, they are new. You will also have to roll over the 'new' users for this month to the lookup at the end of the month

Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...