Serial Number Lookup

Remove the line that says

n.PollingIPAddress like '%${SEARCH_STRING}%' or

there is no column in the table with that name, and it would be the same as n.ipaddress anyway

When you are working out your custom queries the best place to do it is the install the SDK, https://github.com/solarwinds/OrionSDK/releases/tag/v2.0.50 , and use SWQL Studio since it will allow you to browse the tables and also gives much more specific error messages when your query breaks.

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems  
•                 Facebook: Loop1 Systems  
•                 Twitter: @Loop1Systems

Hi guys,

Need a bit help here.... I have this script and that is working like a charm.

Custom SWQL Query:

SELECT n.caption as Node, '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node], '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node], h.ServiceTag, n.customproperties.assettag, n.vendor, n.machinetype, h.manufacturer, h.model, n.location, n.contact

FROM Orion.HardwareHealth.HardwareInfo h

left join orion.nodes n on h.nodeid=n.nodeid

Search SWQL Query:

SELECT n.caption as Node, '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node], '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node], ServiceTag, n.customproperties.assettag, n.vendor, n.machinetype, h.manufacturer, h.model, n.location, n.contact

FROM Orion.HardwareHealth.HardwareInfo h

left join orion.nodes n on h.nodeid=n.nodeid

where n.caption like '%${SEARCH_STRING}%' or

h.ServiceTag like '%${SEARCH_STRING}%' or

n.customproperties.assettag like '%${SEARCH_STRING}%' or

n.vendor like '%${SEARCH_STRING}%' or

n.machinetype like '%${SEARCH_STRING}%' or

h.manufacturer like '%${SEARCH_STRING}%' or

n.location like '%${SEARCH_STRING}%' or

n.contact like '%${SEARCH_STRING}%'

;

I have modified it a bit, so look like this now.

Custom SWQL Query:

SELECT n.caption as Node, '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node], '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node], n.IPAddress, h.ServiceTag, n.machinetype, h.manufacturer

FROM Orion.HardwareHealth.HardwareInfo h

left join orion.nodes n on h.nodeid=n.nodeid

Search SWQL Query:

SELECT n.caption as Node, '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node], '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node], n.IPAddress, n.PollingIPAddress, ServiceTag, n.vendor, n.machinetype, h.manufacturer, h.model, n.location

FROM Orion.HardwareHealth.HardwareInfo h

left join orion.nodes n on h.nodeid=n.nodeid

where n.caption like '%${SEARCH_STRING}%' or

n.PollingIPAddress like '%${SEARCH_STRING}%' or

n.IPAddress like '%${SEARCH_STRING}%' or

h.ServiceTag like '%${SEARCH_STRING}%' or

h.model like '%${SEARCH_STRING}%' or

n.vendor like '%${SEARCH_STRING}%' or

n.machinetype like '%${SEARCH_STRING}%' or

h.manufacturer like '%${SEARCH_STRING}%' or

n.location like '%${SEARCH_STRING}%' or

n.contact like '%${SEARCH_STRING}%'

;

But I have a issue where I would like to search for my WLC controller, here I am using the Polling IP, that is under my Node Detail as you can see here.

I have tried put in the PollingIPAddress in my seach function, but I am getting the below error.

so how can fix this?

Hi all

I am a bit confused here, and I am hoping that you smart people in here can help me out.

I can see in my NPM that I ca search for SerialNumber but this is set for text. So I read through your great idea here and I would like to do this, since we would like to search for serial numbers also.

So here is where I am getting a bit stuck.... When I am making my custom properties and I have choosen my nodes and put in assettag what then?

What should I chose here??

And where is it that I can link some of the above scripts to this custom properties?? I am missing the link here I think..

So I am hoping that your smart guys can help me on this matter, maybe step by step...

Thanks for all the greats scripts that you are doing here.

What you did here is basically an inversion of the way I have been handling this, instead of searching all the random places you can find serial numbers I create a custom property called serialnumber and have a sql script that checks all the tables that I've seen in NPM, NCM, and SAM that hold serial numbers. I'll go ahead and share it since I think it may prove useful to others.  Feel free to critique my sql style, it could probably use some more conditional logic but it does the job for me.  One glaring area I've been struggling with has been that I can't seem to find where solarwinds can get the serial number from ASA firewalls, so if you know please pass it along so i can update the script. I usually set this job to run once every night to sync up any new devices.

/****** Script for populating the SerialNumber custom property based on the many different places serial numbers can be indentified in the database  ******/

--sets the database, could be solarwindsorion, netperfmon, or something specific to your environment

use NetPerfMon

update n

set n.SerialNumber = c.chassisSerialNumber

-- for Cisco NCM Nodes NCM_Cisco_Chassis.chassisserialnumber seems to be the preferred value

FROM NCM_Entity_Physical e

join NCM_NodeProperties b on b.NodeID=e.NodeID

join Nodes n on n.NodeID=b.CoreNodeID

join NCM_Cisco_Chassis c on c.NodeID=b.NodeID

where c.chassisserialnumber is not null and c.chassisSerialNumber!=''

update n

set n.SerialNumber = c.chassisid

-- where ChassisSerialNumber is null NCM_Cisco_Chassis.ChassisID often works

FROM NCM_Entity_Physical e

join NCM_NodeProperties b on b.NodeID=e.NodeID

join Nodes n on n.NodeID=b.CoreNodeID

join NCM_Cisco_Chassis c on c.NodeID=b.NodeID

where c.chassisserialnumber is null and (c.chassisID is not null and c.chassisID!='')

update n

set n.SerialNumber = s.HardwareSerialNumber

-- I believe this table requires SAM

FROM AssetInventory_ServerInformation s

join Nodes n on n.NodeID=s.NodeID

where s.HardwareSerialNumber is not null and s.HardwareSerialNumber!=''

update n

set n.serialnumber='N/A - VM'

--VM's don't have a serial number and they are usually migrated away from physical devices when there are problems at the host level so I give them a static placeholder

from nodes n

join VIM_VirtualMachines v ON n.NodeID=v.NodeID

update n

set n.SerialNumber = h.ServiceTag

-- for Nodes with hardware health polling enabled, includes windows and esxi hosts if they have hardware health/asset inventory working

FROM APM_HardwareInfo h

join Nodes n on n.NodeID=h.NodeID

where h.ServiceTag is not null and h.ServiceTag!=''

update n

set n.serialnumber = s.Status

--for any devices that we have set up a UNDP poller that can discover serial numbers we use that, just have to ensure the UniqueName of the poller includes the word Serial

from CustomPollerStatus s

join CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID

join CustomPollers p on p.CustomPollerID=a.CustomPollerID

join nodes n on n.nodeid=a.nodeid

where p.UniqueName like '%serial%' and (s.Status is not null and s.status!='')

On one of the upgrades, yes.  I had manually entered serial numbers and asset tag numbers for inventory reference.  But those disappeared.  I have not gone back and re-added them yet.  I don't know if they would stick or if I would be wasting my time.

I do think it was one of the upgrade that modified or rebuilt the database.  That would explain the custom data being gone.