Network Management

Identify the number of nodes, interfaces, volumes on Polling Engines using count query

Identify how much nodes, interfaces, volumes, and other elements polled by different modules are assigned to each poller.

First published date

4/8/2020 5:02 PM

Last published date

4/8/2020 5:02 PM

Overview

Run a script against the database manager to identify how much elements are assigned to each polling engine.

Sample output:

Product section

Network Performance Monitor

Cause

Resolution

Run the script below against the Database Manager. Then, export the results to CSV.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

--UnDP
DECLARE @UnDP TABLE (EngineId INT, UnDP_ASsignedCustomPollers INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='Network')>0

	BEGIN

	 INSERT INTO @UnDP  (EngineId, UnDP_ASsignedCustomPollers) 
	  SELECT T2.Engineid AS Engineid, count(CustomPollerASsignmentID) AS UnDP_ASsignedCustomPollers FROM CustomPollerASsignment T1 WITH(NOLOCK)
	  INNER JOIN Nodes T2 WITH(NOLOCK)
	  ON T1.nodeid=T2.nodeid
	  GROUP BY T2.Engineid

	END

ELSE 
	 INSERT INTO @UnDP  (EngineId, UnDP_ASsignedCustomPollers) 
	 SELECT EngineId, 0 AS UnDP_ASsignedCustomPollers FROM Engines

END

--Wireless

DECLARE @Wireless TABLE (EngineId INT, Wireless_AccessPoINTs INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='Network')>0

	BEGIN

	 INSERT INTO @Wireless (EngineId, Wireless_AccessPoINTs) 
	  SELECT T2.Engineid AS EngineID, count(T1.Nodeid) AS Wireless_AccessPoINTs FROM Wireless_AccessPoINTs T1 WITH(NOLOCK)
	  INNER JOIN Nodes T2 WITH(NOLOCK)
	  ON T1.nodeid=T2.nodeid
	  GROUP BY T2.Engineid

	END

ELSE 

	 INSERT INTO @Wireless (EngineId, Wireless_AccessPoINTs) 
	 SELECT EngineId, 0 AS Wireless_AccessPoINTs FROM Engines

END

--VIM

DECLARE @VIM TABLE (EngineId INT, VirtualMachines INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='Network')>0

	BEGIN

	 INSERT INTO @VIM (EngineId, VirtualMachines) 
	  SELECT T2.Engineid AS Engineid, count(T1.HostID) AS VirtualMachines FROM VIM_VirtualMachines T1 WITH(NOLOCK)
	  INNER JOIN Nodes T2 WITH(NOLOCK)
	  ON T1.nodeid=T2.nodeid
	  GROUP BY T2.Engineid

	END

ELSE 

	 INSERT INTO @VIM (EngineId, VirtualMachines) 
	 SELECT EngineId, 0 AS VirtualMachines FROM Engines

END

--HWH

DECLARE @HWH TABLE (EngineId INT, HWH_Sensors INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='Network' OR Name='SAM')>0

	BEGIN

	 INSERT INTO @HWH (EngineId, HWH_Sensors) 
	  SELECT Engineid, count(*) AS HWH_Sensors FROM Nodes T1 WITH(NOLOCK)
	  INNER JOIN APM_HardwareItem T2 ON T1.NodeID=T2.NodeID
	  GROUP BY EngineID

	END

ELSE 

	 INSERT INTO @HWH (EngineId, HWH_Sensors) 
	 SELECT EngineId, 0 AS HWH_Sensors FROM Engines

END

--SAM

DECLARE @SAM TABLE (EngineId INT, SAM_CompONents INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='APM')>0

	BEGIN

	 INSERT INTO @SAM (EngineId, SAM_CompONents) 
	  SELECT EngineId, COUNT(*) AS SAM_CompONents FROM APM_AlertsAndReportsData T1 INNER JOIN Nodes T2 WITH(NOLOCK) ON T1.NodeId=T2.NodeId GROUP BY EngineID 

	END

ELSE 
	 INSERT INTO @SAM (EngineId, SAM_CompONents) 
	  SELECT EngineId, 0 AS SAM_CompONents FROM Engines

END

--IPAM

DECLARE @IPAM TABLE (EngineId INT, IPAM_Subnets INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='IPAM')>0

	BEGIN

	 INSERT INTO @IPAM (EngineId, IPAM_Subnets) 
	 SELECT EngineId, COUNT(*) AS IPAM_Subnets FROM IPAM_Group Group by EngineID

	END

ELSE 
	 INSERT INTO @IPAM (EngineId, IPAM_Subnets) 
	 SELECT EngineId, 0 AS IPAM_Subnets FROM Engines

END

--NTA

DECLARE @NTA TABLE (EngineId INT, NTA_FlowsPerSec INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='NTA')>0

	BEGIN

	 INSERT INTO @NTA (EngineId, NTA_FlowsPerSec) 
	 SELECT EngineID, StatisticsValue AS NTA_FlowsPerSec FROM NetFlowEnginesStatistics WHERE StatisticsName='FlowsPerSecONdForLAST24Hours'
	END

ELSE 
	 INSERT INTO @NTA (EngineId, NTA_FlowsPerSec) 
	 SELECT EngineId, 0 AS NTA_FlowsPerSec FROM Engines

END

--UDT

DECLARE @UDT TABLE (EngineId INT, UDT_Ports INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='DeviceTracker')>0

	BEGIN

	 INSERT INTO @UDT (EngineId, UDT_Ports) 
	 SELECT EngineID, Count(*) AS UDT_Ports FROM Nodes T1 WITH(NOLOCK)
	 INNER JOIN UDT_Port T2 ON T1.NodeID=T2.NodeID
	 GROUP BY Engineid

	END

