Resource showing servers with UNKNOWN C Drives

Question

I am trying to set up a custom query resource that shows any servers that have C drives that are unknown.  The thought behind this was we were looking for a way to alert when WMI goes down on a server and I found a thwack post saying to look at the C drive's status because more often than not if it is UNKNOWN WMI probably isn't working.  Anyway, I have a report that shows this but am unsure how to get this into SWQL and a custom query resource.

Here is how my report is set up. I'd like to show the node name, IP and Alert_Team (custom property) on the resource so our OPS team can easily see what is going on.

christopher.t.jones123 · Accepted Answer

@bharris1, the report will show you the query it is using if you hit the little down arrow next to "All Child conditions..." the only thing it wont do is present you with the available columns. Given what you asked for I believe the following Query is what you're asking for. SELECT V.Node.Caption as [Node Name] , V.Node.IP_Address as [IP Address] , V.Node.CustomProperties.Alert_Team as [Alert Team] , V.Caption as [Volume Name] , CASE WHEN V.VolumeResponding = 'Y' then 'Yes' else 'No' end as [Volume Responding] FROM Orion.Volumes V WHERE V.Caption like '%C:\%' and V.Node.Status <> '9' and V.Status <> '9' and V.VolumeResponding = 'N'

bharris1 · Accepted Answer

I had no idea you could hit the little down arrow. That's awesome! Thanks for the query help. If I wanted to filter out two specific servers how would I do that? I tried this and I apparently have the syntax wrong as it still showed the 3 servers it had shown before. SELECT V.Node.Caption as [Node Name] , V.Node.IP_Address as [IP Address] , V.Node.CustomProperties.Alert_Team as [Alert Team] , V.Caption as [Volume Name] , V.Node.DetailsUrl AS [_LinkFor_Node Name] , CASE WHEN V.VolumeResponding = 'Y' then 'Yes' else 'No' end as [Volume Responding] FROM Orion.Volumes V WHERE V.Caption like '%C:\%' and V.Node.Status <> '9' and V.Status <> '9' and V.VolumeResponding = 'N' AND (V.Node.Caption not like 'SERVER1' OR V.Node.Caption not like 'SERVER2' OR V.Node.Caption not like 'SERVER3')

christopher.t.jones123 · Accepted Answer

@bharris1, and to be honest because they are negative statements you dont need to put them in a group, you can just say and not like ... and not like .. etc

AND V.Node.Caption NOT LIKE '%SERVER1%' and V.Node.Caption NOT LIKE '%SERVER2%' and V.Node.Caption NOT LIKE '%SERVER3%'