How Can I Retrieve Information Required by the Sarbanes-Oxley Act?
Hey, Scripting Guy! As part of our Sarbanes-Oxley requirements I need to get a list of all my Active Directory users, along with the date that their account was created and the last time their password was changed. How can I get a list like that?
Hey, DB. You know, this past weekend the Scripting Guy who writes this column was helping out at a baseball tournament. As the time for the next game approached, this Scripting Guy happened to glance over to the side of the field: there, rising up over the hill, was The Umpire You Never Want to See. Even though the Scripting Guy wasn’t coaching in this game, and even though he had absolutely no stake in the tournament, he couldn’t help but shudder and avert his eyes. Seeing The Umpire simply sends a chill up and down every coach’s spine.
Sarbanes-Oxley tends to have the same effect on system administrators. (Although we’d willing to bet that, unlike some umpires we know, both Sarbanes and Oxley understand the infield fly rule.) Although ostensibly aimed at accountants and auditors, Sarbanes-Oxley has all sorts of implications for system administrators, too: for example, it’s up to the system administrator to produce things like a list of all Active Directory users, the date each account was created, and the last time each password was changed.
Note. True umpire story: One time the Scripting Guy was coaching third base. The Scripting Guy’s runner rounded second and slid into third, right about the time the ball arrived. Everyone turned to The Umpire: was the runner safe or out? The Umpire, meanwhile, turned to the Scripting Guy – the third base coach – and said, “My view was blocked. Was he safe or out?” The Scripting Guy said he was safe.
No, come on: he really was safe. Does that mean that the Scripting Guy would have said he was out if the runner really had been out? Well, no doubt he would have said that – hey, isn’t this column supposed to be about scripting and not about baseball?
If you’re a baseball coach, there’s good reason to be scared of certain umpires. But if you’re a system administrator, there’s no reason to be afraid of Sarbanes-Oxley, at least not when you have scripts like this one at your disposal:
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject(“ADODB.Connection”) Set objCommand = CreateObject(“ADODB.Command”) objConnection.Provider = “ADsDSOObject” objConnection.Open “Active Directory Provider” Set objCommand.ActiveConnection = objConnection
objCommand.Properties(“Page Size”) = 1000 objCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE
objCommand.CommandText = _ “SELECT ADsPath FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=’user'” Set objRecordSet = objCommand.Execute
Do Until objRecordSet.EOF strPath = objRecordSet.Fields(“ADsPath”).Value Set objUser = GetObject(strPath) Wscript.Echo “User: ” & objUser.FullName Wscript.Echo “Account created: ” & objUser.whenCreated & ” GMT” Wscript.Echo “Password last changed: ” & objUser.passwordLastChanged Wscript.Echo objRecordSet.MoveNext Loop
As you can see, this is a script that searches Active Directory; in particular, it searches Active Directory for all the objects that have an objectCategory equal to user (as you probably figured out, that’s just a fancy way of saying that it searches for all the user accounts in Active Directory). As usual, we won’t discuss the nuts-and-bolts of Active Directory searching today; for details, see the two-part Tales from the Script series Dude, Where’s My Printer?
In fact, the only nut or bolt we will mention today is the query we use, a query that retrieves the ADsPath for all the users in the domain:
objCommand.CommandText = _ “SELECT ADsPath FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=’user'”
Good question: why do we retrieve just the ADsPath? Well, what we’re going to do is bind to each individual user account and then retrieve the desired attribute values. The ADsPath – which is the Active Directory equivalent of a UNC file path – makes it a snap to locate and bind to each of these user accounts.
And that’s true: in theory, we could have specified all three of the attributes we’re interested in – FullName, whenCreated, and passwordLastChanged – in our SQL query. We say “in theory” because you can’t actually retrieve the passwordLastChanged attribute from within an LDAP query. Instead you have to bind to the user account to get this value.
Note. The passwordLastChanged attribute is an interesting little attribute: what it does is take the value of the pwdLastSet attribute – which represents the number of 100-second intervals that elapsed between January 1, 1601 and the time the password was last changed – and convert that value to a regular old date-time value. And, yes, if you’re far more comfortable dealing with 100-nanosecond intervals by all means retrieve the value of pwdLastSet instead. (Although you will also have to convert the 64-bit integer format of pwdLastSet to something VBScript can work with.)
To bind to each individual user account we begin by executing our query, then we use this line of code to position ourselves at record 1 in the recordset:
When that’s done we set up a Do Until loop that runs until we reach the end of the recordset (or, for you database aficionados, until the recordset’s EOF – end of file – property is true). Inside that Do Until loop we use this line of code to assign the ADsPath for the first user in the recordset to a variable named strPath:
strPath = objRecordSet.Fields(“ADsPath”).Value
Don’t remember what an ADsPath looks like? This must be your lucky day; we just happened to have a handy sample ADsPath we can share with you:
Best of all, that’s exactly the value we need to pass to the GetObject method in order to bind to the Ken Myer user account:
Set objUser = GetObject(strPath)
After we bind to the user account we can then echo back any – or even all – of the attribute values for that account. As we noted, we’ve chosen to echo back three attribute values:
Wscript.Echo “User: ” & objUser.FullName Wscript.Echo “Account created: ” & objUser.whenCreated & ” GMT” Wscript.Echo “Password last changed: ” & objUser.passwordLastChanged
After echoing the value for the first user in the recordset we simply call the MoveNext method and repeat the process for the second user in the recordset. This continues until we have retrieved and reported back information for all the users in the domain. That information, incidentally, will look something like this:
User: Ken Myer Account created: 8/1/2006 3:01:00 PM GMT Password last changed: 8/1/2006 8:01:00 AM
Oh, you’re right: we did tack a GMT on the end of the date and time the user account was created, didn’t we? Why? Well, the whenCreated attribute reports back the account creation date based on Greenwich Mean Time rather than local time. (By contrast, passwordLastChanged uses the local time when identifying the date and time the password was last changed.) We added the GMT designation to indicate that the displayed date and time is in Greenwich Mean Time.
Alternatively, we could convert the value returned by whenCreated to local time. That’s easy enough, provided you know the difference (or the “offset”) between local time and Greenwich Mean Time. For example, if your local time is 7 hours before Greenwich Mean Time, code similar to this will do the conversion for you:
dtmCreated = objUser.whenCreated dtmCreated = DateAdd(“h”, -7, dtmCreated) Wscript.Echo “Account created: ” & dtmCreated
All we’re doing here is assigning the value of whenCreated to a variable named dtmCreated. We then use this line of code – and the DateAdd function – to add minus-7 hours to that value:
dtmCreated = DateAdd(“h”, -7, dtmCreated)
That results in dtmCreated being equal to our Greenwich Mean Time value minus seven hours. Or, in other words, local time.
Note. If you have a worldwide enterprise spanning multiple time zones, well, this can all get a bit crazy. In cases like that – and assuming that’s OK with Sarbanes and Oxley – you might be better off leaving the whenCreated value set for Greenwich Mean Time.
Time for one last question: why is the Scripting Coach so afraid of The Umpire? Here’s one reason. In one game the bases were loaded with one out. The ball was hit back to the pitcher, who immediately threw home. The catcher touched home plate and then threw to first in time to get the batter. Double play, right? Wrong. The Umpire – who was the only umpire on duty for that game – called the runner at home safe because “the catcher never tagged him.” Of course, the catcher didn’t have to tag him; the catcher only had to have his foot on the base. “Well, I wasn’t watching for that,” said The Umpire. “I was only watching for the tag. So I have to call him safe.”
And what about the runner at first? “Oh, I didn’t see the play at first,” said The Umpire. “I was bending down to pick up my mask. So I guess he’s safe, too.”
And some of you think Sarbanes-Oxley is unfair. You have no idea what unfair really is!