Categories

Monthly Archives: December 2009

Online PowerShell help

One of the reasons I dump the file files to text files is that I am frequently in a position of being on a customer site without direct Internet access.  If I have the files available then I can solve the problems.

If you have Internet access then the help files are available on line at http://technet.microsoft.com/en-us/library/ee307956(WS.10).aspx

PowerShell 2 help files are online at http://technet.microsoft.com/en-us/library/dd347701.aspx

One advantage of using the online help files is that this is the place documentation  errors will be rectified first

Technorati Tags: ,

Module Help Files

The use of modules to supply PowerShell based functionality in Windows 7\Windows 2008 R2 makes accessing the help files a little bit problematic. We have to have the module loaded to access the file.  Sometimes I want to think through what I’m doing and research the help files before I dive into coding.  To make that easier I decide to dump the help files for the modules I an interested in.  For example I have been experimenting with the Group Policy cmdlets in Windows 2008 R2 and wanted the help files accessible.

001
002
003
Get-Command -Module GroupPolicy | foreach {
    Get-Help $_.Name -Full | Out-File -FilePath "D:\Scripts\AD\GPOHelp\$_.txt" -Width 1000
}

 

This just goes through the module and creates a text file for each individual help file.

Not something you necessarily need to do but useful if you don’t want to fire up a machine specifically to access the help files

Access Functions

I have put the module of Access functions I’ve been blogging about over the last few weeks onto my SkyDrive at


http://cid-43cfa46a74cf3e96.skydrive.live.com/browse.aspx/PowerShell%20Scripts/AccessFunctions


The file is alos attached to this post.


The usual warnings apply. This is a work in progress and subject to change.  All scripts, and the module, are presented as is with no warranty. It is your responsibility to test the scripts in your environment.


Technorati Tags: ,,

Proxy for import-module

I do quite a lot with modules as you can tell from my posts over the last few months and have bit a fair collection of modules. One issue I find when loading a module is that I can’t always remember the name of the commands that the module creates. If we use

Import-Module -Name filefunctions

we just get the prompt returned and no information on the commands. After a bit of experimenting I found that

Import-Module -Name filefunctions -PassThru | foreach {Get-Command -Module $_.Name | select name}

will give me the list of commands added by a particular module.  Instead of get-command I could have used

Get-Module filefunctions | select -ExpandProperty ExportedCommands

Rather than remembering to run the get-command I could have implemented a function that would run the commands. However, I thought it might be more fun to create a proxy command as detailed in these posts

http://blogs.msdn.com/powershell/archive/2009/01/04/extending-and-or-modifing-commands-with-proxies.aspx

http://blogs.msdn.com/powershell/archive/2009/01/05/ps-dir-a-d-the-screencast.aspx

I found the video in the second one irritating as it stopped every few seconds to ask if I wanted to continue watching.

Anyway we use the metaprogramming module that can be downloaded from the first post. This gives us a New-ProxyCommand function that we use like this.

New-ProxyCommand Import-Module -CommandType all -AddParameter ListMember > import-mymodule.ps1

The output script then has the code to execute the extra parameter inserted – the skeleton can be found in the block comment at the top of the script. To produce something like this

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
<# You are responsible for implementing the logic for added parameters. These parameters are bound to $PSBoundParameters so if you pass them on the the command you are proxying, it will almost certainly cause an error. This logic should be added to your BEGIN statement to remove any specified parameters from $PSBoundParameters. In general, the way you are going to implement additional parameters is by modifying the way you generate the $scriptCmd variable. Here is an example of how you would add a -SORTBY parameter to a cmdlet: ################################################################################ New ATTRIBUTES if ($ListMember) { [Void]$PSBoundParameters.Remove("ListMember") } ################################################################################ #>

