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

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))

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

Find more posts tagged with

vra

Automation

automate

reserved

ipam

allocate

vrealize

Accepted answers

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
}

All comments

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.

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

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.

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
}

nick_scott

Has anyone implemented this in production ? Hows it working ? Seems like a great example of automation via SolarWinds.

tannerr

Hey Nick, I cant speak as to whether or not other people have tried this in their environments or not. However, we have been running it since March without any real issues. Only problem we had was an IPAM update that changed the login/edit url. It was a simple fix and it has been smooth sailing ever since.

Thanks,

Tanner