• Replies 0 replies
  • Subscribers 23 subscribers
  • Views 321 views
  • Users 0 members are here
Options
Related

SWQL Query to show IPAM Folder Hierarchy and Used IPs

stevenstadel

I had a use case where we wanted to have a report from IPAM showing the Folder (group) hierarchy and only used IPs. The idea was to gather all the static IPS to ensure our security solution was aware of everything. We needed the Group names in the result in order to filter out IPAM Groups we were not interested in. This specific query goes up to 10 parents deep. This is easier to do with SQL since you can use the Alias in the Where statement, but I wanted this in SWQL. 

SELECT DISTINCT
  CASE
    WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.Parent.DisplayName IS NOT NULL THEN 
      (GN.Parent.Parent.DisplayName + ' \ ' + 
      GN.Parent.DisplayName)
    WHEN GN.Parent.DisplayName IS NOT NULL THEN 
      GN.Parent.DisplayName
    ELSE GN.DisplayName
  END AS [Folder],
  GN.Address AS [Subnet], 
  GN.AddressMask AS [NetMask], 
  IPR.IPType, 
  IPR.IPAddress AS [Used_IP_Address] 
FROM 
  IPAM.GroupNode AS GN
JOIN 
  IPAM.IPNodeReport AS IPR ON GN.GroupID = IPR.NodeGroupReport.GroupId
WHERE 
  GN.GroupType = 8
  AND GN.Distance = 0
  AND IPR.IPStatus = 'Used'
  AND (
    CASE
      WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.Parent.DisplayName IS NOT NULL THEN 
        (GN.Parent.Parent.DisplayName + ' \ ' + 
        GN.Parent.DisplayName)
      WHEN GN.Parent.DisplayName IS NOT NULL THEN 
        GN.Parent.DisplayName
      ELSE GN.DisplayName
    END NOT LIKE '%INSERT-FOLDER-SYNTAX-TO-EXCLUDE%'
  )
ORDER BY 
  Folder ASC

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification SolarWinds Lab Link Accounts
About THWACK Blogs Federal & Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2021 SolarWinds Worldwide, LLC. All Rights Reserved.