Handling errors in Powershell (terminating and non-terminating) and capturing errors into a variable without a red letter message being output

 

If you've ever written a script for someone else and wanted to hide (but still capture) the normal red letter output that happens from time to time, you've probably experienced confusion with how Powershell handles terminating and non-terminating errors. The example code below is non-terminating error examples and it is difficult to hide the red letter output but still catch the error to do some error handling within the script. Using " -erroraction silentlycontinue -errorvariable myErr" does not work as would be expected. 

With terminating errors, a standard try catch will suffice and you probably wouldn't want to suppress a terminating error or continue running the script if one occurs. Non-terminating errors are not detrimental to a script running and can continue through if you choose to.

Why you might ask? Sometimes the red letter output will confuse the end user and a simple "Write-Host" will suffice telling them a file doesn't exist, a process doesn't exist, a user doesn't exist or a user doesn't have a photo. Capturing the output will allow the script writer to instead tell the user that "The file doesn't exist, check the name again" in a yellow informational warning output. This way you can also customize what is output to any logging informational file. The script also doesn't appear broken, however if you need to instruct the user on what to do, you can do that with your own output. Below are examples of a standard cmdlet, an AD cmdlet, an Exchange cmdlet and an o365 cmdlet (notice I add a prefix of "eo" for the o365 cmdlet). You can also dot source the variable with different properties this way.

You can also use " -erroraction silentlycontinue" to hide the red letter response, although most people that read this probably already know that. Using -erroraction and using " -errorvariable myErr" does not work, it suppresses the error output and does not store anything in the variable. Take note that the error variable, when defined in the command, does not have a dollar sign before the text.

Here is a description of terminating vs non-terminating errors for your reading.

https://www.tutorialspoint.com/what-is-terminating-and-non-terminating-errors-in-powershell

Individual command examples

 
$suppressedError = $null; try {Get-ChildItem c:\temp\blahblah.txt -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError += $error[0];} Write-Host ""; Write-Host ""; Write-Host "Error: " $suppressedError;

$suppressedError = $null; try {Get-Process 123456789 -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError = $error[0];} Write-Host ""; Write-Host ""; Write-Host "Error: " $suppressedError;

$suppressedError = $null; try {Get-ADUser abcdefghijklm -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError = $error[0];} Write-Host ""; Write-Host ""; Write-Host "Error: " $suppressedError;

$suppressedError = $null; try {Get-UserPhoto -Identity "abcdefghijklm" -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError = $error[0];} Write-Host ""; Write-Host ""; Write-Host "Error: " $suppressedError;

$suppressedError = $null; try {Get-eoUserPhoto "abcdefghijklm" -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError = $error[0];} Write-Host ""; Write-Host ""; Write-Host "Error: " $suppressedError;




Here is an example with all commands error output added to an array. The on prem and o365 Exchange commands might behave strangely if you don't have the cmdlets loaded.

Errors stored in an array example

$suppressedError = $null; try {Get-ChildItem c:\temp\abcdefghijklm.txt -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError += $error[0];} 
try {Get-Process 123456789 -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError += $error[0];} 
try {Get-ADUser abcdefghijklm -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError += $error[0];} 
try {Get-UserPhoto -Identity "abcdefghijklm" -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError += $error[0];} 
try {Get-eoUserPhoto "abcdefghijklm" -ErrorAction Stop -ErrorVariable suppressedError; } catch [System.Exception] { $suppressedError += $error[0];} 
Write-Host ""; Write-Host ""; 
Write-Host "Error count:" $suppressedError.Count;
Write-Host "Error:" $suppressedError;
Write-Host "";
Write-Host "Error 1:" $suppressedError[0]; Write-Host "";
Write-Host "Error 2:" $suppressedError[1]; Write-Host "";
Write-Host "Error 3:" $suppressedError[2]; Write-Host "";
Write-Host "Error 4:" $suppressedError[3]; Write-Host "";
Write-Host "Error 5:" $suppressedError[4]; Write-Host "";

























How to keep your server from locking the screen or turning off the display, evading a timeout period

Sometimes you don't have access to group policy or powercfg.exe and have a virtualized server that makes no sense to have screen lock on, when your desktop/laptop has its own screen locking provision and remoting in through your hardware is the only way the VM can be accessed. It's like locking your car doors when your car is inside of your locked garage.

These 2 registry files will cure that, so that you aren't constantly unlocking your servers, while going back and forth between them and Outlook and whatever else your daily duties involve.

If they change back, a PowerShell script may be required to start upon login to monitor the changes

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows\Control Panel\Desktop]

"ScreenSaveActive"="0"

"ScreenSaverIsSecure"="0"

"ScreenSaveTimeOut"="4294000000"

"SCRNSAVE.EXE"="%systemroot%\\System32\\zscrnsave.scr"

and 

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\System]

"NoDispScrSavPage"=dword:00000000

"HideLogonScripts"=dword:00000000

These will keep you from proverbially (and pointlessly) locking your car inside of your locked garage 

Here is a powershell script that will monitor the keys should they revert back inadvertently .

while($true)
{
$arr_regValueNamesAndValues = New-Object System.Collections.Generic.List[String];
$int_changesCount = 0;
$regHKCU = [Microsoft.Win32.Registry]::CurrentUser;
$regKeyRoot = $regHKCU.OpenSubKey("Software\Policies\Microsoft\Windows\Control Panel\Desktop", $true)
$valueNames = $regKeyRoot.GetValueNames();
foreach ($valueName in $valueNames)
{
$valueNameValue = $regKeyRoot.GetValue($valueName)
$arr_regValueNamesAndValues.Add([string]$valueName + "|" + [string]$valueNameValue);
}
foreach ($regValueNameAndValue in $arr_regValueNamesAndValues)
{
$regValueNameAndValueSplit = $regValueNameAndValue.Split("|");

if ($regValueNameAndValueSplit[0] -eq "ScreenSaveActive")
{
if ($regValueNameAndValueSplit[1] -ne "0")
{
$regKeyRoot.SetValue("ScreenSaveActive", "0", [Microsoft.Win32.RegistryValueKind]::String);
Write-Host " *** Set ScreenSaveActive to 0" ([DateTime]::Now.ToString());
$int_changesCount++;
}
}
elseif ($regValueNameAndValueSplit[0] -eq "ScreenSaverIsSecure")
{
if ($regValueNameAndValueSplit[1] -ne "0")
{
$regKeyRoot.SetValue("ScreenSaverIsSecure", "0", [Microsoft.Win32.RegistryValueKind]::String);
Write-Host " *** Set ScreenSaverIsSecure to 0" ([DateTime]::Now.ToString());
$int_changesCount++;
}
}
elseif ($regValueNameAndValueSplit[0] -eq "ScreenSaveTimeOut")
{
if ($regValueNameAndValueSplit[1] -ne "900")
{
$regKeyRoot.SetValue("ScreenSaveTimeOut", "900", [Microsoft.Win32.RegistryValueKind]::String);
Write-Host " *** Set ScreenSaveTimeOut to 900" ([DateTime]::Now.ToString());
$int_changesCount++;
}
}
else
{
#Write-Host "Unlisted regValueName" ([DateTime]::Now.ToString());
}
}

if ($int_changesCount -eq 0)
{
Write-Host "No changes needed" ([DateTime]::Now.ToString());
}

Sleep -Seconds 30;
}

keywords: disable screen timeout lock registry screen saver off display off powercfg.exe powercfg power options advanced settings power plan view change annoying

So you're trying to use Powershell Out-File or Add-Content to create a csv with data/a variable that has (contains) commas inside of it?

