PowerShell for Windows Admins


January 29, 2015  12:15 PM

Variable select

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Powershell

I was working on some code that  accesses a SQL database this afternoon. I only needed to pull back a single column from a single row but which column to pull back is variable depending on other data.

That’s OK

$query = “SELECT $colname FROM tablename WHERE x = ‘y’”

Invoke-SQLcmd –server <server> –database <database> –query $query

Now the problem hit me as I need to get the actual value from the object that invoke-sqlcmd returns

I normally do this:

Invoke-SQLcmd –server <server> –database <database> –query $query | select –expandproperty <columnname>

And then it dawned on me that I have the column name in $colname so this works

Invoke-SQLcmd –server <server> –database <database> –query $query | select –expandproperty $colname

I got so used to explicitly stating the properties I need that I forgot you could use a variable.  If you want an example to try on any system

Get-Service | select -First 1 | select -ExpandProperty $p1

or you could try

$p1 = ‘Status’
Get-Service | select Name, $p1

and change to

$p1 = ‘DisplayName’
Get-Service | select Name, $p1

Not something you want to do every day but a useful trick when you need it

January 27, 2015  1:47 PM

WMI errors

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Powershell, WMI

Most PowerShell users will have done something like this:

£> Get-WmiObject -ClassName Win32_ComputerSystem
Domain              : WORKGROUP
Manufacturer        : Microsoft Corporation
Model               : Surface Pro 2
Name                : RSSURFACEPRO2
PrimaryOwnerName    :
TotalPhysicalMemory : 8506093568

Or you can use a computername to access a remote system

£> $computer = $env:COMPUTERNAME
£> Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $computer

Domain              : WORKGROUP
Manufacturer        : Microsoft Corporation
Model               : Surface Pro 2
Name                : RSSURFACEPRO2
PrimaryOwnerName    :
TotalPhysicalMemory : 8506093568

But if you try to access a remote machine that doesn’t exist

£> $computer = ‘WillNotBeFound’
£> Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $computer
Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At line:1 char:1
+ Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $computer
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMException
+ FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

 

You’ve heard of try – catch  so you do something like this

$computer = ‘WillNotBeFound’

try {
Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $computer -ErrorAction Stop
}
catch {
Write-Warning “An error occurred”
}

OR your catch may do something to record the error.

$computer = ‘WillNotBeFound’

try {
Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $computer -ErrorAction Stop
}
catch {
Write-Warning “An error occurred for machine: $computer”
}

If you make the warning message specific

$computer = ‘WillNotBeFound’

try {
Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $computer -ErrorAction Stop
}
catch {
Write-Warning “The RPC server is unavailable for machine: $computer”
}

What happens if you have a different error. Lets say you copy the code and change the class. You test the code

$computer = $env:COMPUTERNAME

try {
Get-WmiObject -ClassName Win32_LogicalDsik -ComputerName $computer -ErrorAction Stop
}
catch {
Write-Warning “The RPC server is unavailable for machine: $computer”
}

And get a message

WARNING: The RPC server is unavailable for machine: RSSURFACEPRO2

What you should see is

Get-WmiObject : Invalid class “Win32_LogicalDsik”
At line:1 char:1
+ Get-WmiObject -ClassName Win32_LogicalDsik -ComputerName $computer
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidType: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

 

Because the class is mistyped.

You could try and create a number of catch statements – one for each error type.  Good luck with that. Its a lot of work.  A better approach, in my mind is to extract the error information you need

$computer = $env:COMPUTERNAME

try {
Get-WmiObject -ClassName Win32_LogicalDsik -ComputerName $computer -ErrorAction Stop
}
catch {
$errordata = @”
Computer = $computer
Exception = $($error[0].Exception)
ErrorId = $($error[0].FullyQualifiedErrorId)
“@

Write-Warning $errordata
}

If you look in the PowerShell automatic variable $error – you’ll find its a collection of error messages.  $error[0] contains the last error.  So now you get

£> $computer = $env:COMPUTERNAME

try {
Get-WmiObject -ClassName Win32_LogicalDsik -ComputerName $computer -ErrorAction Stop
}
catch {
$errordata = @”
Computer = $computer
Exception = $($error[0].Exception)
ErrorId = $($error[0].FullyQualifiedErrorId)
“@

Write-Warning $errordata
}
WARNING:   Computer = RSSURFACEPRO2
Exception = System.Management.ManagementException: Invalid class “Win32_LogicalDsik”
ErrorId = GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

 

For a set of computers

$computers = $env:COMPUTERNAME, ‘WillNotBefound’

foreach ($computer in $computers){
try {
Get-WmiObject -ClassName Win32_computersystem -ComputerName $computer -ErrorAction Stop
}
catch {
$errordata = @”
Computer = $computer
Exception = $($error[0].Exception)
ErrorId = $($error[0].FullyQualifiedErrorId)
“@

Write-Warning $errordata
}
}

