Scripting Binge

I'm going through some of my old scripts today, and thought I would share some of the things I've come up with over the next few days. Today I have posted a pair of SecurID reporting scripts I wrote a while back, so check those posts out. tomorrow i'll be talking about some Perl scripts designed to do testing and monitoring of web proxy servers. Stay tuned!

RSA SecurID Monthly Successful Authentication Script

Here's another fun SecurID Script

This is one that is run directly on your ACE Server. It has two parts, one is a TCL script that directly accesses the API on the box. The contents of that TCL script look like this:


------------------------------------------------------------------------------
puts [Sd_ApiInit "" "" 1]
# SQL using Dynamic Select to print out the last login dates for all tokens.
set line 0
set startdate 0
set enddate 0

set startdate [lindex $argv 0]
set enddate [lindex $argv 1]

puts $startdate
puts $enddate


set line [Sd_DynamicSelect auths.csv 0 0 0 0 "" "" "SELECT chUserName,chLogin,dtGMTDate,tGMTTOD,chClientName FROM SDLogEntry WHERE dtGMTDate >=$startdate AND dtGMTDate <=$enddate AND iMessageNum=1011 ORDER by dtGMTDate,chClientName,chLogin" ]
puts $line
puts "Report Complete"


Sd_ApiEnd
exit

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

The second part is another VBS script. This one takes some arguments. It can either take a start date and end date as arguments, or you can pass it 'auto' as a sole parameter and it will use the system date on the box to determine the current month, and get last month's data. The VBS script then executes the TCL script, and generates an output on all the successful authentications for the month. Then using very similar tricks to the previous post, this script parses that data and XREFs it with our Active Directory structure. It compresses the data down so what we have is a report on successful authentications by Day, by Agent Host, and finally by User, with a number of successful auths for each. So for example, if during the course of a day I log in via Citrix 3 times, and by vpn twice. There would be two records for my username on that day. 

Once it has compiled that report, it then assembles it into a nice little Pivot chart for easy manipulation of the data. This allows you to filter the data based on a number of factors such as location or department. Finally, it emails the report off and cleans up after itself, deleting all the interim reports it generated. The VBS script looks like this:

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

On Error Resume Next

If Wscript.Arguments.Item(0) = "auto" Then

intLastMonth = (month(Date) -1)

strMonth = CStr(intLastMonth)

if Len(strMonth) =1 Then

strMonth= "0" & strMonth

end if

If strMonth = "01" or strMonth = "03" or strMonth = "05" or strMonth = "07" or strMonth = "08" or strMonth = "10" or strMonth = "12" Then

strEndMonth = "31"
elseif strMonth = "02" Then
  strEndMonth = "28"

else

strEndMonth = "30"

end if

strYear = CStr(Year(Date))

startdate = strMonth & ".01." & strYear

enddate = strMonth & "." & strEndMonth & "." & strYear

email=true

else

startdate = Wscript.Arguments.Item(0)

enddate = Wscript.Arguments.Item(1)

email=false

end if

set objShell = wscript.createObject("wscript.shell")

iReturn = objShell.Run("D:\Ace\utils\tcl\BIN\tcl-sd month_auths.tcl " & startdate & " " & enddate)

Wscript.sleep 10000

Set objFS =CreateObject("Scripting.FileSystemObject")

set objReportIn = objFS.OpenTextfile("auths.csv")

set objReportOut = objFS.OpenTextfile("AUTH_Report.csv",2, TRUE)

Wscript.Echo "Parsing Report..."

objReportIn.Readline

arrayLine = split(objReportIn.Readline, ",")

user1 = arrayLine(1)

name = arrayLine(0)

adate = arrayLine(2)

agent = arrayLine(4)

userCount = 1

objReportOut.WriteLine("Date,Login,Propper_Name,Num_Auths,Agent_Host,Country,Office,Company,Department,Is_IT")

do until objReportIn.AtEndOfStream

Set objUsers = CreateObject("Scripting.Dictionary")

arrayLine = split(objReportIn.Readline, ",")

If arrayLine(1) <> user1 then

user1= replace(user1,Chr(34),"")

name = replace(name,Chr(34),"")

user1 = replace(user1,","," ")

name= replace(name,","," ")

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"

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")

end if

objReportOut.WriteLine(adate & "," & user1 & "," & name & "," & userCount & "," & agent & "," & country & "," & office & "," & company & "," & department & "," & isIT)

user1 = arrayLine(1)

name = arrayLine(0)

adate = arrayLine(2)

agent = arrayLine(4)

userCount = 1

else

userCount = userCount +1

end if

loop

objReportIn.close

objReportOut.Close

CreatePivot

if email=true then

SendReport

end if

Set objReport1 = objFS.GetFile("D:\Ace\utils\tcl\BIN\Auths\auths.csv")

Set objReport2 = objFS.GetFile("D:\Ace\utils\tcl\BIN\Auths\Auth_Report.csv")

Set objFinal= objFS.GetFile("D:\Ace\utils\tcl\BIN\Auths\RSA_Auth_Report.xls")

objReport1.Delete

objReport2.Delete

objFinal.Delete

Wscript.Echo "Done"

Function CreatePivot

Wscript.echo "creating pivottable"

Set objXRep=CreateObject("Excel.Application")

'objXRep.visible=true

objXrep.Workbooks.open "D:\Ace\utils\tcl\BIN\Auths\AUTH_Report.csv" , true , true

Set mySheet = objXRep.ActiveWorkbook.Worksheets(1)

Dim topBot 

topBot=Split(mySheet.UsedRange.Address,":")

'Wscript.echo topBot(1)

Set myWorkbook=mySheet.Parent 

newName="AuthPivot" 

