By: Tim Smith | Updated: 2017-09-05 | Comments | Related: > PowerShell
Problem
We've experienced multiple failures within our applications that we've tracked to incorrect configuration values, mostly in our configuration tables. Our applications use a configuration table which we extract from to use as pointers for our applications, such as domains, usernames, custom variables, etc. One problem we've discovered is that these variables within the table can be incorrect. Using PowerShell, can we automate checks against these values as a set so we know ahead of time when values are incorrect, such as validating that a username from a table for an application is correct for that application's environment?
Solution
In this tip, we will use PowerShell's Compare-Object to validate when two variables differ. Since the problem mentions a configuration table, we'll start by creating a simple configuration table and add two values that we'll use our comparison.
CREATE TABLE tbConfigExample(
AppKey VARCHAR(25),
ConfigValue VARCHAR(10),
DateCreated DATETIME DEFAULT GETDATE()
)
INSERT INTO tbConfigExample (AppKey,ConfigValue)
VALUES ('CalendarDeskUser','nReader')
, ('CalendarDeskFile','201701.txt')
SELECT * FROM tbConfigExample
We'll only use these values as a test, as we may not need this type of a setup in some cases. Some applications may create objects on the basis of what is in a configuration table (unless it exists), while other applications or database environments may need a validation step prior to a script being run. Where compare-object may provide us with an advantage over a simple if the statement is a situation where we may be comparing multiple values or where we want to see more details about the comparison between the objects, such as returning the values on one side of the indicator or the other.
Compare-Object allows us to compare two objects and in the case of strings, allows us to specify whether we want to compare the case sensitivity as well. Since we'll be using this function, we'll execute some tests with it using simple strings to show some of the configurations or changes we may want to make if we use it for validating one variable with another variable. The below code shows some examples - uncomment each example and look at the output in PowerShell ISE:
#Compare-Object "One" "One" #Compare-Object "One" "Two" #Compare-Object "One" "one" #Compare-Object "One" "one" -CaseSensitive
We notice that the second and fourth example return values of InputObject and SideIndicator values. Because this can seem confusing at first to new users, I'll add the parameter names in example two so it's easier to understand the output:
Compare-Object -ReferenceObject "One" -DifferenceObject "Two"
We see that value "Two" is on the right (the difference object) and "One" is on the left (the reference object). Since it's more intuitive, we should consider using the left value as the "correct" or "base" value. As we return to our example using configuration tables, the reference object will be the values from the configuration table. Before we return, let's highlight the value incorrect on the basis of the correct or base value (in this case, "Two"):
Compare-Object -ReferenceObject "One" -DifferenceObject "Two" | Where-Object {$_.SideIndicator -eq "=>" } | Select-Object InputObject
Returning to our example where our values are saved in a configuration table, we can save the configuration value and compare it to the value we find. For an example, suppose that our CalendarDeskFile is always located within E:\MobileApps\Calendar\BaseFile\, we can get that value and compare it to what we find in the configuration table:
Function Return-ConfigValue {
Param(
[Parameter(Mandatory=$true)][string]$server
, [Parameter(Mandatory=$true)][string]$database
, [Parameter(Mandatory=$true)][string]$readerquery
)
Process
{
$retarr = @()
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=true;"
$genrmd = New-Object System.Data.SqlClient.SqlCommand
$genrmd.Connection = $scon
$genrmd.CommandText = $readerquery
$genrmd.CommandTimeout = 0
try
{
$scon.Open()
$roleread = $genrmd.ExecuteReader()
$genrdatatable = New-Object System.Data.DataTable
$genrdatatable.Load($roleread)
[string[]]$colarray = @()
foreach ($col in $genrdatatable.Columns)
{
$colarray += $col.ColumnName
}
foreach ($rw in $genrdatatable.Rows)
{
foreach ($col in $colarray)
{
$rw["$col"]
}
}
return $retarr
}
catch [Exception]
{
Write-Warning "Return-ConfigValue (Connection: [$server].[$database])"
Write-Warning $_.Exception.Message
Write-Warning "Query: $readerquery"
}
finally
{
$scon.Dispose()
$genrmd.Dispose()
}
}
}
[string[]]$baseobject = Return-Config -server "OurServer" -database "OurDb" -readerquery "SELECT ConfigValue FROM tbConfigExample ORDER BY ConfigValue"
[string[]]$compobject = @()
$file = Get-ChildItem "E:\MobileApps\Calendar\BaseFile" | Select-Object Name
$compobject += $file.Name
### This example uses a contrived config file for comparison:
[xml]$xmlcontrivedexample = "<cfndata><pointOne>1.7</pointOne><user>nReader</user></cfndata>"
$compobject += $xmlcontrivedexample.cfndata.user
### Example of output with wrong configuration
Compare-Object $validate $comp -CaseSensitive | Where-Object {$_.SideIndicator -eq "=>" }
if (Compare-Object $baseobject $compobject)
{
### Alert/Next-Step after problem is found here
}
Remember that if nothing returns, the objects are the same. One final step that we can add in this example is adjusting the query and adding the name of the configuration value so that when we identify a mismatch, we know what it is:
[string[]]$baseobject = Return-Config -server "OurServer" -database "OurDb" -readerquery "SELECT AppKey + '.' + ConfigValue FROM tbConfigExample ORDER BY ConfigValue" [string[]]$compobject = @() $file = Get-ChildItem "C:\ETLFiles\Import\" | Select-Object Name $compobject += "CalendarDeskFile." + $file.Name ### This example uses a contrived config file for comparison: [xml]$xmlcontrivedexample = "<cfndata><pointOne>1.7</pointOne><user>nReader</user></cfndata>" $compobject += "CalendarDeskUser." + $xmlcontrivedexample.cfndata.user
What will add some overhead per application in using this type of validation technique is organizing the configuration data that need to be validated against a source. In this example, we extracted values on the basis of what would be returned in what order from the database and in an environment in which numerous configuration values would be used may require custom scripts to extract these values.
Next Steps
- If we're in a situation where we only need to validate one value, a simple if statement or compare-object on one item will function as well as a list of items, such as an array.
- The SideIndicator points out which side the difference is - left or right. In the example, we look at the right side of the compare indicator; we may want to look at the left, in which case we would adjust our filter to Where-Object {$_.SideIndicator -eq "<=" }.
- Since configuration values can increase as environments grow - such as values like server names, database names, user names, jobs names, etc., any function, procedure or process that returns all these values (or keeps a list of these values) must also grow as these values grow. For an example, if you add a database to an ETL process, then you will want to also make sure that is included in any function that returns all the necessary values for the ETL process.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-09-05
