cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

SWQL : NCM ConnectionProfiles table

Hi, the SQL statement below works in SQL Management studio. The objective is to retrieve the connection profile Name and build rules around it based on its name.

SELECT A.[NodeID]

      ,A.[IP_Address]

      ,A.[Caption]

      ,A.[DNS]

  ,B.EQUIPE_OPS

  ,C.ConnectionProfile

  ,D.Name

  FROM ((([SolarWindsOrion].[dbo].[NodesData] as A

  INNER JOIN SolarWindsOrion.dbo.NodesCustomProperties as B on B.NodeID = A.NodeID)

  INNER JOIN SolarwindsOrion.dbo.NCM_NodeProperties as C on C.CoreNodeID = A.NodeID)

  INNER JOIN SolarwindsOrion.dbo.NCM_ConnectionProfiles as D on D.ID = C.ConnectionProfile)

When I use the SQWL query api with the given statement as $query , (see code below) , I receive the error "Message" : "Source entity [NCM.ConnectionProfiles] not found in catalog",

my $query = sprintf(

"SELECT A.[NodeID]

        ,A.[IP_Address]

        ,A.[Caption]

        ,A.[DNS]

        ,B.EQUIPE_OPS

        ,C.ConnectionProfile

        ,D.Name

  FROM Orion.Nodes A

  INNER JOIN Orion.NodesCustomProperties B on B.NodeID = A.NodeID

  INNER JOIN NCM.NodeProperties C on C.CoreNodeID = A.NodeID

  INNER JOIN NCM.ConnectionProfiles D on D.ID = C.ConnectionProfile

");

What am I missing ?

0 Kudos
10 Replies
Level 9

Thank you tdanner. I was off for 5 weeks I just came back. I'll give it a try !

0 Kudos

This works like a charm !

0 Kudos

Interesting thoughts here.....

0 Kudos

thanks. Well I've been looking for a way to detect and alarm unwanted changes in the connection profiles assigned to the nodes. Since I asked the super users to respect a certain nomenclature, this is going to be easy.

Too bad there's no way to report on Connection profiles , but they opened a feature request. In the meanwhile ....

0 Kudos
Level 9

This sounds good. I could retrieve them in a separate request and match after.

What would be the full url to do such a request ? I've only been exposed to SWQL query so far

0 Kudos

Docs: https://github.com/solarwinds/OrionSDK/wiki/REST#invoke-request

The url for GetAllConnectionProfiles would be https://your-server:17778/SolarWinds/InformationService/v3/Json/Invoke/Cirrus.Nodes/GetAllConnection.... The POST body is a json array (not object!) of the argument values. Since this one takes zero arguments, the post body would just be [ ]

Hey Tim could you please discuss with me in this thread ? I have a similar question, I'm getting the hang of it but I'm still missing some details ...

https://thwack.solarwinds.com/thread/119327

0 Kudos
Level 19

The connection profiles are not available through the query interface. However you can fetch one of them using the Cirrus.Nodes.GetConnectionProfile verb (expects one argument, the ID of the profile to get) or all of them using the Cirrus.Nodes.GetAllConnectionProfiles verb (expects zero arguments).

So what's the reasoning behind this?  It used to be really handy to be able to report on what Nodes were using what Connection Profile and Device Template along with the Username/Password and other settings.  But now, since those entities have been removed from SWIS I can't get that data... It wouldn't be a problem if the NodeProperties SWQL table didn't just return ${Global...} values (like ${GlobalUserName} for UserName) for those things, but since it does I'm really stuck (it returns that ${Global...} variable for every NCM node, even though nearly none of our nodes use the Global connection profiles...).

I've got an admin who is asking for a report on what SSH Username is being used by all Cisco devices and I can't give him one...  I just don't get why the API was changed to remove these things.

One big selling point on using SWQL queries over SQL queries when creating custom reports is that while SolarWinds could always make schema changes to the SQL database and potentially break custom reports, alerts, and views, SWQL queries should always work.  Apparently though that's not the case and is a bit frustrating.  I've got a bunch of NCM reports that broke when upgrading from 7.4 because the Device Template column can no longer be found and I can no longer see NCM usernames.

Could you be so kind to share with us exact queries you were using for reporting in NCM 7.4 which stopped working. I would like to take closer look on those and provide you with some solution.

0 Kudos