SQL Filtering on a Resource using order by.

Hello Everyone,

Trying to get the "Nodes Not Up" resource to have a different sorting than alphabetical and attempted to use Order by 'Status' desc and this just returns an error "Custom SWQL filter is incorrectly formatted." Do we know can you use an Order By clause in that field for sorting or will it just not work there?

Thanks, in advance.

Find more posts tagged with

resource filter

filtering

SWQL

resource

Accepted answers

All comments

adam.beedell

I think that wont work there. The "Filter" section basically goes in a WHERE statement, probably wont let other stuff through

That said, you can quickly replace this with a custom table. Select x from nodes where status = 2 order by y asc/desc

jholmes

I agree with your assessment, I ended up going with All Nodes - Tree and filtered with Status != 1 then used the default grouping by status which got what we wanted. the custom table resource didn't let the nodename link which was required.

bobmarley

You could always make your own custom SWQL widget and do the sorting in the query. The query below will look similar to the one that comes out of the box but nodes are ordered by the time they went down. If there are custom properties that you want to include uncomment and edit the Group below.

SELECT 
    NodeName AS [Node Name],
        '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
        DetailsUrl AS [_LinkFor_Node Name], --Nodes.CustomProperties.Group as [Group],

MAX(tolocal(DOWNEVENT.EVENTTIME)) AS [DOWN TIME]
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS DOWNEVENT
ON NODES.NODEID = DOWNEVENT.NETWORKNODE
WHERE STATUS = 2 --AND NodeName Not Like 'er2%' 
GROUP BY NodeName, StatusIcon, DetailsUrl--, Nodes.CustomProperties.Group
ORDER BY [DOWN TIME] DESC

adam.beedell

That sounds like a fine solution to me - That said you can add a link in custom table resources, either by using [_LinkFor_x] as in bobmarley's example, or with CONCAT('<a href="',detailsurl or whatever,'">',Nodename or whatever,'</a>) as [built_our_own_html_link] and then ticking the "Allow HTML" option against the column

monitoringlife

I am able to use "Order by" on my custom query (and custom table) dashboards. I always build these out in SWQL studio to make sure they are working and to fine tune, then copy them over into the dashboard.

Also highly recommend setting up the search (Search SWQL Query:) on custom queries. Really makes them more usable, I get a lot more of my users using those dashboards.