This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Query Child Nodes

sbeskur over 11 years ago

I am trying to pull some information out of the Solarwinds Orion database. I can get some info the following query:

select NodeId , ip_address, Caption, AssetTag

, Dns , Country , City, State, Status , ChildStatus

FROM Nodes

where country = 'US' and caption like '...%'

order by state, caption

However, one of the nodes I query is a wireless access controller that contains child nodes (Wireless access points). In the web interface this information is presented when one clicks on the parent controller but I would like to query the database for this information and the status of each individual child node (AP).

Is there a table/relation that lists child nodes and status? Any help or sample queries would be greatly appreciated.

SJB

0 mdriskell over 11 years ago

There are canned wireless reports that show this type of info...I'd suggest viewing one of them on the server and checking the SHOW SQL button to get the raw query.
Cancel
Vote Up 0 Vote Down

Cancel
0 sbeskur over 11 years ago in reply to mdriskell

Mike, Thanks for the swift response but I'm not really seeing these reports. I'm quite a newbie to this product so I'll continue to poke around.
This may help with unwinding wireless device statuses but I'd really like to understand how to query any device node that has child nodes.
As there is virtually NO referential integrity (FK's) on this database it is quite difficult to understand the relationships. Does anyone out there have even and "unofficial" schema doc?
Cancel
Vote Up 0 Vote Down

Cancel
0 mdriskell over 11 years ago in reply to sbeskur

This is the raw SQL of the canned wireless availability report. If you remove the availability portion along with the time frames you should get close to what you are looking for.
SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.NodeID AS NodeID,
NPM_NV_WL_APS_V.RecordID AS RecordID,
NPM_NV_WL_REPORT_APS_V.AP_Name AS Name,
NPM_NV_WL_REPORT_APS_V.Controller AS Controller,
Nodes.VendorIcon AS Vendor_Icon,
NPM_NV_WL_REPORT_APS_V.IPAddress AS IP_Address,
AVG((NPM_NV_WL_REPORT_APS_V.Available * 100)) AS AVERAGE_of_Avg_Availability
FROM
(NPM_NV_WL_REPORT_APS_V INNER JOIN NPM_NV_WL_APS_V ON (NPM_NV_WL_REPORT_APS_V.NodeID = NPM_NV_WL_APS_V.NodeID AND NPM_NV_WL_REPORT_APS_V.RecordID = NPM_NV_WL_APS_V.RecordID)) INNER JOIN Nodes ON (NPM_NV_WL_APS_V.NodeID = Nodes.NodeID)
WHERE
( DateTime BETWEEN 41099 AND 41106.625 )
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Cancel
Vote Up 0 Vote Down

Cancel