PowerShell for Windows Admins


August 9, 2011  12:58 PM

Book review: SQL Server DMVs in Action

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

Title: SQL Server DMVs in Action

Author: Ian Stirk

Publisher: Manning

ISBN: 978-1-935182-73-3

 

DMVs are Dynamic Management Views – they were introduced in SQL Server 2005 and further refined in SQL Server 2008. They maintain information about whats happening on on your SQL Server – in real time. They supply a snapshot of whats happening and a view of what has happened as they accumulate data since SQL Server was started.

DMVs are a very powerful, but little understood facet of SQL Server.  The book has 11 chapters:

  1. The DMV gold mine
  2. Common patterns
  3. Index DMVs
  4. Improving poor query performance
  5. Further query improvements
  6. Operating System DMVs
  7. Common Language RunTime DMvs
  8. Resolving Transaction issues
  9. Database level DMVs
  10. The self-healing database
  11. Useful scripts

At 325 pages its not a massive book but it is packed with useful information. The first two chapters supply an overview of DMVs and some common code patterns that will be re-used throughout the book. The second chapter will repay careful reading as the themes introduced here permeate the whole book.

The books second part – chapters 3-11 – cover how to use DMVs to solve database related problems. The chapter titles speak for themselves with chapter 3 showing ways to identify missing indexes and 4 and 5 showing how to identify and improve SQL queries

Chapter 6 covers SQL Server’s interaction with the OS and the server – showing how to identify waits and blocks. Chapter 7 on the CLR is of great interest if you are using CLR code in your queries. Chapters 8 and 9 on transactions and databases are back to finding SQL Server related issues.. The last two chapters show how to pull all of this together to produce a database that can start to heal its own problems – personally I’d prefer it to make suggestions rather than implement but that is just details. The final chapter presents some useful scripts that build on the earlier chapters.

The books 100+ code snippets are ready to use and directly useful in your environment

If you work with SQL Server this is one you should have on your book case.  Highly recommended

August 7, 2011  10:05 AM

MSDiskDriver_Performance

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

We have looked at the MSDiskDriver_Geometry  class now lets look at the MSDiskDriver_Performance class

This is a bit more complicated because the Perfdata property is actually an instance of the MSDiskDriver_PerformanceData class

function get-msdiskdriverperfomance  {            
[CmdletBinding()]            
param (            
 [string]$computer="."            
)            
            
PROCESS{            
$date = Get-Date -Format F            
Get-WmiObject -Namespace root\wmi -Class msdiskdriver_performance `
-ComputerName $computer |             
foreach {            
 $perfdata =  $_ | select InstanceName, Active, DeviceName             
 $perfdata |            
 Add-Member -MemberType NoteProperty -Name TimeStamp -Value $($date) -PassThru |            
 Add-Member -MemberType NoteProperty -Name BytesRead -Value $($_.PerfData.BytesRead) -PassThru |            
 Add-Member -MemberType NoteProperty -Name BytesWritten -Value $($_.PerfData.BytesWritten) -PassThru |            
 Add-Member -MemberType NoteProperty -Name IdleTime -Value $($_.PerfData.IdleTime) -PassThru |            
 Add-Member -MemberType NoteProperty -Name QueryTime -Value $($_.PerfData.QueryTime) -PassThru |            
 Add-Member -MemberType NoteProperty -Name QueueDepth -Value $($_.PerfData.QueueDepth) -PassThru |            
 Add-Member -MemberType NoteProperty -Name ReadCount -Value $($_.PerfData.ReadCount) -PassThru |            
 Add-Member -MemberType NoteProperty -Name ReadTime -Value $($_.PerfData.ReadTime) -PassThru |            
 Add-Member -MemberType NoteProperty -Name SplitCount -Value $($_.PerfData.SplitCount) -PassThru |            
 Add-Member -MemberType NoteProperty -Name StorageDeviceNumber -Value $($_.PerfData.StorageDeviceNumber) -PassThru |            
 Add-Member -MemberType NoteProperty -Name WriteCount -Value $($_.PerfData.WriteCount) -PassThru |                     
 Add-Member -MemberType NoteProperty -Name WriteTime -Value $($_.PerfData.WriteTime)             
            
$perfdata             
}            
}            
}

We get round that by creating an object using select and then using Add-member to add the performance properties.

I get three instances of the MSDiskDriver_Performance  class return which I think correspond to the physical disk and the two logical disks. I need to do a bit more investigation to confirm. In the mean time it does give some interesting statistics on our disks


August 7, 2011  9:26 AM

MSDiskDriver classes

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

The root\wmi namespace contains four MSDiskDriver related classes

gwmi -Namespace root\wmi -Class msdiskdriver* -List | select name

MSDiskDriver
MSDiskDriver_Performance
MSDiskDriver_PerformanceData
MSDiskDriver_Geometry

 

The first class – MSDiskDriver – is a super class that calls the other three. Documentation appears to be almost non-existent (there are references to an older copy of the WMI SDK but that doesn’t seem to be available. The MSDiskDriver_PerformanceData class is referenced from the

MSDiskDriver_Performance class as the Perfdata property. We will ignore the  MSDiskDriver class and start with the disk geometry

 

function get-msdiskdrivergeometry  {           
[CmdletBinding()]           
param (           
[string]$computer="."           
)           
BEGIN{           
Add-Type @"
public enum MediaType : int {
Unknown = 0,
RemovableDisk = 11,
HardDisk = 12
}
"@
           
}           
PROCESS{           
Get-WmiObject -Namespace root\wmi -Class msdiskdriver_geometry `
-ComputerName $computer | select InstanceName, Active,            
@{N="DiskType"; E={[mediatype]($($_.MediaType))}},           
Cylinders, TracksPerCylinder, SectorsPerTrack, BytesPerSector           
}           
}

