Need assistance adding serial number and mac address to webhook trigger. Can't seem to find a variable to add or get a custom variable to work.

Trying to get MAC and Serial Number for node/device into webhook payload, but cannot find a working variable, and all attempts to create a custom variable seem to be failing.

Here is one option that I've tried. I've tried many different formatting options, but just can't seem to get the values into the payloads. I've put the variables that I've been trying to get to work in boldface. All the others work without issue

Any assistance would be greatly appreciate. Thank you all.

{
"app_key": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"status": "${N=Alerting;M=Severity}",
"managed": "${N=SwisEntity;M=CustomProperties.Managed}",
"byob": "${N=SwisEntity;M=CustomProperties.BYOB_Circuit}",
"alert": "${N=Alerting;M=AlertMessage}",
"host": "${N=SWQL;M=SELECT TOP 1 RelatedNodeCaption FROM Orion.AlertObjects WHERE AlertObjectID = ${N=Alerting;M=AlertObjectID} }",
"custid": "${N=SwisEntity;M=CustomProperties.CustID}",
"object": "${N=SWQL;M=SELECT TOP 1 EntityCaption FROM Orion.AlertObjects WHERE AlertObjectID = ${N=Alerting;M=AlertObjectID} }",
"object_type": "${N=Alerting;M=ObjectType}",
"NodeID": "${N=SwisEntity;M=NodeID}",


"serialnum": "${SQL:Select TOP 1 ServiceTag FROM APM_HardwareInfo WHERE nodeid='${NodeID}'}",

 "macid": "${SQL: SELECT TOP 1 MAC FROM NodeMACAddresses FROM APM_HardwareInfo WHERE nodeid=${NodeID}}",


"description": "${N=Alerting;M=AlertDescription}",
"solarwinds_url": "${N=Alerting;M=AlertDetailsUrl}",
"acknowledged": "${N=Alerting;M=Acknowledged}",
"acknowledged_by": "${N=Alerting;M=AcknowledgedBy}",
"polling_engine": "${N=SwisEntity;M=Engine.ServerName}",
"ipaddress": "${N=SwisEntity;M=IP_Address}",
"latitude": "${N=SwisEntity;M=CustomProperties.Latitude}",
"longitude": "${N=SwisEntity;M=CustomProperties.Longitude}",
"location": "${N=SwisEntity;M=Location}",
"carrier": "${N=SwisEntity;M=CustomProperties.CarrierName}",
"carriername": "${N=SwisEntity;M=CustomProperties.CarrierName}",
"contactinfoemail": "${N=SwisEntity;M=CustomProperties.ContactInfoEmail}",
"circuitid": "${N=SwisEntity;M=CustomProperties.CircuitID}",
"siteaddress:": "${N=SwisEntity;M=CustomProperties.SiteAddress}",
"circuitype": "${N=SwisEntity;M=CustomProperties.CircuitType}",
"circuitspeed": "${N=SwisEntity;M=CustomProperties.CircuitSpeed}",
"timestamp": "${N=SWQL;M=SELECT GETUTCDATE() as a1 FROM Orion.Engines}",
"solarwinds_object_id": "${N=Alerting;M=AlertObjectID}"
}

  • are these devices windows servers?  I dont think those two values would populate for any other type of nodes.  

  • 95% of what we monitor are switches and routers.  We realize we will only get data from snmp related devices, not icmp, but it seems even with managed snmp devices, we don't return much data from these tables. Hoping to find the proper location for where this data is stored in the database.

  • I alos realized that when I copy and pasted, I paster one wrong.. "macid": "${SQL: SELECT TOP 1 MAC FROM NodeMACAddresses FROM APM_HardwareInfo WHERE nodeid=${NodeID}}", 

    The way that I have it is actually below.... Sorry about that. was a copy/paste error in this thread. LOL

     "macid": "${SQL: SELECT TOP 1 MAC FROM NodeMACAddresses WHERE nodeid=${NodeID}}",

  • Here is the actual code...without the copy/paste error. Only the app key has been removed.................................................................                                                                            {
    "app_key": "xxxxxxxxxxxxxxxxxxxx",
    "status": "${N=Alerting;M=Severity}",
    "managed": "${N=SwisEntity;M=CustomProperties.Managed}",
    "byob": "${N=SwisEntity;M=CustomProperties.BYOB_Circuit}",
    "alert": "${N=Alerting;M=AlertMessage}",
    "host": "${N=SWQL;M=SELECT TOP 1 RelatedNodeCaption FROM Orion.AlertObjects WHERE AlertObjectID = ${N=Alerting;M=AlertObjectID} }",
    "custid": "${N=SwisEntity;M=CustomProperties.CustID}",
    "object": "${N=SWQL;M=SELECT TOP 1 EntityCaption FROM Orion.AlertObjects WHERE AlertObjectID = ${N=Alerting;M=AlertObjectID} }",
    "object_type": "${N=Alerting;M=ObjectType}",
    "NodeID": "${N=SwisEntity;M=NodeID}",
    "mac_id": "${SQL: SELECT TOP 1 MAC FROM NodeMACAddresses WHERE nodeid='${NodeID}'}",
    "serialnum": "${SQL:Select TOP 1 ServiceTag FROM APM_HardwareInfo WHERE nodeid='${NodeID}'}",
    "description": "${N=Alerting;M=AlertDescription}",
    "solarwinds_url": "${N=Alerting;M=AlertDetailsUrl}",
    "acknowledged": "${N=Alerting;M=Acknowledged}",
    "acknowledged_by": "${N=Alerting;M=AcknowledgedBy}",
    "polling_engine": "${N=SwisEntity;M=Engine.ServerName}",
    "ipaddress": "${N=SwisEntity;M=IP_Address}",
    "latitude": "${N=SwisEntity;M=CustomProperties.Latitude}",
    "longitude": "${N=SwisEntity;M=CustomProperties.Longitude}",
    "location": "${N=SwisEntity;M=Location}",
    "carrier": "${N=SwisEntity;M=CustomProperties.CarrierName}",
    "carriername": "${N=SwisEntity;M=CustomProperties.CarrierName}",
    "contactinfoemail": "${N=SwisEntity;M=CustomProperties.ContactInfoEmail}",
    "circuitid": "${N=SwisEntity;M=CustomProperties.CircuitID}",
    "siteaddress:": "${N=SwisEntity;M=CustomProperties.SiteAddress}",
    "circuitype": "${N=SwisEntity;M=CustomProperties.CircuitType}",
    "circuitspeed": "${N=SwisEntity;M=CustomProperties.CircuitSpeed}",
    "timestamp": "${N=SWQL;M=SELECT GETUTCDATE() as a1 FROM Orion.Engines}",
    "solarwinds_object_id": "${N=Alerting;M=AlertObjectID}"
    }

  • So the problem with SNMP devices is they are not very consistent about what OID they expose serial numbers with.  For some vendors it is a dead simple "call this oid and you will get the serial number" and others are "call this tabular oid and some of the results you get will be serial numbers, but some of them wont be and you have to figure out how to tell them apart, good luck"

    So the downstream effect of it being a mess is that Orion doesnt have one place where you can always reliably find a serial number for SNMP devices.  Depending on the environment you can find some of them in some NCM tables, some of them in some SNMP tables, some of them have to be extracted from UNDP's.  In most of my jobs I end up setting up a custom property that I control, and then writing a collection of SQL scripts that would scour all the different tables in the DB where serials might end up and drops them all into my custom property so I could have that single place to check them all.

  • I'm not seeing an obvious syntax error, I guess it's just pulling blanks?

    Can you see the data you want to pull in these tables with/without the where condition in SWQL studio/SSMS

    (not sure you need actual SQL for this one)

  • Yeah..... most of the time there are just blanks. Even for snmp devices. I can;t seem to find a good place to pull that data from

  • The only thing I can see is possibly SQL should be SWQL ? It looks like you are using SWQL in some of the other selects.

  • yeah.... tried both ways.  Seems to be lack of data to pull... now attempting the suggestion  had above. Trying to figure out how to accomplish his suggestion.

  • Along with the something to pull the correct data down, you may want to wrap your query in a case statement or add a UNION such that if it finds no data it returns something valid, that'll stop the macro text from appearing on route to your receiving system