Duplicate entries in IPAM.GroupNode, and is there a hidden index?

Hello!

There's nothing wrong with your database, probably just some issues with IPAM and SWQL.

If you are trying to get a hold of the used IP addresses, you should be querying the IPAM.IPNode entity with a WHERE Status=1, something like the following:

SELECT IpNodeId, SubnetId, IPOrdinal, IPAddress, IPAddressN, IPMapped, IPMappedN, Alias, MAC, DnsBackward, DhcpClientName, SysName, Description, Contact, Location, SysObjectID, Vendor, VendorIcon, MachineType, Comments, ResponseTime, LastBoot, LastSync, LastCredential, Status, AllocPolicy, SkipScan, LeaseExpires, DnsBy, MacBy, StatusBy, SystemDataBy, DisplayName, InstanceType, Uri, InstanceSiteId
FROM IPAM.IPNode
WHERE status=1

or, to limit it to GroupID (Subnet with ID) 964:

SELECT IpNodeId, SubnetId, IPOrdinal, IPAddress, IPAddressN, IPMapped, IPMappedN, Alias, MAC, DnsBackward, DhcpClientName, SysName, Description, Contact, Location, SysObjectID, Vendor, VendorIcon, MachineType, Comments, ResponseTime, LastBoot, LastSync, LastCredential, Status, AllocPolicy, SkipScan, LeaseExpires, DnsBy, MacBy, StatusBy, SystemDataBy, DisplayName, InstanceType, Uri, InstanceSiteId
FROM IPAM.IPNode
WHERE SUbnetID=964 AND status=1

Let me know if that helps

Thanks, while that helps, my need a is a bit more complicated. We have subnets listed in several groups. The only place I can identify which subnet is one I need is by querying the IPAM.GroupNode table first, looking at the 'comments' field for the strings I need. I think I was able to get something reliable with this:

[code] Get-SwisData $swis "SELECT DISTINCT GroupId, ParentId, Address, AddressMask, CIDR, FriendlyName, Comments, VLAN FROM IPAM.GroupNode WHERE Comments LIKE '%FIELD%MGMT%' OR Comments LIKE '%GIL%MGMT%'" [/code]

I can then turn around and do my checks on each IP in the subnet comparing with IPNode.

The DISTINCT is giving me result for each one at least. I'm just hoping there are not some hidden or something. So far so good though.

That makes sense. I'm confident there's nothing hidden in those two tables and you should be getting the full result set back.

I believe the problem relies at the URI on the IPAM.GroupNode entity hence the duplicate entries:

As long as you don't select the URI in your SELECT statement and include the DISTINCT you should be on the safe side!

Thanks!

Out of curiosity, what is the index for that table? Is it just not exposed to the SWIS API?