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

We currently have two reports that 'sort of' work for two different clients, but both have Aruba / HP / HPE kit. The problem comes when the devices are stacked and the report starts to get real messy, real quick.

What we need to do is extract all physical devices and their serial numbers (along with other info but that is a secondary aspect right now) so that one physical device has one associated serial number per line.

For one client, we are using a custom node poller a la:

The SWQL, which is meaningless to me or in SWQL Studio, for this is:

SELECT [data].[DisplayName] AS [DisplayName],[data].[Node].[DisplayName] AS [DisplayName1],[data].[InstanceSiteId] AS [InstanceSiteId]
FROM orion.npm.custompollerassignmentonnode AS data
WHERE
((([data].[CustomPoller].[OID]) = ('1.3.6.1.2.1.47.1.1.1.1.11')) AND (([data].[Node].[CustomProperties].[Client]) = ('XYZ')))
WITH NOLOCK

And when we add relevant columns to the custom table, the end report looks like a bit like this:

What I think we need to do is extract just the 'Module LevelX' part -- or -- the section that has:     but I suspect, if feasible, the easier one would be Module lLevel1.

When I browse the OID (1.3.6.1.2.1.47.1.1.1.1) via the MIB Browser

Other devices might have multiple blue lines between the serials, but what remains consistent in the report output is the Module line and/or the HPE Model lines.

Question: so is there a way to create the report to just show the relevant lines?

Extra: I mentioned above that we need to extract other info, so the report isn't just a bare:

device 1 unit 1 - serial number 1
device 1 unit 2 - serial number 2
etc

I mention this in case someone has a clever way of extracting just the relevant info as above and placing it one line, one serial. We need to add client, location, etc - all mostly custom properties.

For the record: We've been doing this via custom properties for now where the CP has all serial numbers for that device in one line separated by -- 
e.g. Device 1:  CN5BH1P018 -- CN5BH1P01J

but this causes issues with the reporting team as they have to manually edit and add in the additional lines and cut/paste the serial accordingly.

Bottom line is I guess that would be doable via custom SQL/SWQL but it is way beyond my reach.

Parents
  • 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.




  • I need to link in another table but no matter what format I put my FROM xx JOIN line I get a 'no viable alternative' error message.

    Essentially I need to link in the Orion.Nodes table to extract some data from that. As I don't understand the half of what your code is doing, it could be that my extra join is breaking the code in some manner that I just don't understand.

    Can you advise?

    From this section of one of your replies:

    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

Reply



  • I need to link in another table but no matter what format I put my FROM xx JOIN line I get a 'no viable alternative' error message.

    Essentially I need to link in the Orion.Nodes table to extract some data from that. As I don't understand the half of what your code is doing, it could be that my extra join is breaking the code in some manner that I just don't understand.

    Can you advise?

    From this section of one of your replies:

    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

Children
No Data