Not a customer?
Start a free trial
Not a customer? Click the 'Start a free trial' link to begin a 30-day SaaS trial of our product and to join our community.
Existing Cisco AppDynamics customers should click the 'Sign In' button to authenticate to access the community
Hi Experts,
I have created a PowerShell Tool to generate Daily Performance SLA Calculation for Individual Business Transactions using AppDynamics REST API. I tried my best to add explanation of each code line.
Hope it is helpful!
##It is PowerShell Code, marked it as Python to keep the indent as is##
#Prerequisites: # 0) Make Sure you have basic understanding of PoswerShell and AppD REST API - It is no rocket science
# 1) PowerShell, MicroSoft Excel (I tested it with Excel 2013)
#Instructions: # 0) This tool automatically takes care of EPOCH time calculations. Calcumations are done in
# 1) Make sure user name is followed by '@customer1' or relevent customer ID
# 2) It uses H:\ as default drive to save output excel, you can change it if required
# 3) This reprot is created to support 4 Business Transactiosn, using same user ID for authantication
# 4) Instructions to modify users is mentioned in script
# 5) If you need to add / remove BTs, please understand this script and do it manually
# 6) #This report works in UTC - Coordinated Universal Tim
# 7) Formula Used: Percentage Successful transactions = ((Sum of Call - Sum of Errors )/ Sum of Calls) *100
#Clear Variable Cache (Without popping up erros on PowerShell Screen) - Start
Remove-Variable * -ErrorAction:Ignore
#Clear Variable Cache - Completed
#Custom Message (Optional) - Start
[System.Windows.MessageBox]::Show(' Welcome to AppDynamics Performance Reporting!!
Program: Your Program Name
#This tool was created with a pre-defined set of Business transactions. You are NOT allowed to choose Business Transactions on the go.
#It will save all results in Excel.
#This report works only in UTC - Coordinated Universal Time.
Author: Sahil Gupta - SahiljGupta@gmail.com')
#Custom Message - Completed
#Check today's date - Start
$TodayDate = $(Get-Date)
#Check today's date - Completed
#Capture Start date (SD) - Date FROM which user needs report - Start
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object Windows.Forms.Form -Property @{
StartPosition = [Windows.Forms.FormStartPosition]::CenterScreen
Size = New-Object Drawing.Size 243, 230
Text = 'Start Date'
Topmost = $true
}
$calendar = New-Object Windows.Forms.MonthCalendar -Property @{
ShowTodayCircle = $false
MaxSelectionCount = 1
MaxDate = $TodayDate
#Max date is Today, so user cannot select future date - Comment
}
$form.Controls.Add($calendar)
$okButton = New-Object Windows.Forms.Button -Property @{
Location = New-Object Drawing.Point 38, 165
Size = New-Object Drawing.Size 75, 23
Text = 'OK'
DialogResult = [Windows.Forms.DialogResult]::OK
}
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
$cancelButton = New-Object Windows.Forms.Button -Property @{
Location = New-Object Drawing.Point 113, 165
Size = New-Object Drawing.Size 75, 23
Text = 'Cancel'
DialogResult = [Windows.Forms.DialogResult]::Cancel
}
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
$result = $form.ShowDialog()
if ($result -eq [Windows.Forms.DialogResult]::OK) {
$date = $calendar.SelectionStart
Write-Host "Date selected: $($date.ToShortDateString())"
}
else {Break}
#Break if user select Cancel - Comment
$sd = $date
#Capture Start date (SD) - Date FROM which user needs report - Completed
#Capture End date (ED) - Date TILL which user needs report - started
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
$form = New-Object Windows.Forms.Form -Property @{
StartPosition = [Windows.Forms.FormStartPosition]::CenterScreen
Size = New-Object Drawing.Size 243, 230
Text = 'End Date'
Topmost = $true
}
$calendar = New-Object Windows.Forms.MonthCalendar -Property @{
ShowTodayCircle = $false
MaxSelectionCount = 1
MinDate = $sd
MaxDate = $TodayDate
#Min Date is equal to start date, So user cannot select end date lesser than start date - Comment
#Max date is Today, so user cannot select future date - Comment
}
$form.Controls.Add($calendar)
$okButton = New-Object Windows.Forms.Button -Property @{
Location = New-Object Drawing.Point 38, 165
Size = New-Object Drawing.Size 75, 23
Text = 'OK'
DialogResult = [Windows.Forms.DialogResult]::OK
}
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
$cancelButton = New-Object Windows.Forms.Button -Property @{
Location = New-Object Drawing.Point 113, 165
Size = New-Object Drawing.Size 75, 23
Text = 'Cancel'
DialogResult = [Windows.Forms.DialogResult]::Cancel
}
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
$result = $form.ShowDialog()
if ($result -eq [Windows.Forms.DialogResult]::OK) {
$date1 = $calendar.SelectionStart
Write-Host "Date selected: $($date1.ToShortDateString())"
}
else {Break}
#Break if user select Cancel - Comment
$ed = $date1
Start-Sleep -s 0
#Capture End date (ED) - Date TILL which user needs report - Completed
#Capture Credential - Started
#Make sure user name is followed by '@customer1'
$Credentials = Get-Credential -Credential $null
$user = $Credentials.UserName
$Credentials.Password | ConvertFrom-SecureString
$password = $Credentials.GetNetworkCredential().password
#Capture Credential - Completed
#Generate excel - file NAME - started
#You can change the name of Excel as required here
$ExcelName = ("AppDynamicsSLAreport_Created_" + $($TodayDate.ToShortDateString()) +"_" + (Get-Date -format HHmm) + "_StartDate_" + $($sd.ToShortDateString()) + "_EndDate_"+ $($ed.ToShortDateString()) + ".xlsx")
#Generate excel - file NAME - completed
#calculate no of days - no of times transaction to be run - diff between dates + 1
#Time Span - No of times this iteration will run is $ts - Start
$ts1 = New-TimeSpan -Start $sd -End $ed
$ts = 1 + $ts1.Days # Check results
#Time Span - No of times this iteration will run is $ts - Completed
Start-Sleep -s 0
#EPOCH Time Calculation - AppD REST APIs works in Epoch Time - Comment
$EpochDate = '1970-01-01'
#Base Epoch Date - Comment
## Extra commands for future expansion
# $ts = New-TimeSpan -Start $EpochDate -End $sd
# $ts.TotalSeconds
#$EndDateEpoch = (New-TimeSpan -Start $EpochDate -End $sd).TotalSeconds
#Remove old TEMP Excel files - started
write-host ("Below listed files will be deleted now from H Drive")
write-host(Get-ChildItem H:\ | Where-Object Name -Like SahilTempExcelAppDReport.xlsx)
Remove-Item h:\SahilTempExcelAppDReport.xlsx -ErrorAction:Ignore
#Remove old Excel files - completed
Start-Sleep -s 0
#EXCEL Editing - Started
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$SGSpace= $workbook.Worksheets.Item(1)
$SGSpace.Name = "SahilAppDReport"
#Check and update the cell number FOR Application Name in excel sheet - started
$SGSpace.Cells.Item(1,1)= "AppDynamics Performance Report"
$SGSpace.Cells.Item(1,1).Interior.ColorIndex = 15
$SGSpace.Cells.Item(1,1).Font.Bold = $True
$SGSpace.Cells.Item(2,1)= "Business Transaction 1"
$SGSpace.Cells.Item(2,1).Interior.ColorIndex = 15
$SGSpace.Cells.Item(2,1).Font.Bold = $True
$SGSpace.Cells.Item(3,1)= "Business Transaction 2"
$SGSpace.Cells.Item(3,1).Interior.ColorIndex = 15
$SGSpace.Cells.Item(3,1).Font.Bold = $True
$SGSpace.Cells.Item(4,1)= "Business Transaction 3"
$SGSpace.Cells.Item(4,1).Interior.ColorIndex = 15
$SGSpace.Cells.Item(4,1).Font.Bold = $True
$SGSpace.Cells.Item(5,1)= "Business Transaction 4"
$SGSpace.Cells.Item(5,1).Interior.ColorIndex = 15
$SGSpace.Cells.Item(5,1).Font.Bold = $True
#Check and update the cell number FOR DATE in excel sheet - Completed
Start-Sleep -s 0
###################################################################
#Enough, calling REST API - FOR "Business Transaction 1" - Started
for (($i =0); $i -lt $ts; $i++)
{
$StartDateEpoch = (New-TimeSpan -Start $EpochDate -End $sd).TotalMilliseconds + (86400000*$i)
# Day wise calculation of start time - Epoch format - comment
$EndDateEpoch = $StartDateEpoch + 86400000
# Day wise calculation of end time - Epoch format - comment
#Call rest API for 'Sum of Calls' - started
#Modify as per your environment - comment
$controller = "controller.domain.com"
$port = "443/8080"
$protocol = "https/http"
#In case you want to use dedicated user, please use below code - started
#Do not forget to modify header as stated below
#$account = "customer1"
#$user = "AppD_username"
#$password = "AppD123"
#In case you want to use dedicated user, please use below code - completed
#Modify as per your environment - comment
$controllerEndpoint = "controller/rest/applications/ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
#In case you want to use dedicated user, modify header as - started
#$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}@${account}:${password}"))}
#In case you want to use dedicated user, modify header as - completed
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Calls' - Completed
#Capture 'Sum of Calls' from API response - started
$response -match "<sum>(?<content>.*)</sum>"
$SumofCalls = $matches['content']
#Capture 'Sum of Calls' from API response - Completed
#if 'Sum of Calls' is 0 or NonInteger, exit script. else powershell will try to divde by 0 - Start
If ($SumofCalls -gt 0) {
#Call rest API for 'Sum of Errors' - started
$controllerEndpoint = "controller/rest/applications/SUM-Calls-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Errors' - Completed
#Capture 'Sum of Errors' from API response - started
$response -match "<sum>(?<content1>.*)</sum>"
$SumofErrors1 = $matches['content1']
#Capture 'Sum of Errors' from API response - Completed
#Avoid 0 or NonInteger SumofErrors - Started
If ($SumofErrors1 -gt 0) {
$SumofErrors = $SumofErrors1}
else {
$SumofErrors = 0
}
#Avoid 0 or NonInteger SumofErrors - Completed
#Calculate percentage successfull transaction - started
$PercentSuccessfulTransactions = [math]::Round(((($SumofCalls - $SumofErrors)*100)/$SumofCalls),2)
#Calculate percentage successfull transaction - Completed
#Check background color of metric in excel - started
#You can specify Accepted/agreed SLA here
#I used 99.5 and above as Green (ColorIndex 50), anything below that will be RED (color Indey 3) - comment
if ($PercentSuccessfulTransactions -lt 99.5) {
$ColorIndex = 3
} else {$ColorIndex = 50}
#Check background color of metric in excel - Completed
} else
#NonZero PercentSuccessfulTransactions, Else statement - started
#If Sum of call returns 0, it will be marked as NA
{
$PercentSuccessfulTransactions = "NA"
$ColorIndex = 16
}
#NonZero PercentSuccessfulTransactions, Else statement - started
Write-Host ("Itration Number" + $i)
Write-Host ("start date of itration $i - " + $StartDateEpoch)
Write-Host ("End date of itration $i - " + $EndDateEpoch)
Write-Host ("Sum of Calls of itration $i - " + $SumofCalls)
Start-Sleep -s 0
Write-Host ("Sum of Errors of itration of itration $i - " + $SumofErrors)
Write-Host ("Percent Successful Transactions $i - " + $PercentSuccessfulTransactions)
Write-Host ("Color Index of itration $i - " + $ColorIndex)
#Check and update the cell number FOR DATE in excel sheet - started
$CellNumberRow1 = $i +2
$SGSpace.Cells.Item(1,$CellNumberRow1)= (($sd.AddDays(($i))).ToShortDateString())
$SGSpace.Cells.Item(1,$CellNumberRow1).Interior.ColorIndex = 15
$SGSpace.Cells.Item(1,$CellNumberRow1).Font.Bold = $True
#Check and update the cell number FOR DATE in excel sheet - Completed
#Check and update the cell number in excel sheet - started
$CellNumber = $i +2
$SGSpace.Cells.Item(2,$CellNumber)= $PercentSuccessfulTransactions
$SGSpace.Cells.Item(2,$CellNumber).Interior.ColorIndex = $ColorIndex
$SGSpace.Cells.Item(2,$CellNumber).Font.Color = 16777215
#Check and update the cell number in excel sheet - Completed
Start-Sleep -s 0
}
#Enough, calling REST API - FOR "Business Transaction 1" - Completed
###################################################################
###################################################################
#Enough, calling REST API - FOR "Business Transaction 2" - Started
for (($i =0); $i -lt $ts; $i++)
{
$StartDateEpoch = (New-TimeSpan -Start $EpochDate -End $sd).TotalMilliseconds + (86400000*$i)
# Day wise calculation of start time - Epoch format - comment
$EndDateEpoch = $StartDateEpoch + 86400000
# Day wise calculation of end time - Epoch format - comment
#Call rest API for 'Sum of Calls' - started
$controller = "controller.domain.com"
$port = "443/8080"
$protocol = "https/http"
$controllerEndpoint = "controller/rest/applications/SUM-Calls-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Calls' - Completed
#Capture 'Sum of Calls' from API response - started
$response -match "<sum>(?<content>.*)</sum>"
$SumofCalls = $matches['content']
#Capture 'Sum of Calls' from API response - Completed
#if 'Sum of Calls' is 0 or NonInteger, exit script. else powershell will try to divde by 0 - Start
If ($SumofCalls -gt 0) {
#Call rest API for 'Sum of Errors' - started
$controllerEndpoint = "controller/rest/applications/SUM-ERROR-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Errors' - Completed
#Capture 'Sum of Errors' from API response - started
$response -match "<sum>(?<content1>.*)</sum>"
$SumofErrors1 = $matches['content1']
#Capture 'Sum of Errors' from API response - Completed
#Avoid 0 or NonInteger SumofErrors - Started
If ($SumofErrors1 -gt 0) {
$SumofErrors = $SumofErrors1}
else {
$SumofErrors = 0
}
#Avoid 0 or NonInteger SumofErrors - Completed
#Calculate percentage successfull transaction - started
$PercentSuccessfulTransactions = [math]::Round(((($SumofCalls - $SumofErrors)*100)/$SumofCalls),2)
#Calculate percentage successfull transaction - Completed
#Check background color of metric in excel - started
if ($PercentSuccessfulTransactions -lt 99.5) {
$ColorIndex = 3
} else {$ColorIndex = 50}
#Check background color of metric in excel - Completed
} else
#NonZero PercentSuccessfulTransactions, Else statement - started
{
$PercentSuccessfulTransactions = "NA"
$ColorIndex = 16
}
#NonZero PercentSuccessfulTransactions, Else statement - started
Write-Host ("Itration Number" + $i)
Write-Host ("start date of itration $i - " + $StartDateEpoch)
Write-Host ("End date of itration $i - " + $EndDateEpoch)
Write-Host ("Sum of Calls of itration $i - " + $SumofCalls)
Start-Sleep -s 0
Write-Host ("Sum of Errors of itration $i - " + $SumofErrors)
Write-Host ("Percent Successful Transactions $i - " + $PercentSuccessfulTransactions)
Write-Host ("Color Index of itration $i - " + $ColorIndex)
#Check and update the cell number FOR DATE in excel sheet - started (One Time activity - Already done for 1st BT)
##$CellNumberRow1 = $i +2
##$SGSpace.Cells.Item(1,$CellNumberRow1)= (($sd.AddDays(($i))).ToShortDateString())
##$SGSpace.Cells.Item(1,$CellNumberRow1).Interior.ColorIndex = 24
##$SGSpace.Cells.Item(1,$CellNumberRow1).Font.Bold = $True
#Check and update the cell number FOR DATE in excel sheet - Completed (One Time activity - Already done for 1st BT)
#Check and update the cell number in excel sheet - started
$CellNumber = $i +2
$SGSpace.Cells.Item(3,$CellNumber)= $PercentSuccessfulTransactions
$SGSpace.Cells.Item(3,$CellNumber).Interior.ColorIndex = $ColorIndex
$SGSpace.Cells.Item(3,$CellNumber).Font.Color = 16777215
#Check and update the cell number in excel sheet - Completed
Start-Sleep -s 0
}
#Enough, calling REST API - FOR "Business Transaction 2" - Completed
###############################################################################
###################################################################
#Enough, calling REST API - FOR "Business Transaction 3" - Started
for (($i =0); $i -lt $ts; $i++)
{
$StartDateEpoch = (New-TimeSpan -Start $EpochDate -End $sd).TotalMilliseconds + (86400000*$i)
# Day wise calculation of start time - Epoch format - comment
$EndDateEpoch = $StartDateEpoch + 86400000
# Day wise calculation of end time - Epoch format - comment
#Call rest API for 'Sum of Calls' - started
$controller = "controller.domain.com"
$port = "443/8080"
$protocol = "https/http"
$controllerEndpoint = "controller/rest/applications/SUM-Calls-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Calls' - Completed
#Capture 'Sum of Calls' from API response - started
$response -match "<sum>(?<content>.*)</sum>"
$SumofCalls = $matches['content']
#Capture 'Sum of Calls' from API response - Completed
#if 'Sum of Calls' is 0 or NonInteger, exit script. else powershell will try to divde by 0 - Start
If ($SumofCalls -gt 0) {
#Call rest API for 'Sum of Errors' - started
$controllerEndpoint = "controller/rest/applications/SUM-ERROR-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Errors' - Completed
#Capture 'Sum of Errors' from API response - started
$response -match "<sum>(?<content1>.*)</sum>"
$SumofErrors1 = $matches['content1']
#Capture 'Sum of Errors' from API response - Completed
#Avoid 0 or NonInteger SumofErrors - Started
If ($SumofErrors1 -gt 0) {
$SumofErrors = $SumofErrors1}
else {
$SumofErrors = 0
}
#Avoid 0 or NonInteger SumofErrors - Completed
#Calculate percentage successfull transaction - started
$PercentSuccessfulTransactions = [math]::Round(((($SumofCalls - $SumofErrors)*100)/$SumofCalls),2)
#Calculate percentage successfull transaction - Completed
#Check background color of metric in excel - started
if ($PercentSuccessfulTransactions -lt 99.5) {
$ColorIndex = 3
} else {$ColorIndex = 50}
#Check background color of metric in excel - Completed
} else
#NonZero PercentSuccessfulTransactions, Else statement - started
{
$PercentSuccessfulTransactions = "NA"
$ColorIndex = 16
}
#NonZero PercentSuccessfulTransactions, Else statement - started
Write-Host ("Itration Number" + $i)
Write-Host ("start date of itration $i - " + $StartDateEpoch)
Write-Host ("End date of itration $i - " + $EndDateEpoch)
Write-Host ("Sum of Calls of itration $i - " + $SumofCalls)
Start-Sleep -s 0
Write-Host ("Sum of Errors of itration $i - " + $SumofErrors)
Write-Host ("Percent Successful Transactions $i - " + $PercentSuccessfulTransactions)
Write-Host ("Color Index of itration $i - " + $ColorIndex)
#Check and update the cell number FOR DATE in excel sheet - started (One Time activity - Already done for 1st BT)
##$CellNumberRow1 = $i +2
##$SGSpace.Cells.Item(1,$CellNumberRow1)= (($sd.AddDays(($i))).ToShortDateString())
##$SGSpace.Cells.Item(1,$CellNumberRow1).Interior.ColorIndex = 24
##$SGSpace.Cells.Item(1,$CellNumberRow1).Font.Bold = $True
#Check and update the cell number FOR DATE in excel sheet - Completed (One Time activity - Already done for 1st BT)
#Check and update the cell number in excel sheet - started
$CellNumber = $i +2
$SGSpace.Cells.Item(4,$CellNumber)= $PercentSuccessfulTransactions
$SGSpace.Cells.Item(4,$CellNumber).Interior.ColorIndex = $ColorIndex
$SGSpace.Cells.Item(4,$CellNumber).Font.Color = 16777215
#Check and update the cell number in excel sheet - Completed
Start-Sleep -s 0
}
#Enough, calling REST API - FOR "Business Transaction 3" - Completed
###############################################################################
###################################################################
#Enough, calling REST API - FOR "Business Transaction 4" - Started
for (($i =0); $i -lt $ts; $i++)
{
$StartDateEpoch = (New-TimeSpan -Start $EpochDate -End $sd).TotalMilliseconds + (86400000*$i)
# Day wise calculation of start time - Epoch format - comment
$EndDateEpoch = $StartDateEpoch + 86400000
# Day wise calculation of end time - Epoch format - comment
#Call rest API for 'Sum of Calls' - started
$controller = "controller.domain.com"
$port = "443/8080"
$protocol = "https/http"
$controllerEndpoint = "controller/rest/applications/SUM-Calls-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Calls' - Completed
#Capture 'Sum of Calls' from API response - started
$response -match "<sum>(?<content>.*)</sum>"
$SumofCalls = $matches['content']
#Capture 'Sum of Calls' from API response - Completed
#if 'Sum of Calls' is 0 or NonInteger, exit script. else powershell will try to divde by 0 - Start
If ($SumofCalls -gt 0) {
#Call rest API for 'Sum of Errors' - started
$controllerEndpoint = "controller/rest/applications/SUM-ERROR-PER-MIN-ENDPOINT/&time-range-type=BETWEEN_TIMES&start-time=$StartDateEpoch&end-time=$EndDateEpoch"
$restURL = "${protocol}://${controller}:${port}/${controllerEndpoint}"
$restURL
$headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${user}:${password}"))}
$response = Invoke-WebRequest -Uri $restURL -Headers $headers
$response.content
#Call rest API for 'Sum of Errors' - Completed
#Capture 'Sum of Errors' from API response - started
$response -match "<sum>(?<content1>.*)</sum>"
$SumofErrors1 = $matches['content1']
#Capture 'Sum of Errors' from API response - Completed
#Avoid 0 or NonInteger SumofErrors - Started
If ($SumofErrors1 -gt 0) {
$SumofErrors = $SumofErrors1}
else {
$SumofErrors = 0
}
#Avoid 0 or NonInteger SumofErrors - Completed
#Calculate percentage successfull transaction - started
$PercentSuccessfulTransactions = [math]::Round(((($SumofCalls - $SumofErrors)*100)/$SumofCalls),2)
#Calculate percentage successfull transaction - Completed
#Check background color of metric in excel - started
if ($PercentSuccessfulTransactions -lt 99.5) {
$ColorIndex = 3
} else {$ColorIndex = 50}
#Check background color of metric in excel - Completed
} else
#NonZero PercentSuccessfulTransactions, Else statement - started
{
$PercentSuccessfulTransactions = "NA"
$ColorIndex = 16
}
#NonZero PercentSuccessfulTransactions, Else statement - started
Write-Host ("Itration Number" + $i)
Write-Host ("start date of itration $i - " + $StartDateEpoch)
Write-Host ("End date of itration $i - " + $EndDateEpoch)
Write-Host ("Sum of Calls of itration $i - " + $SumofCalls)
Start-Sleep -s 0
Write-Host ("Sum of Errors of itration $i - " + $SumofErrors)
Write-Host ("Percent Successful Transactions $i - " + $PercentSuccessfulTransactions)
Write-Host ("Color Index of itration $i - " + $ColorIndex)
#Check and update the cell number FOR DATE in excel sheet - started (One Time activity - Already done for 1st BT)
##$CellNumberRow1 = $i +2
##$SGSpace.Cells.Item(1,$CellNumberRow1)= (($sd.AddDays(($i))).ToShortDateString())
##$SGSpace.Cells.Item(1,$CellNumberRow1).Interior.ColorIndex = 24
##$SGSpace.Cells.Item(1,$CellNumberRow1).Font.Bold = $True
#Check and update the cell number FOR DATE in excel sheet - Completed (One Time activity - Already done for 1st BT)
#Check and update the cell number in excel sheet - started
$CellNumber = $i +2
$SGSpace.Cells.Item(5,$CellNumber)= $PercentSuccessfulTransactions
$SGSpace.Cells.Item(5,$CellNumber).Interior.ColorIndex = $ColorIndex
$SGSpace.Cells.Item(5,$CellNumber).Font.Color = 16777215
$selection = $SGSpace.usedRange
#Check and update the cell number in excel sheet - Completed
Start-Sleep -s 0
}
#Enough, calling REST API - FOR "Business Transaction 4" - Completed
###############################################################################
#Allignment and border - started
$selection = $SGSpace.usedRange
$selection.select()
$selection.HorizontalAlignment = -4108 #center
$selection.Borders.LineStyle = 1
$selection = $SGSpace.range("A2:A5")
$selection.select()
$selection.HorizontalAlignment = -4131 #Left
$SGSpace.application.activewindow.splitcolumn = 1
$SGSpace.application.activewindow.freezepanes = $true
#Allignment and border - completed
# Save updated Excel Sheet - comment
$workbook.SaveAs('h:\SahilTempExcelAppDReport.xlsx')
$excel.Quit()
Start-Sleep -s 1
Rename-Item -path "h:\SahilTempExcelAppDReport.xlsx" -NewName ($ExcelName) -Force
#EXCEL Editing - completed
Start-Sleep -s 1
[System.Windows.MessageBox]::Show(" Your Report is ready!!
File Location: H:\'$ExcelName'
Sahil Gupta - SahiljGupta@gmail.com")
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form