Set myCache=myWorkbook.PivotCaches.Add(1,"'" & mySheet.Name & "'!A1:" & topBot(1))

Set myTable=myCache.CreatePivotTable("", newName,TRUE, -1)

Set pivotSheet = myTable.Parent

pivotSheet.Name=newName

 With myTable.PivotFields("Is_IT")

.Orientation = 3

 End With

 

With myTable.PivotFields("Country")

.Orientation = 3

End With

 

With myTable.PivotFields("Office")

.Orientation = 3

End With 

 

With myTable.PivotFields("Department")

.Orientation = 3

End With

 

With myTable.PivotFields("Company")

.Orientation = 3

End With

With myTable.PivotFields("Date")

.Orientation = 1

.Position = 1

End With

With myTable.PivotFields("Agent_Host")

.Orientation = 2

.Position = 1

End With

myTable.AddDataField myTable.PivotFields("Num_Auths"), "Successful Authentications", -4157

With myTable

.ColumnGrand = TRUE

.RowGrand = TRUE

End With

topBot=Split(pivotSheet.UsedRange.Address,":")

Dim col,cols

cols = Split(topBot(1),"$")

col=cols(1)

row = cols(2)

With pivotSheet.Range("B8:" & col & row)

.Interior.ColorIndex = 50

With .Borders(7)

.LineStyle = 1

.Weight = 2

.ColorIndex = -4105

End With

With .Borders(8)

.LineStyle = 1

.Weight = 2

.ColorIndex = -4105

End With

With .Borders(9)

.LineStyle = 1

.Weight = 2

.ColorIndex = -4105

End With

With .Borders(10)

.LineStyle = 1

.Weight = 2

.ColorIndex = -4105

End With

With .Borders(11)

.LineStyle = 1

.Weight = 2

.ColorIndex = -4105

End With

End With

With pivotSheet.Range("B8:" & col & "8")

.HorizontalAlignment = 1

.VerticalAlignment = -4107

.WrapText = False

.Orientation = 45

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = -5002

.MergeCells = False

.Font.Bold = TRUE

.Interior.ColorIndex = 37

' Fit here - before putting in borders

pivotSheet.Columns("A:" & col).AutoFit

End With

With pivotSheet.Range("A9:A" & (row-1))

.Interior.ColorIndex = 37

End With

With pivotSheet.Range( col & row & ":" & col & row)

.Font.Bold = TRUE

End With

With pivotSheet.Range("A9:A" & row)

.Font.Bold = TRUE

End With

With pivotSheet.Range("A" & row & ":" & col & row)

.Interior.ColorIndex = 44

End With

With pivotSheet.Range(col & "9:" & col & row)

.Interior.ColorIndex = 44

End With

Wscript.echo "Saving..."

objXRep.DisplayAlerts = False

objXRep.Workbooks(1).SaveAs "D:\Ace\utils\tcl\BIN\Auths\RSA_Auth_Report.xls", -4143

objXRep.Workbooks(1).Close

set mySheet = nothing

objXRep.Quit

Set objXRep= Nothing

End Function

Function SendReport

strFrom =

strTo =

strSub = Date & " - " & ": Monthly Authentication Report"

strSMTP =

strBody = "Attached is the Monthly RSA SecurID Authentication Report."

strAttachment = Replace(WScript.ScriptFullName, WScript.ScriptName, "RSA_Auth_Report.xls")

set objEmail = CreateObject("CDO.Message")

objEmail.From = strFrom

objEmail.To = strTo

objEmail.Subject = strSub

objEmail.Textbody = strBody

objEmail.AddAttachment(strAttachment)

objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTP

objEmail.Configuration.Fields.Update

objEmail.Send

Set ObjFS = CreateObject("Scripting.FilesystemObject")

'objFS.Deletefile "auths.csv"

'objFS.Deletefile "Auth_Report.csv"

End function

Function Query (strUser)

Set rs = CreateObject("adodb.recordset")

    Connstring = "Provider=ADsDSOObject"

    strSQL = "SELECT distinguishedName,physicalDeliveryOfficeName,Company,Department,extensionAttribute7 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,","," ")

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 " & strUser & " not found"

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

Set Query= objUser

End if

     

  End Function

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

Again, there are some highly specific things built in here, but you could easily change these to suit your needs. Also, it is heavily dependent on Excel to work. I have this setup on our primary ACE server as a monthly job that runs so I can get usage data at the end of each month.

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

Oh no, another Security Blog!

Yes, it's true. I have created another security blog, to add my voice to the screaming masses of wannabe-pontiffs in the Web 2.0 Universe. Will anyone read this, probably not, but I hope that perhaps somebody will stumble across this and take an interest in the things I have to say.

Why you should listen to me: you probably shouldn't, but I am an Information Security professional who specializes in vulnerability assessment and penetration testing. I will be using this blog as an outlet for my observations on Information Security, techniques and tricks I have learned, and general ramblings abound.

Stay tuned for some of the following planned postings:

1. TLC vs the Google Mini 2 - Cracking the Case and installing Debian on a Google Mini Search appliance.
2. Lipstick on a Pig - Why I am not impressed by your "Web Vulnerability Scanner"
3. A Tenable Position - Why Nessus could easily be enough
4. I don't know why you say 403, I say 200 - How always returning a 200 OK server response for login requests can defeat skiddie bruteforcers
5. Knocking over the LAMP Part I (LFI) - Part I of a look at web vulnerabilities specifically dealing with Linux Apache,MySql, and PHP servers. Part I looks at how Local File inclusion can turn from data leakage to remote execute in just a few minutes.
6. Get outta my Face...book - Why Social Networking Sites may be one of the worst things to ever happen to the Internet

I had one or two more I had already considered writing, but I unfortunately didn't write them down....< /irony>