5 Replies Latest reply on Sep 28, 2017 9:35 AM by tannerr

    SQL queries to automate the reservation of IPs (vRealize Automation)

    tannerr

      Hello everyone,

       

      We are currently working to automate our VM deployment process using vRealize Automation (VRA). We see that we cannot do this using the Orion SDK, or a VRA Plugin, though both are being worked on.

       

      A contractor developed the SQL queries below, that we would like to use to get IPs. I was hoping for a second set of eyes to review these and verify that we are not missing anything.

       

      I have tested everything with select queries in SQL Studio (not update), and all looks well. One question I have is: do we need to manually update the reserved count and percent used columns, or will they get updated automatically?

       

      --Get first 'Available' IP (starting after 10) based on Address and CIDR:

      SELECT Top (1) IPAddress FROM IPAM_Node AS a INNER JOIN IPAM_Group as b ON a.SubnetId=b.GroupId WHERE b.Address = 'xxx.xxx.xxx.xxx' AND b.CIDR = 'xx' AND a.Status=2 AND IPOrdinal > 10

       

      --Update the IP Status to 'Used':

      UPDATE IPAM_Node SET Status=4, StatusBy=9, Comments='Automated allocation from vRealize', LastSync=NULL WHERE IPAddress='xxx.xxx.xxx.xxx'

       

      --Update reserved count after allocation:

      UPDATE IPAM_Group SET ReservedCount = ReservedCount + 1 WHERE Address = 'xxx.xxx.xxx.xxx' AND CIDR = 'xx' AND GroupId IN (SELECT SubnetID From IPAM_Node)

       

      --Update percent used:

      DECLARE @PercentUsed decimal(19,5)

          SET @PercentUsed =

        (SELECT cast(cast(a.used as float) / cast(a.AvailableCount as float) as decimal(19,5))

           FROM (SELECT UsedCount + ReservedCount AS used, AvailableCount

                   FROM IPAM_Group

          WHERE Address = 'xxx.xxx.xxx.xxx' AND CIDR = 'xx' AND GroupId IN (SELECT SubnetID From IPAM_Node))

         a)

        UPDATE IPAM_Group SET PercentUsed=@PercentUsed WHERE Address = 'xxx.xxx.xxx.xxx' AND CIDR = 'xx' AND GroupId IN (SELECT SubnetID From IPAM_Node)

       

      Thanks,

      Tanner

        • Re: SQL queries to automate the reservation of IPs (vRealize Automation)
          mesverrum

          I would say the reason for forcing an update to those percentages is that the default interval is only to scan the subnets once every 4 hours, so if you don't include a forced update then any data on the charts could easily be several hours out of date and inaccurate.  I haven't personally experimented with those changes but the logic makes sense to me as a measure to keep the display reasonably accurate.

            • Re: SQL queries to automate the reservation of IPs (vRealize Automation)
              tannerr

              Thanks, mesverrum, that is helpful to know.

               

              We ran a SQL trace to try and identify all of the tables updated when setting an IP to 'used', but there is quite a lot of information there. From what I can see, it looks like the at least the following tables are hit:

              IPAM_Node

              IPAM_Group

              IPAM_NodeAttrData

              IPAM_Events

              IPAM_IPHistory

               

              And there may be more. Our current query doesn't consider these at all. I don't imagine that it would be recommended to not update everything...

               

              Do you (or anyone else) know all the tables/columns that need updated when an IP is set to 'Used'?

               

              Thanks,

              Tanner

            • Re: SQL queries to automate the reservation of IPs (vRealize Automation)
              tannerr

              I have automated our IPAM IP reservation in VRA with some PowerShell WebRequests and SQL queries.

               

              I got my start with the PowerShell script from this comment. I then found this script, doing what I wanted to do, but in Python.

               

              For anyone interested, I have posted our script below.

               

              First, I use this SQL query to get the SubnetID and IPOrdinal to access to the IPAM IP edit page. We query off of the NetworkAddress and NetworkCIDR (if you cannot use SQL, you can get this info using the Orion SDK and SWIS):

              SELECT Top 1 SubnetId,IPOrdinal,IPAddress FROM IPAM_Node AS a INNER JOIN IPAM_Group as b ON a.SubnetId=b.GroupId WHERE b.Address = '" + networkAddress + "' AND b.CIDR = '" + networkCIDR + "' AND a.Status=2 AND IPOrdinal > 10

               

              We then pass this info to Powershell (values in [brackets] are values you need to supply):

               

              #Make IPAM WebRequst

              $ipamSubnetId  = "[subnet_id]"

              $ipamOrdinal   = "[ip_ordinal]"

              $ipamHostname  = "[hostname]"

              $ipamComment   = "[comment]"

              $ipamUsername  = "[username]"

              $ipamPassword  = "[password]"

              $ipamLoginUrl  = "http://[ipam_server]/Orion/Login.aspx"

              $ipamIPEditUrl = "http://[ipam_server]/Orion/IPAM/ip.edit.aspx?SubnetId=$ipamSubnetId&ipOrdinal=$ipamOrdinal"

              $returnCode = 0

               

               

               

              function ExitWithCode

              {

                  param ($exitcode)

                  $host.SetShouldExit($exitcode)

                  exit

              }

               

               

               

              try

              {

                  $postParams    = @{__EVENTTARGET='ctl00$BodyContent$ctl05';

                                     __EVENTARGUMENT='';

                                     'ctl00$BodyContent$Username'=$ipamUsername;

                                     'ctl00$BodyContent$Password'=$ipamPassword;}

                 

                  #Login to IPAM

                  $ipamLogin = Invoke-WebRequest -Uri $ipamLoginUrl -Method POST -SessionVariable my_session -Body $postParams -MaximumRedirection 5

                 

                  #Get required details to update the 'Available' IP

                  $result = Invoke-WebRequest -Uri $ipamIPEditUrl -WebSession $my_session -MaximumRedirection 5

                  $ipamViewState = $result.AllElements | Where Id -eq "__VIEWSTATE" | Select -First 1 -ExpandProperty value

                  $ipamViewGen   = $result.AllElements | Where Id -eq "__VIEWSTATEGENERATOR" | Select -First 1 -ExpandProperty value

                  #The IPAM POST requires that all values are filled in, instead of supplying the ParrentIP and netmask (typically not editable), I parse them out of a request and add them programmatically. 

                  $ipamParentIP  = $result.AllElements | Where Id -eq 'ctl00_ctl00_ctl00_BodyContent_ContentPlaceHolder1_main_txtParent' | Select -First 1 -ExpandProperty value

                  $ipamNetworkIP = $result.AllElements | Where Id -eq 'ctl00_ctl00_ctl00_BodyContent_ContentPlaceHolder1_main_txtNetworkAddress' | Select -First 1 -ExpandProperty value

                 

                  #Craft POST to update IP.

                  $postParams = @{__EVENTTARGET='ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$btnSave';

                                  __EVENTARGUMENT='';

                                  __VIEWSTATE=$ipamViewState;

                                  __VIEWSTATEGENERATOR=$ipamViewGen;

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtParent'=$ipamParentIP;

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtNetworkAddress'=$ipamNetworkIP;

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$ddlStatus'='Used';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$addDhcpReservation$rblReservationType'='DHCP only';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$addDhcpReservation$txtBootFileName'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$remDhcpReservation$txtBootFileName'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$ddlType'='Static';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtAlias'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtDNS'=$ipamHostname;

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtDhcpClientName'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtIPv6'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$ddlScanning'='1';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtMAC'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtComment'=$ipamComment;

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$DnsRecordsGrid$ddgDnsZonesX'='';

                                  'ddgDnsZonesX'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtLastSync'='Never';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtLastResponse'='No Response';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtLastCredential'='None';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtLeaseExpiration'='N/A';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtMachineType'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtVendor'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtSysName'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtSysDescr'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtSysContact'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$txtSysLocation'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$AttributesRepeater$ctl01$AttributeText'='';

                                  'ctl00$ctl00$ctl00$BodyContent$ContentPlaceHolder1$main$AttributesRepeater$ctl02$AttributeText'='';}

                  #Save IP in IPAM

                  #Comment this out to NOT perform any updates to IPAM.

                  $ipamUpdateIP = Invoke-WebRequest -Uri $ipamIPEditUrl -Method POST -WebSession $my_session -Body $postParams -MaximumRedirection 5

                  ExitWithCode $returnCode

              }

              catch

              {

                  $returnCode = -1

                  ExitWithCode $returnCode

              }