Attached below is the script that I used to import my WhatsUp Stat logs into our SQL database.
A couple of things:
1. You must have a DSN setup pointing to your SQL server on the machine where this script is run. Ours is called ITMaster, but you can change that near the top of the script.
2. You must specify the username and password for the DSN connection. Needless to say, make sure the script is stored in a secured location. I have my script run every day, and thus was the reason for having the credentials stored in the script.
3. The script was designed such to be able to 'scrub' the data from the database table and reimport without tweaking the script. The script will examine every log file each time it runs, and determine if it needs to import information into the database. This also means that the script could be run multiple times a day and only add the new records
4. I use Cscript as my default scripting shell. However, Windows defaults to Wscript. If you have quite a few log files, Wscript will display a message box for each log file it finds, and wait for your acknowledgement. Cscript will just open a command prompt window and display them all non-stop. You can either change your default by typing the following at a command prompt:
a. Cscript //h:cscript
i. or, run the script using the following:
b. Cscript WhatsUpStatImport.vbs "\\BogusServer\d$\Program Files\WhatsUp Gold Logs"
With the information in SQL, it is MUCH easier to generate your own set of reports via SQL statements and Excel pivot tables… I'm just hoping the WhatsUp will design the next version to use some form of database for its log storage. Till then, let me know what you think about this script…
-jb
<<WhatsUpStatImport.txt>>
'========================================================================== ' ' VBScript Source File -- Created with SAPIEN Technologies PrimalSCRIPT(TM) ' ' NAME: WhatsUpEventImport.vbs ' ' AUTHOR: Jason M. Bohreer ' DATE : 12/1/2003 ' ' COMMENT: This script is used to import WhatsUp Gold Stat Log files into SQL. ' Certain assumptions are made: ' 1. The first (and only) argument is the path to the log files. These can be local, or remote ' 2. The DSN database connection name is ITMaster, with the specified username and password. Use the script in a secured location, due to plain text password ' 3. The database has already been setup with the following fields ' DateTime (DateTime) ' Map varChar(100) ' DeviceName varChar(50) ' RTT Int(4) ' MaxRTT Int(4) ' MinRTT Int(4) ' DownTime Int(4) ' 4. This script will go through ALL files in the path with the specified LogPrefic each time it is run, but only imports newer lines. ' This allows the script the flexibility to add new records on older files if necissary. '==========================================================================
Dim strLogPath,strBaseName, intYear, intMonth, intDay
Dim fso,oFolder,oFile, oLog, conn, rs
Const DSNName = "ITMaster"
Const DSNUser = ""
Const DSNPass = ""
' Set the path to the location of the ST log files
strLogPath = WScript.Arguments(0)
' Set the prefix & ext that will be allowed to be imported
Const strLogPrefix = "ST"
Const strLogExt = "tab"
' Set the objects used
Set fso = CreateObject("Scripting.FileSystemObject") ' Opens up the
file system object
Set conn = CreateObject("ADODB.Connection")
' Opens up a connection to the database
Set rs = CreateObject("ADODB.RecordSet")
' Opens up a set of records in the database. Used for determining which
records to import.
Set oFolder = fso.GetFolder(strLogPath)
' Opens up a specific path, specified by the strLogPath variable
' Open up the ITMaster database, using the username squire, and password squire
conn.Open DSNName,DSNUser,DSNPass
' Loop through each file in the specified path
For Each File In oFolder.Files
strBaseName = fso.GetBaseName(File)
' Gets only the file name
If Lcase(Left(strBaseName,2)) = Lcase(strLogPrefix) Then ' If the
prefix matches, then continue
strLastLogFile = File
sqlLastRecord = "SELECT MAX(LineNumber) AS LineNumber, LogFile
FROM tblLoggerLog WHERE ([Table] = 'tblWhatsUpStats') GROUP BY LogFile HAVING (LogFile
= '" & strLastLogFile & "')"
rs.Open sqlLastRecord, conn, 3, 3
' Queries the database for the last record
If rs.RecordCount = 0 Then
intLastRecord = 0
Else
If IsNull(rs.Fields("LineNumber")) Then
intLastRecord = 0
Else
intLastRecord = rs.Fields("LineNumber")
End If
End If
rs.Close
InsertFile strLastLogFile,intLastRecord
End If
Next
WScript.Echo("Done!")
Sub InsertFile(LogFile,LastLine)
strSQL = "INSERT INTO tblWhatsUpStats
([DateTime],[Map],[DeviceName],[RTT],[MaxRTT],[MinRTT],[DownTime]) VALUES ("
intCurrentLine = 0
Set oLog = fso.OpenTextFile(LogFile,1)
Do While Not oLog.AtEndOfStream
strLine = oLog.ReadLine
intCurrentLine = intCurrentLine + 1
If intCurrentLine > LastLine Then
aLine = Split(strLine,vbTab)
If UBound(aLine) >= 0 Then strDate = aLine(0)
Else strDate = ""
If UBound(aLine) >= 1 Then strTime = aLine(1)
Else strTime = ""
If UBound(aLine) >= 2 Then strMap = aLine(2)
Else strMap = ""
If UBound(aLine) >= 3 Then strDevice = aLine(3) Else
strDevice = ""
If UBound(aLine) >= 4 Then intRTT = aLine(4)
Else intRTT = ""
If UBound(aLine) >= 5 Then intMaxRTT = aLine(5) Else
intMaxRTT = ""
If UBound(aLine) >= 6 Then intMinRTT = aLine(6) Else
intMinRTT = ""
If UBound(aLine) >= 7 Then intDownTime = aLine(7) Else
intDownTime = ""
strYear = Left(strDate,4)
strMonth = Mid(strDate,5,2)
strDay = Right(strDate,2)
strDate = strMonth & "/" & strDay & "/" & strYear
strTime = StrReverse(strTime)
strSecond = StrReverse(Left(strTime,2))
strMinute = StrReverse(Mid(strTime,3,2))
strHour = StrReverse(Right(strTime,Len(strTime)-4))
strTime = strHour & ":" & strMinute & ":" & strSecond
strDateTime = strDate & " " & strTime
strValues = "'" & strDateTime & "','" & strMap & "','" &
strDevice & "'," & intRTT & "," & intMaxRTT & "," & intMinRTT & "," & intDownTime &
");"
sql = strSQL & strValues
'WScript.Echo(sql)
conn.Execute(sql)
sqlLine = "INSERT INTO tblLoggerLog
(GUI,[Table],LineNumber,DateTime,LogFile) VALUES (1,'tblWhatsUpStats'," &
intCurrentLine & ",'" & Now() & "','" & LogFile & "');"
'WScript.Echo(sqlLine)
conn.Execute(sqlLine)
End If
Loop
oLog.Close
WScript.Echo(intCurrentLine & ":" & LogFile)
End Sub
