Seeking a SWQL Inventory Query (or an ordinary Inventory Report) that includes System Name or Node Name--can you help me?

The Solarwinds SDK has a pretty nice query called Cirrus.EntityPhysical. But it's missing a column to show the System Name or Node Name. Can you help me add that into the query, or point me to a different inventory report that would enable me to discover, for example, all Cisco 2960 switches AND their names?

The existing query I'm starting with looks like this:

SELECT NodeID, LastDiscovery, FirstDiscovery, Missing, Name, Description, Type, ContainedIn, EntityClass, Position, HardwareRevision, FirmwareRevision, SoftwareRevision, Serial, Manufacturer, Model, Alias, AssetID, FieldReplaceable

FROM Cirrus.EntityPhysical

Here's a snippet of the output:

You'll notice there IS a "Name" column, but it only provides the name the manufacturer has provided for the specific item, not the System Name we've put on the switch.

What's the right Query to use to include all this information PLUS the System Name?

The canned Cisco Inventory Report in NPM/NCM shows the System Names, but doesn't include stack information--I don't see how many switches are in stacks with this report. I'd be open to learning what could be done to copy & edit this report to include the stack information as alternative to modifying the SWQL query above.

I've built a custom report that does a pretty good job, but it's no SWQL solution. It looks like this:

Clicking on the Edit Table option:

Would you, instead of running a bunch of manual child conditions, use the Dynamic Query Builder? If so, what options would you use in it?

Yours,

Rick Schroeder

Find more posts tagged with

inventory_report

Accepted answers

mesverrum

Try this,

SELECT n.NodeCaption, ent.LastDiscovery, ent.FirstDiscovery, ent.Missing, ent.Name, ent.Description, ent.Type, ent.ContainedIn, ent.EntityClass, ent.Position, ent.HardwareRevision, ent.FirmwareRevision, ent.SoftwareRevision, ent.Serial, ent.Manufacturer, ent.Model, ent.Alias, ent.AssetID, ent.FieldReplaceable

FROM Cirrus.EntityPhysical ent

JOIN Cirrus.Nodes n on n.NodeID=ent.NodeID

With that join you could potentially also pull in all these values from the cirrus.nodes table as well:

NodeID, CoreNodeID, EngineID, NodeCaption, NodeGroup, AgentIP, AgentIPv6, ManagedProtocol, AgentIPSort, ReverseDNS, Status, StatusText, Community, CommunityReadWrite, SNMPLevel, SysName, SysDescr, SysContact, SysLocation, SystemOID, Vendor, VendorIcon, MachineType, LastBoot, OSImage, OSVersion, ConfigTypes, NodeComments, Username, Password, EnableLevel, EnablePassword, DeviceTemplate, ExecProtocol, CommandProtocol, TransferProtocol, LoginStatus, UseHTTPS, UseUserDeviceCredentials, LastInventory, SNMPContext, SNMPUsername, SNMPAuthType, SNMPAuthPass, SNMPEncryptType, SNMPEncryptPass, AllowIntermediary, LastUpdateTime, LastRediscoveryTime, TelnetPort, SSHPort, SNMPPort, EnableOrionImport, UseKeybInteractiveAuth, ResponseError, ConnectionProfile, EndOfSupport, EndOfSales, EndOfSoftware, EosEntryID, EosType, EosMatchDate, EosVersion, EosLink, EosComments, ReplacementPartNumber, AnotherTestProperty, Department, DeviceFunction, DeviceType, isParent, Loc1_SiteCode, Network, Owner, Sites

But after looking for what you want in my lab you might be better off starting your search from here if you are running npm 12 or newer since they added the stack monitoring capability.

SELECT ID, NodeID, MemberID, Role, RoleDescription, State, StateDescription, MacAddress, Model, SoftwareImage, SerialNumber, HwPriority, SwPriority, SwitchNumber, PowerAvailable, PowerUsed, PostResult

FROM Orion.NPM.SwitchStackMember

Regarding your SWQL versus custom report question, I think this is similar to a "Do I use the CLI or a GUI driven interface to manage my network infra?" GUI is really helpful for helping you find you way around a tool when you aren't exactly sure where you need to go, but once you are comfortable and are getting deep into the advanced settings you are likely to end up wanting the CLI.

I've spent enough time with SWQL that I can almost always build something faster and with better info in SWQL Studio than by using the GUI (especially if the client has a slow web server). I am also not as limited so I can join/correlate data from all through the modules, and perform math operations and such in the code so instead of seeing counts of things where maybe I might prefer percentages and such. Embed links and icons to take me to other pages to really streamline a workflow. It took a lot to get there though, if Solarwinds wasn't my only job then I can admit I would probably not have spent the time to figure out all these tricks.

When I'm training people I generally teach them to use the GUI first, and if they express a need for deeper customization and know enough about SQL to not be afraid I can get into SWQL Studio and show them what's under the hood.

All comments

mesverrum

Try this,

FROM Cirrus.EntityPhysical ent

JOIN Cirrus.Nodes n on n.NodeID=ent.NodeID

With that join you could potentially also pull in all these values from the cirrus.nodes table as well:

But after looking for what you want in my lab you might be better off starting your search from here if you are running npm 12 or newer since they added the stack monitoring capability.

FROM Orion.NPM.SwitchStackMember

rschroeder

That did exactly what I requested. Nicely done! And fast, too!

From your second paragraph, where you referenced I could pull in additional values (e.g.: NodeID, CoreNodeID, EngineID, NodeCaption, etc.), what is the syntax used to add any one of those values, and where/how is it added?

For example, your initial query is:

FROM Cirrus.EntityPhysical ent

JOIN Cirrus.Nodes n on n.NodeID=ent.NodeID

If I wished to add NodeCaption or any of those additional values, where should it be added in the query? After the last line's JOIN statement? Up in the SELECT statement? Somewhere else?

And is it enough to simply paste in "NodeCaption" somewhere in that query, or is there more to it than that? Perhaps it needs something like your last line in the JOIN:

JOIN Cirrus.Nodes n on n.NodeID=ent.NodeID, e.NodeCaption=ent.NodeCaption ?

As you can tell, I'm SQL/SWQL "training-deprived", and the initial baby steps seem to be what many SQL admin experts omit, assuming "everyone knows the very basics."

mesverrum

So the first part, the select, is picking which columns we want to see, so if you want more info just add it to the selection. In cases where there might be two columns with the same name you have to specify which table you want it from. Because i'm lazy about typing I put an alias on the tables to make them shorter, i used ent for the first table and n for cirrus.nodes, so n.vendor would give you a vendor column. SWQL does not let you do a SELECT * to get all the columns the way that SQL would.

On the join line what I'm doing is saying these two tables are related in some way, and then specifying the way they match up. So both tables have a nodeid column that tells us which node they are talking about, so I can join them on that and get all the pieces to line up the way I expect. Most of the data in solarwinds has unique keys to connect tables from one to another, you just need to look around and figure out what they are. A good clue is that most of them have an icon that looks like a key in SWQL studio.

rschroeder

Thank you!