Wednesday, June 17, 2009

VBScripts to Know and Love

I get asked for one thing at least three or four times a week: Can you give me a report on my users' last logon?

Why yes, I can, if you don't mind +-14 days accuracy. Generally, that's good enough, so you can run a report using the user's lastlogontimestamp. That value is replicated, so we can use it. For more details about this attribute, see the Directory Services Team blog .

Given this bright, shiny lastlogontimestamp, it is relatively easy to write a script to extract the information and squirt it out into a text file (tab delimited so you can feed it into Excel nicely). There are some weird things you have to do to the attribute, though, to get nice output. That's why I'm including the vbscript.

There is also a weird thing that you have to do if you want to include the user's Description attribute in your report.

So here it is.
You run it from the command prompt, i.e. cscript userlastlogon.vbs
It pops up three dialog boxes.

BOX 1 - Enter the OU/domain info, as you would for any LDAP query, e.g.:

BOX 2 - Enter the FQDN for the domain controller you want to use, e.g.:

BOX 3 - Enter the name of the output file, e.g.:

It will then spew out the sAMAccountName of the accounts as it processes them, to give you warm fuzzies that it is doing something while it creates the output file you asked for, e.g. userlogons.txt.

The output file will include: name, sAMAccountName, lastLogonTimeStamp, userAccountControl, mail, and description.

Note two things:
  1. the Description attribute is a multi-valued attribute, so it's handled as an array.
  2. the LastLogonTimeStamp has to be manipulated to give you a human-readable date.
Enough said. Here is the code for your viewing pleasure:


'Get domain and DC info
strdomain = INPUTBOX("Please enter the domain context, e.g. DC=mydomain,DC=com: ")
strDC = INPUTBOX("Please enter the FQDN for the DC to connect to: ")
strFile = INPUTBOX("Please enter the output file name: ")

If strdomain="" THEN wscript.quit
If strDC="" THEN Wscript.quit

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.CreateTextFile(strFile, True)
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCOmmand.ActiveConnection = objConnection

strCommand = _
"Select name, sAMAccountName, lastLogonTimeStamp, userAccountControl, mail, description from 'LDAP://" & strDC & "/" & strdomain &"' where userAccountcontrol = '512'"

objCommand.CommandText = strCommandobjCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
usrSam = objRecordSet.Fields("sAMAccountName").Value

usrn = objRecordSet.Fields("Name").Value

If objRecordSet.Fields("mail").Value <> NULL THEN
usrmail = "No Mail"
usrmail = objRecordSet.Fields("mail").Value

strDesc = objRecordSet.Fields("description").Value
If IsArray(strDesc) then
for i=0 to Ubound(strDesc)
usrdesc = usrdesc & " " & strDesc(i)
usrdesc = "No Description"

wscript.echo usrSam

On Error Resume Next

set usrLastLogonTS = objRecordSet.Fields("lastLogonTimeStamp").Value
If Err<>0 THEN
objTextFile.WriteLine usrn & " " & usrsam & " No Date"
intLastLogonTime = usrLastLogonTS.HighPart * (2^32) + usrLastLogonTS.LowPart
intLastLogonTime = intLastLogonTime / (60 * 10000000)
intLastLogonTime = intLastLogonTime / 1440

objTextFile.WriteLine usrn & vbtab & usrsam & vbtab & usrmail & vbtab & usrdesc & vbtab & intlastlogontime + #1/1/1601#
usrdesc = ""

No comments: