topic Re: What is the best way to find out users' working hours per day? in Splunk Search

What is the best way to find out users' working hours per day?

kiran331 — Thu, 01 Dec 2016 17:53:57 GMT

I have a use case to find users' working hours with start time and end time. Which events will show the information required? I tried proxy logs, is there a way to find out working hours with Windows Event Logs?

search I'm using:

index=wineventlog sourcetype="WinEventLog:Security" "username"   | eval time=_time | timechart  span=1d min(time) as "Logon Time", max(time) as "Logoff Time"| convert  timeformat="%m/%d/%y %H:%M:%S" ctime(*)

But its not showing exact values.

Re: What is the best way to find out users' working hours per day?

snoobzilla — Tue, 29 Sep 2020 11:57:50 GMT

Splunk is tricky with both _time and timechart command. Something like the following may be closer to mark.

index=wineventlog sourcetype="WinEventLog:Security" "username"
| stats min(_time) AS Logon max(_time) AS Logoff min(_time) AS _time by username date_mday date_year
| eval HOURS_WORKED=(Logoff-Logon)/(60*60)
| timechart span=1d HOURS_WORKED by username

Note assuming username field is extracted. date_mday date_year are being used to isolate days without touching _time field. Keeping min(_time) AS _time allows timechart command to plot on correct day without a lot of work.

Not tested but should be close. Good luck.

Re: What is the best way to find out users' working hours per day?

sundareshr — Thu, 01 Dec 2016 20:50:50 GMT

See if this works (this assumes user login/logoff once a day)

sourcetype=WinEventLog:Security (EventCode=4624 OR EventCode=4634) | eval Date=strftime(_time, "%Y/%m/%d") | stats earliest(eval(if(EventCode=4624, _time, null())) as Login earliest(eval(if(EventCode=4634, _time, null())) as Logoff by host user | eval duration=Logoff-Login | eval duration=tostring(duration, "duration")

If more than once a day, try using transaction

sourcetype=WinEventLog:Security (EventCode=4624 OR EventCode=4634) (Logon_Type=2 OR Logon_Type=10) | transaction host user startswith=EventCode=4624 endswith=EventCode=4634 |eval duration = tostring(duration, "duration") | table _time host user duration

To further improve this search you can play with LogonType (2=Desktop 10=RDP etc)

Re: What is the best way to find out users' working hours per day?

rjthibod — Thu, 01 Dec 2016 20:56:57 GMT

You would also want to consider LogonType 11 for cached logons as well.

Re: What is the best way to find out users' working hours per day?

kiran331 — Thu, 01 Dec 2016 20:58:29 GMT

I'm seeing Logon type=3, I get the logs from all Domain Controllers.

Re: What is the best way to find out users' working hours per day?

rjthibod — Thu, 01 Dec 2016 21:03:45 GMT

Are all of you logons showing up at type 3 or just the most recent per user? If the latter, I would imagine that would be the side-effect of some network-based resource being made available, e.g., printer or shared drive. If not, then I am not so sure why they would all show up as type 3. I am not an admin/expert on this matter, just know enough to be dangerous.

Re: What is the best way to find out users' working hours per day?

rjthibod — Thu, 01 Dec 2016 21:08:04 GMT

Little bit of self promotion, but any approach based on windows logs or network logs is going to be an approximation given the various assumptions. You need to get a more specific type of user activity data to be really accurate for this kind of report.

Such Splunk-compatible sources are Layer8 and uberAgent.