Custom Widget for additional IPAM Data in the Node Details View

So I always felt like the out of the box widgets didn't do a great job of correlating IPAM data back to the various views where it could be handy.  My client this week had a bunch of interest in IPAM so I was able to carve out some time to build this for them:

pastedImage_0.png

pastedImage_2.png

pastedImage_1.png

Just add a custom query widget to your node details view and paste the below text in

SELECT --node level

'' as [ ]

, concat(n.nodeid,' ',1) as [_linkfor_ ]

, 'Node' as ObjectType

, n.Caption as Object

, n.DetailsUrl as [_linkfor_Object]

, si.StatusName as Status

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon as [_iconfor_Status]

, n.LastSync as LastPolled

, concat(n.vendor, ' - ',n.MachineType) as ExtraInfo

from orion.nodes n

join orion.StatusInfo si on si.StatusId=n.Status

where n.nodeid=${nodeid}

UNION (-- ipam addresses

SELECT

'' as [ ]

, concat(n.nodeid,' ',2) as [_linkfor_ ]

, concat('IPAM ',ip.IPType,' Address') as ObjectType

, isnull(ip.IPAddress ,'Not in IPAM') as Object

, ip.DetailsUrl as [_linkfor_Object]

, ip.IPStatus as Status

, isnull(('/Orion/IPAM/res/images/sw/icon.ip.'+ ip.IPStatus +'.gif'),'/Orion/images/StatusIcons/Small-Down.gif') as [_iconfor_Status]

, ip.LastSync as LastScanned

, concat(ip.Comments,'') as ExtraInfo

from orion.nodes n

left join IPAM.IPNodeReport ip on n.ip=ip.IPAddress

where n.nodeid=${nodeid}

)

UNION ( --DHCP

SELECT

'' as [ ]

, concat(n.nodeid,' ',3) as [_linkfor_ ]

, 'DHCP' as ObjectType

, isnull(lease.ClientName,'Not in Monitored DHCP') as Object

, ip.DetailsUrl as [_linkfor_Object]

, ip.IPStatus as Status

, isnull(('/Orion/IPAM/res/images/sw/icon.ip.'+ ip.IPStatus +'.gif'),'/Orion/images/StatusIcons/Small-Down.gif') as [_iconfor_Status]

, ip.LastSync as LastScanned

, case when lease.ReservationType is not null then concat('Reservation ',lease.ClientMAC) else '' end as ExtraInfo

from  orion.nodes n

join ipam.IPNodeReport ip on n.ip=ip.IPAddress and n.nodeid=${nodeid}

left join IPAM.DhcpLease lease on lease.ClientIpAddress=ip.IPAddress

where ip.IPType = 'Dynamic'

and n.nodeid=${nodeid}

)

UNION ( --DNS

SELECT distinct

'' as [ ]

, concat(n.nodeid,' ',4) as [_linkfor_ ]

, 'DNS Host Record' as ObjectType

, isnull(dns.data,'Not in Monitored DNS') as Object

, ip.DetailsUrl as [_linkfor_Object]

, case when dns.name is null then '' when dns.name like '%'+n.caption+'%' then 'Matched' else 'Possible DNS Mismatch' end as Status

, case when dns.name is null then '' when dns.name like '%'+n.caption+'%' then '/Orion/images/ActiveAlerts/Check.png' else '/Orion/images/ActiveAlerts/Serious.png' end as [_iconfor_Status]

, ds.LastDiscovery as LastScanned

, case when dns.name is not null then concat('Record ',dns.name, ' in zone ', dz.Name) else '' end as ExtraInfo

FROM orion.nodes n

join ipam.IPNodeReport ip on n.ip=ip.IPAddress and n.nodeid=${nodeid}

left join IPAM.DnsRecordReport dns on dns.Data=ip.IPAddress and dns.type in (1)

left join ipam.DnsZone dz on dz.DnsZoneId=dns.DnsZoneId

left join (select top 1 ds.NodeId, max(ds.LastDiscovery) as LastDiscovery from IPAM.DnsServer ds group by ds.nodeid order by max(ds.LastDiscovery) desc ) ds on ds.NodeId=dz.NodeId

where n.nodeid=${nodeid}

)

UNION ( --subnets

SELECT

'' as [ ]

, concat(n.nodeid,' ',5) as [_linkfor_ ]

, 'IPAM ' + isnull(sub.GroupTypeText,'') + ' Group' as ObjectType

, isnull(sub.FriendlyName,'Not in IPAM') as Object

, sub.DetailsUrl as [_linkfor_Object]

, sub.StatusShortDescription as Status

, '/Orion/IPAM/res/images/sw/icon.subnet.'+ sub.StatusShortDescription +'.gif' as [_iconfor_Status]

, sub.LastDiscovery as LastScanned

, case when sub.friendlyname is null then '' else concat(sub.UsedCount, '/', sub.AllocSize, ' used, VLAN ', isnull(sub.VLAN,'Unknown') , ', Comment ', sub.Comments ) end as ExtraInfo

from orion.nodes n

join IPAM.IPNodeReport ip on n.ip=ip.IPAddress

left join ipam.GroupReport sub on sub.GroupId = ip.SubnetId

where n.nodeid=${nodeid}

)

