This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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:

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

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

Top Replies

Parents

0 stevenstadel over 2 years ago

This is great Marc, Any help on how I can add in the IPAM IP custom properties that we have in IPAM?
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 stevenstadel over 2 years ago

This is great Marc, Any help on how I can add in the IPAM IP custom properties that we have in IPAM?
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 stevenstadel over 1 year ago in reply to stevenstadel

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