Splunk Search

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

adewinter
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

norbert_hamel
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.

lpolo
Motivator

dedup will not do what you need.

0 Karma

kristian_kolb
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

lpolo
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

adewinter
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
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...