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.

Update Scopes' Name with SQL Query

juniordev over 9 years ago

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?

0 mharvey over 9 years ago

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 juniordev over 9 years ago in reply to mharvey

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)
Cancel
Vote Up 0 Vote Down

Cancel
0 juniordev over 9 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel