How to extract a time-sheet from a Windows event log

Let’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?

Yes, there is! Now for the good oil …

Step One

Open the Event Viewer (%windir%\system32\eventvwr.msc /s)

Step Two

Create a custom view (Action | Create Custom View...). Select the XML tab, and check the checkbox “Edit query manually”. Enter in the following query:

<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">*
	  [System[(Computer='E91W7CLI64') and (Level=4 or Level=0) and (band(Keywords,9007199254740992)) and TimeCreated[timediff(@SystemTime) &lt;= 9002000000]]]
	  [System[(EventID=4634 or EventID=4647)] or
	   (System[EventID=4624] and EventData/Data[@Name='LogonType']=2)]
	</Select>
  </Query>
</QueryList>

You will have to tweak two parameters in the above query, for your circumstances. The tweaks are:

1. Change Computer='E91W7CLI64' to your local machine name. (hint: right-click properties on your Computer icon to get the name)
2. Change the number part of the TimeCreated[timediff(@SystemTime) <= 9002000000 expression to how-ever far back you want to observe. This particular number equates to about 60 days. Adjust proportionally as required.

Name the filter as you please. I call mine “Logins”. This view observes successful manual log-ins and log-outs for the named computer for the specified time-back until now.

Step Three

Right-click on the filter and select Save All Events in Custom View As.... Select XML as the output format. The exported file now contains all your manual log-in session data.

Step Four

Transform the exported file with this XSLT script. I have tested this with the Saxon XSLT processor (Community edition).

<xsl:stylesheet version="2.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ev="http://schemas.microsoft.com/win/2004/08/events/event"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:fn="http://www.w3.org/2005/xpath-functions"
  xmlns:l="http://www.seanbdurkin.id.au"
  exclude-result-prefixes="xsl xs fn ev">
<xsl:output omit-xml-declaration="yes" encoding="utf-8" indent="yes"/>
<xsl:strip-space elements="*" />
      
<xsl:template match="Events">
 <l:sean-work-log>
  <xsl:variable name="sessions"> 
    <xsl:for-each-group select="ev:Event" group-by="ev:EventData/ev:Data[@Name='TargetLogonId']">
	  <xsl:if test="count(current-group()) eq 2">
	    <xsl:variable name="start"    as="xs:dateTime" select="xs:dateTime( current-group()[2]/ev:System/ev:TimeCreated/@SystemTime)" />
	    <xsl:variable name="end"      as="xs:dateTime" select="xs:dateTime( current-group()[1]/ev:System/ev:TimeCreated/@SystemTime)" />
	    <xsl:variable name="duration" as="xs:duration" select="$end - $start" />
	    <l:session start="{$start}" end="{$end}"
			   x-duration="{$duration}" 
			   day="{fn:format-date( xs:date( $start),'[FNn] [D] [MNn] [Y]')}"
			   duration="{fn:days-from-duration($duration) * 24 + fn:hours-from-duration($duration)} hours and {fn:minutes-from-duration($duration)} minutes" />
	  </xsl:if>
    </xsl:for-each-group>
  </xsl:variable>
 <l:work-days from="{fn:format-date( fn:min( for $d in $sessions/l:session/@start return xs:date( xs:dateTime($d))),'[FNn] [D] [MNn] [Y]')}"
              to="{  fn:format-date( fn:max( for $d in $sessions/l:session/@start return xs:date( xs:dateTime($d))),'[FNn] [D] [MNn] [Y]')}"> 
  <xsl:for-each-group select="$sessions/l:session" group-by="xs:date( xs:dateTime( @start))">
   <xsl:sort  select="xs:date( current-grouping-key())" order="descending" data-type="number" />
   <xsl:variable name="hours" select="fn:sum( for $x in current-group()/@x-duration return fn:minutes-from-duration( $x)) div 60" />
   <xsl:if test="fn:round($hours) ne 0">
     <l:work-day
       day="{fn:format-date( current-grouping-key(),'[FNn] [D] [MNn] [Y]')}"
       x-day="{current-grouping-key()}"
	   round-hours="{fn:round($hours)} hours" 
	   x-hours="{$hours}" />
   </xsl:if>	   
  </xsl:for-each-group>
 </l:work-days>  
 <l:log-sessions>
  <xsl:copy-of select="$sessions" />
 </l:log-sessions>
 </l:sean-work-log>
</xsl:template>
	  
</xsl:stylesheet>
        

Step Five

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.