Using a Hashtable for Key/Value pairs

This is a simple example of how we can use a hashtable to store and update key/value pairs:

#create hashtable
$states = @{}

#add a key/value pair
$states.Add("ExampleKey", "ExampleValue1")

#two ways of getting the value
write-host $states.Get_Item("ExampleKey")
write-host $states.ExampleKey

#update the value for the key 'ExampleKey'
$states.Set_Item("ExampleKey", "ExampleValue2")

#two ways of getting the value
write-host $states.Get_Item("ExampleKey")
write-host $states.ExampleKey

 

Detect if a Computer is a Member of a Specified AD Group

Detect if the Current User is a Member of a Specified AD Group

This script will detect if the current user is a member of a specified AD Group using the [adsiSearcher] type accelerator:

#remember that this is used as a regular expression (using -match), so escape any brackets etc with a back slash
$ADGroup = "Example_AD_Group"
$machineName = "AlkaneMachine"

#check if computeris a member of the group
$ADGroupCount = (([ADSISearcher] "(&(objectCategory=computer)(objectClass=computer)(cn=$machineName))").FindOne().Properties.memberof -match "CN=$ADGroup,").count

if ($ADGroupCount -gt 0)
{
    #computer is a member - do something!
}

 

Checking if System.Object[] contains a value

This isn’t the most interesting of blog posts admittedly.  But I was using the SQL Server Management Objects in my PowerShell script to return a result set from a stored procedure in SQL server.  The data set returned from the Invoke-sqlcmd command was a System.Object[] type.  I wanted to see if a column (the Department column) in the result set contained a specific value. So firstly I obtained the result set like so:

# Load the SQL Server SMO library
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
Import-Module "sqlps" -DisableNameChecking

#SQL Server
$SQLServer = "ALKANESQLSERVER"
#Database
$SQLDatabase = "ALKANESQLDATABASE"
#Return results from stored procedure
$sql_departments = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query 'exec [dbo].[ALKANESTOREDPROCEDURE]'

In PowerShell 3 it was a trivial exercise:

if ($sql_departments."Department" -contains "AlkaneDepartment")
{

}

however in PowerShell 2 it required slightly more legwork, and was a little slower to run:

if (($sql_departments | Select -ExpandProperty Department) -contains "AlkaneDepartment")
{

}

 

 

Export Dynamic Data to a CSV using PowerShell

This post explains how to export dynamic data to a CSV using PowerShell.  I find that hashtables are quite useful for various scenarios when scripting in PowerShell.  We can also utilise them when exporting data using Export-CSV.

I provide two approaches to this.  Both approaches work by adding hashtables to an array.  By default if you export an array of hashtables to an array you will lose the column ordering.  With PowerShell 2 we can circumvent this issue by using ‘Select’ and specifying the order of the columns we would like to select:

$csvnamePS2 = "C:\Temp\ps2_" + (Get-Date -format "dd-MM-yyyy-HH-mm") + ".csv"
$global:csvarrayPS2 = @()

function writeToCSVPS2()
{  
    param (       
        [string]$FirstName,
	[string]$LastName
    )
	$wrapper = New-Object -TypeName PSObject -Property @{ FirstName = $FirstName; LastName = $LastName;} | Select FirstName, LastName
	$global:csvarrayPS2 += $wrapper
}

writeToCSVPS2 "Captain" "Hook"
writeToCSVPS2 "Peter" "Pan"

$global:csvarrayPS2 | Export-Csv -NoTypeInformation -Path $csvnamePS2

With PowerShell 3 we can simplify this, by specifying the [ordered] type for our hashtable:

$csvnamePS3 = "C:\Temp\ps3_" + (Get-Date -format "dd-MM-yyyy-HH-mm") + ".csv"
$global:csvarrayPS3 = @()

function writeToCSVPS3()
{  
    param (       
        [string]$FirstName,
	[string]$LastName
    )
	$wrapper = [ordered]@{ FirstName = $FirstName; LastName = $LastName;}
	$global:csvarrayPS3 += New-Object psobject -property $wrapper
}

writeToCSVPS3 "Captain" "Hook"
writeToCSVPS3 "Peter" "Pan"