Define an enumeration for the media types – values 1-10 and 13-22 related to floppy or similar devices

use Get-WmiObject to get the object and display – use the enum to decode the media type

On my machine this produces

InstanceName      : IDE\DiskST9250320AS_____________________________HP07____\5&b0fd174&0&1.0.0_0
Active            : True
DiskType          : HardDisk
Cylinders         : 30401
TracksPerCylinder : 255
SectorsPerTrack   : 63
BytesPerSector    : 512

 

compare with the results of Win32_DiskDrive

Get-WmiObject Win32_DiskDrive | select PNPDeviceID, Status, MediaType, TotalCylinders,
TracksPerCylinder, SectorsPerTrack, BytesPerSector

PNPDeviceID       : IDE\DISKST9250320AS_____________________________HP07____\5&B0FD174&0&1.0.0
Status            : OK
MediaType         : Fixed hard disk media
TotalCylinders    : 30401
TracksPerCylinder : 255
SectorsPerTrack   : 63
BytesPerSector    : 512


August 5, 2011  1:11 PM

Change Volume Label

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

While we are looking at disks I’ll add this function to the mix

function set-volumelabel {            
[CmdletBinding()]            
param (            
 [string]$computer=".",            
             
 [parameter(Mandatory=$true)]            
 [string]            
 [ValidatePattern("^[A-Z]{1}:{1}`$")]            
 $drive,            
             
 [parameter(Mandatory=$true)]            
 [string]$newname            
              
)            
            
Get-WmiObject -Class Win32_Volume -ComputerName $computer -Filter "DriveLetter='$drive'"  |            
Set-WmiInstance -Arguments @{Label=$newname}            
}

Its job is to set the volume label. Give it a drive and a new name as parameters. The same pair of Get-WmiObject and Set-WmiInstance work their magic again to make the change


August 5, 2011  11:58 AM

Setting the drive letter

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

I’ve been working on some WMI  functions for disks recently. First off setting the disk drive letter.

function set-driveletter {            
[CmdletBinding()]            
param (            
 [string]$computer=".",            
             
 [parameter(Mandatory=$true)]            
 [string]            
 [ValidatePattern("^[A-Z]{1}:{1}`$")]            
 $olddrive,            
             
 [parameter(Mandatory=$true)]            
 [string]            
 [ValidatePattern("^[A-Z]{1}:{1}`$")]            
 $newdrive            
              
)            
            
Get-WmiObject -Class Win32_Volume -ComputerName $computer -Filter "DriveLetter='$olddrive'" |             
Set-WmiInstance -Arguments @{DriveLetter=$newdrive}            
}

The difficult bit was getting the regular expression correct. It tests that we have a single letter and a colon.  The old and new drives are mandatory parameters

Simple call to Get-WmiObject and pipe to Set-WmiInstance.

Use it like this:

set-driveletter -olddrive z: -newdrive i:  

 

Jobs a good un.


August 4, 2011  1:40 PM

Authentication, Impersonation and Privileges

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

So, you want to use a new WMI namespace you have discovered that lets you work with IIS.

PS> Get-WmiObject -Namespace ‘root\webadministration’ -List -ComputerName web01
Get-WmiObject : Access denied
At line:1 char:14
+ Get-WmiObject <<<<  -Namespace ‘root\webadministration’ -List -ComputerName web01
    + CategoryInfo          : NotSpecified: (:) [Get-WmiObject], ManagementException
    + FullyQualifiedErrorId : System.Management.ManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

OK not good.

First thing to try is running PowerShell with elevated privileges – Right click and Run as Adminsitartor.

Nope – still not working

Secondly lets try –EnableAllPrivileges

PS > get-help get-wmiobject -Parameter EnableAllPrivileges

-EnableAllPrivileges [<SwitchParameter>]
   Enables all the privileges of the current user before the command makes the WMI call.

    Required?                    false
    Position?                    named
    Default value
    Accept pipeline input?       false
    Accept wildcard characters?  false

This means that the system recognises our credentials and the privileges we get from them e.g, we are an administrator.

Bad news is that doesn’t do any good. Time to look at the documentation. The IIS provider requires packet privacy which means we need to use the –Authentication parameter.

PS > get-help get-wmiobject -Parameter Authentication

-Authentication <AuthenticationLevel>
    Specifies the authentication level to be used with the WMI connection. Valid values are:

    -1: Unchanged
    0: Default
    1: None (No authentication in performed.)
    2: Connect (Authentication is performed only when the client establishes a relationship with the application.)
    3: Call (Authentication is performed only at the beginning of each call when the application receives the request.)
    4: Packet (Authentication is performed on all the data that is received from the client.)
    5: PacketIntegrity (All the data that is transferred between the client  and the application is authenticated and v
    erified.)
    6: PacketPrivacy (The properties of the other authentication levels are used, and all the data is encrypted.)

    Required?                    false
    Position?                    named
    Default value
    Accept pipeline input?       false
    Accept wildcard characters?  false

Get-WmiObject -Namespace ‘root\webadministration’ -List -ComputerName web01 -Authentication 6

This works BUT you still need to have started PowerShell with elevated privileges.

What happened?

We got caught by WMI providers author. They decided it would be a good idea if the data transmitted was encrypted. 

The various values for the authentication parameter are outlined in the table

Value

Meaning

-1

Unchanged – authentication remains as it was before

0

Default COM authentication level. Authentication is negotiated. WMI uses default Windows Authentication setting. The None setting (1) is never the result of a negotiated authentication.

1

None. No COM authentication in performed.

2

Connect. COM authentication is performed only when the client establishes a relationship with the server. No further checks are performed.

3

Call. COM authentication is performed only at the beginning of each call when the sever receives the request. Only packet headers are signed. No data is encrypted.

4

Packet. COM authentication is performed on all the data that is received from the client. Only packet headers are signed. No data is encrypted

5

PacketIntegrity. All the data that is transferred between the client and the application is authenticated and verified. All packets are signed. No data is encrypted.

6

PacketPrivacy. The properties of the other authentication levels are used, and all the data is encrypted.

These are DCOM authentication levels. DCOM is the transport mechanism for accessing remote machines

Most of the time we don’t need to worry about –Authentication but the most common value if we do will be to use packet privacy

These values are contained in the System.Management.Authenticationlevel enumeration

PS > [enum]::GetNames("System.Management.Authenticationlevel")
Default
None
Connect
Call
Packet
PacketIntegrity
PacketPrivacy
Unchanged

So we can do this

Get-WmiObject -Namespace ‘root\webadministration’ -List -ComputerName web01 `
-Authentication ([System.Management.Authenticationlevel]::Packetprivacy)

OR

Get-WmiObject -Namespace ‘root\webadministration’ -List -ComputerName web01 `
-Authentication Packetprivacy

to make the command more readable.

If you think you have a problem with the authentication level try this test

[enum]::GetNames("System.Management.Authenticationlevel") |
foreach {"`n$_"; Get-WmiObject -Namespace ‘root\webadministration’
-List -ComputerName web01 -Authentication $_}

