This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

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

Custom Widget for additional IPAM Data in the Node Details View

mesverrum over 4 years ago

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

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
0 itInsultant over 2 years ago

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."
Cancel
Vote Up +1 Vote Down

Cancel

0 stevenstadel over 2 years 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_ ]