[CmdletBinding(DefaultParameterSetName='Name')]
param(
    [Switch]
    ${Global},

    [ValidateNotNull()]
    [System.String]
    ${Prefix},

    [Parameter(ParameterSetName='Name', Mandatory=$true, Position=0, ValueFromPipeline=$true)]
    [System.String[]]
    ${Name},

    [Parameter(ParameterSetName='Assembly', Mandatory=$true, Position=0, ValueFromPipeline=$true)]
    [System.Reflection.Assembly[]]
    ${Assembly},

    [ValidateNotNull()]
    [System.String[]]
    ${Function},

    [ValidateNotNull()]
    [System.String[]]
    ${Cmdlet},

    [ValidateNotNull()]
    [System.String[]]
    ${Variable},

    [ValidateNotNull()]
    [System.String[]]
    ${Alias},

    [Switch]
    ${Force},

    [Switch]
    ${PassThru},

    [Switch]
    ${AsCustomObject},

    [Parameter(ParameterSetName='Name')]
    [System.Version]
    ${Version},

    [Parameter(ParameterSetName='ModuleInfo', Mandatory=$true, Position=0, ValueFromPipeline=$true)]
    [System.Management.Automation.PSModuleInfo[]]
    ${ModuleInfo},

    [Alias('Args')]
    [System.Object[]]
    ${ArgumentList},

    [Switch]
    ${DisableNameChecking},

    [Switch]
    ${ListMember})

begin
{
    try {
        $outBuffer = $null
        if ($PSBoundParameters.TryGetValue('OutBuffer', [ref]$outBuffer))
        {
            $PSBoundParameters['OutBuffer'] = 1
        }
        $wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Import-Module', [System.Management.Automation.CommandTypes]::Cmdlet)
        if ($ListMember)
        {
            [Void]$PSBoundParameters.Remove("ListMember")
            $scriptCmd = {& $wrappedCmd @PSBoundParameters | foreach {Get-Command -Module $_.Name | select name}}
        }else
        {
            $scriptCmd = {& $wrappedCmd @PSBoundParameters }
        }
        $steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin)
        $steppablePipeline.Begin($PSCmdlet)
    } catch {
        throw
    }
}

process
{
    try {
        $steppablePipeline.Process($_)
    } catch {
        throw
    }
}

end
{
    try {
        $steppablePipeline.End()
    } catch {
        throw
    }
}
<# .ForwardHelpTargetName Import-Module .ForwardHelpCategory Cmdlet #>

 

The important part is

if ($ListMember)
{
    [Void]$PSBoundParameters.Remove("ListMember")
    $scriptCmd = {& $wrappedCmd @PSBoundParameters | foreach {Get-Command -Module $_.Name | select name}}
}else
{
    $scriptCmd = {& $wrappedCmd @PSBoundParameters }
}

where we add the extra code we need for the new parameter.

We can use the new script in place of import-module

PS> .\import-mymodule.ps1 filefunctions
PS> .\import-mymodule.ps1 filefunctions -Force
PS> .\import-mymodule.ps1 filefunctions -Force -PassThru -ListMember

Name
----
Format-Colorfile
Format-Xml
Get-EmptyFolder
Get-TempContents
New-TempFile
Remove-EmptyFolder
Remove-TempContents
Remove-ZoneIdentifier
Test-ZoneIdentifier

 

It is a simple task to make this a function that is loaded in the profile.

This isn’t a technique for everyone but if you feel comfortable experimenting like this give it a try.  There must be a number of other cmdlets where we can modify the behaviour to add functionality to suit our individual needs.

WMI migration

In case you were wondering why the WMI based posts seem to have dried up – I have opened a new blog

http://itknowledgeexchange.techtarget.com/powershell/

Just for PowerShell and WMI.

I figure there is enough subject matter in the topic to support a separate site.

I will be covering the rest of the PowerShell world from here.

Technorati Tags: ,

Delete Access Table

This times lets go mad and delete a whole table.

001
002
003
004
005
006
007
008
009
010
011
function Remove-AccessTable {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
    [string]$table,
    [System.Data.OleDb.OleDbConnection]$connection
)
    $sql = "DROP TABLE $table "
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
   
    if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

Again we use the SupportsShouldProcess – have to love the amount of functionality we get for one line of code.

Parameters are our table and connection.

We create the SQL and test if we’ve called –whatif or –confirm and then perform the appropriate action.

