Splunk Search

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

kiran331
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

rjthibod
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.

sundareshr
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)

rjthibod
Champion

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

0 Karma

kiran331
Builder

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

0 Karma

rjthibod
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

snoobzilla
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. 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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...