Well maybe not, since I try to keep all these posts under 2000 words, and my utter disdain for Mondays could fill volumes, so instead I’ll just jump right into the reason for this post. Our auditor wanted to know the last time machines changed their password. Not users, and no, she did not want to see a report of stale machine accounts, she wanted to actually see that the machines were changing their passwords, and when they were last set. Yeah, about that. Active Directory stores this in the attribute pwdLastSet. Which sounds pretty straight-forward, but it’s not.
pwdLastSet is stored as a 64bit integer, instead of using a human readable format. The value in this integer represents the total number of 100 nanosecond intervals since midnight on ANSI day one, 1601-01-01, which as it happens was a Monday. Figures. When you view this in ADUC, you see it rendered in humanese. When you query for that value, you get the 64bit integer. Exporting this to a text file (CSV) seems a logical way to proceed, and there are lots of ways to do this (dsquery | dsget, ADFind from JoeWare, CSVDE, others) but then you are stuck with a number that really can’t be used.
And since Excel doesn’t seem to recognise dates before 1900, this presents a problem for any ‘format date’ logic that you might try to pull off in Excel. What you need to do is convert the huge number of 100 nanosecond slices since 1601 to something you can use. Here’s how I do it.
- Take the integer, divide it first by 10 million (the number of 100 nanosecond slices in a second.)
- Then divide it by 3600 (the number of seconds in an hour.)
- Then divide it by 24 (the number of hours in a day) to get the number of days since 1601-01-01.
- Remember that Excel can only deal with dates after 1900-01-01 so finally, we need to subtract from that 109,205 (the number of days between 1601-01-01 and 1900-01-01.)
If we assume that the integer value you are interested in is stored in cell A3, then the formula to get a date out of this is
=(A3/10000000/3600/24)-109205
Format that cell as a date, and you should be good to go. This is ‘close enough’ to get the day and time to the minute in UTC. If you want more precision than that, you can convert the 64bit integer using the w32tm cmd, eg
w32tm /ntte 129211790736213744 [enter] 149550 16:24:33.6213744 - 2010-06-16 12:24:33
That gives you days, hours, minutes, seconds, and fractions on down to the 100th nanosecond in UTC, then shows you date and time to the second in your local timezone. I pity the fool auditor who wants that, especially considering our timesource only offers accuracy to within five minutes.
As soon as I realised ANSI Day One was a Monday, this song popped into my head. The best audio quality vid I could find of this Boomtown Rat’s classic just happens to combine some scenes from House, including my favourite episode with guest star Dave Matthews.
Direct link for RSS and email subscribers…http://youtu.be/NPsZGNon_gg
No related posts.