$global:csvarrayPS3 | Export-Csv -NoTypeInformation -Path $csvnamePS3

 

Get Filename and Versions in Folder and Subfolders

This is just a quick one-liner that I use to get all filenames and their version from a folder and sub folders.  I tend to use it every now and then to compare two folder structures on different machines, and then I can use a text comparison tool (such as www.diffnow.com) to compare the output:

You should first open a Powershell shell and CD to the root folder.  Then paste the following line and run it.  The output will go to c:\temp\filevers.txt.

get-childitem * -include *.* | foreach-object { "{0}`t{1}" -f $_.FullName, [System.Diagnostics.FileVersionInfo]::GetVersionInfo($_).FileVersion } | out-file c:\temp\filevers.txt

Yes, I’m sure there are free tools to accomplish this.  But this is just another option.

Write to the Windows Installer Binary Stream using Powershell

Here’s a small chunk of code from a recent request asking how to stream a file into the binary table of a Windows Installer using Powershell:

$query = "INSERT INTO `Binary` (`Name`,`Data`) Values(?,?)"
$View = $database2.GetType().InvokeMember("OpenView","InvokeMethod",$Null,$database2,($query))
$binaryrecord = $windowsInstaller.GetType().InvokeMember("CreateRecord", "InvokeMethod", $null, $windowsInstaller, 2)  
$binaryrecord.GetType().InvokeMember("StringData", "SetProperty", $null, $binaryrecord, @(1, "AlkaneBinary"))
$fileToStream = "C:\Alkane\AlkaneFile.txt"
$binaryrecord.GetType().InvokeMember("SetStream","InvokeMethod", $null, $binaryrecord, @(2, $fileToStream))
$View.GetType().InvokeMember("Execute", "InvokeMethod", $Null, $View, $binaryrecord)
$View.GetType().InvokeMember("Close", "InvokeMethod", $Null, $View, $Null)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($View) | Out-Null

Count members of an AD Group and write to CSV

This script finds all AD groups beginning with ‘MSI – ‘ or ‘App-V – ‘ and returns the number of members it contains.  Note that it doesn’t recurse nested groups!  It then outputs the results to a CSV file.

$csvPath = "C:\Alkane\AlkaneGroups.csv"

"Group,Count" | Out-File -filepath $csvPath -Append -encoding ASCII

