• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6491
  • Last Modified:

Convert msDS-UserPasswordExpiryTimeComputed value

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.
0
mgbooker
Asked:
mgbooker
1 Solution
 
yo_beeDirector of Information TechnologyCommented:
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
Clear
Get-ADUser -Filter *  -properties pwdLastSet | Select-Object Name,@{Name="Expry";Expression={[datetime]::FromFileTime($_.pwdLastSet)}} | Export-csv c:\User.csv

Open in new window

0
 
tigermattCommented:
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.

-Matt
Copy-of-Conversion-of-Large-Inte.xlsx
0
 
yo_beeDirector of Information TechnologyCommented:
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.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
mgbookerAuthor Commented:
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.
0
 
yo_beeDirector of Information TechnologyCommented:
@MG

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

Also:
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.
0
 
mgbookerAuthor Commented:
tigermatt

I was able to use your spreadsheet and create what I needed. Your information was right on the money.
0
 
mgbookerAuthor Commented:
Very clear answer.
0
 
yo_beeDirector of Information TechnologyCommented:
Just curious if you even tried my solution?
0
 
younghvCommented:
@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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Tackle projects and never again get stuck behind a technical roadblock.
Join Now