Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Need Help with this SWQL Query to consolidate separate Columns into a New Column

The following (very basic) SWQL query provides a Node Inventory, but management would like to add additional info such as node SN, Model No, FW version, etc. The goal is to use NPM as the main source of collecting and providing these node attributes to the local site IT management.

I would like to merge all of the Node Serial Numbers into a new column called 'EquipmentSN' as an example - so for routers, switches, APs, UPS, PDU, SDWAN, etc. all of their serial numbers that are obtained via UnDPs can be collected and consolidated into one column. I'm trying to avoid having a report spreadsheet (currently being generated) that is a mile long with separate columns based on data obtained via the UnDP pollers. I'd appreciate any guidance on this...Thanks in Advance, Will

-- *** Custom Orion Nodes Report - Includes RO/RW Strings ***
SELECT DISTINCT C.SiteID, N.EngineID, N.ObjectSubType AS [Polling Method], N.Status, N.StatusDescription, N.NodeID, C.LTAG, N.IPAddress, N.NodeName, N.DNS, N.Description, N.Vendor, N.MachineType AS [Device Type], N.NodeDescription, HI.ServiceTag AS [Switch/Rtr Serial No], ONPMC.CurrentValue AS [UPS Serial No], ONPMC2.CurrentValue AS [PDU Serial No], ONPMC3.CurrentValue AS [APC SKU No], ONPMC4.CurrentValue AS [Meraki MS250 SN], ONPMC5.CurrentValue AS [Meraki MS250 MDL], ONPMC6.CurrentValue AS [Cisco IE 1000 SN], ONPMC7.CurrentValue AS [Cisco IE 1000 MDL], ONPMC8.CurrentValue AS [Meraki MX64 SN], ONPMC9.CurrentValue AS [Ruckus AP SN], ONPMC10.CurrentValue AS [Ruckus AP MDL], N.Location, C.DeviceLocation, C.BusinessUnit, C.City, C.Owner, C.Department, C.AddressGroup, C.DeviceCategory, C.DeviceRole, C.SerialNumber, C.SLA, C.Alerting, TOLOCAL(OAS.SuppressFrom) AS [AlertsMutedFrom], TOLOCAL(OAS.SuppressUntil) AS [AlertsMutedUntil], N.UnManaged, TOLOCAL(N.UnManageFrom) AS [UnManageFrom], TOLOCAL(N.UnManageUntil) AS [UnmanageUntil], N.AgentPort, N.SNMPVersion, N.Community AS [RO CS-String], N.RWCommunity AS [RW CS-String], OEID.Elements AS [PE Monitored Device Count], C.E911_ERL, E911_LOC_BLDG, C.E911_LOC_FLOOR, c.E911_LOC_SHORT, C.E911_LOC_ZONE,C. E911_LOC_ADDR, C.Comments
FROM Orion.Nodes N
JOIN Orion.NodesCustomProperties C on C.NodeID=N.NodeID
LEFT JOIN Orion.Volumes OV on OV.NodeID=N.NodeID
LEFT JOIN Orion.Engines OEID on OEID.EngineID=N.EngineID
LEFT JOIN Orion.HardwareHealth.HardwareInfo HI on HI.NodeID=N.NodeID
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC on N.NodeID=ONPMC.NodeID AND ONPMC.CustomPollerID = 'e955f524-a816-48f7-9765-ff1be5362c39'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC2 on N.NodeID=ONPMC2.NodeID AND ONPMC2.CustomPollerID = 'acbe9348-ddea-4847-bfa5-ce596ea6f2c7'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC3 on N.NodeID=ONPMC3.NodeID AND ONPMC3.CustomPollerID = 'abddf61e-5b64-437a-821b-e634ab396226'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC4 on N.NodeID=ONPMC4.NodeID AND ONPMC4.CustomPollerID = '489ab128-7ed7-414c-b47b-402cd1dea96d'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC5 on N.NodeID=ONPMC5.NodeID AND ONPMC5.CustomPollerID = '5fb92d74-02bc-4d36-ad64-c949c9d4be04'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC6 on N.NodeID=ONPMC6.NodeID AND ONPMC6.CustomPollerID = 'f77efef1-65bb-46af-a635-695eafc09e9a'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC7 on N.NodeID=ONPMC7.NodeID AND ONPMC7.CustomPollerID = '6f7fc0c8-fd31-4469-9a59-e123e2e3f7b2'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC8 on N.NodeID=ONPMC8.NodeID AND ONPMC8.CustomPollerID = '8f5865e4-d4e8-418a-8ac9-8e7e974fff6f'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC9 on N.NodeID=ONPMC9.NodeID AND ONPMC9.CustomPollerID = '8d5afef5-f608-41a4-af9f-307bc624a569'
LEFT JOIN Orion.NPM.CustomPollerAssignmentOnNode ONPMC10 on N.NodeID=ONPMC10.NodeID AND ONPMC10.CustomPollerID = 'fe4150b0-7159-4865-a7f5-e3913e3a4cb0'
LEFT JOIN Orion.AlertSuppression OAS on OAS.EntityURI=N.URI
ORDER BY C.SiteID ASC, N.IPAddress ASC

Find more posts tagged with

Accepted answers

adam.beedell

The verbs you want are CONCAT() and CASE, looking at that block of script makes my eyes blur though!

All comments

stuartd

Let me state up front that I don't know the answer - far from even close to being proficient in S(W)QL - but I am interested in any response you get.

My solution to date is to put each tech grouping into its own tab e.g.:

At least when the resultant spreadsheet is generated then all Access Points are together. All H3C/HPE nodes are together and so on ... would love to know how (if possible) merging columns can be achieved as well.

xaturbatu

You can use CONCAT(column1,column2) as [newcolumn] in order to concatenate your columns in SWQL, given that most of the columns will be null, but one. Another way is to create only a join instead of multiple custom poller joins, but that requires a bit more insight into the data you have.

You might want to look into SWQL navigation properties to drop a few joins, like the customproperties, engines, they are all already linked to the Nodes table.

zagwill

Hi stuartd,

Thanks for replying, I was trying to get some advise on performing this using SWQL, but so far no response. I use SWQL to generate this Nodes inventory that is imported into Excel for further manipulation by myself and staff members. So far the device SN and MDL numbers is expanding and the end result spreadsheet is become a mile wide. The recipients want tolerate scrolling to the right to get a SN or MDL on a specific device type.

I'm still in search of some SWQL advise on this one.

Thanks for sharing your method.

zagwill

adam.beedell

The verbs you want are CONCAT() and CASE, looking at that block of script makes my eyes blur though!

zagwill

Adam,

Thanks, I'll give these verbs a try. I'm not a SQL/SWQL person, so I've been dragging my knuckles on the ground a lot.

Will

adam.beedell

I've got a message to say there's a new update on this post, but when I try and view it thwack errors, very interesting. Anyone else got the same?

xaturbatu

Probably mine that was finally approved after 11 days