4 Replies Latest reply on Jul 31, 2015 4:28 AM by muralikvp

    Display DHCP-Server in Report

    storniga

      Hi,

       

      I am currently working on creating some custom reports and was wondering how I can create a report containing subnets as well as the dhcp-server where these subnets are configured.

      I have different sites, some subnets are in one site while the corresponding dhcp server is located somewhere else - if someone searches for a subnet and then wants to make some changes at the dhcp server, the person needs to know which dhcp-server he needs to alter.

       

      My reports look e.g. something like this:

      select * from IPAM_Group g

      join IPAM_GroupAttrData d on g.GroupId = d.GroupId

      where g.GroupType='8' and d.Category='Client'

      order by g.Location, d.Site;

       

      Is there maybe already a attribute in this tables that contain the name of the dhcp server, or can i join this information into my query somehow??

        • Re: Display DHCP-Server in Report
          storniga

          PLEASE HELP!

          I know that, when I'm in the network view I can see the dhcp server when I go to the coloum "Scopes in Subnet" and hover over the content of this coloum..

          but I really need to have the DHCP server name displayed in reports as well

           

            • Re: Display DHCP-Server in Report
              muralikvp

              This below query will display the subnet and corresponding DHCP server name

               

              select DHCPServer.FoundAddress as 'DHCP Server Name',g.FriendlyName as 'Subnet Name'  from IPAM_Group g inner join
                [IPAM_DhcpScopeDetails] scope on  (g.GroupId = scope.SubnetId)
                inner join IPAM_DhcpServerDetails DHCPServer on (DHCPServer.NodeId = scope.NodeId)
                 where GroupType = 8
              

               

              This will give you the desired results.

              1 of 1 people found this helpful
                • Re: Display DHCP-Server in Report
                  storniga

                  Thank you very very much!

                  I was wondering how I can have the hostname in addition to the ip address of the DHCP-Server, is that stored in another table again and I have to make another join??

                    • Re: Display DHCP-Server in Report
                      muralikvp

                      Use this below query you will get the DHCP server name as well

                       

                        select n.SysName as 'DHCP server Name' , DHCPServer.FoundAddress as 'DHCP Server IP',g.FriendlyName as 'Subnet Name' 
                        from IPAM_Group g 
                        inner join [IPAM_DhcpScopeDetails] scope on  (g.GroupId = scope.SubnetId)
                        inner join IPAM_DhcpServerDetails DHCPServer on (DHCPServer.NodeId = scope.NodeId)
                        inner join Nodes n on (n.NodeID = DHCPServer.NodeId)      
                        where GroupType = 8
                      
                      1 of 1 people found this helpful