How can I extract one serial number per line. HPE / Aruba

As is fairly typical with me, I spend time typing up my query and then find (mostly) my own solution.

This is not particularly elegant or tidy but it gets to the point.

SELECT CP.Client, N.Caption, N.IPAddress, CP.Client_Site, CPS.Status AS SerialNumber, N.NodeDescription AS Manufacturer, N.Vendor, N.MachineType, N.IOSVersion, 
CP.Maintenance_Provider, CP.Dataroom_Rack, N.Description, N.LastBoot, N.Location, N.Contact, CP.Circuit_Reference_1, CP.Circuit_Reference_2

FROM Orion.NPM.CustomPollerStatusOnNode AS CPS
JOIN Orion.Nodes N ON CPS.NodeID = N.NodeID
JOIN Orion.NodesCustomProperties CP ON CPS.NodeID = CP.NodeID

-- edit xxxxx in next line to reflect client
WHERE CP.Client LIKE 'xxxxxxx%'

-- eliminate non-relevant vendors
AND N.Description NOT LIKE '%AP 105%' AND N.Caption NOT LIKE '%CW-RTR%' AND N.Vendor NOT LIKE '%Brocade%' AND N.Vendor NOT LIKE '%Cisco%' AND N.Vendor NOT LIKE '%Deliberant%'
AND N.Vendor NOT LIKE '%Fortinet%' AND N.Vendor NOT LIKE '%Check%' AND N.Vendor NOT LIKE '%Edge%' AND N.Vendor NOT LIKE '%Frog%' AND N.Vendor NOT LIKE '%windows%'
AND N.Vendor NOT LIKE '%unknown%' AND N.Vendor NOT LIKE '%net-snmp%' AND N.Vendor NOT LIKE '%Ligo%' AND N.Vendor NOT LIKE '%Trapeze%' AND N.Vendor NOT LIKE '%Nokia%'
AND N.Vendor NOT LIKE '%Aruba%' AND N.Vendor NOT LIKE '%Ubiq%' AND N.Vendor NOT LIKE '%Tranz%' AND N.Vendor NOT LIKE '%Siklu%' AND N.Vendor NOT LIKE '%Juniper%'

I guess my only query now is can one add multiple reports together so as to include all their other kit that isn't this specific.

Maybe I have too many filters, but this is one of my SWQL reports I use to get basically all the Serial Numbers from the Aruba switches including modules, PS, transceivers, etc.

Can you try it?

P.S. On line #2 remove the ' symbol. It should be CASE. But it wasn't allowing me to do it here on the forum. I don't know why.

SELECT AgentIP AS [IP Address], NodeCaption AS [Device Name], EntityDescription AS Description, EntityName AS Module, Serial AS [Serial Number], Model, 
'CASE 
     WHEN EntityDescription like '%J9%' THEN '1'
     WHEN EntityDescription like '%JL07%' THEN '1' 
     WHEN EntPhysicalIndex = '26001' THEN '1' 
     WHEN EntPhysicalIndex like '1%' AND EntityName = 'Chassis' THEN '1' 
     WHEN EntPhysicalIndex like '2%' AND EntityName = 'Chassis' THEN '2'
     WHEN EntPhysicalIndex like '3%' AND EntityName = 'Chassis' THEN '3'
     WHEN EntPhysicalIndex like '4%' AND EntityName = 'Chassis' THEN '4'
     WHEN EntPhysicalIndex like '5%' AND EntityName = 'Chassis' THEN '5'
     WHEN EntPhysicalIndex like '6%' AND EntityName = 'Chassis' THEN '6'
     WHEN EntPhysicalIndex like '7%' AND EntityName = 'Chassis' THEN '7'
     WHEN EntPhysicalIndex like '8%' AND EntityName = 'Chassis' THEN '8'
     WHEN ContainedIn like '%1' AND EntityName like 'Port%' THEN '1' 
     WHEN ContainedIn like '%2' AND EntityName like 'Port%' THEN '2'
     WHEN ContainedIn like '%3' AND EntityName like 'Port%' THEN '3'
     WHEN ContainedIn like '%4' AND EntityName like 'Port%' THEN '4'
     WHEN ContainedIn like '%5' AND EntityName like 'Port%' THEN '5'
     WHEN ContainedIn like '%6' AND EntityName like 'Port%' THEN '6'
     WHEN ContainedIn like '%7' AND EntityName like 'Port%' THEN '7'
     WHEN ContainedIn like '%8' AND EntityName like 'Port%' THEN '8'
     WHEN ContainedIn like '1%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '1' 
     WHEN ContainedIn like '2%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '2'
     WHEN ContainedIn like '3%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '3'
     WHEN ContainedIn like '4%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '4'
     WHEN ContainedIn like '5%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '5'
     WHEN ContainedIn like '6%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '6'
     WHEN ContainedIn like '7%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '7'
     WHEN ContainedIn like '8%' AND EntityName not like 'Port%' AND EntityName <> 'Chassis' THEN '8'   
     ELSE '1'
