Convert msDS-UserPasswordExpiryTimeComputed value

Posted on 2012-09-15
Last Modified: 2012-09-18
Please explain how the value for msDS-UserPasswordExpiryTimeComputed can be converted to a date and time value. I am able to export all user accounts to excel, however the value for msDS-UserPasswordExpiryTimeComputed is a long value "129958989964251031". I believe that AD understands this value very well how ever me as a human can not quite grasp this. Another possible solution is to use a function in excel against the value. If you have the function please reply to this post.
Question by:mgbooker
    LVL 21

    Expert Comment

    Are you familar with Powershell at all?
    Here is a simple way to query user AD objects and get certain properties.
    This code snippet is for pwdLastSet with is a LongInterger (Int64) which is the same as the msDS-UserPasswordExpiryTimeComputed Value.
    Replace the pwdLastSet with msDS-UserPasswordExpiryTimeComputed and you should get your desired results.

    import-module activedirectory
    Get-ADUser -Filter *  -properties pwdLastSet | Select-Object Name,@{Name="Expry";Expression={[datetime]::FromFileTime($_.pwdLastSet)}} | Export-csv c:\User.csv

    Open in new window

    LVL 58

    Accepted Solution

    The integer formats which the Directory Service uses to store its so called "Large Integers" is known as the Integer8, a 64 bit integer (8 bytes - thus the '8' in the name). There is a programmatic interface called IADsLargeInteger which is used to manipulate these and retrieve the "high" and "low" parts, but for your purposes this is not going to be necessary.

    However, for completeness, some uses of the integer format store two separate pieces of information - two numbers are encoded into one field by way of the upper 32 bits and the lower 32 bits. The "high part" is stored in the upper 32 bits and the "low part", naturally, in the lower 32 bits. Each part represents an individual integer which can be interpreted as an unsigned number. The use for the two parts which comes to my mind first and foremost is in the storage of the RID allocation to a particular DC from the RID master; the lower and upper bounds of the present allocation is stored in this format.

    There is also a tool available as part of the ldp.exe toolkit which performs the manipulation for you and retrieves the high and low parts, although it is also pretty easy to retrieve with some bitwise operations.

    Nevertheless, you won't be needing any of that, because the entire 64 bits will be used in making your calculation. You may see Internet articles which refer to having to perform mathematical operations on the high and low part, but these are simply to re-construct the full large integer. It appears you can already extract this, so that is of little concern.

    In the msDS-UserPasswordExpiryTimeComputed field, the field represents the number of 100 nanosecond intervals which elapse between the 1st January 1601 and the date and time of password expiry.

    In Excel, you will therefore need to perform several steps to retrieve the information you need:

    Divide the integer from Active Directory by 10000000 (7 zeroes - there are 1x10^9 nanoseconds in a second, but we need 1x10^7 since we are dealing with 100 nanosecond intervals). This retrieves the number of seconds since the epoch (1st January 1601).
    Further divide by 3600 and then 24 to retrieve the number of days since the epoch.
    Now to overcome a complication with Excel's date format, which can only internally serialise dates as far back as 1900. We can't simply enter 01/01/1601 and add the number of days to this; it doesn't work. I tried. Fortunately, someone online nailed it. Simply factor for the 300 year difference by subtracting 109206 days from the computed number of days from the AD value, and then add that to 01/01/1900 00:00:00 to retrieve the password expiry time.
    Confusing, huh?!

    One potential issue to be mindful of is that the number of bits used to represent floating point numbers (internal Excel representation) is insufficient for fully representing the 64-bit large integer values retrieved from Active Directory, and so there will be some minor truncation. The maximal error is of the order of 10^-15 100-nanoseconds and is therefore not of much concern!

    Also, the times are measured in GMT. You should adjust with a further addition of the number of hours offset at the rate of (1/24)*(your GMT offset) to obtain an absolute time if the time of day is also an issue.

    I've attached an example workbook to demonstrate the logic.

    LVL 21

    Expert Comment

    That is a really long explaination for something that is easily accomplished with a simple one liner in Powershell that is exported to a csv file.

    Author Comment

    What would be the formula syntax to applied to each cell in an excel column list?
    I have is a column with a list of all user accounts with the msDS-UserPasswordExpiryTimeComputed value "129958989964251031". I would like to apply a formula to each cell and convert the value into a date time format. I am thankful for the explanation, however I am no Harvard student.
    LVL 21

    Expert Comment


    Have you tried the Powershell line.
    This will output the data to a csv file with a human readable data and Time format.

    How did you originally query that attribute?
    I will assume you used VB or some other script means.  If that is the case you can probably add the function to convert the long Integer to a readable date and time.

    If you post the code you used and maybe we can assist.

    Author Comment


    I was able to use your spreadsheet and create what I needed. Your information was right on the money.

    Author Closing Comment

    Very clear answer.
    LVL 21

    Expert Comment

    Just curious if you even tried my solution?
    LVL 38

    Expert Comment

    @tigermatt -
    That is an incredible comment you posted!

    It is so great to see someone take the time to post such a comprehensive solution - not only the 'what', but the 'why' and 'how' too.

    Very nice work.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now