Goatee PFE

Blog of Microsoft Premier Field Engineer Ashley McGlone featuring PowerShell scripts for Active Directory.

Back To The Future: Working with date data types in Active Directory PowerShell

Back To The Future: Working with date data types in Active Directory PowerShell

  • Comments 11
  • Likes

Crazy Datesback-to-the-future-delorean

Set your watch for January 1, 1601, Marty.  Today we’re working with crazy dates in Active Directory PowerShell.

If you have ever tried to script out Active Directory reports that included date fields, then you have likely run into this challenge.  There are “real” dates, and then “those” dates.  You know… the ones that just look like a bunch of numbers.  Today’s post shows you how to make sense of those crazy Int64 date fields.

Here is an example:

PS C:\> $user = Get-ADUser anlan -Properties cn, LockedOut, pwdLastSet, `
badPwdCount, badPasswordTime, lastLogon, lastLogoff, lastLogonTimeStamp, `
whenCreated, whenChanged

PS C:\> $user

badPasswordTime    : 130320368740838559
lastLogoff         : 0
lastLogon          : 130320396642621473
lastLogonTimeStamp : 130320216613366396
pwdLastSet         : 130320211319441741
whenChanged        : 12/20/2013 1:14:34 PM
whenCreated        : 4/12/2011 10:04:27 PM
(output trimmed)

PS C:\> $user | Get-Member -MemberType Property

   TypeName: Microsoft.ActiveDirectory.Management.ADUser

Name               MemberType  Definition
----               ----------  ----------
badPasswordTime    Property    System.Int64 badPasswordTime {get;set;}
lastLogoff         Property    System.Int64 lastLogoff {get;set;}
lastLogon          Property    System.Int64 lastLogon {get;set;}
lastLogonTimeStamp Property    System.Int64 lastLogonTimeStamp {get;set;}
pwdLastSet         Property    System.Int64 pwdLastSet {get;set;}
whenChanged        Property    System.DateTime whenChanged {get;}
whenCreated        Property    System.DateTime whenCreated {get;}
(output trimmed)

You’ll notice that that data type for the “real” dates is DateTime, but the data type for “those” dates is Int64 FILETIME.

From Int64 FileTime to DateTime and back again

Let’s pick one of these date attributes to convert.  Here is how you can make these into friendly dates:

PS C:\> [datetime]::fromFileTime($user.pwdLastSet)
Friday, December 20, 2013 8:52:11 AM

PS C:\> [datetime]::fromFileTimeUTC($user.pwdLastSet)
Friday, December 20, 2013 1:52:11 PM

You can use the .NET DateTime object methods fromFileTime and fromFileTimeUTC to convert them to readable dates. The UTC version reflects the time as GMT, rather than local time.

What if we want to go the other way? How can we convert a "real" date into one of these?

PS C:\> (Get-Date).ToFileTime()

PS C:\> (Get-Date).ToFileTimeUTC()

Using Int64 dates in your reports

Here is how you can use Select-Object to translate those dates in your AD queries:

PS C:\> $user |
    Select-Object cn, `
    pwdLastSet, @{name='pwdLastSetDT'; `
        expression={[datetime]::fromFileTime($_.pwdlastset)}}, `
    badPasswordTime, @{name='badPasswordTimeDT'; `
        expression={[datetime]::fromFileTime($_.badPasswordTime)}}, `
    lastLogon, @{name='lastLogonDT'; `
        expression={[datetime]::fromFileTime($_.lastLogon)}}, `
    lastLogonTimeStamp, @{name='lastLogonTimestampDT'; `
        expression={[datetime]::fromFileTime($_.lastLogonTimestamp)}}, `
    whenCreated, `

cn                   : anlan
pwdLastSet           : 130320211319441741
pwdLastSetDT         : 12/20/2013 8:52:11 AM
badPasswordTime      : 130320368740838559
badPasswordTimeDT    : 12/20/2013 1:14:34 PM
lastLogon            : 130320396642621473
lastLogonDT          : 12/20/2013 2:01:04 PM
lastLogonTimeStamp   : 130320216613366396
lastLogonTimestampDT : 12/20/2013 9:01:01 AM
whenCreated          : 4/12/2011 10:04:27 PM
whenChanged          : 12/20/2013 2:01:04 PM

Querying with Int64 dates

Now let's query AD with a filter that can talk Int64 date format.  This query will give us all users who have not reset their password in the last 90 days:

PS C:\> Get-ADUser `
-Filter "pwdLastSet –lt $((Get-Date).AddDays(-90).ToFileTimeUTC())"

Note that AD stores this attribute’s date as UTC, so we want to convert to UTC in this filter.

The number zero is a valid value for pwdLastSet and means something else all together, so we need to filter those out of the results:

PS C:\> Get-ADUser `
-Filter "pwdLastSet -lt $((Get-Date).AddDays(-90).ToFileTimeUTC()) `
-and pwdLastSet -ne 0"

Now you know how to convert, report, and filter on those crazy Int64 date fields.

Further Reading

For more information on date attributes and UTC visit the following links:

Happy scripting!

Can you help me?  Yes!

If you would like to have me or another Microsoft PFE visit your company and assist with the ideas presented in this blog post, then contact your Microsoft Premier Technical Account Manager (TAM) for booking information.

For more information about becoming a Microsoft Premier customer email PremSale@microsoft.com.  Tell them GoateePFE sent you.

Sharing Links
  • Great explanation!! Loved the screen shots and quick, easy, examples. I appreciate the sharing of our knowledge.

  • This is wonderful, thank you!

  • you can try this free http://www.online-code.net/unix-timestamp.html">online timestamp converter to get a unix time.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment