Splunk Search

calculate working hours

zendataCH
Explorer

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

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

steveo69
Explorer

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

zendataCH
Explorer

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

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