Submit a ticketCall us

AnnouncementsAre You “Flying Blind?”

When it comes to your complex IT infrastructure, you want to ensure you have a good grasp of what’s going on to avoid any fire drills that result from guesswork. Read our white paper to learn how proactively monitoring your IT environment can help your organization while giving you peace of mind.

Get your free white paper.

Home > Success Center > Network Performance Monitor (NPM) > NPM - Knowledgebase Articles > Total element per engine count SQL query for Orion Platform products

Total element per engine count SQL query for Orion Platform products

Created by Eric Bryant, last modified by Magdalena.Markova on Feb 04, 2019

Views: 2,133 Votes: 3 Revisions: 23

Overview

This article provides a SQL query that lists nodes, interfaces, volumes, SAM components, Hardware Health sensors, IPAM subnets, UDT ports, NTA flows per second, VNQM operations, NCM config downloads, WPM transactions, UnDPs, wireless access points, virtual machines, traps per day and syslogs per day per polling machine.

This article also describes how you can reach the Total elements per polling engine view in the Orion Web Console. The elements included in the view may differ based on the Orion Platform products you have installed.

Environment

  • NPM with any set of Orion Platform products

Detail

You can access similar information (number of monitored nodes, interfaces,..) from the Orion Web Console.

  1. Log in to the Orion Web Console using an administrator account.
  2. Click Settings > All Settings > Database Settings.
  3. Scroll down and click Total elements per poller. 

 

SQL query

Run the following query in the SQL Management Studio. The Database Manager does not handle Else or begin statements effectively.

--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 N.EngineID, Count(C.ID) as SAM_CompONents
FROM dbo.APM_Component AS C
LEFT JOIN dbo.APM_ComponentTemplate CT
ON C.TemplateID = CT.ID
LEFT JOIN dbo.APM_Application App
ON App.Id = C.ApplicationID
LEFT JOIN dbo.Nodes N
ON N.NodeId = App.NodeId
WHERE C.Retained = 0
AND (ISNULL(CT.VisibilityMode, 0) = 0
OR (CT.VisibilityMode = 2
AND C.ApplicationItemID IS NOT NULL)
OR CT.VisibilityMode = 3)
Group by N.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

Tags

Classifications

Public