Anyone got a free moment to help me with swql

[trilobite_rex]

SELECT
Caption as DisplayName,
StatusDescription as [Status]
FROM Orion.Nodes
JOIN Orion.NodesCustomProperties
ON NodeID = Orion.NodesCustomProperties.NodeID
WHERE (Nodes.Status = 2)
AND (Nodes.IsTest = 'False')
AND (Nodes.IsWorkstation = 'False')
AND (MachineType <> 'VMware ESX Server')

is my query but it keeps generating various errors when I try to fix it.  The one as it currently exists is 

Provided SWQL query is not valid. Details: RunQuery failed, check fault information. Ambiguous column name NodeID between entities Orion.Nodes and Orion.NodesCustomProperties.

Also, is there some sort of definitive guide that would walk your through SQL or SWQL out there.  

[jm_sysadmin]

So I wasn't quite sure, but I think 'IsTest ' and 'IsWorkstation' are custom properties on your install, but if not, shout at me. 

SELECT
N.Caption as DisplayName,
N.StatusDescription as [Status]
FROM Orion.Nodes N
WHERE (N.Status = 2)
AND (n.CustomProperties.IsTest = 'False')
AND (n.CustomProperties.IsWorkstation = 'False')
AND (N.MachineType <> 'VMware ESX Server')

If that isn't what you want shout, and I will see if I can dig up some good SWQL links

[jm_sysadmin]

@trilobite_rex 

Here's a 3 part series with a good walk through:

https://thwack.solarwinds.com/t5/Product-Blog/Intro-to-API-SDK-SWQL/ba-p/506794 

The SDK on Github has a Wiki, and lots of good info, specfically check out the SWQL functions:

https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions

And remember that is completely fine to 'learn by doing' with help here on Thwack.

[trilobite_rex]

That seems to have done it.   I am trying to generate a kpi widget to show the same thing

I started with this,

SELECT Count (Status) as value FROM Orion.Nodes WHERE (Status = 2)

when I go to put the AND statements from above it errors out.  

Does it not work the same way?  And yes, any all links are appreciated.

[trilobite_rex]

My next task is 

SELECT Count (Status) as value FROM Orion.Nodes WHERE (Status = 2), but I have the following conditions  

AND (n.CustomProperties.IsTest = 'False')
AND (n.CustomProperties.IsWorkstation = 'False')
AND (N.MachineType <> 'VMware ESX Server')

I am basing it off of this kpi that I have created. 

SELECT Count (Status) as value FROM Orion.Nodes WHERE (Status = 2) AND (MachineType = 'VMware ESX Server')

Is there a join that I need to throw in somewher for the n.CustomProperties selections.

[jm_sysadmin]

Do you get errors? Incorrect results? I have a sample that I think is pretty close in another post, help me understand where you are having issues.

[jm_sysadmin · Accepted solution]

So I am guessing what you are going for, but I think something like this is the data source I would use in a KPI widget:

SELECT
Count(Distinct N.nodeid) as [Number of Down Nodes]
FROM Orion.Nodes N
WHERE (N.Status = 2)
AND (n.CustomProperties.IsTest = 'False')
AND (n.CustomProperties.IsWorkstation = 'False')
AND (N.MachineType not like 'VMware ESX Server')

Here's an example widget I made for showing NCM device back ups:

 The first box percent has a query that looks like 

Select Round(((Count(t0.NCM_ID)*1.0) / (Count(t0.Device)*1.0))*100,1) as [stuff]
From
(SELECT Distinct N.Caption as[Device], CT.NodeID as [NCM_ID]
FROM NCM.NodeProperties as NP
JOIN Orion.Nodes N ON N.NodeID = NP.CoreNodeID
LEFT JOIN NCM.ConfigArchive CT ON NP.NodeID = CT.NodeID
Where N.CustomProperties.Applications like 'Network%') as t0

This is the third box (which is nearly the same as the 2nd, but #2 'is not NULL'

SELECT Count (Distinct N.Caption) as [Count of Missing NCM Backups]
FROM NCM.NodeProperties as NP
JOIN Orion.Nodes N ON N.NodeID = NP.CoreNodeID
LEFT JOIN NCM.ConfigArchive CT ON NP.NodeID = CT.NodeID
Where CT.NodeID IS NULL and N.CustomProperties.Applications like 'Network%'

Sometimes I had to Join views, sometimes I could go with tables that SWIS links for you (like Node Custom Properties). I am guessing you already have the SDK installed, but look for the link icon in a table to know what's available. The first query also had a sub-select, I found it easier to do the math that way. Hopefully some of this helps.

[DaHaole]

Aloha....

Going off what jm_sysadmin started, there seems to be some confusion on which columns are in which table/view.  Try:

SELECT
Caption as DisplayName,
StatusDescription as [Status]
FROM Orion.Nodes AS N
JOIN Orion.NodesCustomProperties AS NCP
ON N.NodeID = NCP.NodeID
WHERE (N.Status = 2) 
AND (NCP.IsTest = 'False') 
AND (NCP.IsWorkstation = 'False')  
AND (N.MachineType <> 'VMware ESX Server') 

The only other issue I might see is what type the IsTest and IsWorkstation.  If they are bool, you might want to adjust the setting to 0 or $False.

Mahalo...

DaHaole