topic Re: How do I count users that have logged in at least once a month for the last 3 months? in Splunk Search

How do I count users that have logged in at least once a month for the last 3 months?

sharonmok — Tue, 22 May 2018 19:29:10 GMT

Hi everyone,

I want to do a distinct count of users that have:

1) Logged in at least once a month AND
2) They've done this in the last 3 consecutive months.

So if they logged in only during one or two of the three months, it won't count them.

Thanks!

Re: How do I count users that have logged in at least once a month for the last 3 months?

pradeepkumarg — Tue, 22 May 2018 19:54:39 GMT

Provided you have month field extracted from your results

.. | stats dc(month) as Month_Count values(month) by USER | where Month_Count  > 3

Re: How do I count users that have logged in at least once a month for the last 3 months?

sharonmok — Tue, 22 May 2018 20:04:32 GMT

Thanks for your reply! I'm getting a 'dc' function is unsupported in 'where' command.

Re: How do I count users that have logged in at least once a month for the last 3 months?

Shan — Wed, 23 May 2018 05:27:01 GMT

@sharonmok

You need to give a alias name.
Try the below query..

| stats dc(Month) as Month,list(Month) as Months,values(Month) as Monthss by User
| where Month>=3
| table User Month Months Monthss

Regards,
Shankarananth T

Re: How do I count users that have logged in at least once a month for the last 3 months?

niketn — Wed, 23 May 2018 06:37:25 GMT

@sharonmok, what are the month and user fields in your query. How do you identify Login attempt? Do you need to count only successful login or failed one as well? Can this be identified based on your data?

If you can add sample data and current query that you have tried, it will help us assist you better.

Re: How do I count users that have logged in at least once a month for the last 3 months?

sharonmok — Tue, 29 Sep 2020 19:40:32 GMT

This is what I had originally:

| dedup user_company, date_month
| stats count(user_companyname) as client_count
| eval A = (client_count /100) * 100
| table A

The last two lines were just to make it into a percentage out of the total number of users. Login attempt is just if they show up in the logs. No need to count the ones that didn't log in.

Thanks!

Re: How do I count users that have logged in at least once a month for the last 3 months?

DalJeanis — Wed, 23 May 2018 18:26:45 GMT

That's why it is best practice to ALWAYS rename aggregate fields.

| stats dc(month) as DCmonth values(month) by USER | where DCmonth > 3

Re: How do I count users that have logged in at least once a month for the last 3 months?

sharonmok — Wed, 23 May 2018 18:54:40 GMT

Thank you! This did exactly what I wanted it to!

Re: How do I count users that have logged in at least once a month for the last 3 months?

pradeepkumarg — Wed, 23 May 2018 22:34:26 GMT

I agree, should always rename aggregated fields. I posted this in rush. I'll fix the answer.