Help With Query For Universal Device Poller

ekarella

I am building a SWQL query to grab MAC address and SerialNumber from MikroTik devices. We have it mostly working, but I am struggling getting the query to properly pull the SerialNumber from the Universal Device Poller we have set up.

Normally, I would simply grab the ServiceTag from the database, but these devices do not provide that information directly via SNMP monitoring (thus our use of the UnDP).

The UnDP we have setup is working as intended and shows the data we need, but none of the queries I have attempted will show the actual data. The column always produces a NULL value for the devices.

• The UnDP is using the following OID: 1.3.6.1.4.1.14988.1.1.7.3
• The name is mtxrSerialNumber
  
  
• The MIB Value Type is "Raw Value" and the format is "Text"

Here is the most recent query I have attempted:

SELECT
    n.NodeID,
    n.Caption AS NodeName,
    n.IP_Address,
    n.Vendor,
    n.MachineType,
    ncm.SysDescr AS OS_Description,
    n.DetailsUrl,
    i.MAC AS MACAddress,
    mxtr.SerialNumber

FROM Orion.Nodes n

-- Get TOP 1 MAC from Interfaces
LEFT JOIN (
    SELECT NodeID, MAC
    FROM Orion.NPM.Interfaces i1
    WHERE MAC IS NOT NULL
      AND InterfaceID = (
          SELECT TOP 1 InterfaceID
          FROM Orion.NPM.Interfaces i2
          WHERE i2.NodeID = i1.NodeID
            AND i2.MAC IS NOT NULL
          ORDER BY InterfaceID
      )
) i ON i.NodeID = n.NodeID

-- NCM SysDescr
LEFT JOIN NCM.Nodes ncm ON ncm.CoreNodeID = n.NodeID

-- Custom Poller (mxtrSerialNumber) using MAX(RawStatus)
LEFT JOIN (
    SELECT
        ca.NodeID,
        MAX(cs.RawStatus) AS SerialNumber
    FROM Orion.NPM.CustomPollerAssignment ca
    JOIN Orion.NPM.CustomPollers cp2
      ON cp2.CustomPollerID = ca.CustomPollerID
    JOIN Orion.NPM.CustomPollerStatistics cs
      ON cs.CustomPollerAssignmentID = ca.CustomPollerAssignmentID
    WHERE cp2.UniqueName = 'mxtrSerialNumber'
    GROUP BY ca.NodeID
) mxtr ON mxtr.NodeID = n.NodeID

WHERE n.Vendor = 'MikroTik'

Any help is appreciated!

TerryHarding

Try replacing WHERE cp2.UniqueName = 'mxtrSerialNumber' with WHERE cp2.OID = '1.3.6.1.4.1.14988.1.1.7.3' and see what that returns.