Stuck - Orion SDK - Python Get Next 2 IP Address from a list of Subnets in IPAM

[EDIT: added missing group by clauses]

let's break down what i think you want:

a) find ONE subnet with at least two free IP addresses

b) return the first two free IP addresses on that subnet

a) would give a query like this:

SELECT TOP 1 N.subnetid,

             Count(*) AS freeipCount

FROM   ipam.subnet S

       INNER JOIN ipam.ipnode N

               ON N.subnetid = S.subnetid

WHERE  S.comments = 'AWS-Prod_GRE_Tunnels'

       AND N.status = 2

GROUP BY   n.subnetid

HAVING Count(*) >= 2

[if you wanted to you could order by freeipCount to pick the subnet with the most or fewest free IP]

adding this to (b) give you a query like this:

SELECT TOP 2

           ipn.ipaddress,

           ipn.subnetid

FROM       ipam.ipnode IPN

INNER JOIN

           (

                      SELECT TOP 1

                                             n.subnetid,

                                 Count(*) AS freeipcount

                      FROM       ipam.subnet S

                      INNER JOIN ipam.ipnode N

                      ON         n.subnetid =s.subnetid

                      WHERE      s.comments='AWS-Prod_GRE_Tunnels'

                      AND        n.status=2

                      GROUP BY   n.subnetid

                      HAVING     Count(*) >=2) fsn ON ipn.subnetid=fsn.subnetid

WHERE ipn.status=2

Richard,

  Thank you for getting back to me!   Yes, I am trying to get two ip addresses within the same subnet.  The query I was using works fine as long as no one accidently marks one of the two addresses in the subnet as used.    All of the Subnets with the comments 'AWS-Prod_GRE_Tunnels' are /30 subnets, so there will only ever be a max of two ip addresses available and i need the next two ip address in the same subnet that match the comments. 

I tried pasting the code into SQWL studio to test it and it  got unsupported literal type [identifier].  I then tried the same code in my python script and it threw up on the syntax "HAVING" saying that it was expecting EOF.

Below is the portion of python script using the recommended syntax under NOT Working .   Note:  I did test the python script to make sure all the communication works. 

Note:  I did test the python script to make sure all the communication works and that I get data back with the BASE Test below. 

Base Test

def main():

    npm_server = 'XXXXXXXX'

    username = 'XXXXXXXXXXX'

    password = 'XXXXXXXXXX'

    swis = SwisClient(npm_server, username, password)

    query = """

    SELECT TOP 2

          SubnetId,

          IPAddress

    FROM

         IPAM.IPNode

    WHERE Status='2'

         AND

    SubnetId

        IN (SELECT TOP 2 SubnetId FROM IPAM.Subnet WHERE Comments='AWS-Prod_GRE_Tunnels')

  """

NOT Working

def main():

    npm_server = 'XXXXXXXX'

    username = 'XXXXXXXXXXX'

    password = 'XXXXXXXXXX'

    swis = SwisClient(npm_server, username, password)

    query = """

    SELECT TOP 1 N.subnetid,

        Count(*) AS freeipCount

    FROM   ipam.subnet S

       INNER JOIN ipam.ipnode N

               ON N.subnetid = S.subnetid

    WHERE  S.comments = 'AWS-Prod_GRE_Tunnels'

       AND N.status = 2

    HAVING Count(*) >= 2

    """

    results = swis.query(query)

    for row in results['results']:

        print("{SubnetId} [{IPAddress}]".format(**row))

if __name__ == '__main__':

    main()

requests.exceptions.HTTPError: 400 Client Error: mismatched input 'HAVING' expecting 'EOF' for url: htt

oops, missed out a group by in my copy&paste.

SELECT TOP 2
          ipn.ipaddress,
          ipn.subnetid
FROM      ipam.ipnode IPN
INNER JOIN
          (
                      SELECT TOP 1
                                            n.subnetid,
                                Count(*) AS freeipcount
                      FROM      ipam.subnet S
                      INNER JOIN ipam.ipnode N
                      ON        n.subnetid =s.subnetid
                      WHERE      s.comments='AWS-Prod_GRE_Tunnels'
                      AND        n.status=2
                      group by n.subnetid
                      HAVING    Count(*) >=2) fsn ON ipn.subnetid=fsn.subnetid
WHERE ipn.status=2

Richard,

Again, thank you very much for your help and assistance.  Ive got a very tight deadline for Monday that I'm trying hard to meet.  If reference to my last response and error, I did ultimatly figure out last night that the group by was missing; however, I'm still getting the same results in that it returns one ip from one subnet and one from the next.

I'm worried that I am not explaining things correctly.  In IPAM I have approx 200 /30 sunbets.  They are only used for AWS - Amazon Web services VPC to VPC point to point GRE tunnels.  They all have the same comment "AWS-prod_GRE_Tunnels".  I have a python script that completely automates a complete AWS VPC infastructure, from the VPC itself, to the security groups, nacls, subnets and even the Cisco CSR routers.  What I'm trying to do is automate the process of getting and using the two ip addresses from the GRE tunnels.  My original Swql / swis select syntax worked perfectly in that it logged into IPAM and got the first two available ip addresses from one of the top "AWS-prod_GRE_Tunnels" commented subnets. 

My problem became evident when I took the top /30 subnet and changed one of the two ip addresses from "available" to "used".  This normally would not happen, but is a possibility so I need to plan for it.  After making that change and rerunning the script, I assumed that the  script would log into IPAM and get the next available /30 subnet that has the two addresses as available since the first one only has one ip address available.  My assumption was wrong.  Instead, it returns the one ip address that is availble from the top subnet and one ip address from the next subnet.  This obviously won't work for me since the ip addresses need to be on the same /30 subnet.

I added the Group By and tried both versions of the recommendation and it still gives me the same results.

I was able to get what I needed by adding available count = 2.