Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

Home > Success Center > Network Performance Monitor (NPM) > Total element per Engine Count SQL Query

Total element per Engine Count SQL Query

Table of contents

Overview

This article discusses over polling and the connection pools per element count for NPM. Applying this query gives a good overview for all product poll per polling engine. 

Environment

All versions of NPM with any set of Orion specific modules.

Detail

The query can only be done successfully in SQL management studio. The Database Manager does not handle Else or begin statements effectively.

The following is the Query script:

--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 (SELECT TOP 1 EngineID FROM Engines WHERE ServerType='Primary') AS EngineId, COUNT(*) AS IPAM_Subnets FROM IPAM_Group
 
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

Last modified
14:00, 13 Nov 2015

Tags

Classifications

Public