topic Re: Count number of users who logged in every hour for last 15 days in Splunk Search

Count number of users who logged in every hour for last 15 days

freephoneid — Thu, 08 Mar 2012 00:22:00 GMT

Hi,

My log contains entries as shown below:

[2012-03-07 23:57:49:107 GMT+00:00][12321312332432545435435543.http-8080-35][com.myabc.myactions.myAction] INFO #login# useremail=myemail@hotmail.com userid=6367 
[2012-03-06 23:57:49:107 GMT+00:00][89237658745354353.http-8080-35][com.myabc.myactions.myAction] INFO #login# useremail=someoneemail@hotmail.com userid=8472

I want to know how many users (basically userids) logged in every hour for last 15 days.
For example: between 12 to 1, 15 users logged in, between 1 to 2, 0 users logged in, etc

How can I get these numbers? Is there a way I can get this number to export it to excel?

Appreciate any help!!

Re: Count number of users who logged in every hour for last 15 days

lguinn2 — Thu, 08 Mar 2012 02:03:13 GMT

Try this

info login | stats dc(userid) by date_month date_mday date_hour

This counts how many unique userids appear in the log for each hour. However, a user is only counted in the hour that they logged in - in other words, if they logged in at 9:00 am and logged out at 5:00 pm, they are only counted in the 9:00 hour. (Assuming that this search only retrieves login events.) If a user logs in 3 times in one hour, he/she is only counted once for that hour.

Once you run the search, click the export button and choose CSV format to output your results to a csv file on your desktop.

If this isn't what you want, can you clarify what other events (like logout events, etc.) might also appear in the logs?

Re: Count number of users who logged in every hour for last 15 days

freephoneid — Thu, 08 Mar 2012 18:46:02 GMT

Thanks for the quick reply. Is there a way to produce the date in mm/dd/yyyy format in single column itself so that when I export it to csv, it'll show up in one column as mm/dd/yyyy?

Re: Count number of users who logged in every hour for last 15 days

lguinn2 — Mon, 28 Sep 2020 11:29:56 GMT

info login | stats dc(userid) userCount by date_month date_mday date_hour date_year |
eval outputdate = tostring(date_month) + "/" + tostring(date_mday) + "/" +
tostring(date_year) | fields + outputDate date_hour userCount

should do it

Re: Count number of users who logged in every hour for last 15 days

freephoneid — Thu, 08 Mar 2012 21:16:34 GMT

Thanks again for quick reply. However, the outputdate is coming as march/3/null. Somehow, the year is coming null all the time & month is coming in words instead of numbers. Any clue?

Re: Count number of users who logged in every hour for last 15 days

lguinn2 — Mon, 28 Sep 2020 11:30:04 GMT

info login |
stats dc(userid) as userCount latest(_time) by date_month date_mday date_hour |
eval outputDate = strftime(_time, "%m/%d/%Y") |
fields + outputDate date_hour userCount

should do better - and it's shorter too!

Typo - outputDate is case-sensitive. Timechart, as Ayn points out, would be easier - I forgot that you can just show the data table from the timechart. This is what you want, not the actual graph.

Re: Count number of users who logged in every hour for last 15 days

Ayn — Thu, 08 Mar 2012 21:57:08 GMT

Timechart seems to be the easiest way to solve this.

... | timechart span=1h dc(userid)

Re: Count number of users who logged in every hour for last 15 days

freephoneid — Thu, 08 Mar 2012 22:31:05 GMT

I tried this but its not showing any result under outputDate column. Is there anything missing in this query? the latest(_time) does not producing any result.