Filtering data on date in PowerShell

When working with large data sets, being able to effectively filter the data is essential. A common exercise where you might need filtering on date is when reading logs, and in this blog post I will show you how you can perform the typical “get all logs from the last x days” and “get all logs between day x and day y” scenarios.

I’ll be using the Get-WinEvent cmdlet in the following examples to fetch Windows event logs.

First I need to examine the output of this command:

Get-WinEvent -LogName System -MaxEvents 1

I’m taking advantage of the fact that this cmdlet lets you choose how many events to fetch, and I only need one event to get an idea of how the output looks.

datefiltering001

So looking at the above output it is clear that it’s the TimeCreated property we need when filtering the event logs.

Get all event logs from the last week

First we need to define a DateTime object that represents our point in time that we want to filter on.

$filterDate = (Get-Date).AddDays(-7)

Conveniently the DateTime object have a method called AddDays, and by adding negative 7 days to the current date we get the date 7 days ago.

Next is just a matter of filter based on the TimeCreated property and our filterDate object, using for instance Where-Object.

Get-WinEvent -LogName System -MaxEvents 1000 | Where-Object {$_.TimeCreated -ge $filterDate}

I’m using the MaxEvents parameter here to speed up the retrieval process, as the system log can be quite large.

datefiltering002

NOTE!

When filtering on dates like in the above example, please be aware of the fact that the Get-Date cmdlet will return a DateTime object which include the date as well as the time. The same is true of the TimeCreated property. This means that when you ask for all logs for the last week, based on the time of day you might not get back the result you would expect!

To work around this problem you can modify your code like so:

$filterDate = (Get-Date).AddDays(-7).Date
Get-WinEvent -LogName System -MaxEvents 1000 | Where-Object {$_.TimeCreated.Date -ge $filterDate}

This change will make sure that you are filtering on only the date portion of the DateTime object, while ignoring the time.

If you like to work with the DateTime object directly, you can use the Today method which will only return the day (as opposed to the Now method that returns both the time and day):

$filterDate = [datetime]::Today.AddDays(-7)
Get-WinEvent -LogName System -MaxEvents 1000 | Where-Object {$_.TimeCreated.Date -ge $filterDate}

Get all event logs between two dates

Since we are after logs from within a given time span we need to create two DateTime objects, representing the start date and the end date respectively. Next we need to alter our filter to check that the TimeCreated property falls within both the startDate and endDate values.

$startDate = Get-Date -Year 2018 -Month 3 -Day 1
$endDate = Get-Date -Year 2018 -Month 3 -Day 3
Get-WinEvent -LogName System -MaxEvents 1000 | Where-Object {($_.TimeCreated.Date -le $endDate) -and ($_.TimeCreated.Date -ge $startDate)}

That’s it folks! Catch you on the next one.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s