Splunk Search

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

Builder

Hi

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.

0 Karma

Champion

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.

Legend

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)

Champion

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

0 Karma

Builder

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

0 Karma

Champion

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.

0 Karma

Builder

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. datemday dateyear 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.

0 Karma