3 Replies Latest reply on Nov 28, 2014 1:55 AM by juniordev

    Update Scopes' Name with SQL Query

    juniordev

      Hi,

      I just want to learn something about scopes. We have 1200 DHCP's and scopes over 2000. We want to update scope name with DHCP server name. For example, you can see below screenshot, we must update to scopes' names (Scope9, Scope8, Scope7 etc.) with 'YOZGAT ADLIYE'. Can we do with any SQL queries? I researched IPAM_Group etc tables but I didn't undertand about relations. Can you help me about this?

       

      Untitled.png

        • Re: Update Scopes' Name with SQL Query
          mharvey

          From what I can tell, there is no way to do that.  I'd imagine if you change the scope names on the DHCP server, it may update the scopes on the next scan.  Otherwise from what I can tell, you'd have to edit each scope individually to change the name.

            • Re: Update Scopes' Name with SQL Query
              juniordev

              OK. So, I have another question. What is the SQL table relationships about the items (DHCP Server, Scope, Nodes etc). I tried to create below query but it is wrong. At least, can you help me about the relationship query? Maybe it is given an idea for possible update query.

               

              SELECT     IPAM_Group_Scopes.Address AS ScopeAddress, IPAM_Group_Scopes.FriendlyName AS Scope_Name, Nodes.IP_Address, Nodes.Caption, Nodes.BirimID,

                                    Nodes.BirimADI, IPAM_DhcpScopeDetails.ScopeId, IPAM_DhcpScopeDetails.GroupId AS DhcpScopeDetail_GroupID, IPAM_DhcpScopeDetails.SubnetId,

                                    IPAM_Group_Scopes.ParentId

              FROM         Nodes INNER JOIN

                                    IPAM_DhcpScopeDetails ON Nodes.NodeID = IPAM_DhcpScopeDetails.NodeId INNER JOIN

                                    IPAM_Group AS IPAM_Group_Scopes ON IPAM_DhcpScopeDetails.ScopeId = IPAM_Group_Scopes.ParentId

              WHERE     (Nodes.NodeID = 964)

            • Re: Update Scopes' Name with SQL Query
              juniordev

              OK, I prepared a sql view. Here it is.

               

              SELECT     Nodes.NodeID, Nodes.IP_Address, Nodes.Caption, IPAM_Subnets.GroupID AS SubnetID, IPAM_Subnets.FriendlyName AS Subnet,

                                    IPAM_Group.GroupID AS ScopeID, IPAM_Group.FriendlyName AS ScopeName,

                                    CASE WHEN ServerType = 1 THEN 'Windows' WHEN ServerType = 2 THEN 'Cisco' WHEN ServerType = 3 THEN 'ASA' ELSE 'Unknown' END AS ServerType,

                                    ROW_NUMBER() OVER (PARTITION BY Nodes.NodeId ORDER BY Nodes.NodeId) AS [RowID]

              FROM         IPAM_DhcpScopeDetails INNER JOIN

                                    IPAM_Group AS IPAM_Subnets ON IPAM_DhcpScopeDetails.SubnetId = IPAM_Subnets.GroupId INNER JOIN

                                    IPAM_Group ON IPAM_DhcpScopeDetails.GroupId = IPAM_Group.GroupId INNER JOIN

                                    Nodes ON IPAM_DhcpScopeDetails.NodeId = Nodes.NodeID INNER JOIN

                                    IPAM_DhcpServerDetails ON IPAM_DhcpScopeDetails.NodeId = IPAM_DhcpServerDetails.NodeId