Below is the code example of why this is such a PITA. It uses Exchange Powershell to grab distribution groups from an OU and then to read the members of each group recursively.

When formatting for an Excel csv that has commas in the data, you have to put quotes around ONLY the cell data that innately contains a comma, if you put commas around each cell's data, only the first cell with commas contained in the data will format correctly and the next cell in that same row that has a comma inside of its data, will not format correctly, which will drive you bonkers if you are like me.

How it looks when wrong (row 2) vs correct (row 4)

The code below adds the data for each row into an array and then loops through it and only prepends/appends quotes to the cell data when the cell data itself has a comma within it. If you want to roll your own, it should be pretty easy to add your data for each cell to an array and then use the loop that has foreach ($outputTemp in $outputArr), down to the code $output = "";. 

I couldn't find this via a web search anywhere else on the internet, so hopefully this helps someone. Comment with questions.

$outFile = "c:\temp\filename.csv";
"ADGroupDN, ADGroupSam, MemberDN, MemberSam" | Out-File $outFile -Encoding utf8;
$groupCount = 0;
$memberCount = 0;
$adGroups = Get-ADGroup -SearchBase "OU HERE" -Filter * -ResultSetSize 99999999; # **************
#Write-Host "Adgroup count" $adGroups.Count;
foreach ($adGroup in $adGroups) 
{
    $groupCount++;
    $members = Get-ADGroupMember $adGroup -Recursive;
    foreach ($member in $members)
    {
        $memberCount++;
        $outputArr = New-Object System.Collections.ArrayList;
        $outputArr.Add($adGroup.DistinguishedName) | Out-Null;
        $outputArr.Add($adGroup.SamAccountName) | Out-Null;
        $outputArr.Add($member.distinguishedName) | Out-Null;
        $outputArr.Add($member.SamAccountName) | Out-Null;
    
        $index = 0;
        foreach ($outputTemp in $outputArr)
        {
            if ($outputTemp.ToString().Contains(","))
		    {
			    $output += "`"$outputTemp`",";
		    }
		    else
		    {
			    $output += $outputTemp + ",";
		    }

            $index++;
		
		    if ($index -eq $outputArr.Count)
            {
                if ($output.EndsWith(","))
                {
                    $output = $output.Remove($output.Length - 1);
                }
            }
        }

        $output | Out-File $outFile -Append -Encoding utf8;
        $outputTemp = $output;
        $output = "";
    }
}

Write-Host $groupCount;
Write-Host $memberCount;

How to convert a CanonicalName (cn) to a DistinguishedName (dn) in Powershell

This was surprisingly not easily found on the internet, so when a coworker asked me how to convert a CanonicalName (cn) to a DistinguishedName (dn) in Powershell, I was surprised I had to roll my own code for them, below is the function.

Usage is easy, it's

$cn = "dc3.dc2.dc1.com/ChildOUName1/ChildOUName2/ChildOUName3/ChildOUName4/ChildOUName5/Lastname, FirstName";
Write-Host "CN is:" $cn;
$dn = cnToDn($cn);
Write-Host "DN is:" $dn;

and you should see:

DN is: CN=Lastname\, FirstName,OU=ChildOUName5,OU=ChildOUName4,OU=ChildOUName3,OU=ChildOUName2,OU=ChildOUName1,DC=dc3,DC=dc2,DC=dc1,DC=com

It should work for object accounts and not just user accounts, the object part was not as thoroughly tested, but if you've come this far you can probably figure out any tweaks needed and report back here your improvements.

 

function  cnToDn($cn)
{
    $cnReversedSections = $null;
    $cnSplit = $cn.Split("/");
    for ($i = ($cnSplit.Count - 1); $i -ge 0;, $i--)
    {
        if ($i -gt 0)
        {
            $cnReversedSections += $cnSplit[$i] + "/";
        }
        else
        {
            $cnReversedSections += $cnSplit[$i];
        }
    }

    $objectValue = $null;
    $dcValues = $null;
    $ouValues = $null;
    $cnReversedSectionsSplitTemp = $cnReversedSections.Split("/");
    $cnReversedSectionsSplit = New-Object System.Collections.ArrayList;
    foreach ($cnReversedSectionsTemp in $cnReversedSectionsSplitTemp) #account for empty array entries because of extra slashes
    {
        if ($cnReversedSectionsTemp -ne "")
        {
            $cnReversedSectionsSplit.Add($cnReversedSectionsTemp) | Out-Null;
        }
    }


    if ($cnReversedSectionsSplit[0].Contains(",")) # user object
    {
        for($i = 0; $i -lt $cnReversedSectionsSplit.Count; $i++)
        {
            if ($i -eq 0) # user object name
            {
                $objectValue = "CN=" + $cnReversedSectionsSplit[$i]; 
                if ($objectValue.Contains(","))
                {
                    $objectValue = $objectValue -replace [RegEx]::Escape(","), "\,";
                }
                $objectValue = $objectValue + ",";            
            }
            elseif ($i -eq ($cnReversedSectionsSplit.Count - 1)) # domain
            {
                $dcValuesArr = $cnReversedSectionsSplit[$i].Split(".");           
                for ($j = 0; $j -lt $dcValuesArr.Count; $j++)
                {
                    if ($j -eq ($dcValuesArr.Count - 1))
                    {
                        $dcValues += "DC=" + $dcValuesArr[$j];
                    }
                    else
                    {
                        $dcValues += "DC=" + $dcValuesArr[$j] + ",";
                    }
                }
            }
            else # the rest in the middle
            {
                $ouValues += "OU=" + $cnReversedSectionsSplit[$i] + ",";
            }
        }
    }
    else # non user object
    {
        for($i = 0; $i -lt $cnReversedSectionsSplit.Count; $i++)
        {
            if ($i -eq ($cnReversedSectionsSplit.Count - 1)) # domain
            {
                $dcValuesArr = $cnReversedSectionsSplit[$i].Split(".");           
                for ($j = 0; $j -lt $dcValuesArr.Count; $j++)
                {
                    if ($j -eq ($dcValuesArr.Count - 1))
                    {
                        $dcValues += "DC=" + $dcValuesArr[$j];
                    }
                    else
                    {
                        $dcValues += "DC=" + $dcValuesArr[$j] + ",";
                    }
                }
            }
            else
            {
                $ouValues += "OU=" + $cnReversedSectionsSplit[$i] + ",";
            }
        }
    }
    $dn = $objectValue + $ouValues + $dcValues;
    return $dn;
}
 

Why won't try/catch work and catch my powershell error

So this drove me nuts for a long time and I couldn't find anything on the net to figure out why Powershell didn't function like the c# code I've written, especially considering Powershell is based and written in c#.

 The below will not fall into the catch portion of the code, why is that?

 

try
{
       Remove-DistributionGroupMember -Identity "#Test" -Member "blah@blah.com";
       
}
catch
{
       $ErrorMessage = $_.Exception.Message; Write-host "This is Exception.Message $ErrorMessage"
       $FailedItem = $_.Exception.ItemName; Write-host "This is Exception.ItemName $FailedItem"
}

 

We have to set $ErrorActionPreference = "Stop";

 If you run it after doing that, you'll fall into the catch portion of the code like you want to.

Reading registry values in powershell without an error red lettering

Surprisingly there wasn't a complete answer for this when searching the web after a coworker asked me for help with this. I found some examples but they still would red letter when the key wasn't present and that's not something you want an end user to see and be confused about. There's obviously a difference between the script having a failure error and not being able to find a registry key value because it happens to not be present or they have a different version of Office for example, so maybe the 15.0 key is present, but the 16.0 is not.

The below should remove the red lettering and allow you to enumerate through the key values if they are present and the path is present. Enjoy.

 
Function Get-RegistryKeyPropertiesAndValues
{
    Param(
    [Parameter(Mandatory=$true)]
    [string]$path)

    if (test-path $path)
    {
        Set-Location -Path $path
        Get-Item . |

        Select-Object -ExpandProperty property |
        ForEach-Object {
            New-Object psobject -Property @{“property”=$_;
            “Value” = (Get-ItemProperty -Path . -Name $_).$_}
        }
    }
    else
    {
        return "path doesn't exist";
    }
}

cls;

$path = "HKCU:\Software\Microsoft\Office\16.0\Outlook\AutoDiscover";
#$path = "HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell";
$valz = Get-RegistryKeyPropertiesAndValues -path $path;

if ($valz -ne "path doesn't exist")
{
    foreach ($val in $valz)
    {
        Write-Host "property: " $val.property;
        Write-Host "value: " $val.Value;
    }
}
else
{
    $valz;
}

Getting or setting values in O365 with a Powershell function

Pay special attention to the values $ConnectionUri, $AzureADAuthorizationEndpointUri and $ModulePath as you may need to change them. My c:\exonline folder is actually a duplicate of the folder found on my machine (after the O365 install process) C:\Users\[sam account name]\AppData\Local\Apps\2.0\998QKRDT.T3O\KL00A4OZ.N0K\micr...exe_1975b8453054a2b5_0010.0000_none_1e2f2accd43128c3 . I don't know if this is a static path or not, aside from the profile name. You can also try this $Path = "$Env:LOCALAPPDATA\Apps\2.0\*\CreateExoPSSession.ps1"

This function uses c# to grab the email address for auth with o365, so that other Powershell modules and code don't have to be loaded before logging in. If you use something other than pass through ticket auth, this function won't work out of the box and you'll have to add creds to your new-pssession. It will open and close the session for each command sent. This can be optimized if you need to loop through a bunch of things in each session, I did not so I did not add that complication.

Try it!


function getExoCommandReturnValue
{
[CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$commandString,

[Parameter(Mandatory=$false, Position=1)]
        [string]$argumentList
    )

$mailAddr =
@'
using System;
using System.Data;
using System.DirectoryServices;
using System.Collections;

namespace mailNS
{
public class mailCl
{
public string getMailProps()
{
    string domainName = System.DirectoryServices.AccountManagement.UserPrincipal.Current.EmailAddress;

return domainName;
}
}
}

'@

try
{
$mailAddrGet = New-Object mailNS.mailCl;
}
catch
{
$assemblies = ("System", "System.Data", "System.DirectoryServices", "System.DirectoryServices.AccountManagement", "System.Security.Principal");

Add-Type -TypeDefinition $mailAddr -ReferencedAssemblies $assemblies -Language CSharp
$mailAddrGet = New-Object mailNS.mailCl;
}

Get-PSSession | where {$_.ComputerName -eq "outlook.office365.com"} | Remove-PSSession
[string] $ConnectionUri = "https://outlook.office365.com/PowerShell"; # may need different value
[string] $AzureADAuthorizationEndpointUri = 'https://login.windows.net/common'; # may need different value

[System.Management.Automation.Remoting.PSSessionOption] $PSSessionOption = $null;
$mailProperty = $mailAddrGet.getMailProps();
$ExoPowershellModule = "Microsoft.Exchange.Management.ExoPowershellModule.dll";
$ModulePath = "c:\exonline\Microsoft.Exchange.Management.ExoPowershellModule.dll"; # this will have to be customized to the file path these files are housed

Import-Module $ModulePath -WarningAction SilentlyContinue -WarningVariable $war1 -DisableNameChecking;

$PSSession = New-ExoPSSession -ConnectionUri $ConnectionUri -AzureADAuthorizationEndpointUri $AzureADAuthorizationEndpointUri -UserPrincipalName $mailProperty -WarningAction SilentlyContinue -WarningVariable $war2 -Confirm:$false -ErrorAction SilentlyContinue -ErrorVariable $err2 -PipelineVariable $pip2 -OutVariable $out2b;

Import-PSSession $PSSession -WarningAction SilentlyContinue -DisableNameChecking -AllowClobber;
$scriptBlock = [scriptblock]::Create($commandString);
$returnValue = Invoke-Command -ScriptBlock $scriptBlock;

return $returnValue;
}

cls;

$user = "exotestuser";

$properties = getExoCommandReturnValue -commandString 'get-mailbox $user';
$properties | fl Litigation*, *quotadefaults* , retentioncomment ;

$properties = getExoCommandReturnValue -commandString 'Set-Mailbox $user -litigationholdowner "lit_hold_owner" -retentioncomment "reten_comment"';

$properties = getExoCommandReturnValue -commandString 'get-mailbox $user';
$properties | fl LitigationHold*, *quotadefaults*, retentioncomment ;

Split a string based on a delimiter character, only if the character is not inside of a set of quotes in Powershell

Below is the function and example. You can also strip the code out of the function and run it inline if you assign values to the 2 variables of $lineToSplit  and $splittingCharacter . $string.Split("$char") works great if there are no quotes where a character needs to be preserved. An example of where this would be useful would be

$lineToSplit = 'home, work, "last, first", mobile, address'; Obviously you don't want to split where the comma is inside of quotes.

$valueSplit = splitOutsideOfQuotes -lineToSplit $valueSplit -splittingCharacter ",";
function splitOutsideOfQuotes
{
[CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$lineToSplit,
        [Parameter(Mandatory=$true, Position=1)]
        [string]$splittingCharacter
    )
#Region Initialization
$charArray = $lineToSplit.ToCharArray();
$stringTemp = "";
[bool] $quoteBlockerFound = $false
$returnSplitArray = New-Object System.Collections.ArrayList;
[int] $index = 0;
[int] $charCount = $charArray.Count;
#EndRegion /Initialization
foreach ($char in $charArray)
{
$index++;
if ($char -ne $splittingCharacter)
{
if ($char -ne " ") # not equal a space
{
if ($char -ne "`"") # not equal a quote
{
$stringTemp += $char;
}
elseif (($char -eq "`"") -and ($quoteBlockerFound -eq $true)) # equals a quote
{
$quoteBlockerFound = $false;
}
else
{
$quoteBlockerFound = $true;
}
}
elseif (($char -eq " ") -and ($quoteBlockerFound -eq $true))
{
$stringTemp += $char;
}
}
elseif (($char -eq $splittingCharacter) -and ($quoteBlockerFound -eq $true))
{
$stringTemp += $char;
}
else
{
$returnSplitArray.Add($stringTemp) | Out-Null;
$stringTemp = "";
}
if ($index -eq $charCount)
{
$returnSplitArray.Add($stringTemp) | Out-Null;
$stringTemp = "";
}
}
return $returnSplitArray;
}

How to read an XLSX file in .net code natively, without any 3rd party tools

Much to my surprise, there is no native way to read xlsx files in C# or PowerShell. Recently I had to write code that grabbed values from an xlsx file on SharePoint and then include the values in an email that is programmatically sent out. I couldn't use 3rd party software, so this presented a challenge.

As it turns out, an xlsx file is actually a compressed file and it can be renamed to a .zip file and then the contents (mostly xmls) extracted and mapped to each other.

The code below will download an xlsx file to a local directory or open it from an local directory if the download is not needed and then it extracts the files inside and maps the xmls. I put comments at the top of the code, you need PowerShell 5.0 or later to leverage the extraction function. It also assumes the first sheet in the spreadsheet is named worksheet1, you'll have to pull that programmatically if that is not the case or change the hard coded name. The code maps the xml that carries a list of shared strings (to save space) to the worksheet xml that has a single numerical representation of that shared string inside of it, as well as a row and column delineator. Dates are tricky as Microsoft uses the number of days from January 1st, 1900 to the value as the actual numerical representation, as well as some formatting data in the XML, but if you are like me you only care about the value itself. There is some caveat where MS used 1900 as a leap year, even though it was not, google that if you are off by a day or two.

If you have more than the standard A to Z columns in use, you'll have to write extra code for that and some of the xml symbolic representations I didn't take the time to figure out what they were for as I didn't need to. If someone does, I'd appreciate a comment here explaining what they found.

If you use this code, all of yoru values should easily be accessible via $columnListOfTuples or $rowListOfTuples, which will include a row and column value, as well as the actual cell value.

This was a PITA so I hope this helps others.

<#

    $columnListOfTuples and $rowListOfTuples have everything mapped

    Each xlsx file is a compressed group of xml files into a zip file that is renamed .xlsx
    There is an xml of shared strings that are then represented by a single numerical value to save space
    There is an xml for each sheet in the spread sheet that has numerical references to the values in the shared strings xml
    To produce a virtual/programmatic representation of the spreadsheet, the code will read the sheet, then read the numerical reference than then cross reference that to the shared strings value
    I have no mapped out all functions and xmls in the compressed file, but this is enough to get you started
    It's only configured for columns A - Z, you'll have to write code for additional columsn
    You need powershell 5.0 or later to have the native unzip feature
    It assumes your first worksheet is named worksheet1
#>

cls;

#Region Read Xlsx file

    #Region Create temporary zip file and location

    $fileDate = [DateTime]::Now.ToShortDateString();
    $fileDate = $fileDate.Replace("/", "-");
    $fileTime = [DateTime]::Now.ToShortTimeString();
    $fileTime = $fileTime.Replace(":", ".");
    $fileTime = $fileTime.Replace(" ", ".");
    $fileDateTime = $fileDate + "_" + $fileTime;

    $networkFile = "http://[xxxxx].xlsx";
    $originalFile = "c:\temp\file.xlsx"; # file must exist and location must have writable rights
    #Invoke-WebRequest -Uri $networkFile -OutFile $originalFile -UseDefaultCredentials; # this is to download a network file locally, may not be needed

    $index = $originalFile.LastIndexOf("\");
    $originalFileDirectory = $originalFile.Remove($index);
    $tempCopiedFileName = $originalFileDirectory + "\tempXlsx-" + $fileDateTime + ".zip";
    $desinationPath = $originalFileDirectory + "\xlsxDecompressed";

    #Endregion /Create temporary zip file and location

    #Region Map all of the zip/xlsx file paths

    $relsXmlFile = $desinationPath + "\_rels\.rels";
    $item1XmlRelsXmlFile = $desinationPath + "\customXml\_rels\item1.xml.rels";
    $item2XmlRelsXmlFile = $desinationPath + "\customXml\_rels\item2.xml.rels";
    $item3XmlRelsXmlFile = $desinationPath + "\customXml\_rels\item3.xml.rels";
    $item1XmlFile = $desinationPath + "\customXml\item1.xml";
    $item2XmlFile = $desinationPath + "\customXml\item2.xml";
    $item3XmlFile = $desinationPath + "\customXml\item3.xml";
    $itemProps1XmlFile = $desinationPath + "\customXml\itemProps1.xml";
    $itemProps2XmlFile = $desinationPath + "\customXml\itemProps2.xml";
    $itemProps3XmlFile = $desinationPath + "\customXml\itemProps3.xml";
    $appXmlFile = $desinationPath + "\[filename]\docProps\app.xml";
    $coreXmlFile = $desinationPath + "\[filename]\docProps\core.xml";
    $customXmlFile = $desinationPath + "\[filename]\docProps\custom.xml";
    $workbookXmlRelsXmlFile = $desinationPath + "\xl\_rels\workbook.xml.rels";
    $theme1XmlFile = $desinationPath + "\xl\theme\theme1.xml";
    $sheet1XmlRelsXmlFile = $desinationPath + "\xl\worksheets|_rels|sheet1.xml.rels";
    $workSheet1XmlFile = $desinationPath + "\xl\worksheets\sheet1.xml";
    $sharedStringsXmlFile = $desinationPath + "\xl\sharedStrings.xml";
    $stylesXmlFile = $desinationPath + "\xl\styles.xml";
    $workbookXmlFile = $desinationPath + "\xl\workbook.xml";
    $contentTypesXmlFile = $desinationPath + "\[Content_Types].xml";

    #Endregion /Map all of the zip/xlsx file paths

    Copy-Item $originalFile -Destination $tempCopiedFileName -Recurse; # copy the xlsx file as a temporary zip file
    Expand-Archive -LiteralPath $tempCopiedFileName -DestinationPath $desinationPath -Force; # unzip the file
    [xml]$fileLines = Get-Content($sharedStringsXmlFile); # read the contents of the shared strings file

    #Region Map shared strings

    $arr_SharedStrings = New-Object System.Collections.Generic.List[String];
    for ($i = 0; $i -lt $fileLines.sst.si.Count; $i++ )
    {
        $line = $fileLines.sst.si[$i];
        $tuple = [tuple]::create([int]$i, [string]("`"" + $line.InnerText + "`"") );
        $arr_SharedStrings.Add($tuple); # map the shared strings to the representational number inside of the xml files
    }

    #Endregion /Map shared strings

    [xml]$fileLines = Get-Content($workSheet1XmlFile);
    $sheetData = $fileLines.worksheet.sheetData;

    #Region Map rows
    $arr_sheet1_rows = New-Object System.Collections.ArrayList; # this will have a list/tuple of each cell that makes up a row
    for ($i = 0; $i -lt $sheetData.row.Count; $i++ )
    {
        $arr_sheet1_individualRow = New-Object System.Collections.Generic.List[String];
        $rowElements = $sheetData.row[$i].c; #grab data of all cells in that row
        foreach ($element in $rowElements)
        {
            $elementsTuple = [tuple]::create([string]$element.r, [string]$element.s, [string]$element.v)
            $arr_sheet1_individualRow.Add($elementsTuple);      
        }

        $rowTuple = [Tuple]::create([int] $i+1, $arr_sheet1_individualRow);
        $arr_sheet1_rows.Add($rowTuple) | Out-Null;
    }

    $rowListOfTuples = New-Object System.Collections.ArrayList; # has row number, then column letter, then cell value
    foreach ($rowMapped in $arr_sheet1_rows)
    {
        $cellValues = $rowMapped.Item2;
        $rowNumber = $rowMapped.Item1;
        foreach ($cellValue in $cellValues)
        {
            $cellValue = $cellValue.Remove(0, 1);
            $cellValue = $cellValue.Remove($cellValue.Length - 1, 1);
            $cellValue = $cellValue.Replace(" ", "");
            $cellValuesSplit = $cellValue.Split(",");
            $columnLetter = $cellValuesSplit[0];
            $columnLetter = $columnLetter -replace '[0-9]',''
            $cellValueOnly = $cellValuesSplit[2];

            $cellTuple = [tuple]::create([int]$rowNumber, [string]$columnLetter, [string]$cellValueOnly);
            $rowListOfTuples.Add($cellTuple) | Out-Null;
        }
    }

    #Endregion /Map shared rows

    #Region Map shared columns

    $rowCount = $arr_sheet1_rows.Count;
    $sheetDataCols = $fileLines.worksheet.cols;
    $arr_sheet1_columns = New-Object System.Collections.ArrayList; # this will have a list/tuple of each cell that makes up a column
    #$arr_sheet1_columns.Add("Place holder") | Out-Null;
    #for ($i = 0; $i -lt $sheetDataCols.col.Count; $i++ )
    for ($i = 0; $i -lt $rowCount; $i++ )
    {
        if ($arr_sheet1_columns.Count -lt $sheetDataCols.col.Count)
        {
            for ($j = 0; $j -lt $sheetDataCols.col.Count; $j++ )
            {
                switch  ($j)
                {
                    0 { $columnLetterTranslated = "A"; break; }
                    1 { $columnLetterTranslated = "B"; break; }
                    2 { $columnLetterTranslated = "C"; break; }
                    3 { $columnLetterTranslated = "D"; break; }
                    4 { $columnLetterTranslated = "E"; break; }
                    5 { $columnLetterTranslated = "F"; break; }
                    6 { $columnLetterTranslated = "G"; break; }
                    7 { $columnLetterTranslated = "H"; break; }
                    8 { $columnLetterTranslated = "I"; break; }
                    9 { $columnLetterTranslated = "J"; break; }
                    10 { $columnLetterTranslated = "K"; break; }
                    11 { $columnLetterTranslated = "L"; break; }
                    12 { $columnLetterTranslated = "M"; break; }
                    13 { $columnLetterTranslated = "N"; break; }
                    14 { $columnLetterTranslated = "O"; break; }
                    15 { $columnLetterTranslated = "P"; break; }
                    16 { $columnLetterTranslated = "Q"; break; }
                    17 { $columnLetterTranslated = "R"; break; }
                    18 { $columnLetterTranslated = "S"; break; }
                    19 { $columnLetterTranslated = "T"; break; }
                    20 { $columnLetterTranslated = "U"; break; }
                    21 { $columnLetterTranslated = "V"; break; }
                    22 { $columnLetterTranslated = "W"; break; }
                    23 { $columnLetterTranslated = "X"; break; }
                    24 { $columnLetterTranslated = "Y"; break; }
                    25 { $columnLetterTranslated = "Z"; break; }
                }
                $arr_sheet1_columns.Add($columnLetterTranslated) | Out-Null;
            }       
        }

        $rowElements = $sheetData.row[$i].c; #grab data of all cells in that row
        foreach ($element in $rowElements)
        {
            $columnLetter = $element.r -replace '[^a-zA-Z-]',''
            for ($k = 0; $k -lt $arr_sheet1_columns.Count; $k++)
            {
                $column = $arr_sheet1_columns[$k];
                if ($column.StartsWith($columnLetter))
                {
                    $columnTemp = $column.ToString() + "|" + $element.v;
                    $arr_sheet1_columns.Remove($column);
                    $arr_sheet1_columns.Insert($k, $columnTemp);
                    $stop = "here";
                }
            }
        }
    }

    $columnListOfTuples = New-Object System.Collections.ArrayList; # has column letter, than row number, then cell value
    foreach ($columnMapped in $arr_sheet1_columns)
    {
        $index = $columnMapped.IndexOf("|");
        $columnMappedLetter = $columnMapped.Remove($index);

        $columnIndividualValues = $columnMapped.Remove(0, $index);
        $columnIndividualValuesSplit = $columnIndividualValues.Split("|");

        $rowNumber = 0;
        foreach($columnIndividualValueSplit in $columnIndividualValuesSplit)
        {   
            $cellTuple = [tuple]::create([string]$columnMappedLetter, [int]$rowNumber, [string]$columnIndividualValueSplit);
            $columnListOfTuples.Add($cellTuple) | Out-Null;
            $rowNumber ++;
        }
    }

    #Endregion /Map shared columns



    # Here you have all of the data and you can parse it however you want, below is an example of that

    #Region Parse the extracted data

    $rowsForParsing = New-Object System.Collections.ArrayList;
    foreach ($arr_sheet1_row in $arr_sheet1_rows)
    {
        $rowNumber = $arr_sheet1_row.Item1;
        $cellValues = New-Object System.Collections.ArrayList; 
        $rowTemp = "";

        $indexer = 0;
        foreach ($rowValue in $arr_sheet1_row.Item2)
        {
            $valueSplit = $rowValue.Replace(" ", "");
            $valueSplit = $valueSplit.Replace("(", "");
            $valueSplit = $valueSplit.Replace(")", "");
            $valueSplit = $valueSplit.Split(",");

            $cellLocation = "";
            $cellLocation = $valueSplit[0];
            $cellSType = "";
            $cellSType = $valueSplit[1];
            $cellStringRepresentationValue = "";
            $cellStringRepresentationValue = $valueSplit[2];
            $translatedValue = "";

            if ($cellStringRepresentationValue -ne "")
            {
                switch ($cellSType)
                {
                    "1" { $translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # represents column headers
                    "2" { $translatedValue = [datetime]::FromOADate($cellStringRepresentationValue); break;}  # number of days from January 1, 1900
                    "3" { $translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # mapped value to strings table
                    "4" {$translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # mapped value to strings table
                    "5" { $translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # mapped value to strings table
                    "6" { Write-Host "#6: " $rowValue; break;}  # not sure what 6 represents yet
                    "7" { Write-Host "#7: " $rowValue; break;}  # not sure what 7 represents yet
                    "8" { Write-Host "#8: " $rowValue; break;}  # not sure what 8 represents yet
                    "9" { Write-Host "#9: " $rowValue; break;}  # not sure what 9 represents yet
                    "10" { Write-Host "#10: " $rowValue; break;}  # not sure what 10 represents yet
                    "11" { Write-Host "#11: " $rowValue; break;}  # not sure what 11 represents yet     
                }
            }

            if (($rowTemp -eq "") -and ($indexer -eq 0))
            {
                $rowTemp = "$translatedValue";
            }
            else
            {       
                $rowTemp = "$rowTemp" + "|" + "$translatedValue";
            }   

            $indexer++;
        }

        if ($rowNumber -eq 22)
        {
            $stop = "here";
        }

        $rowsForParsing.Add("$rowNumber|$rowTemp") | Out-Null;
    }

    $fileInformation = New-Object System.Collections.ArrayList;
    $topRow = $rowsForParsing[1];
    foreach ($rowParsed in $rowsForParsing)
    {
        $rowParsedSplit = $rowParsed.Split("|");    
        $date = $rowParsedSplit[1];

        $pass = $true;
        try
        {
            $dtVal = get-date $date;
        }
        catch
        {
            $pass = $false;
        }

        if ($pass -eq $true)
        {
            if ((get-date) -gt (get-date $date))
            {
                $dateApplicableRow = $rowParsed;
            }
            else
            {
                Write-Host "Top row is $topRow";
                Write-Host "This weeks row is $dateApplicableRow";
                $fileInformation.Add($topRow);
                $fileInformation.Add($dateApplicableRow);
                break;
            }
        }
    }

    #Endregion /Parse the extracted data

#EndRegion /Read Xlsx file

How to replace all commas outside of quotation marks programmatically in Powershell

From time to time I have to parse a csv file that also has names in it, usually lastname, firstname inside of quotes.

This is obnoxious if you do not want them separated and a PITA. The code below allows you to open a file and replace them with a different delineating character programmatically, so with the new format you can then key off of that new character to import into excel as a character delimited file. For the environment I'm in, I usually use the pipe character "|" .


cls
$originalFileLocation = "c:\temp\noncompatibleo365users.txt";
$newFileLocation = "c:\temp\noncompatibleo365usersParsed2.txt";
$fileLines = Get-Content($originalFileLocation);
$replacementCharacter = "|"; # set this to the character you want to use to delineate, instead of a comma
$counter = 0;

foreach ($fileLine in $fileLines)
{
$fileLineSplit = $fileLine.Split(",");
$newLine = "";
foreach ($partOfLine in $fileLineSplit)
{if (($partOfLine -notmatch '"') -and ($quoteFoundBlocker -eq $false))
            {
                $newPartOfLine = $partOfLine.Replace(",", $replacementCharacter);
                $newLine += $newPartOfLine.Trim() + $replacementCharacter;
            }
            else
            {
                if (($partOfLine -match '"') -and ($quoteFoundBlocker -eq $false))
                {
                    $quoteFoundBlocker =  $true;
                }
                elseif (($partOfLine -match '"') -and ($quoteFoundBlocker -eq $true))
                {
                    $quoteFoundBlocker =  $false;
                }
              
                $newPartOfLine = $partOfLine.Trim();
                if ($newPartOfLine.StartsWith('"'))
                {
                    if ($newPartOfLine.EndsWith('"'))
                    {
                        $newLine += $partOfLine.Trim();
                    }
                    else
                    {
                        $newLine += $partOfLine.Trim() + ", ";
                    }
                }
                elseif ($newPartOfLine.Trim().EndsWith('"'))
                {
                    $newLine += $partOfLine.Trim();
                }
                else
                {
                    $newLine += $partOfLine.Trim() + ", ";
                }          
            }
$newLine >> $newFileLocation;
$counter++;
Write-Host "Processed line count:" $counter;
}

and a function format

$returnValue = replaceDelineatorOutsideOfQuotes($sharedString);

function replaceDelineatorOutsideOfQuotes($fileLinesToDelineate)
{
$replacementCharacter = "|";
    [bool] $quoteFoundBlocker = $false;
    $newFileLines = New-Object System.Collections.ArrayList;
    foreach ($fileLine in $fileLinesToDelineate)
    {
        $fileLineSplit = $fileLine.Split(",");
        $newLine = "";
        foreach ($partOfLine in $fileLineSplit)
        {
            if (($partOfLine -notmatch '"') -and ($quoteFoundBlocker -eq $false))
            {
                $newPartOfLine = $partOfLine.Replace(",", $replacementCharacter);
                $newLine += $newPartOfLine.Trim() + $replacementCharacter;
            }
            else
            {
                if (($partOfLine -match '"') -and ($quoteFoundBlocker -eq $false))
                {
                    $quoteFoundBlocker =  $true;
                }
                elseif (($partOfLine -match '"') -and ($quoteFoundBlocker -eq $true))
                {
                    $quoteFoundBlocker =  $false;
                }
              
                $newPartOfLine = $partOfLine.Trim();
                if ($newPartOfLine.StartsWith('"'))
                {
                    if ($newPartOfLine.EndsWith('"'))
                    {
                        $newLine += $partOfLine.Trim();
                    }
                    else
                    {
                        $newLine += $partOfLine.Trim() + ", ";
                    }
                }
                elseif ($newPartOfLine.Trim().EndsWith('"'))
                {
                    $newLine += $partOfLine.Trim();
                }
                else
                {
                    $newLine += $partOfLine.Trim() + ", ";
                }          
            }
        }
      
        $newFileLines.Add($newLine) | Out-Null;
    } return $newFileLines; }

How to get environment variables of the current logged on user, when running scheduled tasks or powershell as the System Account (NtAuthority\System)

While running a scheduled task that I needed to run as the local System account, I noticed that any environment variables were not accessible the normal method inside of Powershell, which was quite the kink. I need access to the logged on user credentials and the documents folder path and there is very little if anything available via search engines that document this, so here we are.

New-PSDrive HKU Registry HKEY_USERS;
$user = get-wmiobject -Class Win32_Computersystem | select Username;
$sid = (New-Object System.Security.Principal.NTAccount($user.UserName)).Translate([System.Security.Principal.SecurityIdentifier]).value;
$val = (Get-Item "HKU:\$sid\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders");
$myDocPath = $val.GetValue("Personal");

If you follow the instructions via this link, you can open powershell as the NTAuthority\System account to test/troubleshoot, etc.
http://powershell-guru.com/powershell-tip-53-run-powershell-as-system/

Enjoy.

Getting and setting IPMI settings through powershell

We had an issue where some servers had IMPI enabled and some did not, in order to get uniformity on > 200 servers, a quick script was developed to read and then set them all to the same value. The racadm commands documented syntax is poor at best unless you want to read through the 200+ plus page manual linked at the bottom of this. Enjoy.

#get command to read values, enable=enabled means that IPMI is enabled

cls

$servers = get-content("\\xxx\c$\Temp\Security Patches\Servers\serversComplete(E2013)-Alphabetical.txt"); # read in a list of servers

foreach ($server in $servers)

{

    $results = Invoke-Command -ComputerName $server -ScriptBlock { racadm get iDRAC.IPMILan }

    foreach ($result in $results)

    {

        if ($result.StartsWith("Enable="))

        {

            Write-Host $server ", IPMI setting is set to: " $result;

        }

    }

}

 # sets ipmi to disabled 

cls

$servers = get-content("\\xxx\c$\Temp\Security Patches\Servers\serversComplete(E2013)-Alphabetical.txt"); # read in a list of servers

foreach ($server in $servers)

{

    $results = Invoke-Command -ComputerName $server -ScriptBlock { racadm set iDRAC.IPMILan.Enable 0 } # 0 to disable, 1 to enable

       Write-Host $server;

       Write-Host $results;

       Write-Host “”;

Write-Host “”;

}

All commands

http://ftp.dell.com/Manuals/all-products/esuprt_electronics/esuprt_software/esuprt_remote_ent_sys_mgmt/integrated-dell-remote-access-cntrllr-7-v1.30.30_User%27s%20Guide_en-us.pdf

(Powershell) Faster: RedistributeActiveDatabases.ps1 –ShowDatabaseCurrentActives or dag distribution script

The output of RedistributeActiveDatabases.ps1 –ShowDatabaseCurrentActives leaves much to be desired, when you are verifying the dag distribution after failover and/or failback, in my opinion. It outputs an endless list of moves you didn't attempt and it takes a fair amount of time to give the result.

I've created a multithreaded powershell script that does the same thing in about 1/3 of the time of the default MS script. You can either use the script name in powershell with the parameter -dagName or you're going to get a pop up box asking for the dag name. The way that it works is that it leverages the add-type cmdlet in powershell to create an object using c# code, which uses multithreading to return an object to the powershell code, which then parses that object and displays the results.

You'll have to insert your Exchange server or load balancing url you use with your domain, but that's all you should have to change unless there is authentication settings specific to your environment. If you don't use kerberos, you'll have to change that part of the code to negotiate or whatever suits your environment. If it's basic you'll need to include explicit credentials, an example of that is shown at the bottom of this blog. If you aren't familiar with c#, implementing that could be confusing to you. Another hangup could be if you use http or https for your connection to your Exchange server or load balanced url. You'll need to run it on a server to ensure it has access to all of the dlls that need to be imported.

All of that being said, hopefully you don't have to make any changes aside from the url name and it works right out of the box for you. Enjoy!

<# CHANGELOG

9/10/18 - added a load balanced url

#>

<# Notes

You will have to alter the exchangeUri variable for your environments

If you have any issues with this, you may have to alter the kerberos authentication information

wmc.AuthenticationMechanism = AuthenticationMechanism.Kerberos; may need to be .Negotiate and/or you may need to connect

directly to a server with the exchangeUri via http or https, instead of a load balanced url

Another hurdle will occur if you don't have the proper assmemblies on the machine and loaded into the GAC so they are 

registered, in other words this probalby won't run on your desktop

#>

<# PARAMETERS #>

param 

(

[Parameter(Mandatory=$false,Position=1)]

    [string]$dagName

#[Parameter(Mandatory=$False,Position=2)]

#[string]$emailAddressNamesList

)

<# /PARAMETERS #>

cls

$localMachineName = $env:computername;

if (($localMachineName.ToString().ToLower() -notlike "*-dag*") -and ($localMachineName.ToString().ToLower() -notlike "*-cas*"))

{

    Write-Host $localMachineName.ToString().ToLower();

$session = new-pssession -ConfigurationName Microsoft.Exchange -ConnectionUri 'http://[exchange server or load balancer url here]/powershell';

import-pssession $session -AllowClobber; 

}

set-adserversettings -viewentireforest $true;

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null;

if (($dagName -eq $null) -or ($dagName -eq ""))

{

$dagName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter a dag name", "Dag");

}

$time = New-Object system.Diagnostics.Stopwatch  

$time.Start() 

$system = $time.Elapsed.TotalMilliseconds

$dagObject = Get-DatabaseAvailabilityGroup -Identity $dagName;

$servers = New-Object System.Collections.ArrayList;

foreach($server in $dagObject.Servers) # foreach($server in $dagObject.Servers){$servers.Add($server) | Out-Null;

{

$servers.Add($server) | Out-Null;

}

$servers = $servers | Sort-Object;

Write-Host "Database distribution check for" $dagName -ForegroundColor Yellow -BackgroundColor Black;

Write-Host "";

$distribution = 

@'

using System;

using System.Collections.Generic;

using System.Collections.ObjectModel;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Management.Automation;

using System.Management.Automation.Runspaces;

using System.Security;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Threading;

using System.Collections.Concurrent;

using System.Diagnostics;

using System.Security.Principal;

namespace MultiThreading

{

public class dagDistribution

{

public string exchangeUri = "http://[exchange server or load balancer url here]/powershell";

public List<string> get(string dag)

{

DateTime start = DateTime.Now;

            var response = new ConcurrentBag<Collection<PSObject>>();

            var exceptions = new ConcurrentQueue<Exception>();

List<string> serversUnsorted = getDagMembers(dag);

            var servers = from s in serversUnsorted orderby s select s;

            try

            {

                Parallel.ForEach(servers, server =>

                {

                    response.Add(runPowerShellScript(server));

                });

            }

            catch (AggregateException ae)

            {

                foreach (var aex in ae.InnerExceptions)

                {

                    exceptions.Enqueue(aex);

                }

            }

            List<string> returnValues = new List<string>();

            foreach (var item in response)

            {

                string returnValue = parseServerResults(item);

                returnValues.Add(returnValue);

            }

            returnValues.Sort();

return returnValues;

}

public List<string> getServers(string[] serverz)

{

DateTime start = DateTime.Now;

            var response = new ConcurrentBag<Collection<PSObject>>();

            var exceptions = new ConcurrentQueue<Exception>();

string[] serversUnsorted = serverz;

            var servers = from s in serversUnsorted orderby s select s;

            try

            {

                Parallel.ForEach(servers, server =>

                {

                    response.Add(runPowerShellScript(server));

                });

            }

            catch (AggregateException ae)

            {

                foreach (var aex in ae.InnerExceptions)

                {

                    exceptions.Enqueue(aex);

                }

            }

            List<string> returnValues = new List<string>();

            foreach (var item in response)

            {

                string returnValue = parseServerResults(item);

                returnValues.Add(returnValue);

            }

            returnValues.Sort();

return returnValues;

}

    private Collection<PSObject> runPowerShellScript(object server)

        {

            Collection<PSObject> psobjs = new Collection<PSObject>();

            string result = "";

            string serverName = server.ToString();

            WSManConnectionInfo wmc = new WSManConnectionInfo(new Uri(exchangeUri));

            wmc.AuthenticationMechanism = AuthenticationMechanism.Kerberos;

            wmc.ShellUri = "http://schemas.microsoft.com/powershell/Microsoft.Exchange";

            using (Runspace runspace = RunspaceFactory.CreateRunspace(wmc))

            {

                PowerShell powershell = PowerShell.Create();

                

                if (runspace.RunspaceStateInfo.State == RunspaceState.Opened)

                {

                    // do nothing

                }

                else

                {

                    runspace.Open();

                    powershell.Runspace = runspace;

                }

                try

                {

                    PSCommand command = new PSCommand();

                    command.AddScript("get-mailboxdatabase -Server " + server + " -Status");

                    powershell.Commands = command;                    

                    psobjs = powershell.Invoke();

                    if (powershell.HadErrors == true)

                    {

                        result = "Failed - " + powershell.Streams.Error[0].ToString();

                        result = result.Replace("\"", "*");

                    }

                }

                catch (Exception ex)

                {

                    string fail = ex.Message;

                }

            }

            object serverNameO = server;

            PSObject serverNameObj = new PSObject(serverNameO);

            psobjs.Insert(0, serverNameObj);

            return psobjs;

        }

        private List<string> getDagMembers(string dagName)
        {
            Collection<PSObject> psobjs = new Collection<PSObject>();
            string result = "";
            string[] servers = null;
            List<string> serverList = new List<string>();

            WSManConnectionInfo wmc = new WSManConnectionInfo(new Uri(exchangeUri));
            wmc.AuthenticationMechanism = AuthenticationMechanism.Kerberos;
            wmc.ShellUri = "http://schemas.microsoft.com/powershell/Microsoft.Exchange";
           

            using (Runspace runspace = RunspaceFactory.CreateRunspace(wmc))
            {
                PowerShell powershell = PowerShell.Create();

                if (runspace.RunspaceStateInfo.State == RunspaceState.Opened)
                {
                    // do nothing
                }
                else
                {
                    runspace.Open();
                    powershell.Runspace = runspace;
                }

                try
                {
                    PSCommand command = new PSCommand();
                    command.AddScript("Get-DatabaseAvailabilityGroup -Identity " + dagName);
                    powershell.Commands = command;
                    psobjs = powershell.Invoke();

                    if (powershell.HadErrors == true)
                    {
                        result = "Failed - " + powershell.Streams.Error[0].ToString();
                        result = result.Replace("\"", "*");
                    }

                    PSPropertyInfo serversTemp = null;
                    foreach (PSObject psobj in psobjs)
                    {
                        serversTemp = psobj.Properties["servers"];
                        string s_servers = serversTemp.Value.ToString();
                        servers = s_servers.Split(' ');
                        foreach (string server in servers)
                        {
                            serverList.Add(server);
                        }
                    }

                }
                catch (Exception ex)
                {
                    string fail = ex.Message;
                }
            }            

            return serverList;
        }

private string parseServerResults(Collection<PSObject> serverObjs) // needs servername, totaldbs, activedbs, passivedbs, preferencecount (11,11,11,11), mounteddbs, dismounteddbs, dagname

        {

            // called independently with each server, first object is always the server name

            Stopwatch stopwatch = new Stopwatch();

            stopwatch.Start();

            int index = 0;

            string returnValue = "";

            string serverName = "";

            int totalDbs = 0;

            int activeDbs = 0; // whichever has activation preference 1

            int passiveDbs = 0; // whichever has activation preference 2, 3 or 4       

            string activeCopyServerName = "";

            int activationPreferenceOne = 0;

            int activationPreferenceTwo = 0;

            int activationPreferenceThree = 0;

            int activationPreferenceFour = 0;

            int mountedCount = 0;

            int dismountedCount = 0;

            string dagName = "";

            string dagServerAndDatabaseName = "";

            foreach (PSObject obj in serverObjs)

            {

                if (index == 0)

                {

                    serverName = obj.ToString();

                }

                totalDbs = (serverObjs.Count - 1);

                PSMemberInfoCollection<PSPropertyInfo> props = obj.Properties;

                string currentPrimaryActivationServer = "";

                foreach (PSPropertyInfo prop in props)

                {

                    if (prop.Name == "MountedOnServer")

                    {

                        currentPrimaryActivationServer = prop.Value.ToString();

                        break;

                    }

                }

                

                List<string> propertyNames = new List<string>();

                foreach (PSPropertyInfo prop in props)

                {

                    string result = prop.Name + " | " + prop.Value;

                    if (prop.Name == "Mounted")

                    {

                        if (prop.Value.ToString() == "True")

                        {

                            if (currentPrimaryActivationServer.ToLower().StartsWith(serverName.ToLower()))

                            {

                                mountedCount++;

                            }

                        }

                        else

                        {

                            dismountedCount++;

                        }

                    }

                    else if (prop.Name == "MountedOnServer")

                    {

                        activeCopyServerName = prop.Value.ToString();

                    }

                    else if (prop.Name == "ActivationPreference")

                    {

                        string arr = prop.Value.ToString();

                        string[] vals = arr.Split(']');

                        foreach (string val in vals)

                        {

                            if (val != "")

                            {

                                string valTemp = val;

                                if (val.Contains("["))

                                {

                                    valTemp = val.Replace("[", "");

                                }

                                string[] preference = valTemp.Split(',');

                                string preferenceZero = preference[0].ToString().Trim();

                                string preferenceOne = preference[1].ToString().Trim();

                                if (preferenceZero.ToLower() == serverName.ToLower())

                                {

                                    if (preferenceOne == "1")

                                    {

                                        if (currentPrimaryActivationServer.ToLower().StartsWith(serverName.ToLower()))

                                        {

                                            activeDbs++;

                                        }

                                        else

                                        {

                                            passiveDbs++;

                                        }

                                    }

                                    else

                                    {

                                        if (!(currentPrimaryActivationServer.ToLower().StartsWith(serverName.ToLower())))

                                        {

                                            passiveDbs++;

                                        }

                                        else

                                        {

                                            activeDbs++;

                                        }

                                    }

                                    switch (preferenceOne)

                                    {

                                        case "1":

                                            activationPreferenceOne++;

                                            break;

                                        case "2":

                                            activationPreferenceTwo++;

                                            break;

                                        case "3":

                                            activationPreferenceThree++;

                                            break;

                                        case "4":

                                            activationPreferenceFour++;

                                            break;

                                        default:

                                            break;

                                    }

                                }

                            }

                        }

                    }

                    else if (prop.Name == "Server")

                    {

                        string activeCopyServerName2 = prop.Value.ToString();

                    }

                    else if (prop.Name == "MasterServerOrAvailabilityGroup")

                    {

                        dagName = prop.Value.ToString();

                    }

                    else if (prop.Name == "MailboxProvisioningAttributes")

                    {

                        dagServerAndDatabaseName = prop.Value.ToString();

                    }

                    propertyNames.Add(prop.Name.ToString()); // cumulative count of the property names

                }

                index++;

            }

            stopwatch.Stop();

            return returnValue = serverName + "|" + totalDbs + "|" + activeDbs + "|" + passiveDbs + "|" + activationPreferenceOne + "," + activationPreferenceTwo + "," +

                activationPreferenceThree + "," + activationPreferenceFour + "|" + mountedCount + "|" + dismountedCount + "|" + dagName;

        }

}

}

'@

try

{

$dagDistribution = New-Object MultiThreading.dagDistribution;

}

catch

{

Add-Type -TypeDefinition $distribution -ReferencedAssemblies System.Collections, System.ComponentModel, System.Data, System.Drawing, System.Linq, System.Management.Automation, System.Security, System.Threading.Tasks, System.Windows.Forms, System.Threading, System.Collections.Concurrent, System.Security.Principal

$dagDistribution = New-Object MultiThreading.dagDistribution;

}

# $results = $dagDistribution.get($dagName);

if ($servers.Count -gt 0)

{

$results = $dagDistribution.getServers($servers);

}

else

{

$results = $dagDistribution.get($dagName);

}

$results = $results | sort;

$outputTable = New-Object system.Data.DataTable “$TableName”;

#servername totaldbs activedbs passivedbs preferencecountlist mounteddbs dismounteddbs dagname

$col0 = New-Object system.Data.DataColumn "     ",([string]);

$col1 = New-Object system.Data.DataColumn ServerName,([string]);

$col2 = New-Object system.Data.DataColumn TotalDbs,([string]);

$col3 = New-Object system.Data.DataColumn ActiveDbs,([string]);

$col4 = New-Object system.Data.DataColumn PassiveDbs,([string]);

$col5 = New-Object system.Data.DataColumn PreferenceCountList,([string]);

$col6 = New-Object system.Data.DataColumn MountedDbs,([string]);

$col7 = New-Object system.Data.DataColumn DismountedDbs,([string]);

$col8 = New-Object system.Data.DataColumn DagName,([string]);

$outputTable.columns.add($col0);

$outputTable.columns.add($col1);

$outputTable.columns.add($col2);

$outputTable.columns.add($col3);

$outputTable.columns.add($col4);

$outputTable.columns.add($col5);

$outputTable.columns.add($col6);

$outputTable.columns.add($col7);

$outputTable.columns.add($col8);

foreach($result in $results)

{

[string]$resultSplit = $result;

$resultSplit1 = $resultSplit.Split("|"); # -split "|";

$row = $outputTable.NewRow();

$temp0 = $resultSplit1[0];

$temp1 = $resultSplit1[1];

$temp2 = $resultSplit1[2];

$temp3 = $resultSplit1[3];

$temp4 = $resultSplit1[4];

$temp5 = $resultSplit1[5];

if ($resultSplit1[6] -ne "")

{

$temp6 = $resultSplit1[6];

}

else

{

$temp6 = "0";

}

$temp7 = $resultSplit1[7];

$row.ServerName = $resultSplit1[0];

$row.TotalDbs = $resultSplit1[1];

$row.ActiveDbs = $resultSplit1[2];

$row.PassiveDbs = $resultSplit1[3];

$row.PreferenceCountList = $resultSplit1[4];

$row.MountedDbs = $resultSplit1[5];

if ($resultSplit1[6] -ne "")

{

$row.DismountedDbs = $resultSplit1[6];

}

else

{

$row.DismountedDbs = "0";

}

$row.DagName = $resultSplit1[7];

$outputTable.Rows.Add($row);

}

$outputTable | format-table -AutoSize;

Write-host "Function completed" -ForegroundColor Yellow -BackgroundColor Black;

Write-host " ";

$time.Stop();

$totalTime = $time.Elapsed.TotalMilliseconds - $system;

[float]$runTimeSeconds = [math]::Round($totalTime/1000, 2);

[float]$runTimeMinutes = [math]::Round($totalTime/60000, 2);

Write-Host "This function took " $runTimeSeconds.ToString() " seconds (or " $runTimeMinutes.ToString() " minutes) to run";

Explicit credential example

 string loginPassword = "password here";

System.Security.SecureString secpassword = new SecureString();

            foreach (char c in loginPassword)

            {

                secpassword.AppendChar(c);

            }

            PSCredential credential = new PSCredential(@"domain\samaccount", secpassword);

            WSManConnectionInfo connectionInfo = new WSManConnectionInfo(new Uri("https://[server name or load balancer url]/powershell"), "http://schemas.microsoft.com/powershell/Microsoft.Exchange", credential);

            connectionInfo.AuthenticationMechanism = AuthenticationMechanism.Basic;