calculate working hours

[zendataCH]

Hi all,
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.

regards,

Steven

[steveo69]

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

[martin_mueller · Accepted solution]

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.

[zendataCH]

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