cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

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

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 . 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

Attachments
Comments

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

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

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 )

--

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?

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 😞

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

Try putting parens around the expression after THEN.

As such:

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

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

      END AS [_LinkFor_Alert]

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

Just wondering - why the use of '' as N and associating icons there vs to the node, app, and cmpnt columns directly?

the case needs to end with ….END AS [column_name]

The width of my resource widget is set to 500px. When it comes to long app or long component names, the icon does not stay on the left. Instead - it is being left alone on the new line and all text goes beneath it. I want icon to always be displayed next-left-centred, regardless of how many lines my app name will be broken into due to narrow column width. Hope make sense...

That certainly got me further, thank you.  I am still having issues though, as now I am just having my query fail for (as of yet) unknown reasons.  I made a new thread at Help on getting HTML links into a Custom Query so as to not hijack the comments section of this one.

EDIT:

Resolved my issue.  I was essentially having a data sanitation issue; more details in the thread.

Alex Slv  im sorry but this is invaluable

Thank you very much for this.. now if you could make one for alerts on node status i.e. up or down that would be grand

Hi, I didn't quite understand what exactly you are trying to achieve. Can

you give a case study, sketch a draft or explain in a bit more details...

On 8 May 2014 22:40, "SolarWinds Community Team" <

OK yeah.. I have read what I typed and I can understand the confusion.. I will show you what I have so far

pastedImage_1.png

Custom query :

Select AlertStatus.TriggerTimeStamp, AlertStatus.ObjectName,  AlertStatus.Notes

From Orion.AlertStatus

WHERE

(AlertStatus.ObjectType = 'Node' AND AlertStatus.State <> '1')

So as you can see from the top screen shot and the SWQL I have written I am attempting to show the currently down nodes on solarwinds and the acknowledged alert with the notes that have been entered.

I would like to have something like what you have with all the functionality of what you have done in your query but all in the same query.

Also on a side note are you able to change say the font size in the query?

All the best

Try these two threads - those should take you on to the right direction with regards to Alerts

Help on getting HTML links into a Custom Query

Custom alerts page using stored procedure and report

Nice job on this!! I love it!

Hi Guys,

Great info, this is going to make my life a lot easier. I am having issues with the query though.

Using the SWQL query - I would like to only show certain applications on either certain nodes or using certain application names.

This query seems to show me everything which is not up/unmanaged etc.

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

But when I add the below to it, it shows absolutely nothing.

AND a.Name in ('Remote Registry','Domain Time II Client','Domain Time II Server','Mail Marshal Services Template','Poller Health Check','Distributed File System (DFS)')

Now ideally I would like to put some wild cards in there too like ('%Registry%',%DFS%) - although if this isn't easy to do then its not critical.

I would also like to filter by nodes. so ('Node1',Node2) as well as application (But again not critical)

Application filtering would be the first priority.

if anyone can help this would be appreciated a lot!

Thanks,

Craig

you can't use wildcards (that I know of) with 'IN' statements..as it equates to a bunch of combined '=' statements in 1.

thus, if you remove your filter and get the full list, you should be using in your "a.name in (....)" statement the full names of the apps in the "APP" column of original output.

is that what you have and you are still getting empty-set results?

Hello,

Thank you for the response.

I have tried just adding AND a.name IN ('Remote Registry Monitor') but it still does not return any information.

Capture.PNG

I have confirmed that is the exact name of the application (Below)

Capture.PNG

But still nothing.

Also, If I am unable to use wildcards with IN statements, do you know of any other way I can use them with this query? I have like 20 applications with the same word in them so would save me a lot of time writing once instead of each one.

Many Thanks,

Craig

for your wildcards, try something like:

and a.name like '%commonword%'

i.e AND a.name like '%MSSQLSERVER%'

     this works for me in swql studio

this worked for me in swql studio for the IN statement:

AND a.name in ('Oracle processes','SSL Cert Exp Date')

maybe go into swql studio, get results you want then use that format in resource

Thank you for the assistance.

I have managed to get it working using the IN method. No idea why it wasn't working before but when I checked it this morning it was just working!

When it comes to the like query - I dont see how it can work as if I put:

AND a.name like DFS

AND a.name like Registry

An application cannot be both named DFS and Registry.so it shows nothing.

If I put:

AND a.name like DFS

OR a.name like Registry

Then the OR query ignores anything below because its an OR and no longer an AND (If this makes sense)

So i'm stuck at this part. But to be honest I get good data back now it just means I manually have to enter a lot of application names. so no biggie

Thanks for you help!

How about the following format? I use it for "Nodes with problems". Notice "LIKE" statements below... just try the same with the above. Let me know how did it go

SELECT 

'' AS n,

n.Caption AS NODE,

n.StatusDescription AS STATUS,

n.Stats.CPULoad AS CPU_LOAD,

n.Stats.PercentMemoryUsed AS MEM_USED,

n.Stats.ResponseTime AS RSPNS_T,

n.Stats.PercentLoss AS PRCNT_LOSS,

