At Rightworks, we are managing a 1400 server RDS farm, spread across three data centers. Due to some issues with running at this scale, we have divided our Login Enterprise setup into 6 pods.
As you can imagine, running a farm this size requires a fair amount of administration and our Operations team will occasionally ask us to disable a test or two as they perform maintenance on these servers.
Also, Login Enterprise, itself, has a feature that disables accounts if they fail to login after ‘x’ number of times. So, occasionally, we’ll get a “Account capacity exceeded” event.
Well, to help us quickly identify what’s happening in the environment, I have created a Login Enterprise Status board that shows me the disabled tests and accounts across all six pods.
I looked at using Power BI (which we use to provide our management team with the performance statistics (workload timers, login timers and session counts) of our targets) and Splunk, which we use to scrape our events (using the Login Enterprise Event Logger tool). But, as I’m not very fluent in these tools, I decided to use what I do know, which is Powershell and HTML/PHP.
Again, I’ll make the disclaimer that I am not a coder, so this is not very elegant, but it is effective.
Overview
The first step was to pull the status for each test in each pod. I could use the Login Enterprise API to do this, but I find it cumbersome and I don’t want to additional load on the appliances. So instead, I queried the SQL database for each pod. I will typically use the database for pulling statistical data and occassionally for making bulk changes (particularly to test configurations as, again, the API is really difficult to use to modify tests).
Powershell – Disabled Tests
So, I came up with the following SQL statement that will grab the names of all tests that not Enabled.
Select Environments.Environments.Name AS Target, Environments.Environments.[Description], Environments.Schedules.IsEnabled
From Environments.Environments
LEFT JOIN Environments.Schedules
ON Environments.EnvironmentId = Environments.Schedules.EnvironmentId
Where Environments.Schedules.IsEnabled = '0'NOTE: We have a few tests that can only be run one at a time due to limitations with licensing for the application being tested. I have configured the workload such that the script itself with disable/enable each test in turn. So, we expect these tests to be disabled and have indicated it using the description field. This is why I included the Description field in the SQL query.
Next, I created a Powershell script that runs this SQL query against each of the 6 databases. It will output the data to a CSV file in my IIS server’s wwwroot folder. The script also adds a column to indicate which pod each test is running in. It also writes the date & time to the header.
$timestamp = Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }
$report="\\<webserver>\c$\inetpub\wwwroot\DisabledTests.csv"
$BSQLServer = "<sql server1>"
$db1 = "<pod1 DB>"
$db2 = "<pod2 DB>"
$db3 = "<pod3 DB>"
$db4 = "<pod4 DB>"
$VSQLServer = "<sql server2>"
$dbV = "<podv DB>"
$GSQLServer = "<sql server3>"
$dbG = "<podg DB>"
$qcd = "Select Environments.Environments.Name AS Target, Environments.Environments.[Description], Environments.Schedules.IsEnabled
From Environments.Environments
LEFT JOIN Environments.Schedules
ON Environments.EnvironmentId = Environments.Schedules.EnvironmentId
Where Environments.Schedules.IsEnabled = '0'"
While ($true) {
$timedate = (Get-Date).ToString()
$Tests1 = Invoke-Sqlcmd -ServerInstance $BSQLServer -Database $db1 -Query $qcd -Username "<db username>" -Password "<db password>" -Verbose -TrustServerCertificate
$tests1 | Add-Member NoteProperty $timedate -Value "Pod1"
$Tests2 = Invoke-Sqlcmd -ServerInstance $BSQLServer -Database $db2 -Query $qcd -Username "<db username>" -Password "<db password>" -Verbose -TrustServerCertificate
$tests2 | Add-Member NoteProperty $timedate -Value "Pod2"
$Tests3 = Invoke-Sqlcmd -ServerInstance $BSQLServer -Database $db3 -Query $qcd -Username "<db username>" -Password "<db password>" -Verbose -TrustServerCertificate
$tests3 | Add-Member NoteProperty $timedate -Value "Pod3"
$Tests4 = Invoke-Sqlcmd -ServerInstance $BSQLServer -Database $db4 -Query $qcd -Username "<db username>" -Password "<db password>" -Verbose -TrustServerCertificate
$tests4 | Add-Member NoteProperty $timedate -Value "Pod4"
$TestsV = Invoke-Sqlcmd -ServerInstance $VSQLServer -Database $dbV -Query $qcd -Username "<db username>" -Password "<db password>" -Verbose -TrustServerCertificate
$testsV | Add-Member NoteProperty $timedate -Value "PodV"
$TestsG = Invoke-Sqlcmd -ServerInstance $GSQLServer -Database $dbG -Query $qcd -Username "<db username>" -Password "<db password>" -Verbose -TrustServerCertificate
$testsG | Add-Member NoteProperty $timedate -Value "PodG"
$AllTests = $($Tests1; $Tests2; $Tests3; $Tests4; $TestsG; $TestsV)
$AllTests | Export-CSV $report -notypeinformation
Start-Sleep -Seconds 300
}Here is a sample of the CSV file.
The script uses a While loop to pull the disabled tests and update the CSV file every 5 minutes.
Powershell – Disabled Accounts
To get the disabled accounts, I used the method using this SQL query:
Select Management.Accounts.Username
From Management.Accounts
Where [Enabled] = '0'Running the scripts
Currently, I have these scripts running fulltime on one of my management servers. Eventually, I want to find a way to run these as a service so that they will run as long as that server is up.
Displaying the CSV in a Browser page
Now, I wanted to create a web page to display this data. The reason I wanted to use a web page is to make use of the Auto-refresh feature that would show the contents of the current CSV files as they were updated.
I had hoped to do this as a stand-alone HTML file, but most of the solutions I found required server based code. I settled on a PHP solution as written by Daniel BP. Here is a link to the page. PHP – CSV Viewer for Web Browsers | Brookepedia (danbp.org).
I modified the script to pull from the two different CSV files and place them in two side-by-side tables on the web page. I also modified some of the asthetics and formatting to suit my needs, so as removing the navigation buttons and hardcoding the column width for the Row column.
Note the meta tag in the HTML head section that sets the page to auto-refresh.
<meta http-equiv="refresh" content="300">
<title>Login Enterprise Status Board</title>Here is the completed code for the LEStatus.php file.
<?php
/* =============================================================
/ * CSV Viewer
* Version 1.0 (05/07/2017)
*
* This application loads and parses a CSV file in the HTML format for browser viewing.
* Optionally the user can set a password in the configuration and then enter it using a GET request:
* Example: www.mysite.com/csvlogview.php?Password=mypassword
*
* Developed by Daniel BP - daniel@danbp.org
* http://www.danbp.org
* Copyright 2017 - Daniel BP
*
* This software is distributed under the MIT License.
* Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*
*
/* =============================================================*/
//------------------------------------------------
//Configuration
//
$testsfile = "Disabledtests.csv"; //CSV file location
$acctsfile = "DisabledAccts.csv";
$delimiter = ","; //CSV delimiter character: , ; /t
$enclosure = '"'; //CSV enclosure character: " '
$password = ''; //Optional to prevent abuse. If set to [your_password] will require the &Password=[your_password] GET parameter to open the file
$ignorePreHeader = 0; //Number of characters to ignore before the table header. Windows UTF-8 BOM has 3 characters.
//------------------------------------------------
//Variable initialization
$logLines = array();
$tableOutput = "<b>No data loaded</b>";
//Get the Disabled Tests table
//Verify the password (if set)
if( $password === ""){
if(file_exists($testsfile)){ // File exists
// Reads lines of file to array
$fileLines = file($testsfile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
//Not Empty file
if($fileLines !== array()){
//Extract the existing header from the file
$lineHeader = array_shift($fileLines);
$logOriginalHeader = array_map('trim', str_getcsv(substr($lineHeader,$ignorePreHeader), $delimiter, $enclosure));
//Process the file only if the system could find a valid header
if(count($logOriginalHeader) > 0) {
//Open the table tag
$tableOutput="<TABLE style='50%'>";
//Print the table header
$tableOutput.="<TR style='background-color: lightgray;text-align:center;'>";
$tableOutput.="<th style='width:10px'><B>Row</B></hD>";
foreach ($logOriginalHeader as $field)
$tableOutput.="<Th><B>".$field."</B></Th>"; //Add the columns
$tableOutput.="</TR>";
//Get each line of the array and print the table files
$countLines = 0;
foreach ($fileLines as $line) {
if(trim($line) !== ''){ //Remove blank lines
$countLines++;
$arrayFields = array_map('trim', str_getcsv($line, $delimiter, $enclosure)); //Convert line to array
$tableOutput.="<TR><TD style='background-color: lightgray;'>".$countLines."</TD>";
foreach ($arrayFields as $field)
$tableOutput.="<TD>".$field."</TD>"; //Add the columns
$tableOutput.="</TR>";
}
}
//Print the table footer
$tableOutput.="<TR style='background-color: lightgray;text-align:center;'>";
$tableOutput.="<TD><B>Row</B></TD>";
foreach ($logOriginalHeader as $field)
$tableOutput.="<TD><B>".$field."</B></TD>"; //Add the columns
$tableOutput.="</TR>";
//Close the table tag
$tableOutput.="</TABLE>";
}
else $tableOutput = "<b>Invalid data format</b>";
}
else $tableOutput = "<b>Empty file</b>";
}
else $tableOutput = "<b>File not found</b>";
}
else $tableOutput = "<b>Invalid password.</b> Enter the password using this URL format: ".$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']."?Password=<b>your_password</b>";
//Get the Disabled Accounts table
//Verify the password (if set)
if( $password === ""){
if(file_exists($acctsfile)){ // File exists
// Reads lines of file to array
$fileLines = file($acctsfile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
//Not Empty file
if($fileLines !== array()){
//Extract the existing header from the file
$lineHeader = array_shift($fileLines);
$logOriginalHeader = array_map('trim', str_getcsv(substr($lineHeader,$ignorePreHeader), $delimiter, $enclosure));
//Process the file only if the system could find a valid header
if(count($logOriginalHeader) > 0) {
//Open the table tag
$AcctsOutput="<TABLE style='50%'>";
//Print the table header
$AcctsOutput.="<TR style='background-color: lightgray;text-align:center;'>";
$AcctsOutput.="<th style='width:10px'><B>Row</B></Th>";
foreach ($logOriginalHeader as $field)
$AcctsOutput.="<Th><B>".$field."</B></Th>"; //Add the columns
$AcctsOutput.="</TR>";
//Get each line of the array and print the table files
$Linenumber = 0;
foreach ($fileLines as $line) {
if(trim($line) !== ''){ //Remove blank lines
$Linenumber++;
$arrayFields = array_map('trim', str_getcsv($line, $delimiter, $enclosure)); //Convert line to array
$AcctsOutput.="<TR><TD style='background-color: lightgray;'>".$Linenumber."</TD>";
foreach ($arrayFields as $field)
$AcctsOutput.="<TD>".$field."</TD>"; //Add the columns
$AcctsOutput.="</TR>";
}
}
//Print the table footer
$AcctsOutput.="<TR style='background-color: lightgray;text-align:center;'>";
$AcctsOutput.="<TD><B>Row</B></TD>";
foreach ($logOriginalHeader as $field)
$AcctsOutput.="<TD><B>".$field."</B></TD>"; //Add the columns
$AcctsOutput.="</TR>";
//Close the table tag
$AcctsOutput.="</TABLE>";
}
else $AcctsOutput = "<b>Invalid data format</b>";
}
else $AcctsOutput = "<b>Empty file</b>";
}
else $AcctsOutput = "<b>File not found</b>";
}
else $AcctsOutput = "<b>Invalid password.</b> Enter the password using this URL format: ".$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']."?Password=<b>your_password</b>";
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta http-equiv="refresh" content="300">
<title>Login Enterprise Status Board</title>
<style>
* {
box-sizing: border-box;
}
.row {
margin-left:-5px;
margin-right:-5px;
}
.column {
float: left;
width: 50%;
padding: 5px;
}
/* Clearfix (clear floats) */
.row::after {
content: "";
clear: both;
display: table;
}
table {
border-collapse: collapse;
border-spacing: 0;
width: 100%;
border: 1px solid #ddd;
zoom: 80%;
}
th, td {
text-align: left;
padding: 6px;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<h1>Login Enterprise Status Board</h1>
<div class="row">
<div class="column">
<h2>Disabled Tests</h2>
<?=$tableOutput ?>
</div>
<div class="column">
<h2>Disabled Accts</h2>
<?=$AcctsOutput ?>
</div>
</div>
</body>
</html>
And finally, here is an example of the final result.
I hope this is helpful to someone. Let me know