You will get Access Denied messages for all but the packet privacy level.

The last part of the puzzle is Impersonation

PS > Get-Help get-wmiobject -Parameter Impersonation

Impersonation <ImpersonationLevel>
    Specifies the impersonation level to use. Valid values are:

    0: Default (reads the local registry for the default impersonation level , which is usually set to "3: Impersonate"
    .)
    1: Anonymous (Hides the credentials of the caller.)
    2: Identify (Allows objects to query the credentials of the caller.)
    3: Impersonate (Allows objects to use the credentials of the caller.)
    4: Delegate (Allows objects to permit other objects to use the credentials of the caller.)

    Required?                    false
    Position?                    named
    Default value
    Accept pipeline input?       false
    Accept wildcard characters?  false

The Impersonate (3) level is what we want. Anonymous isn’t actually used by WMI and Identify usually only allows the checking of access controls.

Delegation is a security risk and isn’t recommended as it can be used to access resources on other systems from the target.

To check the local registry look in 

Get-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\WBEM\Scripting -Name "Default Impersonation Level"

while you are doing that check out

Get-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\WBEM\Scripting -Name "Default Namespace"

as well

In addition to the IIS provider the Microsoft Cluster Services WMI provider also need packet privacy.

Next time you get an Access Denied

  1. Check running PowerShell with elevated privileges
  2. Try –EnableAllPrivileges
  3. Test against various authentication levels (my feeling is to always go to packet privacy as it seems to be that or the default)
  4. Check the Impersonation level

If that doesn’t work – leave a comment here and I’ll try and help


August 3, 2011  2:36 AM

Network speeds–the faster ones

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

Quick update to

http://itknowledgeexchange.techtarget.com/powershell/network-adapter-speed-and-duplex/

I’ve found a full listing of network speeds for *SpeedDuplex at http://msdn.microsoft.com/en-us/library/ff548866(v=VS.85).aspx

and amended the script

function test-duplex {            
[CmdletBinding()]            
param (            
 [string]$computer="."            
)            
BEGIN {            
 $HKLM = 2147483650            
 $reg = [wmiclass]"\\$computer\root\default:StdRegprov"            
 $keyroot = "SYSTEM\CurrentControlSet\Control\Class\{4D36E972-E325-11CE-BFC1-08002BE10318}"            
}            
            
PROCESS {            
            
Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $computer -Filter "IPEnabled='$true'" |            
foreach {            
            
$data = $_.Caption -split "]"            
$suffix = $data[0].Substring(($data[0].length-4),4)            
$key = $keyroot + "\$suffix"            
            
$value = "*PhysicalMediaType"            
$pmt = $reg.GetDwordValue($HKLM, $key, $value)  ## REG_DWORD            
            
## 0=Unspecified, 9=Wireless, 14=Ethernet            
if ($pmt.uValue -eq 14){            
            
$nic = $_.GetRelated("Win32_NetworkAdapter") | select Speed, NetConnectionId            
            
$value = "*SpeedDuplex"            
$dup = $reg.GetStringValue($HKLM, $key, $value)  ## REG_SZ            
            
switch ($dup.sValue) {            
 "0" {$duplex = "Auto Detect"}            
 "1" {$duplex = "10Mbps \ Half Duplex"}            
 "2" {$duplex = "10Mbps \ Full Duplex"}            
 "3" {$duplex = "100Mbps \ Half Duplex"}            
 "4" {$duplex = "100Mbps \ Full Duplex"}            
 "5" {$duplex = "1.0Gbps \ Half Duplex"}            
 "6" {$duplex = "1.0Gbps \ Full Duplex"}            
 "7" {$duplex = "10Gbps \ Full Duplex"}            
}             
            
New-Object -TypeName PSObject -Property @{            
  NetworkConnector = $($nic.NetConnectionID )            
  DuplexSetting = $duplex            
  Speed = $($nic.Speed)            
}            
            
} #if            
} #foreach            
} #process            
} #function

 

Once you reach 10Gbps there isn’t a half duplex option


August 2, 2011  9:02 AM

Using enums

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

Following on my post about using enums http://msmvps.com/blogs/richardsiddaway/archive/2011/08/02/enums.aspx

I thought it might be fun to see how we can make our own.

If we look at Win32_LogicalDisk

PS > Get-WmiObject Win32_LogicalDisk | ft DeviceID, DriveType -a