END AS Member
FROM NCM.Nodes a INNER Join NCM.EntityPhysical b ON a.NodeID = b.NodeID
Where (EntityDescription like 'Aruba%' OR EntityDescription like '%J9%') AND Serial <> ' ' AND (EntityDescription <> 'Aruba' AND EntityName <> 'Switch Management Module') 
Order by AgentIP, Member, EntityDescription, EntityName 

That runs, and I think I can work my way through the SWQL logic (it's way in advance of my coding capabilities) but it seems to only pulls back serials from items where I've already created a UnDP for.

I say this because we have some very clear division lines in that we have 3 major clients with Aruba kit, but I'm only seeing the serials for one of those clients whom have the Aruba/H3C/HPEa kit, but not the other two.So it appears, to me at least, that SolarWinds doesn't natively extract serials for them. Other than this script, have you created any pollers or UnDPs for Aruba/H3C/HPE kit?

p.s.- also don't know why my SWQL query doesn't appear in my prior reply (to myself) so will attempt to edit and fix that.

OK so further on now ...

I replaced your WHERE line with:

 -- WHERE Serial <> ' ' AND EntityName <> ' ' AND Manufacturer = 'HPE' OR Manufacturer = 'Aruba' OR Manufacturer = 'H3C'

And that on the surface seems to give me more, but by no means all, so I come back to have you implemented any pollers or UnDPs that collect these items and if so can you share them please?

No, I'm not using any special poller or UnDP. All the data is extracted from the database. 
Maybe there is a easier way using poller but that is the way I did to get our Aruba's Serial Numbers.

What is your final goal? Maybe I can help you if you are more specific in what Serial Numbers do you need from the stacks. I also have some H3C and 3Coms I can play with.

End goal is we need to produce an inventory listing report on a scheduled basis - one of the primary requirements are the device serial numbers.

Unlike Cisco, I don't seem t be able to extract these natively from the DB for all 3 brands of switch (H3C, HPE and Aruba) - we also need to be able to extract all individual chassis serials from a stack, on a one serial number per line basis. 

Your code, with a one extra AND statement gets me this for Aruba, but I'm still no closer to doing this for HPE and H3C kit. So any insight you might have there would be great, thanks.

I just found out that if the device is using NCM or not could affect the results. So, my code only work for devices on the NCMs, if the device do not participate on NCM it store the data in another table.

I took your very first code and add/modify some lines to make it work in my environment. So far, I can see HP/H3C and Aruba serials numbers. If the device is using UnDP, I also try to get the value from the CPS.status.

Try it out when you have time:

SELECT CP.Client, N.Caption, N.IPAddress, CP.Client_Site, NCME.EntityName AS Module, NCME.Serial AS [Serial Number], CPS.Status AS [Serials using UnDP], N.NodeDescription AS Manufacturer, N.Vendor, N.MachineType, N.IOSVersion, CP.Maintenance_Provider, CP.Dataroom_Rack, N.Description, N.LastBoot, N.Location, N.Contact, CP.Circuit_Reference_1, CP.Circuit_Reference_2

FROM Orion.NPM.CustomPollerStatusOnNode AS CPS
FULL JOIN Orion.Nodes N ON CPS.NodeID = N.NodeID
FULL JOIN Orion.NodesCustomProperties CP ON N.NodeID = CP.NodeID
FULL JOIN NCM.Nodes NCM ON NCM.CoreNodeID = N.NodeID
FULL JOIN NCM.EntityPhysical NCME ON NCME.NodeID = NCM.NodeID

--Edit the CP client
WHERE (CP.Client LIKE 'xxxxxxx%')

--Edit depending on your side. Try changing the word Chassis or remove the whole line to see the full table, then come back and edit it to match the value containing the Serial Number you want  
AND (NCME.EntityName like 'Board%' OR NCME.EntityName like '%Chassis%' OR NCME.EntityName IS NULL) 

--Filtering Manufacturers 
AND (Manufacturer like '%HP%' OR Manufacturer like '%Aruba%' OR Manufacturer like '%H3C%' OR Manufacturer like '%3Com%' )

--Filtering results. If Serial is Empty, dont show me the line.
AND (NCME.Serial <> ' ' OR CPS.Status <> ' ')

--Sort by
Order by AgentIP, EntityDescription, EntityName 

Ahhh - then that would explain why a lot of the devices don't yet appear.

A whole host of the HPE's aren't backed up yet due to an encryption clash and a change needs to be raised to get them sorted. The others haven't yet been added as it is a new environment for us... but where we do have them in NCM that script works just fine. So thanks for your time and knowledge - it is much appreciated.

No problem. I'm learning too.

I think if the device is not in NCM, you should still see the serials gathered by the UnDP (CPS.status values).
That is why I sent you another code for you to try it out. Because it can look into NCM or devices using UnDP.
At least when I tested here on my side.

Best Regards.