Online PowerShell help
Published December 22, 2009One 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
Module Help Files
Published December 21, 2009The 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
Published December 18, 2009I 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.
Proxy for import-module
Published December 17, 2009I 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/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')] [ValidateNotNull()] [Parameter(ParameterSetName='Name', Mandatory=$true, Position=0, ValueFromPipeline=$true)] [Parameter(ParameterSetName='Assembly', Mandatory=$true, Position=0, ValueFromPipeline=$true)] [ValidateNotNull()] [ValidateNotNull()] [ValidateNotNull()] [ValidateNotNull()] [Switch] [Switch] [Switch] [Parameter(ParameterSetName='Name')] [Parameter(ParameterSetName='ModuleInfo', Mandatory=$true, Position=0, ValueFromPipeline=$true)] [Alias('Args')] [Switch] [Switch] begin process end |
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
Published December 14, 2009In 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.
Delete Access Table
Published December 11, 2009This 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
Published December 11, 2009We 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
Published December 10, 2009Now 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")] [parameter(ParameterSetName="unique")] [parameter(ParameterSetName="binary")] [parameter(ParameterSetName="bit")] [parameter(ParameterSetName="tinyinteger")] [parameter(ParameterSetName="smallinteger")] [parameter(ParameterSetName="double")] [parameter(ParameterSetName="real")] [parameter(ParameterSetName="float")] [parameter(ParameterSetName="image")] binary {$sql = "ALTER TABLE $table ADD COLUMN $binname BINARY" } tinyinteger {$sql = "ALTER TABLE $table ADD COLUMN $tnyintname TINYINT" } double {$sql = "ALTER TABLE $table ADD COLUMN $dblname DOUBLE" } |
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-AccessColumn -connection $db -table $table -dtname Mydate New-AccessColumn -connection $db -table $table -uniquename MyUnique New-AccessColumn -connection $db -table $table -binname MyBinary New-AccessColumn -connection $db -table $table -tnyintname MyTiny New-AccessColumn -connection $db -table $table -tnyintname MyTiny2 -notnull New-AccessColumn -connection $db -table $table -dblname MyDouble New-AccessColumn -connection $db -table $table -dblname MyDouble2 -notnull New-AccessColumn -connection $db -table $table -charname MyChar New-AccessColumn -connection $db -table $table -textname MyText New-AccessColumn -connection $db -table $table -imgname MyImg 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
Published December 10, 2009I 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
Published December 5, 2009There 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" |
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.