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"
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.
No comments:
Post a Comment