I'm trying to use data from orion.nodes and ncm.nodes at the same time (list of devices not in ncm)
I can't figure out how to get it working. Is it because I'm digging into two different databases at the same time?
There are a few query features that don't work in SWIS when a query references data that spans multiple databases, and "WHERE NOT IN (SELECT ...)" is one of them. Fortunately you can rewrite this using a join instead of a subquery and then it should work.
SELECT Caption
FROM Orion.Nodes orionnode
LEFT JOIN NCM.Nodes ncmnode ON orionnode.NodeID=ncmnode.CoreNodeID
WHERE orionnode.Vendor='Cisco' AND ncmnode.NodeID IS NULL
Hello, I guess you use SWIS v3 connection, although AFAIK NCM is still on v2. Can you please for NCM operations use v2 connection? If you don't know how to do it, please specify your usecase (SWQL studio, API language)
I'm testing with swql studio, and have tried with both v2 and v3
Does the query in the linked thread work for you?
select caption from orion.nodes where nodeid not in (select corenodeid from ncm.nodes) and vendor='Cisco'
no, it doesn't work for me (honestly I don't know why at this time), but following query should principally do the same for you (under swis v2):
select n.caption from orion.nodes n left join ncm.nodes c on n.nodeid=c.corenodeid
where n.vendor='Cisco' and c.corenodeid is null
Let me know if it helped
EDIT: Tim was quicker:-)
Thank you. This one did what I asked for but created a new problem
As mentioned in my original thread, I was going to query for more, so I also need to query
"Orion.NodesCustomProperties.Provider_name <> 'providerX'" and Unmanaged='False'
Unmanaged='False' is fine; I can just add that to the query, but I guess the other requires me to do one more LEFT JOIN.
How do I specify that?
Hello all,
I found the NCM.Nodes table, had SWQL Generate a Select Statement for me (connected as v3), and tried to run it and got an exception it seems to me that I shouldn't have gotten.
2014-05-23 20:01:40,083 [283] ERROR SolarWinds.InformationService.Core.InformationService - Exception caught in method RunQuerySolarWinds.Data.Query.QueryExecutionException: Unknown provider Cirrus.
Looked on Thwack and they (y'all) say to use v2 with NCM. So I did that in a new instance of SWQL (connected to v2) and it appears to work.
So, it was then time to try it in SoapUI so that I can make sure I can make a REST call to get the same query. However, when I tried the following (by simply switching /v3/ to /v2/, I got no data back.
https://orionAPIServer:17778/SolarWinds/InformationService/v2/Json/Query?query=SELECT NodeID, CoreNodeID, EngineID, NodeCaption, NodeGroup, AgentIP, AgentIPv6, ... SNMPEncryptType, SNMPEncryptPass, AllowIntermediary, UseKeybInteractiveAuth, ResponseError, City, Department, Comments, n_WeKnow, n_mute, Rack, NodeRole, n_muteStartTime, n_muteDuration, SoftwareLevel FROM NCM.Nodes WHERE CoreNodeID = 467
Well, in the XML pane I got "<data contentType="null" contentLength="0"><![CDATA[]]></data>" and oh yeah, I apparently got a 405.
So, is this possible via the REST API, and if so, how? (Since obviously simply switching /v3/ to /v2/ doesn't get it.)
Thanks,Scott
The REST API is only supported in SWISv3. For SWISv2 (including NCM) you must use SOAP or PowerShell (use the -v2 option to Connect-Swis).
NCM 7.3 moves that product from SWISv2 to SWISv3, so you can access data through the REST API. This version is now at the release candidate stage: SolarWinds NCM 7.3 Release Candidate is Available!
Worked for me, just had to change a few things around. Here is my SQL code in report writer!
FROM nodesdata
LEFT JOIN NCM_Nodes ncmnode ON nodesdata.NodeID=ncmnode.CoreNodeID
WHERE nodesdata.Vendor='Cisco' AND ncmnode.NodeID IS NULL
