Thursday, 14 May 2020

Converting between XML systemtime and number of hours or days with Powershell

I recently used an XML query in Powershell that looked like this:

$xmlQuery = @'
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
*[System[(EventID=4624) and TimeCreated[timediff(@SystemTime) &lt;= 86400000]] and EventData[Data[@Name='IPAddress'] and (Data='192.168.11.7')]]
    </Select>
  </Query>
</QueryList>

'@

The query was used to filter events from the event log that occurred within the last 24 hours.  However I needed to change this 7 days.  The unit of time is milliseconds but I wanted to make sure I had it exactly correct, so I checked it using the following commands:

$Start=[datetime]"01/01/2020 00:00"
$End=[datetime]"01/02/2020 00:00"
New-Timespan -Start $Start -End $End

This was my first command to check that I was using the right units of time.  This command returned:

Days              : 1
Hours             : 0
Minutes           : 0
Seconds           : 0
Milliseconds      : 0
Ticks             : 864000000000
TotalDays         : 1
TotalHours        : 24
TotalMinutes      : 1440
TotalSeconds      : 86400
TotalMilliseconds : 86400000

So I could confirm that milliseconds was the right unit of time - and you can see that 86400000 matches the 24 hour time difference I use in my XML query above.  So I wanted to confirm what 7 days would be:

$End=[datetime]"01/08/2020 00:00"
New-Timespan -Start $Start -End $End

Days              : 7
Hours             : 0
Minutes           : 0
Seconds           : 0
Milliseconds      : 0
Ticks             : 6048000000000
TotalDays         : 7
TotalHours        : 168
TotalMinutes      : 10080
TotalSeconds      : 604800
TotalMilliseconds : 604800000

So I could see that the number I needed to use in my query for a time difference of 7 days was 604800000.  So my new XML query would be:

$xmlQuery = @'
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">
*[System[(EventID=4624) and TimeCreated[timediff(@SystemTime) &lt;= 604800000]] and EventData[Data[@Name='IPAddress'] and (Data='192.168.11.7')]]
    </Select>
  </Query>
</QueryList>

'@