DeviceID DriveType
——– ———
C:               3
E:               5
F:               5

Now I know that type 3 is a hard drive and type 5 is a CD/DVD but we want to see it in the code. Up to now I’ve used hash tables

$dt = DATA {            
ConvertFrom-StringData -StringData @'
0 = Unknown
2 = Removable Disk 
3 = Local Disk
4 = Network Drive
5 = Compact Disk
6 = RAM Disk
'@            
}             
Get-WmiObject Win32_LogicalDisk |             
select DeviceID,             
@{N="DiskType"; E={$dt["$($_.DriveType)"]}} |            
Format-Table -a

Create a hash table and then use a calculated field to perform the lookup

An alternative is to use an enumeration

Add-Type @"
public enum DiskType : int {
Unknown = 0,
RemovableDisk = 2, 
LocalDisk = 3,
NetworkDrive = 4,
CompactDisk = 5,
RAMDisk = 6 
}
"@            
             
Get-WmiObject Win32_LogicalDisk |             
select DeviceID,             
@{N="DiskType"; E={[disktype]($($_.DriveType))}} |            
Format-Table -a

Notice that the order of values is reversed between the hash table and the enum.

The lookup syntax is slightly easier but the code to set it up is slightly harder. We also need to make sure there aren’t any spaces in the values we are defining.

Hash table or enum which do you want to use? Either give us the answer and both are relatively easy to implement.  It is possible to define multiple enums in one call to Add-Type


August 1, 2011  7:06 AM

PowerShell Admin Modules

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

Just been checking the stats on the PowerShell Admin Modules – http://psam.codeplex.com/ – and this month its reached over 1000 downloads.

I have a couple of updates in the pipeline that should be available soon.


August 1, 2011  6:57 AM

Where to select

Richard Siddaway Richard Siddaway Profile: Richard Siddaway

Using select-object (aliased to select in common usage) to reduce the number of properties passing into further processing is a common aspect of using PowerShell. However, its not the only place we can do it.

Consider this scenario.

We need to test the network client on a number of remote machines. We can use Win32_NetworkClient

PS > Get-WmiObject -Class Win32_NetworkClient

__GENUS          : 2
__CLASS          : Win32_NetworkClient
__SUPERCLASS     : CIM_LogicalElement
__DYNASTY        : CIM_ManagedSystemElement
__RELPATH        : Win32_NetworkClient.Name="Microsoft Windows Network"
__PROPERTY_COUNT : 6
__DERIVATION     : {CIM_LogicalElement, CIM_ManagedSystemElement}
__SERVER         : RSLAPTOP01
__NAMESPACE      : root\cimv2
__PATH           : \\RSLAPTOP01\root\cimv2:Win32_NetworkClient.Name="Microsoft Windows Network"
Caption          : Workstation
Description      : LanmanWorkstation
InstallDate      :
Manufacturer     : Microsoft Corporation
Name             : Microsoft Windows Network
Status           : OK

 

Now doing this on the local machine is fine but we might want to cut back on the properties returned across the network

PS > Get-WmiObject -Class Win32_NetworkClient -Property Name, Manufacturer, Caption, Description, Status

__GENUS          : 2
__CLASS          : Win32_NetworkClient
__SUPERCLASS     :
__DYNASTY        :
__RELPATH        : Win32_NetworkClient.Name="Microsoft Windows Network"
__PROPERTY_COUNT : 5
__DERIVATION     : {}
__SERVER         :
__NAMESPACE      :
__PATH           :
Caption          : Workstation
Description      : LanmanWorkstation
Manufacturer     : Microsoft Corporation
Name             : Microsoft Windows Network
Status           : OK

This cuts down the data a bit – obviously for a class such as Win32_ComputerSystem that has a lot more properties you would get a bigger impact

Compare this to using select-object

PS > Get-WmiObject -Class Win32_NetworkClient |
select -Property Name, Manufacturer, Caption, Description, Status

Name         : Microsoft Windows Network
Manufacturer : Microsoft Corporation
Caption      : Workstation
Description  : LanmanWorkstation
Status       : OK

OK this looks better BUT remember for a remote machine the filtering happens AFTER the object has been returned.

Locally I would use select and remotely I would think about using –property on Get-WmiObject.

As so often it all depends on what you actually need to do


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: