This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

The Magic of SWQL: Create "All Components With Problems" Resource With Clickable Links, Status Icons and Even Hovers As A Bonus

[UPDATE 22/12/2014]

Please check The Magic Of SQL Scripts: Resource To Display All Your Components With Problems for the new improved SQL version of this solution

-------------------------------------------------------------

Here is how your end result will look like - BEAUTIFUL emoticons_wink.png

abc.JPG

========================================================

Here is what you do to create it:

1. Add "Custom Query" component on your page

2. Use the following SWQL query to return all components with problems, including their Statistic and Message values for different types of components, which is very handy if you have different monitoring scripts:

SWQL SCRIPT UPDATED ON 29/04/2014 TO ACCOMMODATE ALL BELOW COMMENTS AND CHANGES. FINAL VERSION BELOW:

SWQL Query:

SELECT

'' AS n,

n.Caption AS NODE,

'/Orion/images/StatusIcons/small-' + ToString(n.StatusIcon) AS [_IconFor_n],

'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE],

'' AS a,

a.Name AS APP,

'/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_a],

'/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP],

'' AS c,

c.ComponentName AS CMPNT,

'/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif' AS [_IconFor_c],

'/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT],

cs.ComponentStatisticData AS STAT,

'/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT],

cs.ErrorMessage AS MSG,

'/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID

JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID

JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID

WHERE

a.StatusDescription NOT IN ('Unmanaged')

AND c.StatusDescription NOT IN ('Up','Disabled')

AND a.Name NOT IN ('Test Application','Another Test Application','One More Application You Do Not Want To Monitor')

*** SEE PLAIN TEXT ATTACHED WITH THE ABOVE QUERY ***

Result:

Capture.JPG

That's It! Enjoy!

You can then add more filters in "WHERE" section to further restrict and show only components that you are interested in. I have created a whole bunch of them emoticons_wink.png. I advise you to use SWQL Studio from Orion SDK, which makes query building process much more fun and you can see the whole structure of tables in-font of you

If you will experience any problems - please post them below so that we can solve them here and come up with common universal resource for displaying any issues on a component level if there are any issues with the above query

CREDITS: Petr Vilem, lukas.belza for helping me at [SOLVED] SWQL Query: How Do I Output "Statistic" and "Message" Values For The Component?

--

Alex

  • Hi All,

    I have realised that some components are not being picked up by this script, because they are not present in ChartEvidence table, such as "Process Monitoring", "Windows Scheudled Tasks" and some scripts. As suggested by Petr Vilem here - we can use UNION between CurrentStatistics and DynamicEvidence tables to pull out all this components. However, I have discovered that UNION does not work in "SWQL Query Resource" (it does however work in SWQL Studio). So, what I did - is created 2 separate query resources to pull out all this info:

    Here is a full query with UNION. To use with query resources on page - just use two separate select statements for two different queries.

    SELECT

    '' AS n,

    n.Caption AS NODE,

    '/Orion/images/StatusIcons/small-' + ToString(n.StatusIcon) AS [_IconFor_n],

    '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE],

    '' AS a,

    a.Name AS APP,

    '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_a],

    '/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP],

    '' AS c,

    c.ComponentName AS CMPNT,

    '/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif' AS [_IconFor_c],

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT],

    cs.ComponentStatisticData AS STAT,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT],

    cs.ErrorMessage AS MSG,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

    FROM Orion.APM.Component c 

    JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID 

    JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID

    JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID

    WHERE

    a.StatusDescription NOT IN ('Unmanaged')

    AND c.StatusDescription NOT IN ('Up','Disabled')

    UNION 

    SELECT 

    '' AS n,

    n.Caption AS NODE,

    '/Orion/images/StatusIcons/small-' + ToString(n.StatusIcon) AS [_IconFor_n],

    '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE],

    '' AS a,

    a.Name AS APP,

    '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_a],

    '/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP],

    '' AS c,

    c.ComponentName AS CMPNT,

    '/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif' AS [_IconFor_c],

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT],

    de.AvgNumericData AS STAT,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT],

    de2.StringData AS MSG,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

    FROM Orion.APM.Component(nolock=true) c   

    JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID 

    JOIN Orion.APM.DynamicEvidence(nolock=true) de ON de.ComponentStatusID = ccs.ComponentStatusID AND de.ColumnType = 1 

    LEFT JOIN Orion.APM.DynamicEvidence(nolock=true) de2 ON de2.ComponentStatusID = ccs.ComponentStatusID AND de2.ColumnType = 0 

    JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID

    JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID

    WHERE

    a.StatusDescription NOT IN ('Unmanaged')

    AND c.StatusDescription NOT IN ('Up','Disabled')

    P.S. I have placed icons in a separate column to ensure that icons always stays next to object in the table, not above it, when table it relatively narrow in size

    Phew....

  • Guys,

    SCRATCH the above. I believe the first part of the above script will do the job needed (second part just produces duplicate records).

    Here it is - final version (I hope emoticons_happy.png):

    '' AS n,

    n.Caption AS NODE,

    '/Orion/images/StatusIcons/small-' + ToString(n.StatusIcon) AS [_IconFor_n],

    '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE],

    '' AS a,

    a.Name AS APP,

    '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_a],

    '/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP],

    '' AS c,

    c.ComponentName AS CMPNT,

    '/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif' AS [_IconFor_c],

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT],

    cs.ComponentStatisticData AS STAT,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT],

    cs.ErrorMessage AS MSG,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

    FROM Orion.APM.Component c

    JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID

    JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID

    JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID

    WHERE

    a.StatusDescription NOT IN ('Unmanaged')

    AND c.StatusDescription NOT IN ('Up','Disabled')

    --

    Alex

  • I Saw some components missing earlier  and switched the joins to left outer joins then it was fine

  • would you share the whole final query that you have now? I suspect it is pulling info from ChartEvidence as in first version, isn't it?

  • These seemed to work best for me- it's still in testing mode and I think I would pull in Status Description column since Stat and Message is only for some components -

    SELECT

    --Node

    n.Caption AS NODE,

    '/Orion/StatusIcon.ashx?entity=Orion.Nodes&status=' + ToString(n.Status) + '&size=small'  AS [_IconFor_NODE],

    '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE],

    --Application

    a.Name AS APP,

    '/Orion/StatusIcon.ashx?entity=Orion.APM.Application&status=' + ToString(a.status) + '&size=small' AS [_IconFor_APP],

    '/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP],

    --Component

    c.ComponentName AS CMPNT,

    '/Orion/StatusIcon.ashx?entity=Orion.APM.Component&status='  + ToString(c.Status) + '&size=small' AS [_IconFor_CMPNT],

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT],

    --Stat

    ce.AvgStatisticData AS STAT,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT],

    --Message

    ce.ErrorMessage AS MESSAGE,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

    FROM

    Orion.APM.Component(nolock=true) c

    LEFT OUTER JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID

    LEFT OUTER JOIN Orion.APM.ChartEvidence(nolock=true) ce ON ce.ComponentStatusID = ccs.ComponentStatusID

    LEFT OUTER JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID

    LEFT OUTER JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID

    WHERE

    --Stats exist, Application is NOT Unmanaged, and Component is NOT UP

    --ce.AvgStatisticData IS NOT NULL AND

    a.StatusDescription NOT IN ('Unmanaged') AND

    c.StatusDescription NOT IN ('Up','Disabled')

  • Here is my final version, which was flawlessly working in production for over 2 weeks now:

    Capture.JPG

    SELECT

    '' AS n,

    n.Caption AS NODE,

    '/Orion/images/StatusIcons/small-' + ToString(n.StatusIcon) AS [_IconFor_n],

    '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE],

    '' AS a,

    a.Name AS APP,

    '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_a],

    '/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP],

    '' AS c,

    c.ComponentName AS CMPNT,

    '/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif' AS [_IconFor_c],

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT],

    cs.ComponentStatisticData AS STAT,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT],

    cs.ErrorMessage AS MSG,

    '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

    FROM Orion.APM.Component c 

    JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID 

    JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID

    JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID

    WHERE

    a.StatusDescription NOT IN ('Unmanaged')

    AND c.StatusDescription NOT IN ('Up','Disabled')

    AND a.Name NOT IN ('Test Application','Another Test Application','One MOre Application You Do Not Want To Monitor')

    --

    Alex

  • For each of use for newer users:  The link below is a cut and pasteable item to drop directly into the Custom Query object within your Customized Page.  Copying the code from the site mangled the query and I had to fix.  BTW:  Kudos, to all contributors to this thread.  Invaluable.  Alex Slv

    First Query

    Second Query

  • Thank you. I have noticed indeed that sometimes copy-paste is not up to scratch. I have attached plain TXT file with SWQL query as well.

  • Is it possible to do something similiar for the alerts?  The problem I am facing is all alerts use the same column for their associated ID, though that ID might pertain to a node ID, volume ID, etc.  To account for these there is also a column in the alerts table that specifies what type it is.  However, to put it all into practice along the same vein as you did is proving difficult, as I would need some sort of conditional logic to dictate the syntax of the link based on the alert type.  Here is the (non-working) query I came up with:

    SELECT

       tolocal(AStat.TriggerTimeStamp) AS [Time of Alert],

       AStat.ObjectName AS [Alert],

       ADefs.Name AS [Category],

       tolocal(AStat.AcknowledgedTime) AS [Acked Time],

       AStat.AcknowledgedBy AS [Acked By],

       AStat.Notes,

       CASE AStat.ObjectType

          WHEN 'Node' THEN '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(Nodes.NodeID) AS [_LinkFor_Alert]

          WHEN 'Hardware Sensor' THEN '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(Hardware.NodeID) AS [_LinkFor_Alert]

          END

    FROM

       Orion.AlertStatus

          AS AStat

       INNER JOIN Orion.AlertDefinitions

          AS ADefs

          ON AStat.AlertDefID = ADefs.AlertDefID

       LEFT OUTER JOIN Orion.Nodes

          AS Nodes

          ON Nodes.NodeID = AStat.ActiveObject AND AStat.ObjectType = 'Node'

       LEFT OUTER JOIN Orion.HardwareHealth.HardwareItem

          AS Hardware

          ON Hardware.ID = AStat.ActiveObject AND AStat.ObjectType = 'Hardware Sensor'

    WHERE

       Acknowledged = 1

    ORDER BY TriggerTimeStamp

    Unfortunately, SWQL hangs on the CASE syntax and complains about the plus.  Any ideas?

  • i don't think it will let you use case statements...You'd have to go to a stored procedure for that.  I've got a content share for a custom alerts "report" HERE if you'd like to take a look and maybe use as base.