RSA SecurID - Active Directory Cross-Reference Script

Well, I'm not starting out with one of the big planned posts after all. I decided to start out with a couple of posts on some old work I've done. I had found myself spending a lot of times running reports out of our SecurID system, and there were a lot of requests along the lines of "Show me all the people in office X that have an active SecurID". The solution I came up with is a two step process.

Step 1: Run the initial query in the RSA Ace Administration console
I wrote a query to show me specific details out of the server about all users with enabled tokens, including the date the token expires and the date and time they last logged in. That query looks like this:

SELECT chDefaultLogin,chLastName,chFirstName,chSerialNum,iType,dateDeath,dateLastLogin,todLastLogin from SDUser JOIN SDToken on SDUser.iUserNum=SDToken.iUserNum WHERE SDToken.bEnabled=TRUE

It is then set to output to CSV with CSV headers. I save mine to a pre-defined location on my c-drive, the script below could be altered for any location, or could easily be altered to do a dynamic location. I just never got around to making that enhancement

Step 2: Run the VBS script. When the file open dialog appears, select the CSV file RSA output. It will then process the CSV file, making calls to the local Domain Controller to lookup each user and retrieve their data. The script still has some kinks in it that I haven't gotten around to fixing but it works pretty well for what we need. The script looks like this:

---------------------------------------------------------------------------------------------------------

On Error Resume Next

strDistName=""

Set ObjFSO = CreateObject("UserAccounts.CommonDialog") 

ObjFSO.Filter = "Comma-Seperated Values|*.csv|Excel Spreadsheets|*.xls|All Files|*.*" 

ObjFSO.InitialDir = "c:\" 

InitFSO = ObjFSO.ShowOpen

If InitFSO = False Then

    Wscript.Echo "Script Error: Please select a file!"

    Wscript.Quit

end if

Wscript.Echo "Querying Report...."

Set objXRep=CreateObject("Excel.Application")

objXrep.Workbooks.open ObjFSO.FileName , true, true

Set currentworksheet = objXRep.ActiveWorkbook.Worksheets(1)

usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count

usedRowsCount = currentWorkSheet.UsedRange.Rows.Count

Set Cells = currentWorksheet.Cells

Cells(1,9).Value="Country_Ou"

Cells(1,10).Value="Office"

Cells(1,11).Value="Comapany"

Cells(1,12).Value="Department"

Cells(1,13).Value="Status"

Cells(1,14).Value="IS_IT?"

Cells(1,15).Value="Email"

Cells(1,16).Value="DN"

Set objUsers = CreateObject("Scripting.Dictionary")

For intCellCount=2 to usedRowsCount

user1 = Cells(intCellCount,1).value

If objUsers.Exists(user1)=False then

objUsers.Add user1, Query(user1)

end if

if objUsers(user1).Exists("Abort") then

country="N/A"

office="N/A"

company="N/A"

department="N/A"

isIT="N/A"

status="N/A"

email="N/A"

else

country = objUsers.Item(user1).Item("Country")

office = objUsers.Item(user1).Item("Office")

company = objUsers.Item(user1).Item("Company")

department = objUsers.Item(user1).Item("Department")

isIT = objUsers.Item(user1).Item("IsIT")

status = objUsers.Item(user1).Item("Status")

email= objUsers.Item(user1).Item("Email")

strDN=onjUsers.Item(user1).Item("DN")

end if

Cells(intCellCount,9).Value=country

Cells(intCellCount,10).Value=office

Cells(intCellCount,11).Value=company

Cells(intCellCount,12).Value=Department

Cells(intCellCount,13).Value=Status

Cells(intCellCount,14).Value=isIT

Cells(intCellCount,15).Value=email

Cells(intCellCount,16).Value=strDN

Next

Wscript.Echo "closing down.."

objXRep.DisplayAlerts = False

objXRep.Workbooks(1).SaveAs "C:\dat\RSA_Token_Report.xls", -4143

objXRep.workbooks(1).Close

Set currentWorkSheet = Nothing

objXRep.Quit

Set objXRep= Nothing

Wscript.echo "Done!"

Function Query (strUser)

Set rs = CreateObject("adodb.recordset")

    Connstring = "Provider=ADsDSOObject"

    strSQL = "SELECT distinguishedName,physicalDeliveryOfficeName,Company,Department,extensionAttribute7,mail,userAccountControl FROM 'LDAP://dc=int,dc=dir,dc=willis,dc=com' WHERE objectCategory='user' AND sAMAccountName='" & strUser & "'"

    rs.Open strSQL, Connstring

    

Set objUser = CreateObject("Scripting.Dictionary")

if not rs.eof and not rs.bof Then 

objUser.Add "Login" , strUser

strDN= rs("distinguishedName")

set regExDN = New RegExp

regExDN.Pattern = "OU=iTrash"

regExDN.Global = True

regExDN.IgnoreCase = True

If regExDN.Test(strDN)=TRUE then

CountryOU= "iTrash"

Else

regExDN.Pattern = "OU=\w+,OU=iResources"

Set OUMatch= regExDN.Execute(strDN)

strOU = OUMatch.Item(0).Value

regExDN.Pattern = ",OU=iResources"

strOU = RegExDN.Replace(strOU,"")

regExDN.Pattern = "OU="

strOU = RegExDN.Replace(strOU,"")

CountryOU= strOU

end if

company = rs("Company").Value

department = rs("Department").Value

office =  rs("physicalDeliveryOfficeName").Value

office = replace(office,","," ")

company = replace(company,","," ")

department = replace(department,","," ")

UAC= rs("userAccountControl").Value

mail = rs("mail").Value

If UAC=514 then

objUser.Add "Status", "Disabled"

Else

objUser.Add "Status", "Enabled"

End if

objUser.Add "DN", strDN

objUser.Add "Email", mail

objUser.Add "Country" , CountryOU

objUser.Add "Office" , office

objUser.Add "Company", company

objUser.Add "Department", department

If (rs("extensionAttribute7")="Information Technology") then

objUser.Add "IsIT", "TRUE"

else

objUser.Add "IsIT", "FALSE"

end if

Set Query= objUser

Else

'Wscript.echo "Username not found"

objUser.Add "Abort", "Username not found"

Set Query= objUser

End if

     

End Function

-----------------------------------------------------------------------------------------------------------

This is probably version 5 or 6 at this point. I've made adjustments to add data, and added the dictionary objects so that it can keep track of the users it's already looked up. While this adds to the memory requirements of the script, it causes a huge increase in the speed with which it runs, because it is not making repeat queries to the DC.

P.S. Also note that the script directly calls Excel to handle it's parsing, so it will not work if MS Excel is not installed on the machine. You could very easily write something like this in Perl that did all the parsing itself, but at the time, I was constrained by the environment I was working in. Enjoy