Posts Tagged ‘Powershell’

Script of the Day – shutdown your VMware ESX estate with PowerCLI

February 7th, 2011 No comments

The following script is straight from

I have used it a few times and it is very effective and easy to use.

Connect-VIServer MyVIServer

# Get All the ESX Hosts
$ESXSRV = Get-VMHost

# For each of the VMs on the ESX hosts
Foreach ($VM in ($ESXSRV | Get-VM)){
# Shutdown the guest cleanly
$VM | Shutdown-VMGuest -Confirm:$false

# Set the amount of time to wait before assuming the remaining powered on guests are stuck
$waittime = 200 #Seconds

$Time = (Get-Date).TimeofDay
do {
# Wait for the VMs to be Shutdown cleanly
sleep 1.0
$timeleft = $waittime - ($Newtime.seconds)
$numvms = ($ESXSRV | Get-VM | Where { $_.PowerState -eq "poweredOn"}).Count
Write "Waiting for shutdown of $numvms VMs or until $timeleft seconds"
$Newtime = (Get-Date).TimeofDay - $Time
} until ((@($ESXSRV | Get-VM | Where { $_.PowerState -eq "poweredOn" }).Count) -eq 0 -or ($Newtime).Seconds -ge $waittime)

# Shutdown the ESX Hosts
$ESXSRV | Foreach {Get-View $_.ID} | Foreach {$_.ShutdownHost_Task($TRUE)}

Write-Host ";Shutdown Complete"


Script of the Day – import all VMs from a Datastore to VMWare ESX / ESXi

February 3rd, 2011 No comments

So your DC fell over . . but you have a copy of all your vmdks etc and would like to import them to a new ESX host . .

The following script will run you through a series of prompt and then import all VMs from a DS to the specified ESX cluster

# Code by Alan van Wyk
# This script prompts for a Cluster and a Datastore and imports all VMs from the DS to the Cluster (to be used in DR emergencies etc)

# Simple function to tidy display at prompt screen
Function Selections () # Refresh screen at top of page, shows user selections
Write-Host ("Virtual Center: ") -ForegroundColor Blue -NoNewLine; Write-Host (" " + $vc)
Write-Host ("Destination Data Center: ") -ForegroundColor Blue -NoNewLine; Write-Host (" "  + $DC2)
Write-Host ("Destination Cluster: ") -ForegroundColor Blue -NoNewLine; Write-Host (" "  + $Cluster2)
Write-Host ("Destination Datastore: ") -ForegroundColor Blue -NoNewline; Write-Host (" "  + $Datastore2)
Function quit #quits script
Function Pause ($Message="Press any key to continue...") #pause and wait for user to hit key to continue
Write-Host -NoNewLine $Message
$null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
Write-Host ""

$vc = Read-Host "Please specify your Virtual Center"
Write-Host "Connecting to Virtual Center... "
 Connect-VIServer $vc

#Select DC
 $x = @(0) * 20 # initialize the array
 $i=0 # intialize the indexer
 Get-Datacenter `
 | select -first 20 `
 | % { $x[$i]=$_.Name; "{0} {1}" -f $i++, $_.Name } # store the DCs into an array and display the array
 Write-Host ("`n Please select Destination DataCenter from index above:`n") -ForegroundColor Yellow -BackgroundColor Red
 $index = read-host index # ask for an index
 $dc2 = $x[$index] # select index

 # Select cluster
 $x = @(0) * 20 # initialize the array
 $i=0 # intialize the indexer
 Get-Datacenter $dc2    | Get-Cluster `
 | select -first 20 `
 | % { $x[$i]=$_.Name; "{0} {1}" -f $i++, $_.Name } # store the Clusters into an array and display the array
 Write-Host ("`n Please select Destination Cluster from index above:`n")    -ForegroundColor Yellow -BackgroundColor Red

 $index = read-host index # ask for an index
 $Cluster2 = $x[$index] # select index

 $x = @(0) * 20 # initialize the array
 $i=0 # intialize the indexer
 get-cluster $cluster2 | get-vmhost | Get-Datastore `
 | select -first 20 `
 | % { $x[$i]=$_.Name; "{0} {1}" -f $i++, $_.Name } # store the Datastores into an array and display the array
 Write-Host ("`n Please select Destination Datastore from index above:`n")    -ForegroundColor Yellow -BackgroundColor Red
 $index = read-host index # ask for an index
 $Datastore2 = $x[$index] # select index

 #Prompt for confirmation

 $confirmation = Read-Host "Type CONTINUE to confirm that you would like to import all VMs from $Datastore2 to $cluster2"
 If ($confirmation -cne "CONTINUE")
 Write-Host ("Settings not confirmed - disconnecting from Virtual Center") -BackgroundColor Red -ForegroundColor Yellow
 disconnect-viserver -confirm:$false

 $dsname = $Datastore2
 $datacenter = $dc2
 $cluster = $cluster2

# Code below by LucD

$ESXname = Get-Cluster $cluster | Get-VMHost | select -First 1
$dsBrowser = Get-View (Get-View (Get-VMHost -Name $ESXname).ID).DatastoreBrowser
$folder = Get-View (Get-Datacenter -Name $datacenter | Get-Folder -Name "vm").ID
$pool = Get-View (Get-Cluster -Name $cluster | Get-ResourcePool -Name "Resources").ID

foreach($dsImpl in $dsBrowser.Datastore){
 $ds = Get-View $dsImpl
 if($ds.Summary.Name -ne $dsname){continue}

 $datastorepath = "[" + $ds.Summary.Name + "]"

 $searchspec = New-Object VMware.Vim.HostDatastoreBrowserSearchSpec
 $searchSpec.matchpattern = "*.vmx"

 Write-Host "Searching in path" $datastorepath

 $task = Get-View ($dsBrowser.SearchDatastoreSubFolders_Task($datastorePath, $searchSpec))
 while ($task.Info.State -eq "running" -or $task.Info.State -eq "queued"){
 sleep 5
 if($ -ne $null){
 foreach ($file in ${

 if($file.FolderPath -match ".snapshot"){continue}
 $found = $FALSE
 foreach($vmx in $vms){
 if(($file.FolderPath + $file.File[0].Path) -eq $vmx){
 Write-Host "`tVM is registered"
 $found = $TRUE
 if (-not $found -and $task.Info.Result[0].File -ne $null){
 $vmx = $file.FolderPath + $file.File[0].Path
 $params = @($vmx,$null,$FALSE,$pool.MoRef,$null)
 Write-Host "Registering" $vmx
 $folder.GetType().GetMethod("RegisterVM_Task").Invoke($folder, $params)


Script of the Day – changing ESX NTP servers

February 2nd, 2011 No comments

The following script will amend the NTP server settings for all ESX hosts in your VC

$ntp1 = <ipaddress>"
$ntp2 = "<ipaddress>"
$oldntp = "<ipaddress>"
$oldntp2 = "<ipaddress>"

Add-PSSnapin VMware.VimAutomation.Core
connect-viserver -server <vcentername> -credential (Get-Credential)

$vmhosts = Get-VMHost

foreach ($element in $vmhosts)
Remove-VMHostNtpServer -NtpServer $oldntp -VMHost $element
Remove-VMHostNtpServer -NtpServer $oldntp2 -VMHost $element
Add-VmHostNtpServer -NtpServer $ntp1 -VMHost $element
Add-VmHostNtpServer -NtpServer $ntp2 -vmhost $element

$ntpd = Get-Vmhostservice -VMHost $element | where {$_.key -eq 'ntpd'}
Restart-VMHostService $ntpd -Confirm:$false


February 1st, 2011 No comments
VMKFSTOOLS                 VMware ESX Server Manual                 VMKFSTOOLS

     vmkfstools - VMware ESX Server file system management tool

     vmkfstools OPTIONS
     vmkfstools OPTIONS PARTITION
     vmkfstools OPTIONS DEVICE
     vmkfstools OPTIONS PATH

     VMware ESX Server is Copyright 2000-2006 VMware, Inc. All rights

     vmkfstools is a program for creating and manipulating virtual disks, file
     systems, logical volumes and physical storage devices on the VMware ESX
     Server. It supports the creation of a VMware ESX Server File System
     (VMFS) on a partition of a disk, and the management of files (such as
     virtual disks) stored on VMFS.

     OPTIONS is one or more command-line options that specify the operation.
     The file or VMFS file system being manipulated may be specified after the
     options by a relative or absolute file path name in the /vmfs hierarchy.

     The PARTITION argument is used for specifying partitions, and should be
     of the form vmhbaA:T:L:P where A, T, L and P are integers representing
     adapter, target, LUN and partition respectively. The partition digit must
     be greater than zero.  For example, vmhba0:2:3:1 refers to the first par-
     tition on LUN 3, target 2, HBA 0.

     The DEVICE argument is used for specifying devices, and should be a path
     name beginning with /vmfs/devices, which is the mount point of the device
     file system. There are sub-mounts for each device class. For example,
     /vmfs/devices/disks for local or SAN-based disks, /vmfs/devices/lvm for
     VMKernel logical volumes, /vmfs/devices/generic for generic SCSI devices
     like tape drives, etc.

     The PATH argument is used for specifying a VMFS file system or file, and
     should be an absolute or relative path that names a directory or a file
     under /vmfs.  For example, a VMFS file system could be specified via a
     path such as:

     /vmfs/volumes/<file_system_UUID> or /vmfs/volumes/<file_system_label>

     A VMFS file would be specified via a path such as:

     /vmfs/volumes/<file_system_label|file_system_UUID>/[dir]/myDisk.vmdk or

     if the current working directory is the parent directory of myDisk.vmdk.

     The long and short forms of options, shown here listed together, are

     -C, --createfs vmfs3
     -b, --blocksize #[mMkK]
     -S, --setfsname fsName
         Create a VMFS file system on the specified partition,
         e.g. vmhba1:0:0:1. The partition becomes the file systemâs head
         partition. The file block size can be specified via the ´-b´
         option. The default file block size is 1MB. The file block size
         must be either 1MB, 2MB, 4MB or 8MB. In ESX Server 3, VMFS-2 file
         systems are read-only in that users will not be allowed to create
         or modify them. VMFS-3 file systems will not be accessible from
         ESX 2.x hosts.

         The -S option sets the label of the VMFS file system, and can only
         be used in conjunction with the ´-C´ option. This label can then
         be used to specify a VMFS file system in subsequent vmkfstools
         commands or in a virtual machine configuration file. The label
         will also appear in a listing produced by ´ls -l /vmfs/volumes´
         as a symbolic link to the VMFS file system. VMFS labels can be up
         to 128 characters long. They cannot contain leading or trailing
         spaces. After creating the file system, the label can be changed
         using the command ´ln -sf /vmfs/volumes/<FS UUID>
         /vmfs/volumes/<New label>´.

     -Z, --extendfs extension-partition
         Extend an existing VMFS-3 file system with the specified head
         partition by adding another partition designated by
         ´extension-partition´. A VMFS-3 file system can have at most 32
         partitions. This option will not work on VMFS-2 file systems as
         they are read-only in ESX Server 3.

     -P, --queryfs
     -h, --human-readable
         List the attributes of a VMFS file system when used on any file or
         directory of a VMFS file system. It lists the VMFS version number,
         the number of partitions constituting the specified VMFS file
         system, the file system label (if any), file system UUID,
         available space, and a listing of the device names of all the
         partitions constituting the file system. If partitions backing
         VMFS file system go offline then the number of partitions and
         available space reported change accordingly. The ´h´ option
         causes sizes to be printed in human-readable format (such as 5k,
         12.1M, or 2.1G).

     VMFS-2 to VMFS-3 file system upgrade is a two step process. Before file
     system upgrade can begin the `vmfs2` and `vmfs3` drivers must be unloaded
     and the auxiliary file system driver, `fsaux`, should be loaded. The
     first step of upgrade uses the `-T` option. Once the first step com-
     pletes, the auxiliary file system driver, `fsaux`, must be unloaded and
     the `vmfs2` and `vmfs3` drivers reloaded. The second step of file system
     upgrade makes use of the `-u` option.

     -T, --tovmfs3
     -x, --upgradetype [zeroedthick|eagerzeroedthick|thin]
         Converts a VMFS-2 file system to VMFS-3 format, preserving all files
         on the file system. Only file systems with block size 8MB or less
         can be converted. The conversion is in-place and the auxiliary file
         system driver (`fsaux`) module must be loaded. ESX Server will
         try to ensure that no local process or remote ESX Server is currently
         accessing the VMFS file system to be converted. The conversion is a
         one-way operation and once the VMFS-2 file system is converted to
         VMFS-3 file system, it cannot be rolled back to VMFS-2. The `-x
         thin` option results in the conversion of VMFS-2 files into
         thin-provisioned VMFS-3 files (as opposed to thick-provisioned
         files). File blocks that are part of a ´thick´ file, but were
         never written to, are discarded from the file to enable storage
         overcommitment.  The default upgrade option, `-x zeroedthick`,
         retains the properties of ´thick´ files across the upgrade.
         The `-x eagerzeroedthick` option eagerly zeroes out previously
         unwritten blocks. Note that the upgrade process may run much longer
         when invoked with the `-x eagerzeroedthick` option, as opposed
         to the other two options.

     -u, --upgradefinish /vmfs/volumes/<file system label|file system UUID>
         Once the first step of file system upgrade has completed (using
         `-T`), the `vmfs2` and `vmfs3` modules must be reloaded and
         the `-u` option used to complete the upgrade.

     -c, --createvirtualdisk #[gGmMkK]
     -a, --adaptertype [buslogic|lsilogic] srcFile
     -d, --diskformat [zeroedthick|eagerzeroedthick|thick|thin]
         Create a virtual disk with the specified size on the VMFS file
         system. The size is specified in bytes by default, but can be
         specified in kilobytes, megabytes or gigabytes by adding a suffix
         of ´k´, ´m´, or ´g´ respectively. The ´adaptertype´ option
         allows users to indicate which device driver should be used to
         communicate with the virtual disk. See the `SUPPORTED DISK
         FORMATS` section for details on the disk formats supported by
         the ´-d´ option. The default disk format is ´zeroedthick´. The
         `-d eagerzeroedthick` option is used to create `thick` virtual
         disk with zeroed out contents.

     -U, --deletevirtualdisk
         Delete files associated with the specified virtual disk.

     -E, --renamevirtualdisk srcDisk
         Rename files associated with a specified virtual disk to the
         specified name.

     -i, --clonevirtualdisk srcDisk
     -d, --diskformat [rdm:<device>|rdmp:<device>|raw:<device>|thin|2gbsparse]
         Create a copy of a virtual disk or raw disk. The copy will be in
         the specified disk format. See the `SUPPORTED DISK FORMATS´
         section for details on the disk formats supported by the ´-d´
         option. To clone ESX REDO logs while preserving their hierarchy, use
         the Service Console command ´cp(1)´.

     -e, --exportvirtualdisk dstDisk
         This operation is deprecated. Use ´-i srcDisk -d 2gbsparse´ to
         achieve what it used to.

     -X, --extendvirtualdisk #[gGmMkK]
         Extend the specified VMFS virtual disk to the specified length. This
         command is useful for extending the size of a virtual disk
         allocated to a virtual machine after the virtual machine has been
         created. However, this command requires that the guest operating
         system has some capability for recognizing the new size of the
         virtual disk and taking advantage of this new size (e.g. by
         updating the file system on the virtual disk to take advantage of
         the extra space).

     -M, --migratevirtualdisk
         Migrate an ESX2 virtual disk to an ESX3 virtual disk.

     -r, --createrdm /vmfs/devices/disks/...
         Map a raw disk to a file on a VMFS file system. Once the mapping
         is established, it can be used to access the raw disk like a
         normal VMFS virtual disk. The ´file length´ of the mapping is
         the same as the size of the raw disk that it points to.

     -q, --queryrdm
         List the attributes of a raw disk mapping. When used with a
         ´rdm:<device>´ or ´raw:<device>´ specification, it prints out the
         vmhba name of the raw disk corresponding to the mapping
         referenced by the <device>. It also prints out identification
         information for the raw disk (if any).

     -z, --createrdmpassthru /vmfs/devices/disks/...
         Map a passthrough raw disk to a file on a VMFS file system. This
         allows a virtual machine to bypass the VMKernel SCSI command
         filtering layer done for VMFS virtual disks. Once the mapping is
         established, it can be used to access the passthrough raw disk
         like a normal VMFS virtual disk.

     -Q, --createrawdevice /vmfs/devices/generic/...
         Create a raw device descriptor file on a VMFS file system. This
         should only be used for generic SCSI devices like tape drives.

     -v, --verbose #
         This option is ignored for the queryrdm option. Setting the
        verbosity level will list additional information for the virtual disk

     -g, --geometry
         Get the geometry information (cylinders, heads, sectors) of a
         virtual disk.

     -w, --writezeros
         Initialize the virtual disk with zeros. Any existing data on virtual
         disk is lost.

     -j, --inflatedisk
         Convert a `thin` virtual disk to `thick` with the additional
         guarantee that any data on `thin` disk is preserved and any blocks
         that were not allocated get allocated and zeroed out.

     These are the arguments that can be passed to the ´-d´ option for the
     ´-c´ (create virtual disk) and ´-i´ (clone virtual disk) operations.

         This is the default option when creating new virtual disks.
         A zeroed thick disk has all space allocated at creation
         time, and this space is wiped clean of any previous contents on the
         physical media.

         An eager zeroed thick disk has all space allocated and zeroed out at
         creation time. Such disks may take longer time during creation
         compared to other disk formats.

         A thick disk has all space allocated at creation time. This
         space may contain stale data as it exists on the physical media.

         Space required for thin-provisioned virtual disk is allocated and
         zeroed on demand as opposed to upon creation.

         Virtual compatibility mode raw disk mapping.

         Physical compatibility mode (pass-through) raw disk mapping.

         Raw device.

         A sparse disk with 2GB maximum extent size. Disks in this format
         can be used with other VMware products.

     -s, --scan adapterName
         Scan a specified adapter for devices and/or LUNs. This option is
         especially useful for adapters which are connected to storage area
         networks (SANs). If a new device or LUN becomes accessible via the
         adapter, ESX Server will register this new device or LUN and
         make it usable by virtual machines. If an existing device or LUN
         is not being used and now appears to be gone, it will be removed from
         ESX Server data structures and will no longer be accessible to
         virtual machines.

     -L, --lock [reserve|release|lunreset|targetreset|busreset] /vmfs/devices/disks/...
         Manage SCSI reservations on physical targets or LUNs. These
         commands can interrupt the operations of other servers on a storage
         area network (SAN), so they should be used with great caution. The
         `reserve` command will reserve the specified raw disk.
         After the reservation, other servers will get a SCSI reservation
         conflict if they attempt to access that disk, but the server that did
         the reservation will be able to access the disk normally. The
         `release` command will release the reservation on the specified
         disk. Other servers will be able to access the disk again. The
         `lunreset` command resets a single LUN only instead of all
         the LUNs attached to a target. The `targetreset`  and `busreset`
         command will reset target and bus respectively causing SCSI
         reservations to be dropped. This option is potentially disruptive to
         all servers sharing the storage and is only meant to be used
         in the context of Clustering.

     vmkfstools -C vmfs3 -b 1m -S myVMFS vmhba1:3:0:1

     Creates a new VMFS-3 file system with label `myVMFS` on the 1st partition
     of target 3 and LUN 0 of vmhba adapter 1. The file block size is 1MB.

     vmkfstools -Z vmhba0:1:2:4 vmhba1:3:0:1

     Extends the newly created file system by adding the 4th partition of tar-
     get 1 and LUN 2 of vmhba adapter 0. As a result, the file system will
     span two partitions - vmhba1:3:0:1 and vmhba0:1:2:4. Here, vmhba1:3:0:1
     is the name of the head partition of the file system that is to be

     vmkfstools -c 2048m /vmfs/volumes/myVMFS/myOS.vmdk

     Creates a 2GB VMFS virtual disk with the name `myOS.vmdk` on the VMFS
     file system named `myVMFS`. This virtual disk may then be accessed by a
     virtual machine.

     vmkfstools -i /vmfs/volumes/templates/gold-master.vmdk /vmfs/vol-

     Clones the contents of a gold master virtual disk image from a template
     repository to a virtual disk named `myOS.vmdk` on the VMFS file system
     called myVMFS. A virtual machine can be configured to use this virtual
     disk by adding the following lines to its configuration file:

       scsi0:0.present = TRUE
       scsi0:0.fileName = /vmfs/volumes/myVMFS/myOS.vmdk

     vmkfstools -r /vmfs/devices/disks/vmhbaX:Y:Z:0 foo-rdm.vmdk

     Creates a mapping file, `foo-rdm.vmdk`, to the raw disk `vmhbaX:Y:Z:0`.
     The mapping file `foo-rdm.vmdk` is created in the current VMFS working
     directory, and can be used in a virtual machine in the same manner as in
     the previous example.

     vmkfstools -s vmhba1

     Scan adapter vmhba1 for any new targets or LUNs and for any removed tar-
     gets or LUNs.

VMnix                            May 11, 2006                            VMnix
Categories: VMWare Tags: , ,

Script of the day – writing to a cell in Excel

January 31st, 2011 No comments

Ever needed to inject info to a cell in an Excel spreadsheet – repeatedly . . . you can do so from Powershell . . like so:

Function write-excel ($ExcelFile, $WorkSheet, $Column, $Row, $Value)
	 Write value(s) to Excel cells
	 No return - Updates an Excel Cell
		e.g. Specify the requested parameter, if not specified this will be prompted
		PS C:\&gt; write-excel $ExcelFile $WorkSheet $Column $Row $Value
	$File = (ls $ExcelFile).FullName
	# Open Excel
	$Excel = New-Object -Com Excel.Application

	# Prevent Overwrite and Macro Prompt

	# Open Template File

	$WorkBook = $Excel.Workbooks.Open($File)
	$WorkSheet = $WorkBook.Worksheets.Item(&quot;$WorkSheet&quot;)
	$Worksheet.Cells.Item($Row,$Column) = $Value

	# Setting All Variables to Null

 	$WorkBook = $Null
	$WorkSheet = $Null
	$Excel = $Null

	# Releasing Object Wrapper


Script of the day – ping a range of IP addresses

January 28th, 2011 No comments

Friday today, so just a quick and easy script . . nothing clever

In PowerShell, ping functionality can be handled by using the ‘test-connection’ cmdlet, or simply using .Net

A ping using test-connection is just:

$object = New-Object system.Net.NetworkInformation.Ping

You can wrap it as function to get even more out of this:

function ping-ip {
param( $ip )
trap {$false; continue}
$timeout = 1000
$object = New-Object system.Net.NetworkInformation.Ping
(($object.Send($ip, $timeout)).Status -eq 'Success')
ping-ip &quot;;

You can use this function to ping IP addresses or hostnames and will get either TRUE or FALSE as a return . . so you can use this in an if statement or similar


If (Ping-IP &quot;labserver001.local&quot;){write-host &quot;Lab domain appears to be online&quot;}

You can add a loop to create your own network segment scan:

0..255 | % { $ip = &quot;192.168.2.$_&quot;; &quot;$ip = $(ping-ip $ip)&quot; }

Powershell – Script of the Day – Menu-Plus

January 27th, 2011 No comments

Yesterday, we created a simple Powershell menu

Today’s script is a feeder for the menu, that allows you use any filed of an object as your menu source and return a different field of it as your return value.

For Example:
the following query will give you a list of the filenames in the current directory, once you select an item, it will return that ‘LastWriteTime’ of that file. – Simple

PS:8 &amp;gt;menu-plus -object (gci) -displayfield &quot;name&quot; -menuTitle Please select a file -returnfield &quot;LastWriteTime&quot;
Function menu-plus ($object, $displayfield, $menuTitle, $returnfield)

Feeder object to Menu Function to Enable return of different field in Object

Requires:Menu Function
Creates a menu that allows return of a different object field.

$object - the array that we're sselecting from
$Displayfield : Name of the field that will be displayed for selection
$MenuTitle : The prompt to included at top of menu
$Returnfield : Field to be returned - defauilts to all fields


PS: &amp;gt;$a = gci
PS: &amp;gt;$b=menu-plus -object $a -displayfield &quot;name&quot; -menuTitle Please select a file -returnfield &quot;LastWriteTime&quot;
¦ Please select a file ¦

PS:87 &amp;gt;$b.LastWriteTime
25 March 2010 15:20:53
$menulist = @()
ForEach ($item in $object){$menulist += $item.$displayfield}
$returnval = menu $menulist $menuTitle $returnfield
$output = $object | where {$_.$displayfield -eq $returnval} | Select $returnfield
return $output

Script of the Day – Powershell Menu Select list

January 26th, 2011 No comments

Sometimes you’d like to prompt a user to select an option from a list in Powershell . .

try this:

Function menu
	 Generate a small &quot;DOS-like&quot; menu.
	  Allows you to pick  a menuitem using up and down arrows, select by pressing ENTER
		e.g. Specify the requested parameter, if not specified this will be prompted
		C:\&gt; $Options = &quot;Option1&quot;, &quot;Option2&quot;, &quot;Option3&quot;, &quot;Option4&quot;, &quot;Option5&quot;
		C:\&gt; $selection = Menu $Options &quot;Please select an Option?&quot;

		* Please select an Option? *


		write-host $selection

    param ([array]$menuItems, $menuTitle = &quot;MENU&quot;, [switch]$quit)
    $vkeycode = 0
    $pos = 0
	If ($quit){$menuItems += &quot;Quit&quot;}
    DrawMenu $menuItems $pos $menuTitle
    While ($vkeycode -ne 13) {
        $press = $host.ui.rawui.readkey(&quot;NoEcho,IncludeKeyDown&quot;)
        $vkeycode = $press.virtualkeycode
        Write-host &quot;$($press.character)&quot; -NoNewLine
        If ($vkeycode -eq 38) {$pos--}
        If ($vkeycode -eq 40) {$pos++}
        if ($pos -lt 0) {$pos = 0}
        if ($pos -ge $menuItems.length) {$pos = $menuItems.length -1}
        DrawMenu $menuItems $pos $menuTitl
	If ($($menuItems[$pos]) -eq 'Quit'){return}
	{Write-Output $($menuItems[$pos])}

function DrawMenu {
    ## supportfunction to the Menu function above
    param ($menuItems, $menuPosition, $menutitle)
    $fcolor = $host.UI.RawUI.ForegroundColor
    $bcolor = $host.UI.RawUI.BackgroundColor
    $l = $menuItems.length + 1
    $menuwidth = $menutitle.length + 4
    Write-Host &quot;`t&quot; -NoNewLine
    Write-Host (&quot;#&quot; * $menuwidth) -fore $fcolor -back $bcolor
    Write-Host &quot;`t&quot; -NoNewLine
    Write-Host &quot;# $menutitle #&quot; -fore $fcolor -back $bcolor
    Write-Host &quot;`t&quot; -NoNewLine
    Write-Host (&quot;#&quot; * $menuwidth) -fore $fcolor -back $bcolor
    Write-Host &quot;&quot;
    Write-debug &quot;L: $l MenuItems: $menuItems MenuPosition: $menuposition&quot;
    for ($i = 0; $i -le $l;$i++) {
        Write-Host &quot;`t&quot; -NoNewLine
        if ($i -eq $menuPosition) {
            Write-Host &quot;$($menuItems[$i])&quot; -fore $bcolor -back $fcolor
        } else {
            Write-Host &quot;$($menuItems[$i])&quot; -fore $fcolor -back $bcolor

Powershell – function to Query a SQL database

January 21st, 2011 2 comments

# Today’s quick and easy function is a simple one that I regularly use to query a SQL database.

It will return an object containing the result of your query – so makes SQL access very simply from Powershell

Function QuickQuery-SQL {
## Performs a T-SQL query against an SQL 2000/2005/2008
## with the result returned as as a PowerShell object.
## QuickQuery-SQL "server" "database" "t-sql query"
## Find NodeID from FindIt DB
## $output = QuickQuery-SQL "MySqlServername" "MyDatabase" "SELECT * FROM MyTable WHERE name = 'MySearchValue'"
Param ($server = "MyServer\MyInstance",
$database = "master",
$query = "SELECT * FROM sysdatabases",
$connectionName = "PS QuickQuery SQL",
$commandTimeout = 15)
$conn =new-object ('System.Data.SqlClient.SqlConnection')
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
Write-Debug ("Function: Query-SQL: $server $database")
if (test-path variable:\conn) {
} else {
$conn =new-object ('System.Data.SqlClient.SqlConnection')
$sqlCmd =New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandTimeout = $CommandTimeout
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $conn
$data = $sqlCmd.ExecuteReader()
while ($ -eq $true) {
$max = $data.FieldCount -1
$obj =New-Object Object
For ($i = 0; $i -le $max; $i++) {
$name = $data.GetName($i)
if ($name.length -eq 0) {
$name = "field$i"
$obj |Add-Member Noteproperty $name -value $data.GetValue($i) -Force
$conn = $null