I would like to use Splunk to generate working hours report.
the Idea is to see the time diff between a user login and logout minus the time the screen is locked.
I have now something like this:
source=WinEventLog:Security EventCode=4624 OR EventCode=4647| transaction host startswith=4624 endswith=4647 maxevents=2 | table host duration _time Account_Name
but it is still far from doing the trick...
any help would be really appreciated.
Assuming you have the data, you could do this:
sourcetype=yourdata login OR logout OR lock OR unlock | transaction userid startswith="login OR unlock" endswith="logout OR lock" | stats sum(duration) by userid
Take a look at this run-anywhere example:
| gentimes start=-1 increment=3h | table starttime | streamstats count as userid | eval userid=round((userid+1)/4) | sort - starttime | rename starttime as _time | eval mod = (_time % (3600*4))/3600 | eval _raw = case(mod==3,"logout",mod==0,"unlock",mod==1,"lock",mod==2,"login")
This generates some dummy login/lock/unlock/logout sequence for two users. Each user is logged in for nine hours, with three hours of locked screen in between. Append this from above:
... | transaction userid startswith="login OR unlock" endswith="logout OR lock" | stats sum(duration) by userid
And you get this result:
userid sum(duration) 1 21600 2 21600
That's six hours, the three locked-screen-hours aren't counted. You can play around with the dummy data to see if it matches every scenario you have.
thanks a lot for your help. unfortunately, it is not really a working solution for Windows logs. here you can see all the events that have to be taken into account: http://blogs.msdn.com/b/ericfitz/archive/2008/08/20/tracking-user-logon-activity-using-logon-events....
Hi. We use Logfiller and the Logfiller Splunk app for this.
Not only does it show log on / log off time, but it calculates actual usage of a system - not just how long Users are logged on for, but actual usage of the keyboard and mouse...