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


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()
130320405471207431

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

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, `
    whenChanged

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!

Comments (12)

  1. Anonymous says:

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

  2. Anonymous says:

    Pingback from Episode 251 – PowerScripting Podcast – Josh Swenson talks about using PowerShell | PowerShell.org

  3. Anonymous says:

    Pingback from Back To The Future: Working with date data types in Active Directory PowerShell | MS Tech BLOG

  4. Anonymous says:

    Pingback from Episode 251 – PowerScripting Podcast – Josh Swenson talks about using PowerShell | PowerShell.org

  5. Anonymous says:

    226 Microsoft Team blogs searched, 49 blogs have new articles. 125 new articles found searching from

  6. Anonymous says:

    Usually I like to offer deep technical content on the blog, but today I’m going to keep it simple. Everyone should be keenly aware that Windows XP support officially ends on April 8, 2014 . Many companies are migrating from Windows XP and need a

  7. Anonymous says:

    Welcome! Today’s post includes demo scripts and links from the Microsoft Virtual Academy event: Using PowerShell for Active Directory . We had a great time creating this for you, and I hope you will share it with anyone needing to ramp up their

  8. Anonymous says:

    Scripting tips for cleaning up stale Active Directory accounts.

  9. Charles Town says:

    This is wonderful, thank you!

  10. Anonymous says:

    Today we learn how to efficiently filter event log queries, going beyond simple event ID filtering into the specific values of the XML message data. Then we will run this filter against multiple servers in parallel for faster data collection.

  11. JonyGreen says:

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

    1. TM says:

      What for? This is a Powershell blog.

      $unixtime = “1474344700”
      [datetime]$epoch= ‘1970-01-01 00:00:00’
      $convertedDate = $epoch.AddSeconds($unixTime)

      $convertedDate
      Tuesday, 20 September 2016 4:11:40 AM