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

[Noah]

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.  

[ITWhisperer]

| 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]

Thank you. I modified it a little and worked. 

[bowesmana]

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