topic Re: How to count the unique values of an extracted field by month? in Splunk Search

How to count the unique values of an extracted field by month?

di2esysadmin — Tue, 28 Jun 2016 14:01:48 GMT

We're trying to understand what our growth rate is in Nexus usage. I've been asked to find the unique number of users that log in month over month for the last year or so.

The following search correctly counts the number of unique usernames over the timespan of the search.

index=main host=nexs*prod* "Searching for user" source="/data/sonatype-work/nexus/logs/nexus.log*" source!="/data/sonatype-work/nexus/logs/nexus.log*.tmp" | stats dc(username)

But I've been unsuccessful in getting the results for number of unique usernames for Jan, Feb, Mar, etc. I've tried all kinds of combinations. Can someone help?

THANKS!

Re: How to count the unique values of an extracted field by month?

javiergn — Tue, 28 Jun 2016 14:09:38 GMT

Is this what you are looking for?

index=main host=nexs*prod* "Searching for user" source="/data/sonatype-work/nexus/logs/nexus.log*" source!="/data/sonatype-work/nexus/logs/nexus.log*.tmp"
| bin _time span=1mon
| stats dc(username) by _time

Re: How to count the unique values of an extracted field by month?

renjith_nair — Tue, 28 Jun 2016 14:11:16 GMT

Does this help you?

index=main host=nexs*prod* "Searching for user" source="/data/sonatype-work/nexus/logs/nexus.log*" source!="/data/sonatype-work/nexus/logs/nexus.log*.tmp" |timechart span=1mon values(username)

If the result is the expected one, then you can adjust the search to show Month name and also mvexpand to expand the list

    index=main host=nexs*prod* "Searching for user" source="/data/sonatype-work/nexus/logs/nexus.log*" source!="/data/sonatype-work/nexus/logs/nexus.log*.tmp" |timechart span=1mon values(username) as username|mvexpand username|eval month=strftime(_time,"%b")

Re: How to count the unique values of an extracted field by month?

woodcock — Tue, 28 Jun 2016 14:11:30 GMT

Like this:

index=main host=nexs*prod* "Searching for user" source="/data/sonatype-work/nexus/logs/nexus.log*" source!="/data/sonatype-work/nexus/logs/nexus.log*.tmp" | eval date_month=strftime(_time, "%b") | chart dc(clerkID) BY host date_month

Re: How to count the unique values of an extracted field by month?

di2esysadmin — Tue, 28 Jun 2016 18:11:13 GMT

This works, but it provides a list of the unique users and all I need is the count. But if I needed the list, this would do it! 🙂

Re: How to count the unique values of an extracted field by month?

ppablo — Wed, 29 Jun 2016 00:51:02 GMT

Hey @di2esysadmin

I see you upvoted this answer, but you didn't accept it. If it did solve your question, please don't forget to resolve the post by clicking "Accept" directly below @javiergn's answer.

Re: How to count the unique values of an extracted field by month?

woodcock — Wed, 29 Jun 2016 13:04:01 GMT

Did this work for you?

Re: How to count the unique values of an extracted field by month?

di2esysadmin — Wed, 29 Jun 2016 13:27:28 GMT

Sorry. I didn't try every suggestion. I will though. 🙂

Re: How to count the unique values of an extracted field by month?

woodcock — Wed, 29 Jun 2016 13:38:36 GMT

Once you do, pick the best one and click Answer to close the question.

Re: How to count the unique values of an extracted field by month?

renjith_nair — Thu, 30 Jun 2016 01:50:45 GMT

ok that was extended. If you replace values(username) by dc(username) in the first search will give you the count