4 Replies Latest reply on Nov 25, 2017 11:56 AM by maryan.dmytriv

    IP Address Query

    erwabo

      Im looking to query a next available IP address in a range.

       

      I have gotten this far  Get-SwisData $swis 'SELECT TOP 1 I.IpAddress FROM IPAM.IPNode I WHERE Where IpAddress >30 AND Status=2 AND I.Subnet.DisplayName = ''10.8.63.0/24'''

       

      But I need the first available between a range like between 10.8.63.30 - 10.8.63.230

       

      Havent been able to figure this out. I tried to filter results but all of their methods are string values and Im not sure how to filter those.  Additionally if you change the 'SELECT TOP' value from 1 to say
      5 or 10 or 20, its duplicates the values it returns, which im not sure why it does that.  There must be another 'WHERE' clause I use to filter the results on the initial query.

      Any ideas?

        • Re: IP Address Query
          mesverrum

          You may end up having to spend some time playing with it but the newest versions of swql have substring and replace functions, perhaps you can rig something up like (replace(i.ipaddress,'%.',''))+0 to drop everything but the last octet and then convert it to an int.  I'm not entirely sure the replace function works with wildcards though and if so will it only replace one octet or everything to the left of the final period. Let us know if you get any success with that or some up with a different work around.

          • Re: IP Address Query
            maryan.dmytriv

            Here is a sample how you can achive your goal

             

            $startIp = '20.20.20.14'

            $startIpParts = $startIp.Split('.')

            $startIpHex = [String]::Format("{0:x2}{1:x2}{2:x2}{3:x2}-0000-0000-0000-000000000000", [convert]::ToInt32($startIpParts[3]), [convert]::ToInt32($startIpParts[2]), [convert]::ToInt32($startIpParts[1]), [convert]::ToInt32($startIpParts[0]))

             

            $endIp = '20.20.20.125'

            $endIpParts = $startIp.Split('.')

            $endIpHex = [String]::Format("{0:x2}{1:x2}{2:x2}{3:x2}-0000-0000-0000-000000000000", [convert]::ToInt32($endIpParts[3]), [convert]::ToInt32($endIpParts[2]), [convert]::ToInt32($endIpParts[1]), [convert]::ToInt32($endIpParts[0]))

             

            $query = 'SELECT TOP 1 I.IpAddress FROM IPAM.IPNode I WHERE Status=2 AND I.Subnet.DisplayName = ''Morden'' AND IPAddressN >= ''' + $startIpHex + ''' AND IPAddressN <= ''' + $endIpHex + ''''

             

            Get-SwisData $swis $query

            2 of 2 people found this helpful