[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert msDS-UserPasswordExpiryTimeComputed value

Posted on 2012-09-15
9
Medium Priority
?
5,589 Views
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.
0
Comment
Question by:mgbooker
9 Comments
 
LVL 24

Expert Comment

by:yo_bee
ID: 38401788
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
 
LVL 58

Accepted Solution

by:
tigermatt earned 2000 total points
ID: 38402412
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
 
LVL 24

Expert Comment

by:yo_bee
ID: 38402442
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:mgbooker
ID: 38403119
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
 
LVL 24

Expert Comment

by:yo_bee
ID: 38403126
@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
 

Author Comment

by:mgbooker
ID: 38404014
tigermatt

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

Author Closing Comment

by:mgbooker
ID: 38404017
Very clear answer.
0
 
LVL 24

Expert Comment

by:yo_bee
ID: 38404204
Just curious if you even tried my solution?
0
 
LVL 38

Expert Comment

by:younghv
ID: 38411337
@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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here's a look at newsworthy articles and community happenings during the last month.
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

834 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