"Custom Query" resource issue

ChrisDavis_HSV

In an effort to try to remove custom coded resources and replace them with the native "Custom Query" resource, I have run into an odd issue that I can't find any information on why or how to work around. I am trying to produce a simplified list of nodes and their status, similar to the "Nodes with Problems" resource, but without the extra columns. This query works, but it doesn't quite result in the "look" that I want:

SELECT n.Caption as [Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_IconFor_Node Name], n.StatusDescription FROM Orion.Nodes n WHERE n.Status<> 9 and n.Severity > 0

So, I tried the following:

SELECT ' ' as [ ], n.Caption as [Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_IconFor_ ], n.StatusDescription FROM Orion.Nodes n WHERE n.Status<> 9 and n.Severity > 0

This results in an error. The funny thing is, if I move the columns around, it works:

SELECT n.Caption as [Node Name], ' ' as [ ], '/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_IconFor_ ], n.StatusDescription FROM Orion.Nodes n WHERE n.Status<> 9 and n.Severity > 0

The thing I can't figure out is why. Both queries execute fine in SQWL Studio. Why does having a quoted value in the first column matter to the resource? I say that is the issue because in my attempts to format things, I found that this construct also fails:

SELECT ' ' + n.Caption as [Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_IconFor_Node Name], n.StatusDescription FROM Orion.Nodes n WHERE n.Status<> 9 and n.Severity > 0

I tried to contact support, but the answer I get is that they don't support customizations (even though this issue is with their native resource and SWQL). So now I reach out to THWACK in an effort to get a bug submission if nothing else...

ChrisDavis_HSV

I apparently never posted the solution that I found... my bad.

The issue is that when displaying a query result, the "Custom Query" resource requires that the first column to contain sort-able values that are non-calculated (read directly queried values). A blank or space isn't, so it fails. The way I was able to get around this is to hide the first column by putting a "_" on the column label. So my query became:

SELECT n.Severity as [_Severity], ' ' as [ ], n.Caption as [Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_IconFor_ ], n.StatusDescription FROM Orion.Nodes n WHERE n.Status<> 9 and n.Severity > 0

Now it works.

Just in case someone else out there has a similar issue.