I recently came to the realization that I could be using the SQL backend systems of the different products I maintain to allow for additional insight into operations. As part of this I started to look at comparing my Whatsup Gold implementation with my HP IMC implementation. The systems fundamentally perform similar functions of management of the network but there is a basic difference WhatsUp Gold has the capability of taking a action as a result of a condition being met and IMC does not. Since I have been running two systems and periodically the two systems come out of sync with respect to whether a device is in one system or the other I took a look this morning and decided to start reporting against each database with respect to each other and came up with the query below. This query when used as a WhatsUp Gold Dynamic Group SQL query shows the devices that are listed in WhatsUp Gold but not in IMC.
—————-
SELECT DISTINCT WhatsUp15.dbo.Device.nDeviceID
FROM
WhatsUp15.dbo.MonitorState
INNER JOIN WhatsUp15.dbo.Device ON WhatsUp15.dbo.MonitorState.nMonitorStateID = WhatsUp15.dbo.Device.nWorstStateID
INNER JOIN WhatsUp15.dbo.DeviceType ON WhatsUp15.dbo.Device.nDeviceTypeID = WhatsUp15.dbo.DeviceType.nDeviceTypeID
FULL OUTER JOIN WhatsUp15.dbo.NetworkInterface ON WhatsUp15.dbo.Device.nDefaultNetworkInterfaceID = WhatsUp15.dbo.NetworkInterface.nNetworkInterfaceID
FULL OUTER JOIN config_db.imc_config.tbl_dev ON WhatsUp15.dbo.NetworkInterface.sNetworkAddress = config_db.imc_config.tbl_dev.dev_ip
WHERE
(NOT (WhatsUp15.dbo.NetworkInterface.sNetworkAddress = N’127.0.0.1′)) AND
(config_db.imc_config.tbl_dev.dev_ip IS NULL) AND
(NOT (WhatsUp15.dbo.MonitorState.nInternalMonitorState = 2)) AND
(NOT (WhatsUp15.dbo.DeviceType.sDisplayName LIKE N’%Windows%’)) AND
(WhatsUp15.dbo.NetworkInterface.bPollUsingNetworkName = 0)
—————–
This is based on my own environment with a shared SQL server and the IMC using config_db as a database and WhatsUp Gold using WhatsUp15 as a database. This could easily be accomplished by having linked servers in case of a split deployment.
The where clauses at the bottom do the following:
Statement 1: Ensure no loopback addresses are included
Statement 2: Ensure that the device doesn’t exist in IMC
Statement 3: Ensure the device is not in Maintenance mode in Whatsup Gold
Statement 4: Remove all devices that are Windows based ( I don’t monitor these with IMC)
Statement 5: Remove all devices that are polled using DNS name ( IMC doesn’t allow DNS resolution it only uses IP address once a device is created in the system)
I have included a diagram of the tables used and their relationships:
Leave a Reply