As an example of deleting a column and a table consider this code

PS> Import-Module accessfunctions -Force
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> Remove-AccessColumn -table NewTable2 -column MyReal -connection $db -WhatIf
What if: Performing operation "ALTER TABLE NewTable2 DROP COLUMN MyReal" on Target "c:\test\test03.mdb".
PS> Remove-AccessColumn -table NewTable2 -column MyReal -connection $db
0
PS> Remove-AccessTable -table NewTable2  -connection $db -WhatIf
What if: Performing operation "DROP TABLE NewTable2 " on Target "c:\test\test03.mdb".
PS> Remove-AccessTable -table NewTable2  -connection $db
0
PS> Close-AccessDatabase $db

One thing to remember is that if you are developing module using the –Force on an import-module refreshes the functions with the latest versions.

Delete an Access Column

We have seen how to add a column to our access database – what about removing a column?

001
002
003
004
005
006
007
008
009
010
011
012
function Remove-AccessColumn {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
    [string]$table,
    [string]$column,
    [System.Data.OleDb.OleDbConnection]$connection
)
    $sql = "ALTER TABLE $table DROP COLUMN $column"
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
   
    if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

 

We use the SupportsShouldProcess=$true to get the –whatif functionality.  Parameters are table, column name and the connection.

We create the SQL and either run it or perform the –whatif processing.  Remember we also get –confirm if we require it.

Add a column to an Access Table