order by [_linkfor_ ]

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

  • This is great Marc, Any help on how I can add in the IPAM IP custom properties that we have in IPAM? 

  • This is great, but there's a minor flaw.  It seems the navigation for the custom query is broken.

    Asked support about this and here's what they said.  "The SWIS query does not support returning TOATALROWS count when the query unions multiple tables into one. Getting rid of unions in query should solve the problem."

  • With Added IPAM Custom Properities 

    SELECT -- Node Details
         '' AS [ ]
         , CONCAT (
              [Nodes].NodeID
              , ' '
              , 1
              ) AS [_LinkFor_ ]
         , 'Node' AS [Element Type]
         , [Nodes].Caption AS Element
         , [Nodes].DetailsUrl AS [_LinkFor_Element]
         , [Status].StatusName AS Status
         , CONCAT (
              '/Orion/images/StatusIcons/Small-'
              , [Nodes].StatusIcon
              ) AS [_IconFor_Status]
         , ToLocal([Nodes].LastSync) AS [Last Polled]
         , CONCAT (
              [Nodes].Vendor
              , ' - '
              , [Nodes].MachineType
              ) AS [Extra Info]
    FROM Orion.Nodes AS [Nodes]
    JOIN Orion.StatusInfo AS [Status]
         ON [Status].StatusID = [Nodes].STATUS
    WHERE [Nodes].NodeID =   ${NodeID}
    
    UNION
    
    (
         SELECT -- IPAM addresses
              '' AS [ ]
              , CONCAT (
                   [Nodes].NodeID
                   , ' '
                   , 2
                   ) AS [_LinkFor_ ]
              , CONCAT (
                   'IPAM '
                   , [IP].IPType
                   , ' Address'
                   ) AS [Element Type]
              , IsNull([IP].IPAddress, 'Not in IPAM') AS Element
              , [IP].DetailsUrl AS [_LinkFor_Element]
              , [IP].IPStatus AS Status
              , IsNull(('/Orion/IPAM/res/images/sw/icon.ip.' + [IP].IPStatus + '.gif'), '/Orion/images/StatusIcons/Small-Down.gif') AS [_IconFor_Status]
              , [IP].LastSync AS LastScanned
              , CONCAT (
                   ip.Comments
                   , ''
                   ) AS [Extra Info]
         FROM Orion.Nodes AS [Nodes]
         LEFT JOIN IPAM.IPNodeReport AS [IP]
              ON [Nodes].IP = [IP].IPAddress
         WHERE [Nodes].NodeID =   ${NodeID}
         )
    
    UNION
    
    (
         SELECT --DHCP
              '' AS [ ]
              , CONCAT (
                   [Nodes].NodeID
                   , ' '
                   , 3
                   ) AS [_LinkFor_ ]
              , 'DHCP' AS [Element Type]
              , IsNull([Lease].ClientName, 'Not in Monitored DHCP') AS Element
              , [IP].DetailsUrl AS [_LinkFor_Element]
              , [IP].IPStatus AS Status
              , IsNull(('/Orion/IPAM/res/images/sw/icon.ip.' + [IP].IPStatus + '.gif'), '/Orion/images/StatusIcons/Small-Down.gif') AS [_IconFor_Status]
              , [IP].LastSync AS LastScanned
              , CASE 
                   WHEN [Lease].ReservationType IS NOT NULL
                        THEN CONCAT (
                                  'Reservation (MAC: '
                                  , [Lease].ClientMAC
                                  , ')')
                   ELSE ''
                   END AS [Extra Info]
         FROM Orion.Nodes AS [Nodes]
         JOIN IPAM.IPNodeReport AS [IP]
              ON [Nodes].IP = [IP].IPAddress
                   AND [Nodes].NodeID =   ${NodeID}
         LEFT JOIN IPAM.DhcpLease AS [Lease]
              ON [Lease].ClientIpAddress = [IP].IPAddress
         WHERE [IP].IPType = 'Dynamic'
              AND [Nodes].NodeID =   ${NodeID}
         )
    
    UNION
    
    (
         SELECT DISTINCT -- DNS Information
              '' AS [ ]
              , CONCAT (
                   [Nodes].nodeid
                   , ' '
                   , 4
                   ) AS [_LinkFor_ ]
              , 'DNS Host Record' AS [Element Type]
              , IsNull([DNS].data, 'Not in Monitored DNS') AS Element
              , [IP].DetailsUrl AS [_LinkFor_Element]
              , CASE 
                   WHEN [DNS].name IS NULL
                        THEN ''
                   WHEN [DNS].name LIKE '%' + [Nodes].Caption + '%'
                        THEN 'Matched'
                   ELSE 'Possible DNS Mismatch'
                   END AS Status
              , CASE 
                   WHEN [DNS].name IS NULL
                        THEN ''
                   WHEN [DNS].name LIKE '%' + [Nodes].Caption + '%'
                        THEN '/Orion/images/ActiveAlerts/Check.png'
                   ELSE '/Orion/images/ActiveAlerts/Serious.png'
                   END AS [_IconFor_Status]
              , [DS].LastDiscovery AS LastScanned
              , CASE 
                   WHEN [DNS].name IS NOT NULL
                        THEN CONCAT (
                                  'Record '
                                  , [DNS].name
                                  , ' in zone '
                                  , [DZ].Name
                                  )
                   ELSE ''
                   END AS [Extra Info]
         FROM Orion.Nodes AS [Nodes]
         JOIN IPAM.IPNodeReport AS [IP]
              ON [Nodes].IP = [IP].IPAddress
                   AND [Nodes].NodeID =   ${NodeID}
         LEFT JOIN IPAM.DnsRecordReport AS [DNS]
              ON [DNS].Data = [IP].IPAddress
                   AND [DNS].type IN (1) -- DNS A Record
         LEFT JOIN IPAM.DnsZone AS [DZ]
              ON [DZ].DnsZoneId = [DNS].DnsZoneId
         LEFT JOIN (
              SELECT TOP 1 [DS].NodeId
                   , MAX([DS].LastDiscovery) AS LastDiscovery
              FROM IPAM.DnsServer AS [DS]
              GROUP BY [DS].NodeID
              ORDER BY MAX([DS].LastDiscovery) DESC
              ) AS [DS]
              ON [DS].NodeID = [DZ].NodeID
         WHERE [Nodes].NodeID =   ${NodeID}
              AND [DS].LastDiscovery IS NOT NULL
         )
    
    UNION
    
    (
         SELECT -- DHCP/Subnet Information
              '' AS [ ]
              , CONCAT (
                   [Nodes].NodeID
                   , ' '
                   , 5
                   ) AS [_LinkFor_ ]
              , 'IPAM ' + ISNULL([Subnet].GroupTypeText, '') + ' Group' AS [Element Type]
              , IsNull([Subnet].FriendlyName, 'Not in IPAM') AS Element
              , [Subnet].DetailsUrl AS [_LinkFor_Element]
              , [Subnet].StatusShortDescription AS Status
              , '/Orion/IPAM/res/images/sw/icon.subnet.' + [Subnet].StatusShortDescription + '.gif' AS [_IconFor_Status]
              , [Subnet].LastDiscovery AS LastScanned
              , CASE 
                   WHEN [Subnet].FriendlyName IS NULL
                        THEN ''
                   ELSE CONCAT (
                             [Subnet].UsedCount
                             , '/'
                             , [Subnet].AllocSize
                             , ' used | VLAN '
                             , IsNull([Subnet].VLAN, 'Unknown')
                             , ' | Comment: '
                             , [Subnet].Comments
                             )
                   END AS [Extra Info]
         FROM Orion.Nodes AS [Nodes]
         JOIN IPAM.IPNodeReport AS [IP]
              ON [Nodes].ip = [IP].IPAddress
         LEFT JOIN IPAM.GroupReport AS [Subnet]
              ON [Subnet].GroupId = [IP].SubnetID
         WHERE [Nodes].NodeID =   ${NodeID}
         )
    UNION
    (
     SELECT '' AS [ ]
              , CONCAT (
                   [Nodes].NodeID
                   , ' '
                   , 6
                   ) AS [_LinkFor_ ]
              , 'Custom Properties'
                   AS [Element]
              , CONCAT (
                   'IP_Notes '
                   , 'EDIT'
                   ) AS [Element Type]
              , IP.DetailsUrl AS [_LinkFor_Element]
              , [IP].IPStatus AS Status
              , IsNull(('/Orion/IPAM/res/images/sw/icon.ip.' + [IP].IPStatus + '.gif'), '/Orion/images/StatusIcons/Small-Down.gif') AS [_IconFor_Status]
              , ToLocal([IPN].LastSync) AS [Last Polled]
             , [IPN].CustomProperties.IP_Notes as [Extra Info]
             FROM Orion.Nodes AS [Nodes]
         LEFT JOIN IPAM.IPNode AS [IPN]
              ON [Nodes].IP = [IPN].IPAddress
         JOIN IPAM.IPNodeReport AS [IP]
              ON [Nodes].ip = [IP].IPAddress
         WHERE [Nodes].NodeID =  ${NodeID}
    )
    ORDER BY [_LinkFor_ ]