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.

SQL Query for Inventory Report

tigerr over 2 years ago

I'm trying to get a SQL query that lists all the nodes and their IPs, serials etc, however, the issue is that I want it to list also switch stacks and their members with their serials, not have them all as one node (Inventory purposes), I found an existing report that already has the following query but it's in swql and it also doesn't include custom properties and that's something I need:

SELECT OrionNodes.Caption, OrionNodes.DetailsUrl, OrionNodes.Status, OrionNodes.ChildStatus, NcmEntityPhysical.EntityName, OrionNodes.IP_Address, NcmEntityPhysical.EntityDescription, NcmEntityPhysical.Serial, NcmEntityPhysical.Manufacturer, NcmEntityPhysical.Model, NcmNodeProperties.NodeComments, OrionNodes.SysName, OrionNodes.NodeDescription, OrionNodes.Location, OrionNodes.Contact, OrionNodes.MachineType, OrionNodes.LastBoot, OrionNodes.IOSVersion
FROM NCM.NodeProperties AS NcmNodeProperties
INNER JOIN Orion.Nodes AS OrionNodes ON NcmNodeProperties.CoreNodeID=OrionNodes.NodeID
LEFT JOIN NCM.EntityPhysical AS NcmEntityPhysical ON NcmEntityPhysical.NodeID=NcmNodeProperties.NodeID AND (NcmEntityPhysical.EntityClass='3' OR NcmEntityPhysical.EntityClass='chassis(3)')
WHERE OrionNodes.Vendor='Cisco'
order by OrionNodes.Caption

How do I come up with something like this using SQL and also include custom properties in it?

Top Replies

alain_p over 2 years ago +1

Hi tigerr, Adding the custom property is this SWQL query is quite easy. Add this 'OrionNodes.CustomProperties.CP1' & replace CP1 with the name of your custom property. If you have multiple, just add…

Parents

0 alain_p over 2 years ago

Hi tigerr,

Adding the custom property is this SWQL query is quite easy.
Add this 'OrionNodes.CustomProperties.CP1' & replace CP1 with the name of your custom property.
If you have multiple, just add more ;-)

To get you started with your SQL query: (Replace CP1, CP2 with real values/names)

SELECT
NPM.Caption AS 'Name'
, NPM.Vendor AS 'Manufacturer'
-- Values of Custom Properties in NPM
, NPM.CP1 AS 'CP1-Name'
, NPM.CP2 AS 'CP2-Name'
, NCMEP.Model AS 'Model number'
, NCMEP.Serial AS 'Serial number'
-- Convert EOL date to ISO format (ex 2021-01-01)
, LEFT(CONVERT(varchar,EndOfSupport,120),10) AS 'Retire Date'
FROM
NCM_NodeProperties AS NCMNP
INNER JOIN
Nodes AS NPM
ON NCMNP.CoreNodeID = NPM.NodeID
LEFT JOIN
NCM_Entity_Physical AS NCMEP
ON (NCMEP.NodeID = NCMNP.NodeId AND NCMEP.Class = 3)
WHERE
NPM.Vendor = 'Cisco'

If you want to know what is in a certain database table via SQL --> Create a report in Solarwinds or use the SQL tools on your server & use 'SELECT * FROM DataBaseTableName'
Be carefull with the NCM_EntityPhysical table --> add ' WHERE Class = 3' to the query

Hope this helps.
If so, please mark this answer as Verified.

Wkr,
alain_p
Cancel
Vote Up +1 Vote Down

Cancel
0 tigerr over 2 years ago in reply to alain_p

Thank you alain_p the SWQL part worked great!

However, I couldn't get the SQL part to work properly since it's missing all the stuff from my original request and the model/serial number values are actually not populating, seems like the table reference is different.
Cancel
Vote Up 0 Vote Down

Cancel
0 alain_p over 1 year ago in reply to tigerr

Hi tigerr

Could you please share a bit more details of what is missing / not working?
On what version are you for NPM & NCM?
Tables used are Nodes, NCM_NodeProperties & NCM_Entity_Physical

Maybe you could break the SQL into little pieces to get an idea where the DB tables are different.
Node status & a clickable URL for a node are not in the SQL query.

Please try these 2 SQL queries in a report. Replace 'X' by an existing node number.

SELECT
NPM.Caption AS 'Name'
, NPM.Vendor AS 'Manufacturer'
-- Values of Custom Properties in NPM
, NPM.CP1 AS 'CP1-Name'
, NPM.CP2 AS 'CP2-Name'
FROM
Nodes AS NPM
WHERE
NPM.NodeID = X

SELECT
NCMEP.Model AS 'Model number'
, NCMEP.Serial AS 'Serial number'
-- Convert EOL date to ISO format (ex 2021-01-01)
, LEFT(CONVERT(varchar,EndOfSupport,120),10) AS 'Retire Date'
FROM
NCM_NodeProperties AS NCMNP
INNER JOIN
Nodes AS NPM
ON NCMNP.CoreNodeID = NPM.NodeID
LEFT JOIN
NCM_Entity_Physical AS NCMEP
ON (NCMEP.NodeID = NCMNP.NodeId AND NCMEP.Class = 3)
WHERE
-- NPM.Vendor = 'Cisco'
NPM.NodeID = X

Good luck!

Wkr,
alain_p
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 alain_p over 1 year ago in reply to tigerr

Hi tigerr

Could you please share a bit more details of what is missing / not working?
On what version are you for NPM & NCM?
Tables used are Nodes, NCM_NodeProperties & NCM_Entity_Physical

Maybe you could break the SQL into little pieces to get an idea where the DB tables are different.
Node status & a clickable URL for a node are not in the SQL query.

Please try these 2 SQL queries in a report. Replace 'X' by an existing node number.

SELECT
NPM.Caption AS 'Name'
, NPM.Vendor AS 'Manufacturer'
-- Values of Custom Properties in NPM
, NPM.CP1 AS 'CP1-Name'
, NPM.CP2 AS 'CP2-Name'
FROM
Nodes AS NPM
WHERE
NPM.NodeID = X

SELECT
NCMEP.Model AS 'Model number'
, NCMEP.Serial AS 'Serial number'
-- Convert EOL date to ISO format (ex 2021-01-01)
, LEFT(CONVERT(varchar,EndOfSupport,120),10) AS 'Retire Date'
FROM
NCM_NodeProperties AS NCMNP
INNER JOIN
Nodes AS NPM
ON NCMNP.CoreNodeID = NPM.NodeID
LEFT JOIN
NCM_Entity_Physical AS NCMEP
ON (NCMEP.NodeID = NCMNP.NodeId AND NCMEP.Class = 3)
WHERE
-- NPM.Vendor = 'Cisco'
NPM.NodeID = X

Good luck!

Wkr,
alain_p
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data