Custom Dashboard Widget to Display Muted Nodes

[tsanford]

Hello everyone, 

We are looking to create a custom widget on our SolarWinds dashboard that lists any nodes that have been placed into maintenance mode and have had alerts muted. We have a working custom widget that lists unmanged and muted nodes, however we are trying to narrow it down to just muted nodes. I have attached the SWQL code from our working widget and the code from the widget that we are trying to modify to see only muted nodes. The one we are attempting to build displays the data we want in SWQL studio, but not when putting the code into the widget on the dashboard. All we see is "there was an error processing this request." I hope I have selected the proper location for this post. Please let me know if additional info is needed. 

Thank you in advance! 

- Trevor

[mesverrum · Accepted solution]

So the query has 3 nested queries in it, but the outermost query doesn't have anything defining the order, which is what the web UI gets mad about.  See the last line here for the fix:

## This code displays only muted nodes in SWQL, however displays no data on the SolarWinds "Custom Query" dashboard widget ##

SELECT 
'Muted' as [Status]
,n.caption
,tostring(tolocal(SuppressFrom)) as [From]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(SuppressUntil )) end AS [Until]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, ae.AccountID AS [Account]

FROM Orion.AlertSuppression asup 
join orion.nodes n on asup.entityuri=n.uri
join (
    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID
join (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

order by n.caption

[tsanford]

This did it, thank you! 

[mesverrum]

The one that doesnt work doesn't seem to have an order by, for some reason the web ui gets fussy when relatively complex queries dont specify the order by.  The issue comes up at least once a month.

[tsanford]

Thank you both for the replies. Apologies, this is a bit outside of my normal area. By chance, could you provide an example of specifying an order by? In the code that is not working in the widget, I found the following line: Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

Thank you, 

- Trevor

[ioan_bucsa]

This is the error I am seeing in Orion.Web.log when running the query:

It's odd that SWQL queries work differently from Studio than from the web application. I always tested my queries in SWQL Studio first and was under the impression that the back-end mechanism is identical.