This script uses a user filled in excel sheet (xlsx), the sheet contains 3 columns, source, destination and service. Since Tufin SecureChange cannot do remove or deny, asking user to choose remove or deny is meaningless.
the below script has tested working, but a lot of exception handling and error handling has not been in place yet…so this is actually more of a demo and not a final product. If the script reads an empty excel sheet then Tufin SecureChange will throw a http 400 bad request kind of thing.
The concept is to break the entire ticket xml template into blocks, the blocks are broken into source blocks, destination blocks, service blocks and finally access request blocks, these four blocks are dynamic, and they need to be treated like lego sets, depending on how many values the blocks will increment with values specified in the excel sheet.
Algorithm
1. Create an excel.application com-object, open the workbook, then the worksheet.
2. Store the name of the worksheets into a variable. The data type store in this variable is actually a group of hash tables.
3. From the first worksheet, check if the second row of the three columns (1st row is column name) is empty, if it is just quit.
4. Collect the first column, then the next until the service column.
5. Use the collections and put them into xml templates, then concatenate them to the access request template.
6. if there is more than one worksheet, all source, destination and service collections will be re-initialize, and the cycle from 4 to 5 repeats itself until there is no more valid worksheet left.
#ignore certificate validation
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
#force to use TLSv1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
function GatherSrcHosts($new_host){
$src_addr = @"
<source type="IP">
<ip_address>$new_host</ip_address>
<netmask>255.255.255.255</netmask>
</source>
"@
return $src_addr + "`r"
}
function GatherDstHosts($new_host) {
$dst_addr = @"
<destination type="IP">
<ip_address>$new_host</ip_address>
<netmask>255.255.255.255</netmask>
</destination>
"@
return $dst_addr + "`r"
}
function GatherServices($service) {
if($service.ToString().ToLower() -like "*tcp*")
{
$proto = "TCP"
$port_num = $service.Replace("tcp","")
}
elseif($service.ToString().ToLower() -like "*udp*") {
$proto = "UDP"
$port_num = $service.Replace("udp","")
}
else {
$proto = "TCP"
$port_num = $service.Replace("tcp","")
}
$svc = @"
<service type="PROTOCOL">
<protocol>$proto</protocol>
<port>$port_num</port>
</service>
"@
return $svc + "`r"
}
function GetAccessRequests($src_collections,$dst_collections,$service)
{
$accessRequest = @"
<access_request>
<users>
<user>Any</user>
</users>
<sources>
$src_collections
</sources>
<destinations>
$dst_collections
</destinations>
<services>
$services
</services>
<action>Accept</action>
<labels/>
</access_request>
"@
return $accessRequest + "`r"
}
$tufinWorkflow = @"
<subject>Test</subject>
<priority>Normal</priority>
<domain_name></domain_name>
<workflow>
<name>Firewall_Automation</name>
<uses_topology>true</uses_topology>
</workflow>
"@
function Obfuscate_Password($username){
if ((Test-Path .\fwr_s.do) -eq $true) {
$password = Get-Content ".\fwr_s.do" | ConvertTo-SecureString
$credential = New-Object System.Management.Automation.PSCredential($username,$password)
} else {
Read-Host $username -AsSecureString | ConvertFrom-SecureString | Out-File -NoClobber .\fwr_s.do
#This is to allow time for the file to be written, otherwise there is a possibility that invoke-restmethod will throw an exception that is really weird.
#Eg. Invoke-RestMethod : You must write ContentLength bytes to the request stream before calling [Begin]GetResponse.
Start-Sleep -Seconds 2
$password = Get-Content ".\fwr_s.do" | ConvertTo-SecureString
$credential = New-Object System.Management.Automation.PSCredential($username,$password)
}
return $credential.GetNetworkCredential().Password
}
#1 is the title, for user to see
$a = 2 #source
$b = 2 #destination
$c = 2 #service
$d = 2 #action
$e = 2 #comment
$src = @() # source ip address collection
$dst = @()
$srcHosts = @() # source hostnames collection
$dstHosts = @()
$services = @() # collection of services
$src_collections = @()
$dst_collections = @()
$accessRequest = @()
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false # To prevent the excel from opening visibly.
$wb = $xl.Workbooks.Open("H:\Tufin\tests\Rulebook.xlsx")
#uncomment to check the sheet in a workbook.
#$wb.Sheets | Select-Object -Property Name
$rule_name = $wb.Worksheets | Select-Object "Name"
foreach($rule in $rule_name)
{
$ws = $wb.Sheets.item($rule.Name)
if($ws.Range("A" + $a).Text -eq "" -or $ws.Range("B" + $b).Text -eq "" -or $ws.Range("C" + $c).Text -eq "")
{
break
}
# can use as a function for this part
while($true)
{
if($ws.Range("A" + $a).Text -ne "") # collect until an empty cell.
{
$srcHosts += $ws.Range("A" + $a).Text
}
else
{
break
}
$a += 1
}
foreach($srcHost in $srcHosts)
{
$src += [System.Net.DNS]::GetHostEntry($srcHost).AddressList.IPAddressToString
}
#consider a function
while($true)
{
if($ws.Range("B" + $b).Text -ne "")
{
$dstHosts += $ws.Range("B" + $b).Text
}
else
{
break
}
$b += 1
}
foreach($dstHost in $dstHosts)
{
$dst += [System.Net.DNS]::GetHostEntry($dstHost).AddressList.IPAddressToString
}
while($true)
{
if($ws.Range("C" + $c).Text -ne "")
{
$services_s += @($ws.Range("C" + $c).Text)
}
else
{
break
}
$c += 1
}
#$ws.Range("D2").Text
if($ws.Range("E2").Text -ne "")
{
$comment = $ws.Range("E2").Text
}
foreach($dst_ip in $dst) {
$dst_collections += GatherDstHosts($dst_ip)
}
foreach($src_ip in $src) {
$src_collections += GatherSrcHosts($src_ip)
}
foreach($service in $services_s)
{
$services += GatherServices($service)
}
#join access request on each worksheet
$accessRequest += @"
<access_request>
<users>
<user>Any</user>
</users>
<sources>
$src_collections
</sources>
<destinations>
$dst_collections
</destinations>
<services>
$services
</services>
<action>Accept</action>
<labels/>
</access_request>
#reinitialize the values, otherwise there will be duplicates.
"@
$a = 2 #source
$b = 2 #destination
$c = 2 #service
$d = 2 #action
$e = 2 #comment
$src = @() # source ip address collection
$dst = @()
$srcHosts = @() # source hostnames collection
$dstHosts = @()
$services_s = @()
$services = @()
$src_collections = @()
$dst_collections = @()
}
#Clean up
$xl.Workbooks.Close()
$xl.Quit()
[void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($ws)
[void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($wb)
[void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($xl)
[GC]::Collect()
$body = @"
<ticket>
$tufinWorkflow
<steps>
<step>
<name>Open request</name>
<tasks>
<task>
<fields>
<field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="multi_access_request">
<name>Firewall Rule</name>
$accessRequest
</field>
</fields>
</task>
</tasks>
</step>
</steps>
<comments/>
</ticket>
"@
$usr = "admin"
$pwd = Obfuscate_Password($usr)
$cred = "${usr}:${pwd}"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($cred)
$base64 = [System.Convert]::ToBase64String($bytes)
$basicAuthValue = "Basic $base64"
$headers = @{ Authorization = $basicAuthValue }
#sleep to defeat the z monster = "You must write ContentLength bytes to the request stream before calling [Begin]GetResponse."
Start-Sleep -Seconds 5
try
{
Invoke-RestMethod -Uri "https://$Tufin_Secure_Change_IP/securechangeworkflow/api/securechange/tickets/" -Method Post -ContentType "application/xml" -Headers $headers -Body $body
}
catch {
$err=$_.Exception
$err.Message
}
cyruslab 