Login Enterprise Test Configuration Report

During some troubleshooting of some issues, I discovered that one of my tests was not pointing to the correct target server. We have created a Continuous Test that uses that RDP connector for each RDSH server in our farm. The name of the test is the Computer Name of the target server.

So, I decided that I needed to write a quick report that I can check the configuration of my all of my tests without having to click on each test. (we have 1400+ tests in our environment!)

The Approach

I really just wanted to pull the following items for each test:

Test name
Description
Target server (what we call an ASP)
Launcher Group
Account Group
Test Interval
Application Group

As usual, I started by looking at the SQL database to form a query to pull those specific data points. However, since the architecture of the data is not well designed, it would have required a number of joins and filters which would make the query hard to create and harder to maintain. So, even though the query takes 5 time as long, I used the Login Enterprise API to pull this data.

I normally pull this data and write it out as a CSV file, which I view in Excel. But, since I wanted to find discrepancies in the configuration and looking at 1400 rows of data is easy to get lost in, I decided that I wanted to write it out as a native Excel workbook. This way I can use Powershell to highlight the cells with discrepancies.

Powershell

So, the first step was to create a Powershell object that uses the Excel COM object. Once I had that object, I can define some details for the workbook, such as how many worksheets I wanted. In this case, I wanted a separate worksheet for each pod, a total of six. Then I named each worksheet for its pod.

$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.visible=$true
$ExcelObj.SheetsInNewWorkbook = 6
$workbook = $excelObj.Workbooks.Add()

$Pod1wksht = $workbook.Worksheets.Item(1)
$Pod1wksht.Name = "Pod1"
$Pod2wksht = $workbook.Worksheets.Item(2)
$Pod2wksht.Name = "Pod2"
$Pod3wksht = $workbook.Worksheets.Item(3)
$Pod3wksht.Name = "Pod3"
$Pod4wksht = $workbook.Worksheets.Item(4)
$Pod4wksht.Name = "Pod4"
$PodGwksht = $workbook.Worksheets.Item(5)
$PodGwksht.Name = "PodG"
$PodVwksht = $workbook.Worksheets.Item(6)
$PodVwksht.Name = "PodV"

Next, I define the name of the XLSX file to save it as. Since I figured that I might want to compare configurations from diferent points in time, I added timestamp to the file name.

$timestamp = Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }
$ExcelFile = "C:\Users\Cl002309\OneDrive - Rightworks\Documents\Powershell Tools\TestConfigurationsAllPods-$timestamp.xlsx"

As I mentioned, I used the LE API. I pulled the Get-Tests function from the LoginEnterpriseFunctions.ps1 as noted in Powershell Script with Functions to control and extract data from Login Enterprise – Login VSI. I did modify a few pieces of this function.

The first was to update the version of the API being accessed. I modified the URI from Uri = ‘https://’ + $fqdn + ‘/publicApi/v4/tests‘ to Uri = ‘https://’ + $fqdn + ‘/publicApi/v7-preview/tests’. Next, I add the Parameter sections so that I could pass the FQDN and Token for each appliance, as highlighted below in blue.

function Get-Tests {
    Param (
        [parameter(Mandatory=$true, Position=0)] $fqdn,
        [parameter(Mandatory=$true, Position=1)] $token,
        [string]$orderBy = "name",
        [string]$testType = "continuousTest",
        [string]$Direction = "Asc",
        [string]$Count = "10000",
        [string]$Include = "all"
    )

    # this is only required for older version of PowerShell/.NET
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls11

    # WARNING: ignoring SSL/TLS certificate errors is a security risk
    [System.Net.ServicePointManager]::ServerCertificateValidationCallback = [SSLHandler]::GetSSLHandler()

    $Header = @{
        "Accept"        = "application/json"
        "Authorization" = "Bearer $token"
    }

    $Body = @{
        testType  = $testType
        orderBy   = $orderBy
        direction = $direction
        count     = $Count
        include   = $Include 
    } 

    $Parameters = @{
        Uri         = 'https://' + $fqdn + '/publicApi/v7-preview/tests'
        Headers     = $Header
        Method      = 'GET'
        body        = $Body
        ContentType = 'application/json'
    }

    $Response = Invoke-RestMethod @Parameters
    $Response.items 
}

Then, for each pod, I pull the data by calling the function and place it in the $Pod1Tests object. Next, I add the header cells in row 1 of the Pod1 worksheet.

Next, I iterate through the $Pod1Tests array and write out the values for each record in the array to the appropriate cell.

For the Target cell, I use an IF statement to check if the target server ($test.connectionResources.connector.hostList.endpoint) matches the test name. If it does not, I color the cell red and the text white.

And then I set all of the cells with content in them to autosize based in the content.

#================
# Process Pod1
#================

$Pod1Tests = Get-Tests -fqdn <pod1 FQDN> -token <pod1 token>
$Pod1wksht.Cells.Item(1,1) = "Test Name"
$Pod1wksht.Cells.Item(1,2) = "Description"
$Pod1wksht.Cells.Item(1,3) = "ASP"
$Pod1wksht.Cells.Item(1,4) = "Launcher Group"
$Pod1wksht.Cells.Item(1,5) = "Account Group"
$Pod1wksht.Cells.Item(1,6) = "Test Interval"
$Pod1wksht.Cells.Item(1,7) = "Appplication Group"


$Row = 3
 
ForEach ($test in $Pod1Tests) {
    $Pod1wksht.Cells.Item($Row,1) = $test.name
    $Pod1wksht.Cells.Item($Row,2) = $test.description
    $Pod1wksht.Cells.Item($Row,3) = $test.connectionResources.connector.hostList.endpoint
        If ($test.name -ne $test.connectionResources.connector.hostList.endpoint) {
            $Pod1wksht.Cells.Item($Row,3).Interior.ColorIndex = 3
            $Pod1wksht.Cells.Item($Row,3).Font.ColorIndex = 2
            $Pod1wksht.Cells.Item($Row,3).Font.Bold=$True
            }

    $Pod1wksht.Cells.Item($Row,4) = $test.connectionResources.launchergroups.name
    $Pod1wksht.Cells.Item($Row,5) = $test.connectionResources.accountgroups.name 
    $Pod1wksht.Cells.Item($Row,6) = $tests.scheduleIntervalInMinutes
    $Pod1wksht.Cells.Item($Row,7) = $test.workload.steps.applicationgroup.name
    $Row++   
}
#adjusting the column width so all data’s properly visible
$usedRange = $Pod1wksht.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null

I repeat this for each pod and then I save and close the worksheet.

#saving & closing the file
$workbook.SaveAs($ExcelFile)
$excelobj.Quit()

And that’s it. Here is what the results looks like.

The Approach

Powershell

Related Posts

Leave a ReplyCancel reply