New Dashboard custom properties query

Question

Hopefully have the right place to post this:

I have an SWQL query but it doesn't seem to be working as it should I wonder if someone can point me in the right direction please its the part that is in Bold:

I think its the brackets that are in the wrong place?

Ive tried AND instead of OR and LIKE instead of NOT LIKE

as what Im trying to say is:

I want it to check the status  and the campus ( are down )   BUT not  to not show the campus as down -  if the comments equal comments1 or team responsible equal teamA or if the alerting node room is none alerting noderoom

SELECT N.TheCount

, CASE WHEN D.Link is null then NULL 
 ELSE CONCAT(D.Link,'?filters=', d.InstanceSiteId, '_Orion.Nodes_Status:eq:2') 
 END AS Link
FROM
( SELECT COUNT(1) as TheCount FROM Orion.Nodes C WHERE (Status = 2 AND C.CustomProperties.Campus LIKE '%' + ('campus1')) AND ( C.CustomProperties.Comments NOT LIKE '%' + ('comment1') OR C.CustomProperties.Team_Responsible NOT LIKE '%' + ('TeamA') OR C.CustomProperties.NodeRoom NOT LIKE '%' + ('none Alerting noderoom'))) N
LEFT JOIN 
(
 SELECT TOP 1 InstanceSiteId, '/apps/platform/dashboard/' + TOSTRING(DashboardID) as Link
 FROM Orion.Dashboards.Instances A
 WHERE DisplayName='Networks All Nodes'
 ORDER BY DashboardID
) D ON 1=1

thanks very much

Nigel

adam.beedell · Answer

I think this can just be a case statement. Could perhaps do a with a WHERE NOT IN (Subquery witht he 3 conditions) or an equivalent join

lynchnigel · Answer

[quote userid="87197" url="~/products/network-performance-monitor-npm/f/forum/102130/new-dashboard-custom-properties-query"]I want it to check the status  and the campus ( are down )   BUT not  to not show the campus as down -  if the comments equal comments1 or team responsible equal teamA or if the alerting node room is none alerting noderoom[/quote]My apologies, I want to check that the status is down (2) and the campus (if that is down)

And if any of these other statements are true not to set the campus as down (C.CustomProperties.Comments NOT LIKE '%' + ('comment1') OR C.CustomProperties.Team_Responsible NOT LIKE '%' + ('TeamA') OR C.CustomProperties.NodeRoom NOT LIKE '%' + ('none Alerting noderoom'))

adam.beedell · Answer

Looks like we're trying to generate a modern dashboard link with a filter based on a triple condition

The first subquery feels like it could just be in the CASE statement using orion.nodes instead with a bit less complexity.

The '%'+ X line is really weird, it works but it feels wrong. '%'+n.caption works for example without the brackets and that, or if it's not a SWQL variable but a thwack-censored string then just a % infront of the string works

Left join on 1=1 is weird

I think this could just be a case statement and maybe a join