You get the data where you can contact the machine – otherwise a warning message that the server is unavailable.  Rather than outputting a warning message create an object from the error data and save to a CSV file


January 26, 2015  12:41 PM

Unravelling lists of distinguished names

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Active Directory, Powershell

There are a number of AD properties such as MemberOf and directrports that consist of a collection of distinguisednames.  Sometimes you need the  name  of the object rather than the distinguished name:

function getname {
[CmdletBinding()]
param (
[string]$dn
)

$name = Get-ADObject $dn | select -ExpandProperty Name

return $name
}
Get-ADUser -Filter * -SearchBase “OU=Testing,DC=Manticore,DC=org” -Properties MemberOf |
foreach {

$name = $psitem.Name

foreach ($member in $psitem.MemberOf) {
$props = [ordered]@{
User = $name
Group = getname -dn $member
}

New-Object -Property $props -TypeName PSObject

}
}

In this example I’m looking at the MemberOf property.  Pull back the set of users and foreach look at each member in the MemberOf list. I pass that to a function that returns the name – I can reuse the function in other scripts.

Don’t make the mistake of looking at group membership in this way though. You can get the names of group members much more simply

Get-ADGroupMember -Identity tg1 | select name

 


January 26, 2015  11:08 AM

Filtering on if an AD property exists

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Active Directory, Powershell

There are times when you want to filter the results based on whether a user has an AD property set.  You could do this:

Get-ADUser -Filter * -Properties Title | Where Title  | select Name, Title

However, that involves pulling back all of the users and then filtering. Not very efficient especially across the network in a big domain.

A better solution would be to use an LDAP filter

Get-ADUser -LDAPFilter “(Title=*)” -Properties Title  | select Name, Title

Get-ADUser does the checking to see if the user has a title set so only those objects with that property are retrieved. If you want to see the value of the property you need to include it by using the –Properties parameter.


January 21, 2015  12:21 PM

Awkward file and folder names

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Powershell

Spent some time today dealing with a situation where there were special characters – namely [ ] in folder a file names

£> Get-ChildItem -Path C:\Test
Directory: C:\Test
Mode                LastWriteTime     Length Name
—-                ————-     —— —-
d—-        21/01/2015     17:58            Awkward [One]
d—-        21/01/2015     17:59            Simple One

 

Each folder has 3 files:

File 1.txt
File 2.txt
File 3.txt

Get-ChildItem -Path ‘C:\Test\Simple One’

will work and show you the contents. When I’m typing paths like this I let Tab completion do the work. Type c:\test\ and use the Tab key to cycle round the available folders. This gives

£> Get-ChildItem -Path ‘C:\Test\Awkward `[One`]’
Get-ChildItem : Cannot find path ‘C:\Test\Awkward `[One`]’ because it does not exist.
At line:1 char:1
+ Get-ChildItem -Path ‘C:\Test\Awkward `[One`]’
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : ObjectNotFound: (C:\Test\Awkward `[One`]:String) [Get-ChildItem], ItemNotFoundException
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Unfortunately, the construct produced by Tab completion doesn’t work.  You need to double up on the back ticks so that it functions as an escape character.

Get-ChildItem -Path ‘C:\Test\Awkward “[One“]’

But that only shows you the folder not the contents.

Get-ChildItem -Path ‘C:\Test\Awkward “[One“]\*’

OR

Get-ChildItem -Path ‘C:\Test\Awkward “[One“]’ -Include *

Will show you the contents of the folder.

But bizarrely

Get-Content -Path ‘C:\Test\Awkward `[One`]\File 1.txt’

Works. As does

Copy-Item -Path ‘C:\Test\Awkward `[One`]\File 1.txt’ -Destination c:\test2

By using back ticks and quotes you can get round most problems like this. Other characters that cause similar problems are commas and quote marks.

Best advice of all – don’t use those awkward characters in your file names if you can possibly avoid it.

 

 


January 18, 2015  10:11 AM

PowerShell Summit NA 2015 Agenda changes

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Powershell

We’ve had to make some minor changes to the Summit agenda – the revised schedule is shown on the event web site – http://eventmgr.azurewebsites.net/event/home/PSNA15


January 18, 2015  5:02 AM

PowerShell Heroes 2015

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Powershell

Powershell.org has announced the 2015 list of PowerShell Heroes- http://powershell.org/wp/2015/01/17/announcing-our-2015-powershell-heroes/

These are people who have made an outstanding contribution to the PowerShell community but have not been recognised in other ways (such as an MVP award)

Please join me in congratulating them


January 12, 2015  11:41 AM

Event log dates

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Powershell

You can use Get-EventLog to query the event logs on you system

Get-EventLog -LogName System

One frequent task is to check if events occurred during a specific timespan. You may feel that you need to use a where-object filter to do this but there is a simple method.

