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!!
Timechart seems to be the easiest way to solve this.
... | timechart span=1h dc(userid)
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?
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.
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.
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?
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
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?