Now we have created our Table we can start adding columns

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
function New-AccessColumn {
[CmdletBinding()]
param (
    [System.Data.OleDb.OleDbConnection]$connection,
    [string]$table,
    [switch]$notnull,

    [parameter(ParameterSetName="datetime")] 
    [string]$dtname,

    [parameter(ParameterSetName="unique")] 
    [string]$uniquename,

    [parameter(ParameterSetName="binary")] 
    [string]$binname,

    [parameter(ParameterSetName="bit")] 
    [string]$bitname,

    [parameter(ParameterSetName="tinyinteger")] 
    [string]$tnyintname,

    [parameter(ParameterSetName="smallinteger")] 
    [string]$smlintname,
  
    [parameter(ParameterSetName="integer")] 
    [string]$intname,


    [parameter(ParameterSetName="double")]  
    [string]$dblname,

    [parameter(ParameterSetName="real")] 
    [string]$realname,

    [parameter(ParameterSetName="float")] 
    [string]$floatname,
   
    [parameter(ParameterSetName="decimal")] 
    [string]$decname,
   
    [parameter(ParameterSetName="money")] 
    [string]$mnyname,
   
    [parameter(ParameterSetName="char")] 
    [string]$charname,
   
    [parameter(ParameterSetName="text")] 
    [string]$textname,

    [parameter(ParameterSetName="image")] 
    [string]$imgname,
   
    [parameter(ParameterSetName="char")]
    [parameter(ParameterSetName="text")] 
    [int]$size = 10
)   
    switch ($psCmdlet.ParameterSetName){
        datetime     {$sql = "ALTER TABLE $table ADD COLUMN $dtname DATETIME" } 

        binary       {$sql = "ALTER TABLE $table ADD COLUMN $binname BINARY" } 
        bit          {$sql = "ALTER TABLE $table ADD COLUMN $bitname BIT" } 
       
        unique       {$sql = "ALTER TABLE $table ADD COLUMN $uniquename UNIQUEIDENTIFIER" } 

        tinyinteger  {$sql = "ALTER TABLE $table ADD COLUMN $tnyintname TINYINT" } 
        smallinteger {$sql = "ALTER TABLE $table ADD COLUMN $smlintname SMALLINT" } 
        integer      {$sql = "ALTER TABLE $table ADD COLUMN $intname INTEGER" } 

        double       {$sql = "ALTER TABLE $table ADD COLUMN $dblname DOUBLE" } 
        float        {$sql = "ALTER TABLE $table ADD COLUMN $floatname FLOAT" } 
        real         {$sql = "ALTER TABLE $table ADD COLUMN $realname REAL" } 
        decimal      {$sql = "ALTER TABLE $table ADD COLUMN $decname DECIMAL" } 
        money        {$sql = "ALTER TABLE $table ADD COLUMN $mnyname MONEY" } 
       
        char         {$sql = "ALTER TABLE $table ADD COLUMN $charname CHARACTER($size)" }
        text         {$sql = "ALTER TABLE $table ADD COLUMN $textname TEXT($size)" }
        image        {$sql = "ALTER TABLE $table ADD COLUMN $imgname IMAGE" }                
               
    }
    if ($notnull) {$sql = $sql + " NOT NULL"}
   
    Write-Debug $sql
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

The first set of parameters define the connection and table name. The $notnull parameter adds the NOT NULL statement to the SQL we create. This forces us to add a value for the column when we insert a row. These parameters are not part of a parameter set – this means they work with all parameter sets.

Parameter sets are used in this function to make the data types mutually exclusive – i.e. one call to Add-AccessColumn can add one column of one, and only one, data type. The size parameter applies to character and text data types so is a member of both parameter sets.

The parameter set is used in a switch statement to create the SQL to add a column of the correct type. If the $notnull switch is set we add the appropriate commands to the SQL.

With advanced functions in PowerShell v2 we get the ability to use the common parameters so I can use

New-AccessColumn -connection $db -table $table -dtname Mydate2 –notnull  -Debug

and the Write-Debug statements will be triggered. In this case I get a dump of the SQL I have created. The function finishes by executing the SQL against the database as we have seen before.

As an example of using the function

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
Import-Module accessfunctions -Force
$db = Open-AccessDatabase -name test03.mdb -path c:\test

$table = "NewTable2"
New-AccessTable -table $table -connection $db

New-AccessColumn -connection $db -table $table -dtname Mydate
New-AccessColumn -connection $db -table $table -dtname Mydate2 -notnull 

New-AccessColumn -connection $db -table $table -uniquename MyUnique
New-AccessColumn -connection $db -table $table -uniquename MyUnique2 -notnull

New-AccessColumn -connection $db -table $table -binname MyBinary
New-AccessColumn -connection $db -table $table -bitname MyBit
New-AccessColumn -connection $db -table $table -binname MyBinary2 -notnull
New-AccessColumn -connection $db -table $table -bitname MyBit2 -notnull

New-AccessColumn -connection $db -table $table -tnyintname MyTiny
New-AccessColumn -connection $db -table $table -smlintname MySmall
New-AccessColumn -connection $db -table $table -intname MyInt

New-AccessColumn -connection $db -table $table -tnyintname MyTiny2 -notnull
New-AccessColumn -connection $db -table $table -smlintname MySmall2 -notnull
New-AccessColumn -connection $db -table $table -intname MyInt2 -notnull

New-AccessColumn -connection $db -table $table -dblname MyDouble
New-AccessColumn -connection $db -table $table -realname MyReal
New-AccessColumn -connection $db -table $table -floatname MyFloat
New-AccessColumn -connection $db -table $table -decname MyDecimal
New-AccessColumn -connection $db -table $table -mnyname MyMoney

New-AccessColumn -connection $db -table $table -dblname MyDouble2 -notnull
New-AccessColumn -connection $db -table $table -realname MyReal2 -notnull
New-AccessColumn -connection $db -table $table -floatname MyFloat2 -notnull
New-AccessColumn -connection $db -table $table -decname MyDecimal2 -notnull
New-AccessColumn -connection $db -table $table -mnyname MyMoney2 -notnull

New-AccessColumn -connection $db -table $table -charname MyChar
New-AccessColumn -connection $db -table $table -charname MyChar2 -size 20
New-AccessColumn -connection $db -table $table -charname MyChar3 -size 20 -notnull

New-AccessColumn -connection $db -table $table -textname MyText
New-AccessColumn -connection $db -table $table -textname MyText2 -size 20
New-AccessColumn -connection $db -table $table -textname MyText3 -size 20 -notnull

New-AccessColumn -connection $db -table $table -imgname MyImg 
New-AccessColumn -connection $db -table $table -imgname MyImg2  -notnull

Close-AccessDatabase $db

 

Producing this becomes easy when using cut and paste within an editor.

Next we will look at removing columns and tables

New Access Table

I have already presented a function to create a new access table but I wasn’t very happy with it because we had to supply the full table creation SQL script.

I have altered that function so it creates an empty table. We can then use the Add-AccessColumn function to add columns. This also means I have a function to add columns to a table as and when I need them.

The function to create a table becomes

001
002
003
004
005
006
007
008
009
010
function New-AccessTable {
## assumes database is open
param (
    [string]$table,
    [System.Data.OleDb.OleDbConnection]$connection
)
    $sql = " CREATE TABLE $table"
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

Our parameters become the connection and a table name.  I don’t check that the table name exists – that will be a future refinement. We then issue a simple CREATE TABLE command.

Creating Objects

There is a post on the PowerShell Team blog about using New-Object - http://blogs.msdn.com/powershell/archive/2009/12/05/new-object-psobject-property-hashtable.aspx

The –property parameter discussed in this post is something that I had only come across recently.  In a number of recent posts I have used Add-Type to create a new object by using C# code to define a new class rather than using New-Object and Add-Member.  The post on the team blog, plus questions I’d been asked about why I used add-type got me thinking about creating objects.

In PowerShell v1 we used to do this

001
002
003
$new1 = New-Object -TypeName PSObject
$new1 | Add-Member -Name p1 -Value 1 -MemberType NoteProperty -PassThru |
        Add-Member -Name p2 -Value "a" -MemberType NoteProperty

 

We would create an object and then use Add-Member to add the properties.  The object could then be used

001
002
003
004
$new1 | ft -AutoSize

$new1.p1 = "b"
$new1 | ft -AutoSize

 

This would give the results we expect

p1 p2
-- --
1 a

p1 p2
-- --
b  a

Notice that we have been able to change the type of p1 from an integer to a string.

In v2 we get the –property parameter on New-Object

001
002
003
004
005
$p = @{
    p1 = 1
    p2 = "a"
}
$new2 = New-Object -TypeName PSObject -Property $p

In this method we create a hash table holding the properties and the values. It can be used when we create the object so that the properties are immediately populated.  This is a neater method than the multiple calls to Add-Member we saw earlier.

The object can be used as before

001
002
003
004
$new2 | ft -AutoSize
$new2.p1 = "b"

$new2 | ft -AutoSize

 

with the following results

p2 p1
-- --
a   1

p2 p1
-- --
a  b

Again notice that we can change the type of p1.

If we want to use a PSObject and create properties then using a hash table in v2 involves less typing, looks neater and is probably easier to understand.

In a number of recent posts I have been creating a C# class rather than using PSObject. This is a better variant of that technique showed to me by Doug Finke (thanks Doug)

001
002
003
004
005
006
007
008
009
010
011
012
013
$code = @"
public class testobject {
    public int p1 {get; set;}
    public string p2 {get; set;}
}
"@

Add-Type -TypeDefinition $code -Language CSharpversion3
$p = @{
    p1 = 1
    p2 = "a"
}

$new3 = New-Object -TypeName testobject -Property $p

 

I have created a simple .NET class with our two properties. Add-Type is used to add the definition into PowerShell.  I can then create an object using a hash table for the properties. This probably looks more complicated but there is a significant difference when we come to use it

001
002
003
004
$new3 | ft -AutoSize
$new3.p1 = "b"

$new3 | ft -AutoSize

 

gives us

p1 p2
-- --
1 a

Exception setting "p1": "Cannot convert value "b" to type "System.Int32". Error: "Input string was not in a correct format.""
At line:16 char:7
+ $new3. <<<< p1 = "b"
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

p1 p2
-- --
1 a

We can’t change the type of p1 because it is defined by the class we created.  This gives me an extra level of protection on what I’m doing as I can’t make a mistake and set the property to the wrong type.

PowerShell often gives us a number of ways of achieving the goal. In this case choose the one that best fits your requirements.

Technorati Tags: ,