ISR Computing -

How to retrieve data directly from Whats Up Gold database

This VB Script shows how to retieve data directly from WUP database. It generates xml file, which resides on the web server, so later it will be easier to poll information from web.

'
' Retrieve device status from Whats Up Gold
'
' nWorstStateID => What the worst state of the monitor   Maintanance (4), Up(6), Down (16)
' nDeviceTypeID => Firewall (66), Router (65), Switch (73)
' sDisplayName => Display name for the device
 
Dim timeStart
timeStart = "1/1/1970 12:00:00 AM" 
Dim connection, dsn, recordset, sql
'declare the SQL statement that will query the database
sql = "SELECT Device.*, NetworkInterface.sNetworkAddress AS [IP Address] " &_
"FROM Device " &_
"LEFT OUTER JOIN NetworkInterface ON NetworkInterface.nDeviceID = Device.nDeviceID " &_
"LEFT OUTER JOIN MonitorState ON Device.nWorstStateID = MonitorState.nMonitorStateID " &_
"LEFT OUTER JOIN DeviceType ON DeviceType.nDeviceTypeID = Device.nDeviceTypeID " &_
"WHERE Device.nDeviceTypeID = '73'"

'initialise the dsn variable
dsn="dsn=WhatsUp"  'mydsn will be the name you have chosen for your dsn
'create an instance of the ADO connection and recordset objects
Set connection = CreateObject("ADODB.Connection")
Set recordset = CreateObject("ADODB.Recordset") 
'Open the connection to the database
connection.Open dsn
'Open the recordset object, execute the SQL
recordset.Open sql,connection
'now lets see if there are any records returned
If recordset.Eof Then
Response.Write("No records returned.")
Else
'If there are records then loop through the fields 
Dim objFSO, objOutFile
Set objFSO = CreateObject("Scripting.FileSystemObject")     'create  and open text file 
Set objOutFile = objFSO.OpenTextFile("C:\Program Files\Ipswitch\WhatsUp\HTML\NmConsole\wupreport.xml", 2, True) 
objOutFile.Write "<?xml version='1.0' encoding='utf-8' standalone='yes'?>" & vbNewline
objOutFile.Write " <report>" & vbNewline
objOutFile.Write "  <date>" & Date() & " " & Time() & "</date>" & vbNewline
objOutFile.Write "  <gmdate>" & datediff("s", timeStart, now()) & "</gmdate>" & vbNewline
Do While Not recordset.EOF
objOutFile.Write "  <record>" & vbNewline
objOutFile.Write "   <id>" & recordset("nDeviceID") & "</id>" & vbNewline
objOutFile.Write "   <displayname>" & recordset("sDisplayName") & "</displayname>" & vbNewline
objOutFile.Write "   <ipaddress>" & recordset("IP Address") & "</ipaddress>" & vbNewline
objOutFile.Write "   <state>" & recordset("nWorstStateID") & "</state>" & vbNewline
objOutFile.Write "  </record>" & vbNewline
'move on to the next record
recordset.MoveNext
Loop
objOutFile.Write " </report>" & vbNewline
End If
'close the connection and recordset objects and free up resources
objOutFile.Close
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing