{"id":100,"date":"2013-10-27T19:11:25","date_gmt":"2013-10-27T08:11:25","guid":{"rendered":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/?p=100"},"modified":"2013-11-13T23:50:53","modified_gmt":"2013-11-13T12:50:53","slug":"how-to-extract-a-time-sheet-from-a-windows-event-log","status":"publish","type":"post","link":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/archives\/100","title":{"rendered":"How to extract a time-sheet from a Windows event log"},"content":{"rendered":"<p>Let&#8217;s say you are required to keep a time-sheet for services performed, and for the most part, the hours of services performed corresponds to when you are logged into a specific Win7+ machine. The best way to keep a time-sheet is with pencil and paper, or a spread-sheet, or the corporate time-sheet keeping system. But what if there is a problem with your normal record-keeping system? Is there a way to leverage the windows event log without an enormous amount of pain?<br \/>\n<img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.keepandshare.com\/graphics\/lp\/signup_sheets\/time_sheet_med.jpg?w=640\" alt=\"Timesheet\" \/><br \/>\nYes, there is! Now for the good oil &#8230;<\/p>\n<h2>Step One<\/h2>\n<p>Open the Event Viewer (<code>%windir%\\system32\\eventvwr.msc \/s<\/code>)<\/p>\n<h2>Step Two<\/h2>\n<aside style=\"width:300px; background-color:#E0D584;\" class=\"sbd-aside sbd-right sbd-round\"><header class=\"sbd-header\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/seanbdurkin.id.au\/pascaliburnus2\/wp-content\/plugins\/sbd-aside\/expand.gif?resize=13%2C14\" width=\"13\" height=\"14\" border=\"0\"\n alt=\"Show\/Hide\" title=\"Show\/Hide\"\n onclick=\"togglePannel(this)\"\/>What does the query do?<\/header> <div class=\"sbd-hidden sbd-tight\"><hr class=\"sbd-swish\"\/><span class=\"sbd-content\">\n<p>This query selects events from<\/p>\n<ul>\n<li>the &#8220;Security&#8221; Log (the <em>Path=<\/em> part).<\/li>\n<li>It <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/windows\/desktop\/dd996910(v=vs.85).aspx\" title=\"Syntax for the value of the Select element\" target=\"_blank\">selects events<\/a> from the named computer (the <em>Computer=<\/em> part);<\/li>\n<li>informational (as opposed to errors, warnings etc (<em>Level=4 or Level=0<\/em> part);<\/li>\n<li>with a keyword &#8220;Audit Success&#8221; (the <em>band<\/em> part);<\/li>\n<li>not too old (<em>TimeCreated&#8230;<\/em> part); and<\/li>\n<li>either any kind of log-out (<em>EventID&#8217;s 4634 or 4647<\/em>);<\/li>\n<li>or a manual (as opposed to programmatic) log-in (EventId=4624 is log-in; LogonType=2 is the manual part).<\/li>\n<\/ul>\n<\/span><\/div><\/aside>\nCreate a custom view (<code>Action | Create Custom View...<\/code>). Select the XML tab, and check the checkbox &#8220;Edit query manually&#8221;. Enter in the following query:<\/p>\n<pre class=\"brush: xml; title: ; notranslate\" title=\"\">\r\n&lt;QueryList&gt;\r\n  &lt;Query Id=&quot;0&quot; Path=&quot;Security&quot;&gt;\r\n    &lt;Select Path=&quot;Security&quot;&gt;*\r\n\t  &#x5B;System&#x5B;(Computer='E91W7CLI64') and (Level=4 or Level=0) and (band(Keywords,9007199254740992)) and TimeCreated&#x5B;timediff(@SystemTime) &amp;lt;= 9002000000]]]\r\n\t  &#x5B;System&#x5B;(EventID=4634 or EventID=4647)] or\r\n\t   (System&#x5B;EventID=4624] and EventData\/Data&#x5B;@Name='LogonType']=2)]\r\n\t&lt;\/Select&gt;\r\n  &lt;\/Query&gt;\r\n&lt;\/QueryList&gt;\r\n<\/pre>\n<p>You will have to tweak two parameters in the above query, for your circumstances. The tweaks are:<\/p>\n<ol>\n<li>Change <code>Computer='E91W7CLI64'<\/code> to your local machine name. (hint: right-click properties on your Computer icon to get the name)<\/li>\n<li>Change the number part of the <code>TimeCreated[timediff(@SystemTime) &lt;= 9002000000<\/code> expression to how-ever far back you want to observe. This particular number equates to about 60 days. Adjust proportionally as required.<\/li>\n<\/ol>\n<p>Name the filter as you please. I call mine &#8220;Logins&#8221;. This view observes successful manual log-ins and log-outs for the named computer for the specified time-back until now.<\/p>\n<h2>Step Three<\/h2>\n<p>Right-click on the filter and select <code>Save All Events in Custom View As...<\/code>. Select XML as the output format. The exported file now contains all your manual log-in session data.<\/p>\n<h2>Step Four<\/h2>\n<p>Transform the exported file with this XSLT script. I have tested this with the Saxon XSLT processor (Community edition).<\/p>\n<pre class=\"brush: xml; title: ; notranslate\" title=\"\">\r\n&lt;xsl:stylesheet version=&quot;2.0&quot;\r\n  xmlns:xsl=&quot;http:\/\/www.w3.org\/1999\/XSL\/Transform&quot;\r\n  xmlns:ev=&quot;http:\/\/schemas.microsoft.com\/win\/2004\/08\/events\/event&quot;\r\n  xmlns:xs=&quot;http:\/\/www.w3.org\/2001\/XMLSchema&quot;\r\n  xmlns:fn=&quot;http:\/\/www.w3.org\/2005\/xpath-functions&quot;\r\n  xmlns:l=&quot;http:\/\/www.seanbdurkin.id.au&quot;\r\n  exclude-result-prefixes=&quot;xsl xs fn ev&quot;&gt;\r\n&lt;xsl:output omit-xml-declaration=&quot;yes&quot; encoding=&quot;utf-8&quot; indent=&quot;yes&quot;\/&gt;\r\n&lt;xsl:strip-space elements=&quot;*&quot; \/&gt;\r\n      \r\n&lt;xsl:template match=&quot;Events&quot;&gt;\r\n &lt;l:sean-work-log&gt;\r\n  &lt;xsl:variable name=&quot;sessions&quot;&gt; \r\n    &lt;xsl:for-each-group select=&quot;ev:Event&quot; group-by=&quot;ev:EventData\/ev:Data&#x5B;@Name='TargetLogonId']&quot;&gt;\r\n\t  &lt;xsl:if test=&quot;count(current-group()) eq 2&quot;&gt;\r\n\t    &lt;xsl:variable name=&quot;start&quot;    as=&quot;xs:dateTime&quot; select=&quot;xs:dateTime( current-group()&#x5B;2]\/ev:System\/ev:TimeCreated\/@SystemTime)&quot; \/&gt;\r\n\t    &lt;xsl:variable name=&quot;end&quot;      as=&quot;xs:dateTime&quot; select=&quot;xs:dateTime( current-group()&#x5B;1]\/ev:System\/ev:TimeCreated\/@SystemTime)&quot; \/&gt;\r\n\t    &lt;xsl:variable name=&quot;duration&quot; as=&quot;xs:duration&quot; select=&quot;$end - $start&quot; \/&gt;\r\n\t    &lt;l:session start=&quot;{$start}&quot; end=&quot;{$end}&quot;\r\n\t\t\t   x-duration=&quot;{$duration}&quot; \r\n\t\t\t   day=&quot;{fn:format-date( xs:date( $start),'&#x5B;FNn] &#x5B;D] &#x5B;MNn] &#x5B;Y]')}&quot;\r\n\t\t\t   duration=&quot;{fn:days-from-duration($duration) * 24 + fn:hours-from-duration($duration)} hours and {fn:minutes-from-duration($duration)} minutes&quot; \/&gt;\r\n\t  &lt;\/xsl:if&gt;\r\n    &lt;\/xsl:for-each-group&gt;\r\n  &lt;\/xsl:variable&gt;\r\n &lt;l:work-days from=&quot;{fn:format-date( fn:min( for $d in $sessions\/l:session\/@start return xs:date( xs:dateTime($d))),'&#x5B;FNn] &#x5B;D] &#x5B;MNn] &#x5B;Y]')}&quot;\r\n              to=&quot;{  fn:format-date( fn:max( for $d in $sessions\/l:session\/@start return xs:date( xs:dateTime($d))),'&#x5B;FNn] &#x5B;D] &#x5B;MNn] &#x5B;Y]')}&quot;&gt; \r\n  &lt;xsl:for-each-group select=&quot;$sessions\/l:session&quot; group-by=&quot;xs:date( xs:dateTime( @start))&quot;&gt;\r\n   &lt;xsl:sort  select=&quot;xs:date( current-grouping-key())&quot; order=&quot;descending&quot; data-type=&quot;number&quot; \/&gt;\r\n   &lt;xsl:variable name=&quot;hours&quot; select=&quot;fn:sum( for $x in current-group()\/@x-duration return fn:minutes-from-duration( $x)) div 60&quot; \/&gt;\r\n   &lt;xsl:if test=&quot;fn:round($hours) ne 0&quot;&gt;\r\n     &lt;l:work-day\r\n       day=&quot;{fn:format-date( current-grouping-key(),'&#x5B;FNn] &#x5B;D] &#x5B;MNn] &#x5B;Y]')}&quot;\r\n       x-day=&quot;{current-grouping-key()}&quot;\r\n\t   round-hours=&quot;{fn:round($hours)} hours&quot; \r\n\t   x-hours=&quot;{$hours}&quot; \/&gt;\r\n   &lt;\/xsl:if&gt;\t   \r\n  &lt;\/xsl:for-each-group&gt;\r\n &lt;\/l:work-days&gt;  \r\n &lt;l:log-sessions&gt;\r\n  &lt;xsl:copy-of select=&quot;$sessions&quot; \/&gt;\r\n &lt;\/l:log-sessions&gt;\r\n &lt;\/l:sean-work-log&gt;\r\n&lt;\/xsl:template&gt;\r\n\t  \r\n&lt;\/xsl:stylesheet&gt;\r\n        \r\n<\/pre>\n<h2>Step Five<\/h2>\n<p>Read the output document and transcribe to your work-sheet. The output has two sections: A day-by=day summary and a list of log-in sessions. In the day-by-day summary, if the total for the day is less than half an hour, it will be filtered out. Hours for each day are rounded to the nearest hour. If you have different rounding rules, adjust the transform in step four as required.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Let&#8217;s say you are required to keep a time-sheet for services performed, and for the most part, the hours of services performed corresponds to when you are logged into a specific Win7+ machine. The best way to keep a time-sheet &hellip; <a href=\"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/archives\/100\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8],"tags":[],"class_list":["post-100","post","type-post","status-publish","format-standard","hentry","category-xslt-2-0"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2QXbt-1C","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/posts\/100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/comments?post=100"}],"version-history":[{"count":13,"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/posts\/100\/revisions"}],"predecessor-version":[{"id":115,"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/posts\/100\/revisions\/115"}],"wp:attachment":[{"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/media?parent=100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/categories?post=100"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/seanbdurkin.id.au\/pascaliburnus2\/wp-json\/wp\/v2\/tags?post=100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}