ELSE 
	 INSERT INTO @UDT (EngineId, UDT_Ports) 
	 SELECT EngineId, 0 AS UDT_Ports FROM Engines

END

--VNQM

DECLARE @VNQM TABLE (EngineId INT, VNQM_OperatiONs INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='VNQM')>0

	BEGIN

	 INSERT INTO @VNQM (EngineId, VNQM_Operations) 
	 SELECT EngineID, Count(*) AS VNQM_Operations FROM Nodes T1 WITH(NOLOCK)
	 INNER JOIN VOIPOperationInstances T2 ON T1.NodeID=T2.SourceNodeID or T1.NodeID=T2.TargetNodeID
	 GROUP BY Engineid

	END

ELSE 
	 INSERT INTO @VNQM (EngineId, VNQM_OperatiONs) 
	 SELECT EngineId, 0 AS VNQM_OperatiONs FROM Engines

END

--NCM

DECLARE @NCM TABLE (EngineId INT, NCM_CONfigDownloadsPerDay INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='NCM')>0

	BEGIN

	 INSERT INTO @NCM (EngineId, NCM_CONfigDownloadsPerDay) 
	 SELECT EngineID, Count(*) AS NCM_CONfigDownloadsPerDay FROM Nodes T1 WITH(NOLOCK)
	 INNER JOIN NCM_NodeProperties T2 ON T1.NodeID=T2.CoreNodeID
	 INNER JOIN NCM_TransferQueue T3 ON T2.NodeID=T3.NodeID
	 WHERE datetime>DATEADD(Day, -1, GETDATE())
	 GROUP BY Engineid

	END

ELSE 
	 INSERT INTO @NCM (EngineId, NCM_CONfigDownloadsPerDay) 
	 SELECT EngineId, 0 AS NCM_CONfigDownloadsPerDay FROM Engines

END

--WPM

DECLARE @WPM TABLE (EngineId INT, WPM_TransactiONs INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='TransactiONs')>0

	BEGIN

	 INSERT INTO @WPM (EngineId, WPM_TransactiONs) 
	 SELECT PollingEngineId as EngineID, Count(*) AS WPM_TransactiONs FROM SEUM_Agents T2
	 INNER JOIN SEUM_TransactiONs T3 ON T2.Agentid=T3.agentid
	 GROUP BY PollingEngineId

	END

ELSE 
	 INSERT INTO @WPM (EngineId, WPM_TransactiONs) 
	 SELECT EngineId, 0 AS WPM_TransactiONs FROM Engines

END

--Traps

DECLARE @Traps TABLE (EngineId INT, TrapsPerDay INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='Network')>0

	BEGIN

	 INSERT INTO @Traps (EngineId, TrapsPerDay) 
	 SELECT EngineID, Count(*) AS TrapsPerDay FROM Traps
	 WHERE DateTime>DATEADD(Day, -1, GETDATE())
	 GROUP BY Engineid
	END

ELSE 
	 INSERT INTO @Traps (EngineId, TrapsPerDay) 
	 SELECT EngineId, 0 AS TrapsPerDay FROM Engines

END

--Syslog

DECLARE @Syslog TABLE (EngineId INT, SyslogPerDay INT)

BEGIN

IF (SELECT COUNT(*) FROM MODULES WHERE Name='Network')>0

	BEGIN

	 INSERT INTO @Syslog (EngineId, SyslogPerDay) 
	 SELECT EngineID, Count(*) AS SyslogPerDay FROM Syslog
	 WHERE DateTime>DATEADD(Day, -1, GETDATE())
	 GROUP BY Engineid
	END

ELSE 
	 INSERT INTO @Syslog (EngineId, SyslogPerDay) 
	 SELECT EngineId, 0 AS SyslogPerDay FROM Engines

END

--Final SELECT Statement

SELECT Servername,  Nodes,  Interfaces, Volumes,  T2.SAM_Components, T14.HWH_Sensors, T6.IPAM_Subnets, T8.UDT_Ports, T7.NTA_FlowsPerSec, T9.VNQM_Operations, T10.NCM_ConfigDownloadsPerDay, T11.WPM_Transactions, T3.UnDP_ASsignedCustomPollers, T4.Wireless_AccessPoints, T5.VirtualMachines, T12.TrapsPerDay, T13.SyslogPerDay FROM Engines T1 WITH(NOLOCK) 

LEFT JOIN @SAM T2 
ON T1.EngineID=T2.EngineId

LEFT JOIN @UnDP T3
ON T1.EngineID=T3.EngineId

LEFT JOIN @Wireless T4
ON T1.EngineID=T4.EngineId

LEFT JOIN @VIM T5
ON T1.Engineid=T5.EngineId

LEFT JOIN @IPAM T6
ON T1.EngineID=T6.EngineId

LEFT JOIN @NTA T7
ON T1.EngineId=T7.EngineId

LEFT JOIN @UDT T8
ON T1.EngineId=T8.EngineId

LEFT JOIN @VNQM T9
ON T1.EngineId=T9.EngineId

LEFT JOIN @NCM T10
ON T1.EngineId=T10.EngineId

LEFT JOIN @WPM T11
ON T1.EngineId=T11.EngineId

LEFT JOIN @Traps T12
ON T1.Engineid=T12.Engineid

LEFT JOIN @Syslog T13
ON T1.EngineId=T13.EngineId

LEFT JOIN @HWH T14
ON T1.EngineID=T14.EngineId

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same if any.