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

  • Wow, thanks for this one.  I didn't realize I could put queries directly into components.  This will keep me occupied all weekend.

    This query goes nicely next to the Top 100 Nodes by Percent Packet Loss component.  If only they were empty...

  • awesome, thanks for sharing...been trying to wrap my head around this stuff...I need to figure out how to use in alerting next emoticons_happy.png

  • Amazing. And do share your other ones- SWQL is the great democratizer of Solarwinds for those of us who can figure our SQL but not .Net emoticons_wink.png

    the only thing I do different for ours is I try to always cut paste the same Caption as [Node],... As [ _IconFor_Node], and ... As [_LinkFor_Node] in the beginning so everyone can see node status first- then I would change iconfor sev to icon for component so it's more clear

    Then when dependencies kick in you'll show node and component status as is warning or unreachable.

  • Hi njoylif,

    What exactly you would like to achieve with alerting? I might be a able to help

  • Good point.

    I agree, using corresponding icons next to NODE, APP and COMPONENT is very useful. Also, you can attach icons to any column, not necessarily to a new one as I created above

    How about this:

    SELECT 

    n.Caption AS NODE,

    a.Name AS APP,

    c.ComponentName AS CMPNT, 

    ce.AvgStatisticData AS STAT, 

    ce.ErrorMessage AS MSG,

    '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_NODE], 

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

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

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

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

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

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

    '/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.ChartEvidence(nolock=true) ce ON ce.ComponentStatusID = ccs.ComponentStatusID

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

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

    WHERE

    ce.AvgStatisticData IS NOT NULL AND

    a.StatusDescription NOT IN ('Unmanaged') AND

    c.StatusDescription NOT IN ('Up')

    (I could not figure out how to make it nice-looking SQL script. Seems like when you reply to post this formatting option is not available. Hope you guys are ok with this ugly-looking bunch of code emoticons_happy.png )

    --

    Alex

  • I like it a lot - adding to our app dashboard I made some small modifications:

    • used StatusIcon vs StatusLED for node so I get the small child status dots
    • edit - changed all the statusicons to the new wsdl style which I have yet to fully vet but solves an issue with application icons named inconsistently
    • reorg all the links and icons with their objects and added comments for easier tracking...

    Nodes_StatusIcon.png

    Nodes_App_Cmpt_StatusIcons.png

    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

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

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

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

    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')

  • I'm trying to figure out the relationships btn alert "base" object and how to associate to custom properties.

    i.e.

    I have app CP for applications called AlertEmails where the app owners email group(s) should be.

    for a component check...I send TO: ${SQL${Node.Colon}select AlertEmails from APM_ApplicationCustomProperties where ApplicationID=${ApplicationID}}

    I'd like to know how to do it in SWQL fashion, I.E. assume Orion.APM.Component base object; I'd think Application.CustomProperties.<column name> would work...but doesn't seem to..

    so start at component schema, go to application schema to customProperties schema and get field...

    Am I nuts?  scratch that...irrelevant.

    Thanks!

  • NJoy,

    unlike SQL SWQL keeps custom properties in other tables and while you can navigate to them through SWQL navigation you cannot double hop navigate. if you are starting with component then try joining application then navigate to application.customproperties.colomn.

    looking in both SWQL studio and also the sdk read me guide will help to see where you can navigate to

  • Hi bluefunelemental,

    The following was not giving me icon with small child dots as you have on your screenshot.

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

    I have used the one below to accomplish this.

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

  • Alex- typo on my part- that's the correct field. Cool eh?