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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...