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

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

  • 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_ ]

Reply
  • 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_ ]

Children
No Data