n.Stats.LastBoot AS LAST_BOOT,

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

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

FROM

Orion.Nodes(nolock=true) n

WHERE

n.StatusDescription NOT LIKE ('Node status is Up%') AND

n.StatusDescription NOT LIKE ('Node status is Unmanaged%') AND

n.CustomProperties.n_mute_dashb <> 'True'

Here is your answer

AND (

  a.Name LIKE ('%Windows%') OR

  a.Name LIKE ('%VMWare%')

)

This looks very powerful.

I have a couple of questions.

I took the text supplied; copy/pasted it into a new "Custom Query" resource on a test page.

When I apply it & the page refreshes I get this error.

"Error: A query to the SolarWinds Information Service failed."

Do I need something not supplied in SW or by my sql?

My error.jpgsoftware is version

NPM 10.7

There are a number of variations on the query in this thread, and many of them rely on the SAM product. Do you have SAM installed? If so, what version? Also, please post the query you are using.

tdanner,

thank you for responding.

I do not know which application the SAM is so I would assume it is not one of the Applications we own currently.

As for the query, I used the thread creators query attached to the thread itself. It says it is version 3.

https://thwack.solarwinds.com/servlet/JiveServlet/download/174568-3-45852/SWQL_Query.txt

That's correct, it is version 3 (SWQL). It definitely works over here.

I guess try to re-type the above manually. Sometimes I have found that what would appear to be the same (such as single/double qutes for example), on the ASCII level is entirely different

I do not have SAM so if that is required then it wont work.

I have

NCM

NPM

NTA

VNQM

Oh, by the way, and if you are copy/pasting "Nodes with problems" SWQL then you would need to remove last line of the code, as it is only applicable to my specific environment

SAM is our Server and Application Monitor product. It provides the Orion.APM.Component, Orion.APM.CurrentStatistics, and Orion.APM.Application entities referenced in that query (among many other features).

you are correct, the ANDs would not match in that fashion.

boils down to order of operation...so you have to put () around the a.name like '' section

i.e.

WHERE

condition.a=x AND condition.b=y

AND (a.name like '%DFS%' or a.name like '%registry%' or a.name like '%mufasa%')

AND condition.c=z

HTH

OH I FEEL SO CLOSE BUT FAILED This works in SWQL but not in a custom query any ideas? I have to UNION due to the custom poller's not fiting into this board alert situation.

[code]

select '<img src=/NetPerfMon/images/Vendors/' +ToString(T.VendorIcon)+'/>' as Vendor

,'<a href=/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' +ToString(T.NodeID)+ '>' + T.Caption +'</a>' as Node_Name

,'<img src=/NetPerfMon/images/small-' +ToString(T.GroupStatus)+ '/>' as Status

,T.Message,T.IP_Address,T.EventDate from (

select e.Nodes.VendorIcon

,e.Nodes.Caption

,e.Message

,e.Nodes.GroupStatus

,ToLocal(e.EventTime) as EventDate

,e.Nodes.IP_Address

,e.Nodes.Status

,a.Acknowledged

,e.Nodes.NodeID

from Orion.AlertStatus(nolock=true) as a

inner join Orion.Events(nolock=true) as e on MINUTEDIFF(0,a.TriggerTimeStamp) = MINUTEDIFF(0,e.EventTime)

  AND ToString(a.ActiveObject) = ToString(e.NetObjectID)

UNION ALL (

select oe.Nodes.VendorIcon,oe.Nodes.Caption,oe.Message,oe.Nodes.GroupStatus,ToLocal(oe.EventTime) as EventDate,oe.Nodes.IP_Address,oe.Nodes.Status,oa.Acknowledged, oe.Nodes.NodeID

from Orion.AlertStatus(nolock=true) as oa

left outer join Orion.NPM.CustomPollerStatusOnNode(nolock=true) as cps on oa.ActiveObject = ToString(cps.CustomPollerAssignmentID)

inner join Orion.Events(nolock=true) as oe on MINUTEDIFF(0,oa.TriggerTimeStamp) = MINUTEDIFF(0,oe.EventTime)

  AND oe.NetworkNode = cps.NodeID

)

) T

where

T.Status NOT IN (2,9)

AND T.Acknowledged <> 1

order by T.EventDate DESC

[/code]

Try having the first column selected be a standard ID field or something.

Strange but I found the SWQL input sanitation to prefer it.

Thanks,

Christian

What are you trying to achieve? Not so long ago I have converted SWQL into SQL and I am using SQL now - it is much more easier this way to build queries and you can still use them as resources, reports, alerts triggers, etc.

What is your final goal?

Fantastic query, it helps us a lot!

I have one more thing which would help us.

Is there a way to add "last database sync" or (much better) the time when components/node has gone in critical state ?

(Instead of stat text, which is not so important for us.)

Thanks!

br

Alois

yes, there is... I am getting ready next article which will explain how to do this with SQL ... I hope someone can help with SWQL in the meantime ...

Version history
Revision #:
1 of 1
Last update:
‎04-10-2014 12:34 PM
Updated by: