This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

Parents
  • 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

Reply
  • 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

Children
No Data