Report with Custom SWQL query + Search SWQL query

Akhitoakhee

I have created a custom SWQL query report, but when I try to enable search using the "Search SWQL Query" option, the search field doesn't appear in the output.

Could you let me know if I'm missing any steps? This is my first time using a search in a report.

Custom SWQL query:

SELECT
n.Caption AS [Node Name],
n.IPAddress,
CASE n.Status
WHEN 1 THEN 'Up'
WHEN 2 THEN 'Down'
WHEN 3 THEN 'Warning'
WHEN 9 THEN 'Unreachable'
ELSE 'Other'
END AS [Status],
n.MachineType,
cp.Site_node AS [Site],
e.DisplayName AS [Polling Engine],
c.OSImage AS [OS Image],
n.LastBoot
FROM Orion.Nodes n
LEFT JOIN Orion.NodesCustomProperties cp ON n.NodeID = cp.NodeID
LEFT JOIN Cirrus.Nodes c ON c.CoreNodeID = n.NodeID
LEFT JOIN Orion.Engines e ON n.EngineID = e.EngineID
ORDER BY n.Caption


Search SWQL query:

SELECT
n.Caption AS [Node Name],
n.IPAddress,
CASE n.Status
WHEN 1 THEN 'Up'
WHEN 2 THEN 'Down'
WHEN 3 THEN 'Warning'
WHEN 9 THEN 'Unreachable'
ELSE 'Other'
END AS [Status],
n.MachineType,
cp.Site_node AS [Site],
e.DisplayName AS [Polling Engine],
c.OSImage AS [OS Image],
n.LastBoot
FROM Orion.Nodes n
LEFT JOIN Orion.NodesCustomProperties cp ON n.NodeID = cp.NodeID
LEFT JOIN Cirrus.Nodes c ON c.CoreNodeID = n.NodeID
LEFT JOIN Orion.Engines e ON n.EngineID = e.EngineID
WHERE
n.Caption LIKE '%${SEARCH_STRING}%' OR
n.IPAddress LIKE '%${SEARCH_STRING}%' OR
cp.Site_node LIKE '%${SEARCH_STRING}%' OR
c.OSImage LIKE '%${SEARCH_STRING}%'
ORDER BY n.Caption

yellowtj

Just an FYI, I have not had a chance to play with dynamic reports yet, but I did notice your use of the case statement.  Instead of a case statement you can do a join to the Orion.StatusInfo table to get a text  status message.