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