Submit a ticketCall us

AnnouncementsFace your biggest database issues head-on

Our new eCourse helps you navigate SQL Server performance blocks by teaching you how to recognize and deal with the three DBA Disruptors: Performance Hog, Blame Shifter, and Query Blocker. Register today to learn how to defend your environment and fend off menacing disruptions.

Register for your free eCourse.

Home > Success Center > Server & Application Monitor (SAM) > SAM - Knowledgebase Articles > How to display all components polled for each polling engine

How to display all components polled for each polling engine

Table of contents

Updated: May 31, 2018

Overview

This article describes how to run an SQL query that displays all components polled for each polling engine. This value is usually higher than the licensed component count displayed in the user interface. 

Environment

All SAM versions

Steps

  1. Log in to the Orion Server.
  2. Open Database Manager.
  3. Run the following query:

    SELECT Count(ccs.ApplicationID) [Component Count], e.EngineID, ae.ServerName, ae.ServerType
    FROM [dbo].[APM_CurrentComponentStatus] ccs
    JOIN [APM_Application] ap ON ccs.ApplicationID = ap.ID
    JOIN [Nodesdata] node ON ap.NodeID = node.NodeID
    JOIN [Engines] e ON node.EngineID = e.EngineID
    JOIN [Engines] ae ON ae.EngineID = e.EngineID
    GROUP BY  e.EngineID, ae.ServerName, ae.ServerType
    ORDER BY [Component Count] DESC

 

The following query lists only non-disabled components of non-unmanaged application monitors count per poller:

SELECT Count(cc.ApplicationID) [Component Count], e.EngineID, ae.ServerName, ae.ServerType
FROM [dbo].[APM_Component] cc
JOIN [APM_Applications] ap ON cc.ApplicationID = ap.ID
JOIN [Nodes] node ON ap.NodeID = node.NodeID
JOIN [Engines] e ON node.EngineID = e.EngineID
JOIN [AllEngines] ae ON ae.EngineID = e.EngineID
where isdisabled is null and ap.Unmanaged = 0
GROUP BY  e.EngineID, ae.ServerName, ae.ServerType
ORDER BY [Component Count] DESC

 

The following example query determines if there are a large number of components in a single monitor type (such as AppInsight monitors): 

SELECT Count(*) AS 'num'
  FROM [APM_CurrentComponentStatus]
WHERE ApplicationID in (select ID from [dbo].[APM_Application]
WHERE Name like '%iis%')
ORDER BY num desc;

 

The following query lists the application IDs that are polling a large number of components: 

SELECT ccs.ApplicationID, ap.Name, Count(ccs.ApplicationID) Component_Count, node.Caption, e.EngineID FROM [dbo].[APM_CurrentComponentStatus] ccs
JOIN [APM_Application] ap on ccs.ApplicationID = ap.ID
JOIN [Nodesdata] node on ap.NodeID = node.NodeID
JOIN [Engines] e on node.EngineID = e.EngineID
GROUP BY ap.Name, ccs.ApplicationID, node.Caption, e.EngineID
ORDER BY Component_Count DESC

 

The following query lists all the components and their current status:

SELECT n.Caption AS NodeName, a.Name AS ApplicationName, c.Name AS ComponentName, ccs.ErrorMessage AS ComponentErrorMessage
  FROM APM_CurrentComponentStatus ccs
  JOIN APM_Component c ON ccs.ComponentID = c.ID
  JOIN APM_Application a ON c.ApplicationID = a.ID
  JOIN NodesData n ON a.NodeID = n.NodeID
  WHERE ccs.Availability != '1' AND (ccs.ErrorMessage IS NOT NULL AND ccs.ErrorMessage != '')

 

The following query lists components count per node without unmanaged nodes/applications and disabled components:

SELECT n.Nodeid, n.Caption, count(c.id) as Count
FROM nodes n
JOIN APM_Application a on n.nodeid=a.nodeid
JOIN APM_Component c on a.id=c.applicationid

where IsDisabled is NULL and a.Unmanaged = 0 and n.UnManaged = 0
GROUP by n.nodeid, n.caption

order by count desc
 

The following query lists applications and their components per node without unmanaged nodes/applications and disabled components (use it in SQL Management Studio):

SELECT n.Nodeid, n.Caption, a.name, c.name
FROM nodes n
JOIN APM_Application a on n.nodeid=a.nodeid
JOIN APM_Component c on a.id=c.applicationid
where IsDisabled is NULL and a.Unmanaged = 0 and n.UnManaged = 0
GROUP by n.nodeid, n.caption, a.name, c.name

 

The following query lists applications and their polling frequencies (both the setting set at the template level and set at the application level). If you unsuppress the last line it will display applications polling under 300 second (default) intervals: 

SELECT a.ID, a.Name, a.NodeID, a.TemplateID, asg.Value AS [Application Setting], ats.Value AS [Template Setting]
  FROM [dbo].[APM_Application] a
  LEFT JOIN [dbo].[APM_ApplicationTemplateSetting] ats ON (ats.ApplicationTemplateID = a.TemplateID AND ats.[Key] = '__Frequency' )
  LEFT JOIN [dbo].[APM_ApplicationSetting] asg ON (asg.ApplicationID = a.ID AND asg.[key] = '__Frequency')
  WHERE (asg.Value IS NOT NULL OR ats.Value IS NOT NULL)
  --AND (asg.Value < '300' OR ats.Value < '300')

 

 

Last modified

Tags

Classifications

Public