Get-EventLog -LogName System -After (Get-Date -Date ‘1/1/2015′)

Will return all events after the given date. if you don’t give a time your results start at midnight.

Get-EventLog -LogName System –Before (Get-Date -Date ’10/1/2015′)

Will return all events before 10 January 2015.

You ususally use –Before in conjunction with –After to specify a data range

Get-EventLog -LogName System -After (Get-Date -Date ‘1/1/2015′) -Before (Get-Date -Date ’10/1/2015′)

You can make these ranges quite specific

Get-EventLog -LogName System -After (Get-Date -Date ’10/1/2015 14:31:00′) -Before (Get-Date -Date ’10/1/2015 15:00:00′)


January 11, 2015  5:21 AM

Add-Computer is your friend

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Active Directory, Powershell

When spinning up new machines and joining them to the domain at least 2 reboots are required. One when you rename the machine (Windows used to let you specify the name at install time – the removal of that feature was a step backwards) and the other when you join it to the domain.

I was looking at the help file for Add-Computer and discovered you can rename the machine as you join it to the domain. Miss one reboot and get the build finished quicker.

$cred = Get-Credential manticore\richard
Add-Computer -Credential $cred -DomainName manticore -NewName W12R2Web02 -Restart –Force

Nice and simple – I like this.


January 9, 2015  11:30 AM

foreach, pipelines and $_

Richard Siddaway Richard Siddaway Profile: Richard Siddaway
Active Directory, Powershell

I’ve recently seen a few questions where people have been using a pipeline inside a foreach loop and experienced problems when they’ve tried to access properties on the looping objects. To illustrate we’ll start with a CSV file containing emailaddresses and job titles.

£> Import-Csv -Path C:\Test\userdata.txt

emailaddress             title
————             —–
gdreen@Manticore.org     Boss
dbrown@Manticore.org     Underling
dwhite@Manticore.org     Underling
jdaven@Manticore.org     Minion
fgreen@Manticore.org     Minion
dgreensmth@Manticore.org Minion
dgreenly@Manticore.org   Minion

This is definitely an employee friendly organisation

At the moment AD doesn’t contain any job title information

£> $users = Import-Csv -Path C:\Test\userdata.txt

foreach ($user in $users){
$mail = $user.EmailAddress

Get-ADUser -Filter {EmailAddress -eq $mail} -Properties Title |
select Name, Title

}

Name                                      Title
—-                                      —–
Dave Green
Dave Brown
Dave White
Jo Daven
Fred Green
Dale Greensmith
Dave Greenly

The approach that causes problems is this:

£> $users = Import-Csv -Path C:\Test\userdata.txt

foreach ($user in $users){
$mail = $user.EmailAddress

Get-ADUser -Filter {EmailAddress -eq $mail} -Properties Title |
foreach {Set-ADUser -Identity $_ -Title $_.Title} |

Get-ADUser -Filter {EmailAddress -eq $mail} -Properties Title |
select Name, Title
}

Name                                      Title
—-                                      —–
Dave Green
Dave Brown
Dave White
Jo Daven
Fred Green
Dale Greensmith
Dave Greenly

When you use foreach as a keyword the $_ and $psitem variables aren’t available. These variables represent the current object on the pipeline.  The foreach keyword loop doesn’t have a pipeline as such.

Inside the foreach a pipeline is created

Get-ADUser -Filter {EmailAddress -eq $mail} -Properties Title |
foreach {Set-ADUser -Identity $_ -Title $_.Title -PassThru} |
select Name, Title

$_ is used correctly to identify the object on which Set-ADUser is to work – its the current object on the pipeline.

The use of  $_.Title  to set the user’s job title is where the problem really bites.  $_.Title  refers to the Title property of the current object on the pipeline so you are setting the Title to its existing value.

You need to reach back to the $user object that represents the current object from the set you are looping through with foreach to get the correct value

£> $users = Import-Csv -Path C:\Test\userdata.txt

foreach ($user in $users){
$mail = $user.EmailAddress

Get-ADUser -Filter {EmailAddress -eq $mail} -Properties Title |
foreach {Set-ADUser -Identity $_ -Title $user.Title} |

Get-ADUser -Filter {EmailAddress -eq $mail} -Properties Title |
select Name, Title
}

Name                                      Title
—-                                      —–
Dave Green                                Boss
Dave Brown                                Underling
Dave White                                Underling
Jo Daven                                  Minion
Fred Green                                Minion
Dale Greensmith                           Minion
Dave Greenly                              Minion

 

You’ll see similar problems if you have nested foreach-object loops or a switch statement inside a foreach-object loop.  $_ always refers to the current context and you have to either reach back to the looping variable in the csae of a foreach or set variables on the data in the outer foreach before entering the nested foreach.

 

 


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: