{"id":66783,"date":"2006-08-01T07:44:00","date_gmt":"2006-08-01T07:44:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/08\/01\/how-can-i-retrieve-information-required-by-the-sarbanes-oxley-act\/"},"modified":"2006-08-01T07:44:00","modified_gmt":"2006-08-01T07:44:00","slug":"how-can-i-retrieve-information-required-by-the-sarbanes-oxley-act","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-retrieve-information-required-by-the-sarbanes-oxley-act\/","title":{"rendered":"How Can I Retrieve Information Required by the Sarbanes-Oxley Act?"},"content":{"rendered":"

\"Hey, \n

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?

— DB<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019t coaching in this game, and even though he had absolutely no stake in the tournament, he couldn\u2019t help but shudder and avert his eyes. Seeing The Umpire simply sends a chill up and down every coach\u2019s spine.<\/P>\n

Sarbanes-Oxley<\/B><\/A> tends to have the same effect on system administrators. (Although we\u2019d 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\u2019s 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. <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. True umpire story: One time the Scripting Guy was coaching third base. The Scripting Guy\u2019s 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 \u2013 the third base coach \u2013 and said, \u201cMy view was blocked. Was he safe or out?\u201d The Scripting Guy said he was safe.<\/P>\n

No, come on: he really was<\/I> safe. Does that mean that the Scripting Guy would have said he was out if the runner really had<\/I> been out? Well, no doubt he would have said that \u2013 hey, isn\u2019t this column supposed to be about scripting<\/I> and not about baseball?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

If you\u2019re a baseball coach, there\u2019s good reason to be scared of certain umpires. But if you\u2019re a system administrator, there\u2019s no reason to be afraid of Sarbanes-Oxley, at least not when you have scripts like this one at your disposal:<\/P>

On Error Resume Next<\/p>\n
Const ADS_SCOPE_SUBTREE = 2<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objCommand =   CreateObject(“ADODB.Command”)\nobjConnection.Provider = “ADsDSOObject”\nobjConnection.Open “Active Directory Provider”\nSet objCommand.ActiveConnection = objConnection<\/p>\n
objCommand.Properties(“Page Size”) = 1000\nobjCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE <\/p>\n
objCommand.CommandText = _\n    “SELECT ADsPath FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'”  \nSet objRecordSet = objCommand.Execute<\/p>\n
objRecordSet.MoveFirst<\/p>\n
Do Until objRecordSet.EOF\n    strPath = objRecordSet.Fields(“ADsPath”).Value\n    Set objUser = GetObject(strPath)\n    Wscript.Echo “User: ” & objUser.FullName\n    Wscript.Echo “Account created: ” & objUser.whenCreated & ” GMT”\n    Wscript.Echo “Password last changed: ” & objUser.passwordLastChanged\n    Wscript.Echo \n    objRecordSet.MoveNext\nLoop\n<\/PRE>\n
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<\/B> equal to user<\/I> (as you probably figured out, that\u2019s just a fancy way of saying that it searches for all the user accounts in Active Directory). As usual, we won\u2019t discuss the nuts-and-bolts of Active Directory searching today; for details, see the two-part Tales from the Script<\/I> series Dude, Where\u2019s My Printer?<\/B><\/A><\/P>\n
In fact, the only nut or bolt we will<\/I> mention today is the query we use, a query that retrieves the ADsPath<\/B> for all the users in the domain:<\/P>
objCommand.CommandText = _\n    “SELECT ADsPath FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'”\n<\/PRE>\n
Good question: why do<\/I> we retrieve just the ADsPath? Well, what we\u2019re going to do is bind to each individual user account and then retrieve the desired attribute values. The ADsPath \u2013 which is the Active Directory equivalent of a UNC file path \u2013 makes it a snap to locate and bind to each of these user accounts.<\/P>\n
And that\u2019s true: in theory, we could have specified all three of the attributes we\u2019re interested in \u2013 FullName<\/B>, whenCreated<\/B>, and passwordLastChanged<\/B> \u2013 in our SQL query. We say \u201cin theory\u201d because you can\u2019t actually retrieve the passwordLastChanged attribute from within an LDAP query. Instead you have to bind to the user account to get this value. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. The passwordLastChanged attribute is an interesting little attribute: what it does is take the value of the pwdLastSet<\/B> attribute \u2013 which represents the number of 100-second intervals that elapsed between January 1, 1601 and the time the password was last changed \u2013 and convert that value to a regular old date-time value. And, yes, if you\u2019re 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.)<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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:<\/P>
objRecordSet.MoveFirst\n<\/PRE>\n
When that\u2019s 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\u2019s EOF<\/B> \u2013 end of file \u2013 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:<\/P>
strPath = objRecordSet.Fields(“ADsPath”).Value\n<\/PRE>\n
Don\u2019t 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:<\/P>
LDAP:\/\/cn=Ken Myer,ou=Finance,dc=fabrikam,dc=com\n<\/PRE>\n
Best of all, that\u2019s exactly the value we need to pass to the GetObject<\/B> method in order to bind to the Ken Myer user account:<\/P>
Set objUser = GetObject(strPath)\n<\/PRE>\n
After we bind to the user account we can then echo back any \u2013 or even all \u2013 of the attribute values for that account. As we noted, we\u2019ve chosen to echo back three attribute values:<\/P>
Wscript.Echo “User: ” & objUser.FullName\nWscript.Echo “Account created: ” & objUser.whenCreated & ” GMT”\nWscript.Echo “Password last changed: ” & objUser.passwordLastChanged\n<\/PRE>\n
After echoing the value for the first user in the recordset we simply call the MoveNext<\/B> 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:<\/P>
User: Ken Myer\nAccount created: 8\/1\/2006 3:01:00 PM GMT\nPassword last changed: 8\/1\/2006 8:01:00 AM\n<\/PRE>\n
Oh, you\u2019re right: we did<\/I> tack a GMT<\/B> on the end of the date and time the user account was created, didn\u2019t 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. <\/P>\n
Alternatively, we could convert the value returned by whenCreated to local time. That\u2019s easy enough, provided you know the difference (or the \u201coffset\u201d) 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:<\/P>
dtmCreated = objUser.whenCreated\ndtmCreated = DateAdd(“h”, -7, dtmCreated)\nWscript.Echo “Account created: ” & dtmCreated\n<\/PRE>\n
All we\u2019re doing here is assigning the value of whenCreated to a variable named dtmCreated. We then use this line of code \u2013 and the DateAdd<\/B> function \u2013 to add minus-7 hours to that value:<\/P>
dtmCreated = DateAdd(“h”, -7, dtmCreated)\n<\/PRE>\n
That results in dtmCreated being equal to our Greenwich Mean Time value minus seven hours. Or, in other words, local time.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. If you have a worldwide enterprise spanning multiple time zones, well, this can all get a bit crazy. In cases like that \u2013 and assuming that\u2019s OK with Sarbanes and Oxley \u2013 you might be better off leaving the whenCreated value set for Greenwich Mean Time.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Time for one last question: why is<\/I> the Scripting Coach so afraid of The Umpire? Here\u2019s 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 \u2013 who was the only umpire on duty for that game \u2013 called the runner at home safe because \u201cthe catcher never tagged him.\u201d Of course, the catcher didn\u2019t have<\/I> to tag him; the catcher only had to have his foot on the base. \u201cWell, I wasn\u2019t watching for that,\u201d said The Umpire. \u201cI was only watching for the tag. So I have to call him safe.\u201d<\/P>\n
And what about the runner at first? \u201cOh, I didn\u2019t see the play at first,\u201d said The Umpire. \u201cI was bending down to pick up my mask. So I guess he\u2019s safe, too.\u201d<\/P>\n
And some of you think Sarbanes-Oxley is unfair. You have no idea what unfair really is!<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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?— DB Hey, DB. You know, this past weekend the […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[7,3,8,5],"class_list":["post-66783","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-scripting-guy","tag-searching-active-directory","tag-vbscript"],"acf":[],"blog_post_summary":"
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?— DB Hey, DB. You know, this past weekend the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66783","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66783"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66783\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}