$groups = Get-ADGroup -filter {(name -like "MSI - *") -or (name -like "App-V - *")}
foreach($group in $groups){
	$countUser = (Get-ADGroup $group -Properties *).member.count
	Write-Host "The group $($group.Name) has $countUser user(s)."
	"""$($group.Name)"",$countUser" | Out-File -filepath $csvPath -Append -encoding ASCII
}

 

Query a Windows Installer (MSI) using Powershell

This is just a quick example of how we can query a Windows Installer using Powershell code:

$msiOpenDatabaseModeReadOnly = 0
$msiOpenDatabaseModeTransact = 1

$windowsInstaller = New-Object -ComObject windowsInstaller.Installer

$pathToMSI = "C:\Users\xxxx\Desktop\AlkaneExample.msi"

$database = $windowsInstaller.GetType().InvokeMember("OpenDatabase", "InvokeMethod", $null, $windowsInstaller, @($pathToMSI, $msiOpenDatabaseModeReadOnly))

$query = "SELECT Property, Value FROM Property"
$propView = $database.GetType().InvokeMember("OpenView", "InvokeMethod", $null, $database, ($query))
$propView.GetType().InvokeMember("Execute", "InvokeMethod", $null, $propView, $null) | Out-Null
$propRecord = $propView.GetType().InvokeMember("Fetch", "InvokeMethod", $null, $propView, $null)
		
while  ($propRecord -ne $null)
{
	$col1 = $propRecord.GetType().InvokeMember("StringData", "GetProperty", $null, $propRecord, 1)
	$col2 = $propRecord.GetType().InvokeMember("StringData", "GetProperty", $null, $propRecord, 2)
 
	write-host $col1 - $col2
	
	#fetch the next record
	$propRecord = $propView.GetType().InvokeMember("Fetch", "InvokeMethod", $null, $propView, $null)	
}

$propView.GetType().InvokeMember("Close", "InvokeMethod", $null, $propView, $null) | Out-Null          
$propView = $null 
$propRecord = $null
$database = $null

PLEASE NOTE

When writing Powershell functions to return single values (a string, for example) please ensure you pipe any calls to ‘Execute’ or ‘Close’ to ‘Out-Null’ (please see above).  This will stop the function from returning a System.Object[] (since Powershell functions return the whole output from the function, and not just the value after the ‘return’ statement)!

A Powershell Array of Hashtables instead of a Multidimensional Array

When scripting and coding a solution we’re often required to read from back-end databases.  Sometimes if we want to reduce the amount of ‘reads’ to the database (if, for example, we know the information isn’t going to change very frequently) or we want faster access by storing the data in-memory, we may choose to write all the information we’ve retrieved from the database into an array.

 

Once we’ve stored the data inside our (multidimensional) array, retrieving a particular record is sometimes an obstacle.  Particularly when we’re using unnamed indexes to reference records and array dimensions.  To circumvent this issue, I like to store my records in an (single dimension) array of hashtables!  This allows me to retrieve records very easily based on specific hashtable names!

$allRecords = @()

$allRecords += (@{ 
						value="alkane1";
						text="solutions1";
						additional="this is some additional data for my record";
						enabled=$true;
					})
				
$allRecords += (@{ 
						value="alkane2";
						text="solutions2";
						additional="this is some additional data for my record";
						enabled=$false;
					})
						
#retrieve selected record from array
$selectedRecord = $allRecords | Where-Object { $_.value -eq "alkane1" }
if ($selectedRecord -ne $null)
{
	write-host $selectedRecord.value
	write-host $selectedRecord.text
	write-host $selectedRecord.additional
	write-host $selectedRecord.enabled
}

 

Using Powershell to Databind a Combobox with a Value and some Text

Creating a combobox in Powershell and adding an item to it is a relatively trivial task:

$combobox = New-Object System.Windows.Forms.ComboBox
$combobox.Items.add("alkane")

The text of the selected combobox can be obtained like so:

$combobox.Text

This is a combobox in its most simplistic form. The trouble is, I’m from an ASP.Net background and it’s often handy to bind a value (the hidden reference to the selected item – usually a primary key integer) AND some text (the value that the user sees in the combobox – the ‘friendly’ name).  This requires a bit more leg work to implement and can be done by using a datatable, adding data to the datatable, and binding this datatable to our combobox like so:

[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
[void][System.Windows.Forms.Application]::EnableVisualStyles()

#create a form
$form = New-Object System.Windows.Forms.Form

#create a datatable to bind to our combobox
$datatable = New-Object system.Data.DataTable
		
#Define Columns
$col1 = New-Object system.Data.DataColumn "Value",([string])
$col2 = New-Object system.Data.DataColumn "Text",([string])

#add columns to datatable
$datatable.columns.add($col1)
$datatable.columns.add($col2)
		
#Create a row
$datarow1 = $datatable.NewRow()

#Enter data in the row
$datarow1.Value = "Example Value 1"
$datarow1.Text = "Example Text 1"

#Add the row to the datatable
$datatable.Rows.Add($datarow1)

#Create another row
$datarow2 = $datatable.NewRow()

#Enter data in the row
$datarow2.Value = "Example Value 2"
$datarow2.Text = "Example Text 2"

#Add the row to the datatable
$datatable.Rows.Add($datarow2)

#create a combobox
$combobox = New-Object System.Windows.Forms.ComboBox		
$combobox.Add_SelectedIndexChanged({
		#output the selected value and text
		write-host $combobox.SelectedItem["Value"] $combobox.SelectedItem["Text"]
})

#clear combo before we bind it
$combobox.Items.Clear()

#bind combobox to datatable
$combobox.ValueMember = "Value"
$combobox.DisplayMember = "Text"
$combobox.Datasource = $datatable

#add combobox to form
$form.Controls.Add($combobox)	

#show form
[void]$form.showdialog()