5 Replies Latest reply on Jul 30, 2017 7:12 PM by robert.booth

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

    robert.booth

      I am extremely new to Orion SDK and have much to learn.  I have been really struggling over the last several days with performing a task that I responsible for.  We have a cloud environment that is constantly changing in that we are spinning up and tearing down VPCs.  For each of the deployments there are dual virtual routers that I configure with GRE tunnels for connectivity.   I have everything automated with the exception of the IP Addressing for the GRE tunnels.  I have approximately 200 /30 subnets that I've added to IPAM. 100 for prod & 100 for nonprod.  During my automation script after the routers are launched, I am trying to use Orion SDK to get the next two IP addresses within the same /30 subnet so that the script can use those IP addresses and auto build the GRE tunnel and connectivity. Everything seemed to be working fine with the syntax below, until I decided to test what would happen if someone accidentally put one of the ip addresses in IPAM into a "used" status.  I thought that would just cause the script go to the next /30 Subnet that had two available ip addresses with a comment of "AWS-Prod_GRE_Tunnels"; however, that is not what is occuring.  Instead it returns the other ip address that is available and one address from the next subnet.  How do I make sure I only get two available IP address from the same /30?  I thought using the "SubnetId" would take care of that, but clearly I am missing something.  In the example below, Orion should have returned the two IP address that have a subnetid of 2286 since they are the next two available in the same subnet matching the comments.

       

      Any feedback and or suggestions will be greatly appreciated. 

       

      SELECT TOP 2 IPAddress, SubnetId FROM IPAM.IPNode WHERE Status=2 and SubnetId IN (SELECT TOP 2 SubnetId From IPAM.Subnet as s WHERE Comments='AWS-Prod_GRE_Tunnels')

       

      Example of the SWQL query

       

      Second Example

       

       

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

          [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

            • Re: Stuck - Orion SDK - Python Get Next 2 IP Address from a list of Subnets in IPAM
              robert.booth

              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

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

                  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
                    • Re: Stuck - Orion SDK - Python Get Next 2 IP Address from a list of Subnets in IPAM
                      robert.booth

                      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.

                      • Re: Stuck - Orion SDK - Python Get Next 2 IP Address from a list of Subnets in IPAM
                        robert.booth

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