How do you get a list of unique users for the month?

[Adam_Marx]

A beginner here trying to learn SPLUNK...

I have some daily log files where users can login and out multiple times a day, and I would like to be able to identify the user and how many times they have logged in throughout the month, with maybe the top 15 users listed.

I've tried a couple of things, but I don't seem to be able to get unique user counts correct....

|stats count dc(customer) BY customer, database 
|top limit=15 customer, database

Any help would be appreciated.

Thanks,

[mrunals]

The other answers are pointing you in the right direction based on the specific wording you used. However, if you wanted to find out how many days someone logged in (ie Fred used the application 10 days in Feb while Mary used the application 23 days) you could do something like this

... | bin span=1d _time | stats dc(_time) as uniqueDays by customer database | sort -uniqueDays 

You could combine both pieces of data with something like

... | bin span=1d _time | stats dc(_time) as uniqueDays count as uniqueLogins by customer database

[Adam_Marx]

My apologies for not making the question as specific as it needed to be for the assist, I'm still trying to learn what the questions are and how to ask them.

mrunals, that is pretty close to what I'm looking for and possibly because my question was asked incorrectly, I'll try to rephrase the question.

Users can login multiple times a day and query the database multiple times a day. So, the users name will appear in the log numerous times in a day sometime up to 50 times, so uniqueness of the user is important but also the number of logins as this would determine the ranking of the users. I'm trying to show how many times in a month/week/year that Fred/Mary/George have logged in and the databases that they have logged into, displaying the top 15 users for each database, by ranking.

In a 7day look I would anticipate seeing all the databases listed with the top 15 users, most users will have logged in daily and would have a count of 7, others less. The ranking (number of time Fred is in the log for the day) would help determine the top users (Fred logged in multiple times and Mary only once so Fred would be ranked higher than Mary).

Hopefully this helps better describe what I'm trying to accomplish.

mrunals, can you help me understand how your suggestion is supposed to work? bin, is that getting a unique set of data for 1day then getting distinct (_time) and counting? shouldn't changing the span from 1d to 7d cause UniqueDays to be 7 instead of 1?

Thanks,

[mrunals]

What bin does is 'smoosh' , in this case, the time field so that all of the dates appear the same (essentially midnight of the day in question). I'm doing this because now you can run your query across a number of days - 7, 30, etc - and get the unique number of days a user might have logged in. To see this in action you could do something like

...| eval foo = _time | bin span=1d _foo | convert ctime(foo) | table _time foo

Even though you've smooshed (quite a technical term I must say) the time field you still have individual login instances which allows you to also get a total count of logins per user & database.

Sorting is simply a matter of picking which field you want and of course you can sort on multiple fields. Hopefully that helps a bit.

[somesoni2]

Try like this (I removed dc(customer) from the search as it'll always return 1 because customer fields in included in by clause of stats.

your current search
|top limit=15  customer, database

OR

your current search
|stats count  BY customer, database 
| sort 15 -count

OR

your current search
|stats count BY customer, database 
| sort  -count | head 15

[somesoni2]

You're getting count of total logins, not count of logs per day. The solution was based on following line from your question

I would like to be able to identify the user and how many times they have logged in throughout the month, with maybe the top 15 users listed.

What's your exact requirement?

[Adam_Marx]

Thanks for the reply but I don't think those are giving the results I was expecting.

In a 7day query each user potentially would have a maximum of 7 days but the counts being retrieved are considerably higher than that leading me to believe they aren't counting unique users for the day

[Vijeta]

@Adam_Marx
|stats count(customer) as customer_count by customer| top limit=15

[Vijeta]

@Adam_Marx can you share your logs?