Quick and Dirty way of creating objects in a loop so you can easily export to a report

You may, or may not be familiar with the quick and dirty way of creating a custom Powershell Object where you pipe an empty string to Select-Object and store the result in a variable like this:

$myCustomObject = “” | select ‘jpegFile’,’guid’

Then you can assign values to the attributes like this

$myCustomObject.’jpegFile’ = <FilePathtoJpeg>

$myCustomObject.’guid’ = <GUID>

I’ve often used this in a loop and create an array of objects to later export using export-csv.  This means inside the loop I was first creating a new object and setting the attribute values using the lines above in every iteration.  I have no idea how long I’ve been doing this but today working on another script I realised that I could shorten this to a single line of code inside the loop.

$photosToUploadtoAD += “” | select @{n=’jpegFile’;e={“$($SPImportPath)\$($employee.sAMAccountName.toLower()).jpg”}},@{n=’guid’;e={$employee.ObjectGUID.toString()}}

Oh so simple and I have no idea why I’d not thought of doing such an obvious thing before.  I guess for complex objects the command line might get unhealthy long but then you can use the other little trick of creating a variable to hold the select-object statement, e.g.

$objectProperties = (

$photosToUpdate += “” | select $objectProperties



This is a function I got from the internet ( http://www.powertheshell.com/autodetecting-csv-delimiter/ ) and modified a little can be used to determine the delimiter used in a CSV file.

It does this in such a simple way you’ll be wondering why you didn’t think of it yourself.  It parses each line and counts the obvious ‘candidates’ for a delimiter and then after each line checks if the ‘usual suspects’ ( great film by the way and  if you haven’t seen it yet the what have you been watching, neighbors, come on this should be on everyone’s watch list)  then you can use the appropriate –delmiter switch on the import-command.

A CSV file will use different delimiters depending on the country you live in apparently.  so the C in Comma Separated Values clearly does not stand for comma 🙂

In the UK it’s not uncommon to see PIPE delimited file i.e. using | instead of , probably because when you open it with excel it doesn’t  mangle the ‘integer looking’ values in the row, e.g. a telephone number like this +44 20 7123 1234 canget converted into a numeric value including an exponential and don’t get me started on how Excel mangles date formats depending on the workstation local, but we should all blame ourselves for allowing each country to ‘choose’ a date format.  Why can’t we all agree one way or the other which parts of a date are which, i.e is this the 12th of the 1st and of which month, 12/1/2018 – you are either slightly early or exceptionally late to the party!

12/1/2018 – you are either slightly early or exceptionally late to the party! Neither is a good place to be.

Anyway,. let talk about CSV files.  This handy function should be able to work out what character is being used as a delimiter and then you can use this information in an import-csv file to ensure that you import the data correctly.

e.g. Import-Csv –Delimiter  (Get-CSVDelimiter c:\temp\mycsvfile.csv )

Although I’d put a little more error checking around it if you are planning on using this in your scripts.

Like all code I get from the internet I try to analyse how it does what it does and in doing this I saw some nice ideas around formatting the output.  This blog post on PSCustom objects will tell you all ( hopefully everything ) you need to know about PSCustom objects.  I particularly liked the elegant solutions on controlling the output attributes, something we take for granted when we output an objects contents to screen.

Haven’t you noticed that poweshell does not always show every attribute of an object – try using ‘| select * | fl’ some time to see the difference.  I wish the Quest commandlets would do this as those commandlets return a lot of attributes which is why they tend to be a lot slower than the AD commandlet equivalents.


function Get-CSVDelimiter { Version 1.00
param (
  # Path name to CSV file
  # can be submitted as string or as File object
begin {
  # list of ascii codes that typically cannot be a delimiter: 0-9, A-Z, a-z, and space:
  $excluded = ([Int][Char]'0'..[Int][Char]'9') + ([Int][Char]'A'..[Int][Char]'Z') + ([Int][Char]'a'..[Int][Char]'z')  + 32
  function Get-DelimitersFromLine {
   $quoted = $false
   $result = @{}
   # examine line character by character
   foreach($char in $line.ToCharArray()) {
    # if a double-quote is detected, toggle quoting flag - ignores everything inside a double quoted string
    if ($char -eq '"') { $quoted = -not $quoted } elseif ($quoted -eq $false) { if ($excluded -notcontains [Int]$char) {  $result.$([Int]$char) ++  } }
   } # foreach($char in $line.ToCharArray()) {
  } # Get-DelimitersFromLine
} # Begin
 process {
  # initialize variables
  $oldcandidates = $null
  # examine each line in CSV file:
  $file = [System.IO.File]::OpenText($Path)
  $lines = 0
  $foundDelimiter = $false
  While (-not $file.EndOfStream) {
   $line = $file.ReadLine()
   if ( $foundDelimiter ) { continue }
   # examine current line and get possible delimiters:
   $candidates = Get-DelimitersFromLine $line
   # if this is the first line, skip analysis
   if ($oldcandidates -eq $null) {
    # if first line starts with "#", ignore
    if (-not $line.StartsWith('#')) { $oldcandidates = $candidates }
   } # else, identify ascii codes that have the same frequency in both this line and the previous line, and store in hash table $new:
   else {
    $new = @{}
    $keys = $oldcandidates.Keys
    foreach($key in $keys) {
     if ($candidates.$key -eq $oldcandidates.$key) {
      $new.$key = $candidates.$key
    $oldcandidates = $new
    # if only 1 possible delimiter is left, we are done
    # exit loop, no necessity to examine the remaining lines:
    if ($oldcandidates.keys.count -lt 2) {
     $foundDelimiter = $true
  } # While (-not $file.EndOfStream) {
  # create return object
#  $rv = New-Object PSObject | Select-Object -Property Name, Path, Delimiter, FriendlyName, ASCII, Columns, Rows, Status
#  $rv.Rows = $lines
#  $rv.Path = $Path
#  $rv.Name = Split-Path -Path $Path -Leaf
  # no keys found:
  if ($oldcandidates.keys.count -eq 0) {
   $rvStatus = 'No delimiter found'
  } # exactly one key found, good:
  elseif ($oldcandidates.keys.count -eq 1) {
   $ascii = $oldcandidates.keys | ForEach-Object { $_ }
   $rvASCII = $ascii
   # convert ascii to real character:
   $rvDelimiter = [String][Char]$ascii
   # number of Columns is frequency of delimiter plus 1:
   $rvColumns = $oldcandidates.$ascii + 1
   # add friendly names for the most common delimiter types:
   switch ($ascii) {
      9 { $rvFriendlyName = 'TAB'       }
     43 { $rvFriendlyName = "Plus"      }
     44 { $rvFriendlyName = 'Comma'     }
     58 { $rvFriendlyName = 'colon'     }
     59 { $rvFriendlyName = 'Semicolon' }
    124 { $rvFriendlyName = 'Pipe'      } 
   $rvStatus = 'Found'
  } # elseif ($oldcandidates.keys.count -eq 1) {
  # ambiguous delimiters detected, list ambiguous delimiters
  else {
   # convert delimiter ascii keys in a comma-separated list of quoted characters:
   $delimiters = (($oldcandidates.keys | ForEach-Object { ('"{0}"' -f [String][Char]$_) }) -join ',')
   $rvStatus =  "Ambiguous separator keys: $delimiters"
  $rv = [PSCustomObject]@{
    'PSTypeName'   = 'My.DelimiterTypeObject'
    'FileName'      = $(Split-Path -Path $Path -Leaf)
    'Path'          = $Path
    'Columns'       = $rvColumns
    'Rows'          = $lines
    'DelimiterName' = $rvFriendlyName
    'Delimiter'     =  $rvDelimiter
    'ASCII'         = $rvASCII
    'Status'        = $rvStatus
  # ETS: set default visible properties on return object (applies to PS 3.0 only, no effect in PS 2.0):
  [String[]]$properties = 'FileName','DelimiterName','Columns','Rows'
  [System.Management.Automation.PSMemberInfo[]]$PSStandardMembers = New-Object System.Management.Automation.PSPropertySet DefaultDisplayPropertySet,$properties
  $rv | Add-Member -MemberType MemberSet -Name PSStandardMembers -Value $PSStandardMembers
  # return result:
} # process {
} #                     Get-CSVDelimiter Version 1.00

A simple way of adding a menu to a script

I was actually showing someone at work how easy it was to access SQL using PowerShell
and as I was walking my colleague through the code another team member scoffed at me
as I used the words “iterate through the data rows returned to print them out to screen”.

Ahh it’s hard not to eaves drop when in the office, especially if the topic is one you
are familiar with and even harder not to comment after overhearing something,
I do it all the time 🙂

The following discussion was that there are better ways of displaying the returned SQL
table data than ‘iterating’ through the rows and using write-host to print to screen.

I thought immediately of using ‘Out-Gridview’ perhaps, so I did a quick google to see
what other people were doing and came across this interesting idea using ‘Out-Gridview’ 
to create a simple user menu.

The blog contained this simple bit of code:

$Menu = [ordered]@{
  1 = 'Do something'
  2 = 'Do this instead'
  3 = 'Do whatever you  want'

$Result = $Menu | Out-GridView -PassThru  -Title 'Make a  selection'
Switch ($Result)  {
  {$Result.Name -eq 1} {'Do something'}
  {$Result.Name -eq 2} {'Do this instead'}
  {$Result.Name -eq 3} {'Do whatever you  want'}

This is such a clever idea, so I wanted to convert it to a function that I could use
in some future scripts and of course blog about it.

To convert a script into a function ( and this is just a quick conversion as I’ve not added any parameter help etc. ) I do the following:

I usually write the function in place just above the original code so I  can run some tests through the two sets of code side by side.  It also means I can see both the original code and my function right next to each other to confirm I translated it properly.

Lets start then by naming the function, using an approved verb.

Function Get-MenuItem {


Now add a parameter block but what do I need to parameterise?  The point of a function is to reuse code.  Hard coding the menu items to choose from in the function might be a little limiting but you could use a default value for the parameters if you had a regular menu that you wanted to use.  I’ve not done that here by the way.

To add a default value just put an equals sign after the parameter name and set a value. When calling the function the default value will be used if the parameter is not provided on the calling command line.

Choosing what to convert to a parameter

Function Get-MenuItem {
 param (

If you look at the section of code you are converting and identify the things that either are already variables or are hard coded lines of code you might want to set at run time rather than have them hard coded in your script.

In this case the $menu variable holds the menu items to choose so it’s this that I’ll use as a parameter.

$menu is of type [System.Collections.Specialized.OrderedDictionary] – see the scripting guy blog for details on this new variable type found in Powershell 3.0

I’ve called my parameter $menuItems but it’s not a [System.Collections.Specialized.OrderedDictionary] it’s an array variable, we will
build the OrderedDictionary inside the function.

Another reason to use a function is to ‘hide’ the complexity of a task and simplify the main section of the script.

We can call the script using a one liner:

Get-MenuItem @('Do something','Do whatever you want','Do this instead')

I used a loop to build the OrderedDictionary using the $menuItems parameter to provide the values.  I could add code to sort this data before I put the array elements into the OrderedDictionary – you can see how it’s easy to get carried away and add more and more functionality.  Lets resist that temptation for now, we just need to get it working first and add functionality later if we have time.

 $Menu  = [ordered]@{} # intialises the OrderedDictionary
 $items = 1 # used to create a unique key 1,2,3 etc.
 ForEach ( $menuItems in $menuItems ) {
  $Menu.Add($items,$menuItems) # adds the menu item to the OrderedDictionary

At this point I could have finished the function with the remaining code from the original script

$Result = $Menu | Out-GridView -PassThru  -Title 'Make a  selection'
Switch ($Result)  {
  {$Result.Name -eq 1} {'Do something'}
  {$Result.Name -eq 2} {'Do this instead'}
  {$Result.Name -eq 3} {'Do whatever you  want'}

But wait how is that dynamic and set at run time?

It’s not so I need find a way of writing a generic select statement. I ran the script with a break point at the start of the switch statement so I could inspect the variable and I realised that the switch statement was redundant completely.

$result was a ‘DictionaryEntry‘ e.g.

Name     Value
—-            —–
2               Do this instead

So rather than use a switch statement that prints the same text that held in $result.Value I can just return $result.Value – easy!

I was done at this point but before moving onto the next challenge I added some code to make the function a little more resilient to errors.

The user could select more than one item, not realise they need to click OK or perhaps they click the close button.  To handle these scenarios I added this code:

 $selectionMade = $false # used to break out of the do loop when a valid choice is made by the user
 do {
  $Result = $Menu | Out-GridView -PassThru  -Title 'Make a  selection and click OK'
  if ( ( $Result.Count -eq 1 ) -or ( $Result -eq $null ) ) { # only 1 item selected so we can break out of the loop
   $selectionMade = $true
  elseif ( $Result.Count -gt 1 ) { # more than one item selected so we need to tell the user to select just one option
   [System.Windows.MessageBox]::Show('You cannot select multiple choices - please select 1 menu option') | Out-Null
 } until ( $selectionMade )
 if ( $Result -eq $null ) { # this use case is when the user pressed close or cancel we'll return $null
  [System.Windows.MessageBox]::Show('No Choice was made so cannot continue - Quitting script') | Out-Null

I also used this quick way of informing the user whats happening with a message box:

[System.Windows.MessageBox]::Show('No Choice was made so cannot continue')

And that’s it! ( for now anyway ) – here is the full function:

Function Get-MenuItem {
 param (
 $Menu = [ordered]@{}
 $items = 1
 ForEach ( $menuItems in $menuItems ) {
 $selectionMade = $false
 do {
  $Result = $Menu | Out-GridView -PassThru  -Title 'Make a  selection and click OK'
  if ( ( $Result.Count -eq 1 ) -or ( $Result -eq $null ) ) {
   $selectionMade = $true
  elseif ( $Result.Count -gt 1 ) {
   [System.Windows.MessageBox]::Show('You cannot select multiple choices - please select 1 menu option') | Out-Null
 } until ( $selectionMade )
 if ( $Result -eq $null ) {
  [System.Windows.MessageBox]::Show('No Choice was made so cannot continue - Quitting script')  | Out-Null
 Return $Result.value

Get-MenuItem  @('Do something','Do whatever you want','Do this instead')


Finding your Exchange Servers

For similar reason I mentioned in this post ‘How to locate your ARS servers using the service connection point’ I wrote a function to find my exchange servers.

It’s never a good idea to hard code stuff into your scripts as these make your code less portable and also you are at the mercy of environment changes.  Your scripts will fail when the hard coded variable value no longer matches the server or object your are trying to connect to.

By default the function returns exchange servers from the local site of the machine running the script. if the $InSiteOnly parameter is specified the function only returns exchange servers in the local / specified site unless there are no servers in that site when it will return servers from all sites.

Function Get-ExchangeServers { # Version 2.00
 param (
  [parameter(Mandatory=$false,Position=1,HelpMessage='Returns the exchange server names from the specified site in preference to any other site')][string]$ADSiteName,
 [parameter(Mandatory=$false,Position=2,HelpMessage='When present will only return exchange servers from the local / specified site unless the site does not contain more than "$maxNumberOfServers" exchange servers then it will return servers from all sites in addition to the specified site')][switch]$InSiteOnly,
[parameter(Mandatory=$false,Position=0,HelpMessage='The maximum number of exchange servers to return, it will by default return local / specified site servers at the top of the list')][ValidateRange(1,[int]::MaxValue)]
 [parameter(Mandatory=$false,Position=3,HelpMessage='Returns the specified exchange server version only from the specified site in preference to any other site')] [ValidateSet("2013","2016")][string]$Version 
 if ( $Version ) {
  switch ( $Version ) {
   "2013" { $VersionString = "Version 15.0" }
   "2016" { $VersionString = "Version 15.1" }
 if ( $ADSiteName ) { # AD Site name specified so get the site DN  $computerSiteDN = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Sites  | Where-Object { $_.name -eq $ADSiteName } | Select-Object @{name="DN";expression={$_.GetDirectoryEntry().distinguishedName}} | Select-Object -ExpandProperty DN }
 else { 
$ADSiteName = [System.DirectoryServices.ActiveDirectory.ActiveDirectorySite]::GetComputerSite().GetDirectoryEntry().name
 if ( $computerSiteDN -eq $null ) { 
# AD Site name not specified or not found so get the local machines site
  $computerSiteDN = [System.DirectoryServices.ActiveDirectory.ActiveDirectorySite]::GetComputerSite().GetDirectoryEntry().distinguishedName
 if ( $computerSiteDN -eq $null ) {
  Throw "FATAL ERROR: Unable to get the AD site DN"
 $returnData = @() # ensures that an array of server names is always returned
 # search the site for exchange servers
 $SearchTool = <strong>New-Object</strong> DirectoryServices.DirectorySearcher([ADSI]('LDAP://' + ([ADSI]'LDAP://RootDse').configurationNamingContext))

 $SearchTool.Filter = "(objectClass=msExchExchangeServer)"
 $ExchangeServers = $SearchTool.FindAll()
 # get the exchange servers that are in the local / specified AD Site
 $exchangeServersInSite    = @($ExchangeServers | <strong>Where-Object</strong> { $_.Properties.msexchserversite -eq $computerSiteDN })
 if ( $VersionString ) {
  $exchangeServersInSite    = @($exchangeServersInSite  | Where-Object { $_.Properties.serialnumber.substring(0,12) -eq $VersionString })
 $exchangeServersInSite = @($exchangeServersInSite | Select-Object</strong> @{name="name";expression={$_.properties.name}} | Select-Object  `
-ExpandProperty  name)
 if ( $exchangeServersInSite.count -le 0 ) { # no servers found in local / specified AD site so lets get all exchange servers in all sites
  $exchangeServersInSite = @($ExchangeServers | <strong>Where-Object</strong> { $_.Properties.msexchserversite -ne $computerSiteDN } )
  if ( $VersionString ) {
   $exchangeServersInSite    = @($exchangeServersInSite  | <strong>Where-Object</strong> { $_.Properties.serialnumber.substring(0,12) -eq $VersionString })}
  $exchangeServersInSite = @($exchangeServersInSite | Select-Object @{name="name";expression={$_.properties.name}} | Select-Object `
-ExpandProperty</em>  name)}
 if ( $exchangeServersInSite.count -le 0 ) {
  Throw "FATAL ERROR: Unable to find any Exchange servers"
 if ( $InSiteOnly ) {
  # Return just the exchange servers we have so far unless the site specified 
  # has no servers then return servers from all sites
  $returnData += $exchangeServersInSite | Get-Random -Count $(if ($exchangeServersInSite.count -le $maxNumberOfServers ) { $exchangeServersInSite.count } else { $maxNumberOfServers })}
 else {
  if ( $maxNumberOfServers -le $exchangeServersInSite.count ) { 
# number of servers requested can be delivered from the in site server list so 
# lets return them
 $returnData += $exchangeServersInSite | Get-Random -Count $maxNumberOfServers }
  else { 
# we need more servers so lets add in additional servers from the other sites
   $exchangeServersNotInSite = @($ExchangeServers | Where-Object { $_.Properties.msexchserversite -ne $computerSiteDN } )
   if ( $VersionString ) {
    $exchangeServersNotInSite    = @($exchangeServersNotInSite  | Where-Object { $_.Properties.serialnumber.substring(0,12) -eq $VersionString })
   $exchangeServersNotInSite = @($exchangeServersNotInSite | Select-Object @{name="name";expression={$_.properties.name}} | Select-Object -ExpandProperty</em>  name)

   $returnData += $($exchangeServersInSite + $( $exchangeServersNotInSite | Get-Random -Count</em> $(if ($exchangeServersNotInSite.count -le $($maxNumberOfServers - $exchangeServersInSite.count) ) { $exchangeServersNotInSite.count } else { $($maxNumberOfServers - $exchangeServersInSite.count) })))
 if ( $ReturnData.count -le 0 ) {
  Write-Error "ERROR: No exchange Servers Returned for site: '$ADSiteName'"
 Return ,$returnData
}            # Get-ExchangeServers            Version 2.00


ARS bugs and annoyances – Managed Unit not sorted

In reshaping my deployment of ARS 7 I’ve made extensive use of the dynamic objects ARS provides, i.e. Managed Units and Dynamic groups.  Both of these are defined by a set of membership rules.  In doing so I came across one limitation ( or bug ) and one annoyance.  I’d like these to be ‘Feature Requests’ for the next version of ARS.

  • The bug – objects in a Managed Unit are not sorted
  • The annoyance – You cannot rename the membership rules in a dynamic object

The bug ( although I suspect Quest / Dell / Quest / One Identity, never thought about this ) is that if I use a Custom Include Query that displays the OUs below a target ‘searchRoot’ the OUs are not displaed in any order and there is no control over this, e.g. If I target a users OU and under this OU there is an OU for each country the MU displays the countries in a random order.  If you want to try this out  use this query as a membership rule ‘(&(objectCategory=organizationalUnit)(street=DisplayOUInMU))’ where I tag the OUs street attribute with either ‘DisplayOUInMU’ or ‘Don’tDisplayInMU’  I also have a 3rd setting ‘DisplayObjectsinMU’ which allows me to also display the objects in the OU in the MU.

I think that the MU should by default always sort the objects it displays in alphabetical order.  In case you were wondering why I don’t just add the OUs implicitly there are two reasons, one, there are a lot of them and two, what if we add another country OU, I wanted to make the MU automatically pick it up.  I have a fix for this by the way, add the dynamic rule but also add the explicit OU objects that already exist in the OU that you want to display.  Any new OUs will get the correct ‘street’ attribute value as I use an ARS policy to update the street attributed based on the parent OU.  The new OUs won’t be sorted so you will need to go and update the MU membership rules although now I am writing this I could write an ARS Policy script to automate this but I’ll wait a little in case One Identiry decide to add this feature / bug fix to the next ARS version.

The annoyance – You cannot rename the membership rules in a dynamic object.  This should be an easy thing to allow in the same way as you can rename the PVG rules in an ARS Policy.  I have dynmaic objects with 3 of even 4 ‘custom searches’ wouldn’t it be nice to be able to give these a meaningful name so you don’t have to open each one when you need to modify it?

How to locate your ARS servers using the service connection point

In case you need to know which servers to connect to using Connect-QADService and you don’t want your script to have hard coded domain information in so the code is more portable, i.e. will run in any environment I came up with a script to locate the available ARS servers using the service connection points published into AD by the ARS servers.

Quest / Dell / Quset / On Identity still havent update the path and use the products original ( Enterprise Directory Manager) name so the SCP are located here: CN=Enterprise Directory Manager,CN=Aelita,CN=System,<Domain DN> Version 6 didn’t have a port number but ARS 7 did.  I don’t know if this is unique to my environment as I was running the two ( ARS 6.9 and 7.x ) services in parrallel or if this was a hard coded change from ARS 7.   If you use this script and find the port is different in your environment let me know.

Call the function like this 

 $ARSServerFQDN = Get-ARS7Servers | select -First 1 

or without the select statement if you want to see all the servers.  You can then use this to control which server you are connecting to 

 connect-QADService -service $ARSServerFQDN -proxy
Function Get-ARS7Servers { # Version 2.00
 $searchRoot = "CN=Enterprise Directory Manager,CN=Aelita,CN=System,$([System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().GetDirectoryEntry() | select -ExpandProperty DistinguishedName)" 
 Get-QADObject  -SearchRoot  $searchRoot -Type serviceConnectionPoint | SELECT  -ExpandProperty Name | Where { $_.indexOf(":17228") -gt 0 } | Select @{name='serverName';expression={$_.split(":")[0]}} | select -ExpandProperty serverName
}            # Get-ARS7Servers                Version 2.00