Splunk Search
Highlighted

Cumulative count of new users over all time (or custom range)

Explorer

I have a field "LYC_USERNAME" that shows up in our logs. In order to determine the total number of distinct users of our system, I would like to sum all distinct values of that field over a custom time range (or over all time). I have managed to determine the total new users by running:

| stats dc(LYC_USERNAME) as ucount

but this gives me a single number. I would like to see how the number increases over time by plotting it on a chart. Appending | timechart ucount
to the end of the search query does not work, unfortunately.

Thanks!

Tags (5)
0 Karma
Highlighted

Re: Cumulative count of new users over all time (or custom range)

Explorer

I think I've figured it out:
| dedup LYC_USERNAME | timechart count as uname span=1d | streamstats sum(uname) as "Cumulative New Users" | rename uname as "New Users per Day"

0 Karma
Highlighted

Re: Cumulative count of new users over all time (or custom range)

Motivator

Take a look at the question presented in URL:

http://answers.splunk.com/answers/78181/type-of-visitor-new-or-returning-can-be-done-with-single-spl...

It might help you.

Or you may do this:

<search> | timechart span=1d dc(LYC_USERNAME) as ucount

In this example it will calculate the number of unique LYC_USERNAME found in a day. For example: If the time period of the search is the last 7 days, the result set will be the unique number of users found per day within these 7 days. You can modify the value of span as you need it (e.g, 1d, 1h, 6h).

Thanks,
Lp

0 Karma
Highlighted

Re: Cumulative count of new users over all time (or custom range)

Ultra Champion

No.

The dedup will work in the opposite way, since the newest events are returned first from a search. Thus your search will show that you 50 new users today, 10 yesterday and then keep falling as you move back in time.

<search> | timechart span=1d dc(LYC_USERNAME) as usercount | delta usercount as "New Users"

Try this instead.

/K

0 Karma
Highlighted

Re: Cumulative count of new users over all time (or custom range)

Motivator

dedup will not do what you need.

0 Karma
Highlighted

Re: Cumulative count of new users over all time (or custom range)

Communicator

Well, you can use dedup command with sortby:

| dedup LYC_USERNAME sortby -_indextime

This will keep only the first (oldest) occurence of LYC_USERNAME in your results, then you can build your timechart / streamstats on this. Note that in this example the sorting is done by the time of indexing the event. This might match your requirements, otherwise change this to another field.