Showing the script line number in a debug message

I came across the tip on another site and thought it worth putting here too.  Often when writing debug messages or even sending an email alert when one of my production scripts fails I send the line number if failed on.  The easy way to do this is to hard code the line into the message but then when you subsequently edit the script the line number is changed so if you want that line number to be right you need to calculate it dynamically.

All you need to is create a function and then call it…then you can use the returned value in your messages:

function Get-CurrentLineNumber {

try { Bad-Command }
catch {
write-host “Script Failed at line No: $(Get-CurrentLineNumber)”

Here’s the link to the original post


Pasting Values in Excel


This is something I rediscovered this morning and thought it was worth a quick blog.  How often have you wanted to do something and you google lots of examples but when you read them you find that the code shown uses “constants” which are listed as the well known constant name, e.g. I wanted to paste the values from some cells in a worksheet to another cell in an Excel workbook.

Googling revealed examples but most of them refer to using an excel constant in this case $excel.pasteSpecial(xlPasteValues) but of course that code doesn’t work in powershell.  I initially solved it but finding a post that told me the value of xlPasteValues was in fact -4163.  So:

$range.PasteSpecial(4163) | Out-Null

should do the trick but how memorable is that!

Yesterday when I was googling this I didn’t find any useful links but this morning researching for this post I hit upon this one which is actually pretty useful as it tells you all the values of the xlPasteTypes :

Anyway the tip I’m blogging about here is how to get these values into your script in a readable and memorable way.  It’s something I’d already done a while back in my Excel functions ( not sure not if I ever got around to publishing them, I should really if I haven’t as I use them all the time, in fact so much I converted them into a module so if people – like anyone reads this – are interested let me know and I’ll spend some time publishing them, maybe a function at a time).

Anyway in my excel function module I use these lines of code , the last one being the new one added today:

$lineStyles = “” -as [type]
$colorIndexs = “” -as [type]
$borderWeights = “” -as [type]
$chartTypes = “” -as [type]
$pasteTypes = “microsoft.Office.Interop.Excel.XlPasteType” -as [type]

# Enumeration types are used to tell Excel which values are allowed
# for specific types of options. As an example, xlLineStyle enumeration
# is used to determine the kind of line to draw: double, dashed, and so on.
# These enumeration values are documented on MSDN.
# To make the code easier to read, I  created shortcut aliases for each
# of the four enumeration types we will be using.
# Essentially, we’re casting a string that represents the name of the
# enumeration to a [type]. This technique is actually a pretty cool trick:

I even wrote some lines in my code to remind me what these were and how to use them – but I’d forgotten all about them.

# added “Microsoft.Office.Interop.Excel” in as some sites implied that you have to have it
# but my code to center a cell worked fine without it – but when you search for help on Excel
# you often see people using the constants so if by loading this I can use the constants too making the code look cleaner.
# e.g. $sheet.Cells.item(1,1).HorizontalAlignment = $xlConstants::xlCenter
# rather than $sheet.Cells.item(1,1).HorizontalAlignment = -4108
# $($xlConstants::xlCenter).value__ returns -4108

for this example If I want to cut and paste the values from one set of cells I do the following:

$workbook = $(New-Object -comobject excel.application)
$workbook.workbooks.Open(“c:\Temp\test123.xlsx”) | Out-Null
$worksheet = $workbook.activesheet
$workbook.visible = $true
$cellReference = “A1:A4”
$range = $worksheet.range($cellReference)
$range.Copy() | out-null
$range = $worksheet.range(“B1”)
#$range.PasteSpecial(-4163) | Out-Null # instead of this meaningless bit of code use the constants
$range.PasteSpecial([Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteValues) | Out-Null

If you use Powergui you can expand the variables and see what’s inside them 🙂


Bit Masking and Password Not Required

An recent ADRAP highlighted lots of accounts in my AD had a flag set to allow blank password.  Now you would assume that if you set a GPO to enforce a password policy which includes length, complexity, frequency of change ( both min and max ) that you would not be able to set a blank password.  It seems that’s not entirely true.  Bit 6 if set means a blank password can be used bypassing any domain policy.  My next post will be how I detect this using PowerShell and most importantly how to fix it although with this post you should have all the amo you need to shoot yourself in the foot 🙂 BE CAREFUL with any script that will update the userAccountControl!

The userAccountControl attribute is a Bit Mask.  What this means is that each bit is used to determine some element of, well user account control :-), e.g does the password expire, is the account enabled, can the user change the password.  Here’s the full list of things it controls.


I’ve seen lots of posts on updating the userAccountControl that go like this – just add 32 to the userAccountControl.  NO! NO! NO! don’t do it.  yes it may be adding 32 to the userAccountControl but what if the bit is already set.  Where possible use native command line switches to manipulate the userAccountControl attribute. e.g. enabling or disabling an account.

You could do this by manipulating the userAccountControl attribute directly.  You will see LDAP queries using (userAccountControl:1.2.840.113556.1.4.803:=2) – what’s that about?  That is simply using a BINARY AND against the userAccountControl and seeing if it = 2 – look in the table above, 2 means disabled but I’m digressing.

So you could just add two to the value to disable an account but if the account were already enabled then the userAccountControl will now be set to 4 or 100 in binary and look bit 2 is 0 so the account is now enabled.  What this means is you always need to check the value before updating it.  That’s easy when you can filter your return set using the LDAP filter or perhaps a command line switch to return only enabled users.  Now it’s safe to add 2 to the value.  Typically userAccount control attribute values will be 512 for a NORMAL enabled account 514 for a disabled one.  What if it were 576?  That’s 512 so an enabled user and 64 for password can’t change.  What about 528 or 8389136?  Lots of tools will show these in HEX as well so adding to the confusion.  This means I need to check for all possible values before I can add the bit number I need.  Get it wrong and strange things can happen, e.g. lets say I want to set the flag to allow a blank password – so add 32 yes?  What if it were already enabled then when I add 32 the userAccountControl is now 64 which is password can’t change.

What’s the solution then?  Binary logic.  Have a read of this wiki  – some of it like the top part had me thinking did I really forget that much Math?  further down you get to see the different binary operations and how they are used.  What we are looking for is a way to turn on or off specific bits of the bit mask.  Keeping with the example of bit 6 for the blank password use a binary exclusive OR of -bxor to clear the bit or a binary OR to set it.  The beauty of this is I don’t care what the current value is as the binary operation only operated on the bit I’m interested in.

This will clear the flag ( replace -bxor with bor to set the flag ).

Set-QADUser -Connection $connection -Identity $user -ObjectAttributes @{userAccountControl=$user.userAccountControl -bxor 32}

Using admin credentials

Lots of powershell commands will allow the use of alternate ( admin ) credentials. To capture these you can simply use the get-credentials cmdlet. In a production script you might want to make this a little more robust.

I regularly do this in my scripts, especially the ones that use ARS. Today I finally got around to writing a function to capture these so that I can reuse this in other scripts that I will write in the future.

I reused a function I found on the internet, show-popup which I modified just slightly to make it more user friendly but otherwise it’s unchanged. Why write my own when there is so much good stuff out there! this allows me to popup questions and responses on screen in your face rather than at the command prompt.

The script below will check the username is prefixed by the domain name and that a password was entered. Because Connect-QADService seems to take forever to timeout if the username is invalid I check the username is valid by using get-qaduser before finally trying a connection.

The script returns a connection object which you can assign to a variable and then use the -connection switch on all subsequent cmdlets.

I hope someone else finds this useful!

function Get-Connection {
param ( $domain )
Function Show-PopUp {

[parameter(Mandatory=$true, ValueFromPipeLine=$true)][Alias(“Msg”)][string]$Message,
[parameter(Mandatory=$false, ValueFromPipeLine=$false)][Alias(“Ttl”)][string]$Title = $null,
[parameter(Mandatory=$false, ValueFromPipeLine=$false)][Alias(“Duration”)][int]$TimeOut = 0,
[parameter(Mandatory=$false, ValueFromPipeLine=$false)][Alias(“RI”)][switch]$ReturnInteger,
[parameter(Mandatory=$false, ValueFromPipeLine=$false)][Alias(“But”,”BS”)][ValidateSet( “OK”, “OKCancel”, “AbortRetryIgnore”, “YesNoCancel” , “YesNo” , “RetryCancel”)][string]$ButtonSet = “OK”,
[parameter(Mandatory=$false, ValueFromPipeLine=$false)][Alias(“ICO”)][ValidateSet( “None”, “Critical”, “Question”, “Exclamation” , “Information” )][string]$IconType = “None”
$ButtonSets = “OK”, “OKCancel”, “AbortRetryIgnore”, “YesNoCancel” , “YesNo” , “RetryCancel”
#$ButtonSets = “OK”, “OC”, “ARI”, “YNC” , “YN” , “RetryCancel”
$IconTypes = “None”, “Critical”, “Question”, “Exclamation” , “Information”
$IconVals = 0,16,32,48,64
if((Get-Host).Version.Major -ge 3){
$Button = $ButtonSets.IndexOf($ButtonSet)
$Icon = $IconVals[$IconTypes.IndexOf($IconType)]
$ButtonSets|ForEach-Object -Begin{$Button = 0;$idx=0} -Process{ if($_.Equals($ButtonSet)){$Button = $idx };$idx++ }
$IconTypes |ForEach-Object -Begin{$Icon = 0;$idx=0} -Process{ if($_.Equals($IconType) ){$Icon = $IconVals[$idx]};$idx++ }
$objShell = New-Object -com “Wscript.Shell”
if ( $ReturnInteger ) { Return $objShell.Popup($Message,$TimeOut,$Title,$Button+$Icon)}
else {
switch ($objShell.Popup($Message,$TimeOut,$Title,$Button+$Icon)) {
-1 { return “TimeOut” }
1 { return “OK” }
2 { return “Cancel” }
3 { return “Abort” }
4 { return “Retry” }
5 { return “Ignore” }
6 { return “Yes” }
7 { return “No” }
} # switch ($retInt)
$loop = $true
$retry = $false
$getCreds = $false
$domainPrefix = “$domain\”
do {
if ( ( $creds ) -and ( $retry -eq $false ) ) {
$Answer = Show-PopUp -Message “Logged on as ‘$($creds.UserName)’ Use these credentials or Cancel run” -Title “Logon” -ButtonSet “YesNoCancel” -IconType “Information”
if ( $Answer -eq “Cancel” ) { exit }
elseif ( $Answer -eq “No” ) { $creds = Get-Credential -UserName $creds.UserName -Message “Enter Admin Credentials using $domainPrefix” }
else {
try {
if ( $creds ) { $username = $creds.UserName } else { $username = $domainPrefix }
$creds = Get-Credential -UserName $username -Message “Enter Admin Credentials using $domainPrefix”
catch {
Show-PopUp -Message “User Clicked ‘Cancel’ Script Ending” -Title “Thats all folks!” -IconType “Information” -TimeOut 5
if ( $creds -eq $null ) {
Show-PopUp -Message “User Clicked ‘Cancel’ Script Ending” -Title “Thats all folks!” -IconType “Information” -TimeOut 5
# check we got some credentials and they look OK
if ( $creds.username.toLower().IndexOf($domainPrefix.ToLower()) -lt 0 ) {
Remove-Variable creds
$retry = $true
show-PopUp -Message “UserName must be prefixed with $domainPrefix” -Title “User Name Blank” -IconType “Information” -TimeOut 5
if ( $creds.GetNetworkCredential().Password -eq “” ) {
$retry = $true
show-PopUp -Message “Password Cannot be BLANK” -Title “Password Blank!” -IconType “Information” -TimeOut 5
# check the username is a valid AD user as the connect-QADservice takes ages to time out!
if ( $(Get-QADUser $creds.username) -eq $null ) {
show-PopUp -Message “Unable to authenticate to AD using : ‘$($creds.username)'” -Title “User Account NOT FOUND!” -IconType “Information”
$retry = $true
try {
Write-Host “Connecting to ARS please wait….” -ForegroundColor Green -BackgroundColor Black
$connection = Connect-QADService -Proxy -Credential $creds
catch {
if ( “Cancel” -eq $(Show-PopUp -Message “ERROR 1 : Failed to authenticate to ARS using: ‘$($creds.UserName)'” -Title “Connection Failure!” -ButtonSet “RetryCancel” -IconType “Critical” ) ) {
Remove-Variable creds
else {
$retry = $true
# check we got a valid connection to ARS
if ( $connection.ManagedDomains.Count -gt 0 ) { $loop = $false }
else {
if ( “Cancel” -eq $(Show-PopUp -Message “ERROR 2 : Failed to connect to get managed domains using: ‘$($creds.UserName)'” -Title “Connection Failure!” -ButtonSet RetryCancel -IconType “Critical”) ) {
Remove-Variable creds
else {
$retry = $true
} while ( $loop )
return $connection

$conn = Get-Connection “”

Type casting

For an actor type casting might be a bad thing but when scripting it may be the only way to go in some cases.  I like to enable the support teams the ability to change the script input without having access to the script ( a bad thing to allow, trust me – I’ll cover that in the next post now I thought of it!).

I often do this by allowing the support team to modify a config file which will be located on a share.  In this particular instance my script would use a CSV file to read in a list of groups to process.  So we could see how that file changed over time I told the team to copy the current file and increment the file name by one, e.g the file name was groupList_v1.csv and they copy it and then call it v2.csv and edit the new file.    This worked great until they got to double figures and then my script stopped using the latest file.

I chose this method by the way as the file dates can not be relied upon as the last edit might not be in the file with the largest version number.

Any way a quick look at the script revealed the problem.

I was grabbing the version number by using the substring method


Yes my script will break when they get to version 100.   $ADGroupListFile contains file objects and I get the filename using .BaseName then I want to find the starting position of the _v characters and grab the 2 numbers after that.

The problem is that powershell will treat that as a string.  I hadn’t thought about this when I wrote it and although I was comparing it with an integer powershell seems to convert the integer I had defined to a string for a comparison.  This worked OK until of course we hit double figures.  Then it kept selecting version 9 as the highest.

The solution was to force powershell to use the variables as integers by telling it what the variable types were, e.g. using [int] in front of the variable.

[int]$ADGroupListFileVersion = 0
ForEach ( $ADGroupsListFile in $ADGroupsListFiles ) {
# get file version No. and take the latest one – there should only be one file but just in case
 Write-Debug $ADGroupsListFile.BaseName
 Write-Debug $ADGroupListFileVersion
if ( [int]$ADGroupsListFile.BaseName.substring($ADGroupsListFile.BaseName.indexOf(“_v”)+2) -gt        [int]$ADGroupListFileVersion ) {
[int]$ADGroupListFileVersion = `    [int]$ADGroupsListFile.BaseName.substring($ADGroupsListFile.BaseName.indexOf(“_v”,$ordinalIgnorecase)+2)

Listing your parameters

Have you ever wanted to work out which commands were included on the command line? There are a couple of cmdlets that will let you see the parameters that you defined as well as the ‘builtin’ ones like ErrorAction and Debug etc.

$PSBoundParameters is only useful when you want to get to the parameters used on the commandline

$PSBoundParameters does not tell us anything about the script parameters that are not provided at the command line so we need to find something with all the parameters.  The obvious choice is $MyInvocation.

$MyInvocation has all the information you need on your script or function it’s well worth the effort investigating it’s properties

$MyInvocation has both a BoundParameters ( the ones you used on the command line ) and a list of the defined parameters in $MyInvocation.MyCommand.Parameters. Both of these store the parameters as a key value pair.

Here’s a little demo script I wrote to investigate these, the initial script idea came from here

I instantiated an array object to hold the builtin parameters because I was only really interested in the one’s I had defined in the script. I used the array to determine if I was going to ignore the current parameter being enumerated. Calling the script with the following parameters: -Mandatory2 C -Mandatory1 “c:\temp\” -erroraction stop
you get the following output:

The script has 5 parameters defined in a param statement
3 parameters are provided on the cmdline:
‘Mandatory2’ = ‘C’
‘Mandatory1’ = ‘c:\temp\’
‘ErrorAction’ = ‘Stop’
These parameters have been configured with default values:
‘Optional1’ = ‘Foo’
‘Optional2’ = ‘Bar’
‘Debug’ = ‘True’
and these have no value set:

Here’s the code example used.

[string] $Mandatory1,
[ValidateSet("A","B","C", "D")]
[string] $Mandatory2,
[string] $Optional1 = "Foo",
[string] $Optional2 = "Bar",
[string] $Optional3
$builtinParameters = @("ErrorAction","WarningAction","Verbose","ErrorVariable","WarningVariable","OutVariable","OutBuffer","Debug")
$totalParameterCount = $($MyInvocation.MyCommand.Parameters.count)
$parameterCount = 0 
($MyInvocation.MyCommand.Parameters ).Keys | ForEach {
   if ( $builtinParameters -notcontains $_ ) {
$boundParameters = @()
Write-Host "The script has $parameterCount parameters defined in a param statement"
Write-Host "$($MyInvocation.BoundParameters.count) parameters are provided on the cmdline:"
$MyInvocation.BoundParameters.keys | ForEach {
 Write-Host "'$($_)' = '$($PSBoundParameters.Item($_))'"
Write-Host "These parameters have been configured with default values:"
$parametersToIgnore = $builtinParameters + $boundParameters

#$PSBoundParameters.Keys | ForEach {
#   Write-Host "'$($_)' = '$($PSBoundParameters.Item($_))'"

($MyInvocation.MyCommand.Parameters ).Keys | ForEach {
 if ( $boundParameters -notcontains $_ ) {
  $val = (Get-Variable -Name $_ -EA SilentlyContinue).Value
  if( $val.length -gt 0 ) {
   "'$($_)' = '$($val)'"

Write-Host "and these have no value set:"
($MyInvocation.MyCommand.Parameters ).Keys | ForEach {
    if ( $parametersToIgnore -notcontains $_ ) {
    $val = (Get-Variable -Name $_ -EA SilentlyContinue).Value
    if( $val.length -eq 0 ) {

Adding default values to psBoundParameters

Heres a useful tidbit of information.   I may have mentioned before that you can call a function and use a hash table that contains the function parameters. You can also use $psBoundParameters which stores the function or script parameters.

If you call Send-MailMessage any parameters passed must have values or the command will fail, or worse prompt you for them if they are mandatory parameters

Have you ever called a function or a cmdlet and had to put a conditional statement around it because you call the function multiple times ( why else would you have a function ???? ) but not all the calls use the same set of parameters. In the script I was working on today I had a function that used the Send-MailMessage cmdlet. Sometimes I passed an attachment and in other calls I didn’t. My function then has an optional parameter -attachments which meant that I had to use a conditional statement to call a different command line when there was an attachment to send.   This could get complex really quickly so I was looking for a way that would always work. The obvious solution was to call the Send-MailMessage using @PSBoundParameters as the cmdline parameters. This way I don’t need a conditional statement to get it working as if I don’t provide the attachments parameter to the function this is not in the psBoundParameters collection.

The problem comes when you want to set some default values too. The psBoundParameters collection only includes parameters provided in the function call.

psBoundParameters only contains parameters that were passed when calling the script / function – the defaults set in the param statement are not included

You would think if I declared the params as follows, that when I call send-MailMessage providing @psBoundParameters as the argument list that it would use the parameter defaults but it doesn’t.  If I don’t provide the parameters when calling the function then psBoundParameters has no parameters in it and the command will fail or prompt you r for missing mandatory parameters:

  $from = "" ,
  $to =   "",
  $smtpserver = ""
  $subject = "Default Subject"
  $body = "There was a problem with the passed parameter defaults please investigate",

The fix is to add the default parameters to the psBoundParameter collection

if ( !$psBoundParameters.ContainsKey('from') ) {
  $psBoundParameters.from = ""
if ( !$psBoundParameters.ContainsKey('to') ) {
 $ = ""
if ( !$psBoundParameters.ContainsKey('smtpServer') ) {
 $psBoundParameters.smtpServer = ""
if ( !$psBoundParameters.ContainsKey('subject') ) {
 $psBoundParameters.subject = "Default Subject"
if ( !$psBoundParameters.ContainsKey('body') ) {
 $psBoundParameters.body = "There was a problem with the passed parameter defaults please investigate"
Send-MailMessage @psBoundParameters

Now it will all work – although of course I now have just as many conditional statements as I had when deciding on what parameters to use when calling send-MailMessage 😦

At least the script will be a little easier to read and of course I might need this technique in more than one line of code in the function so it’s definitely a solution to keep in your scripting back pocket!