PowerGUI – Powershell Remote Installed Hotfixes & Patches

Here is a Node Script to get the installed patches or hotfixes on a remote computer:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’) | Out-Null
$name = [Microsoft.VisualBasic.Interaction]::Inputbox(“Enter the IP or Name of the server:”)
Get-WmiObject Win32_QuickFixEngineering -ComputerName “$($name)” | Where-Object{$_.HotFixID -ne “File 1”} | Write-Output

I also have a system monitor to keep track of disk space and CPU/Memory Usage…Click here!

If you found this useful, subscribe.


Powershell Disk Space

If you found this useful, subscribe.

This is a newer, cleaner version of a previous post.



# Getting disk information
[System.Object[]]$disks = @()
$Private:wmiDisks = (Get-WmiObject Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3})

foreach ($Private:wmiDisk in $wmiDisks){
$Private:tmp = New-Object -TypeName System.Object
$tmp | Add-Member -Name DeviceID -Value $wmiDisk.DeviceID -MemberType NoteProperty
$tmp | Add-Member -Name FreeSpace -Value $([Math]::Truncate($wmiDisk.FreeSpace / 1GB)) -MemberType NoteProperty
$tmp | Add-Member -Name TotalSpace -Value $([Math]::Truncate($wmiDisk.Size / 1GB)) -MemberType NoteProperty
$disks += $tmp}

I also have made a monitoring system free for your use, here.


Powershell System Monitoring

In the next couple days I plan on releasing to you a script and database that you can use to get information about your servers and track them over time. What they do is relatively simple but should help many of you with some very common admin headaches!


It is now here!




Technorati Code QQXYEUN7JHW2

Powershell – How to Remove Array Elements

Normally, to remove array elements you simply assign the elements you want to keep back to the array’s name. What is really happening is the array is getting copied to a new array with the same name like this:

$array = $array[0..($i – 1)] + $array[($i – 1)..$array.Count-1]

So here is something strange that I noticed today. If you are looping through an array, based on the length of the array, and remove an element; the loop will get stuck in an infinite loop. Here is an example:

[string[]]$array = (1..10)

for ($i = 0; $i -lt $array.Count; $i += 1){

$array = $array[0..($i – 1)] + $array[($i – 1)..$array.Count-1]


From what I can see, it looks like the $array.Count in argument list for the for loop only gets calculated once. When an element is removed, it can never break out of the loop because the condition never gets satisfied. If you were to do this in VB it would error out, but not in Powershell, this is a possible bug they will have to fix in a later release.

If you found this useful, subscribe.

SQL Server Maintenance Plans vs. Powershell Scripts

I have been working on maintenance plans for some time now and while they are very powerful I am starting to wonder if Powershell might be a better platform to perform my server maintenance. I am starting to lean towards Powershell and here is why. Keep in mind that this is up for debate and I am sure this could stir up some controversy. 

With Powershell it is easier to get access to the OS.  With maintenance plans it gets a little more complicated. Maintenance plans are built for SQL DBAs, not for the person who owns the whole box. Sometimes DBAs have limited access to the server, so for those people Powershell isn’t the best choice. Also if you don’t have a programming background, Powershell might not be for you although you might want to consider it since there are some very simple scripts that can get some serious information to you very quickly, but I digress.

Scripts are highly portable. SSIS packages are as well but you can’t edit them easily without BIDS. Notepad is all you need for Powershell.

I am going to keep you all posted as I am making changes to my scripts.  Once I make a really good script I will post it.

PowerGUI – Using Powershell to get Disk Space

This is a script you can use in PowerGUI as a script node. This handy little script will give you the basic information of your drives so you can figure out if you disks are going to run out of space! I had to make a modification to this script, it works much better now! I apologize to everyone who was using the older script, which works well in Powershell but not PowerGUI.

Note: This will work with your existing credentials.

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’) | Out-Null
$name = [Microsoft.VisualBasic.Interaction]::Inputbox(“Enter the IP or Name of the server:”)

$drives = gwmi win32_logicaldisk -ComputerName $name | where{ $_.drivetype -eq 3 }
$driveArray = @()
$counter = 0
foreach ($drive in $drives){
$driveArray += New-Object -TypeName System.Object
$driveArray[$counter] | Add-Member -MemberType NoteProperty -Name name -Value $drive.Name
$driveArray[$counter] | Add-Member -MemberType NoteProperty -Name percentfree -Value ([int] [System.Math]::Round(($drive.FreeSpace) / $drive.Size * 100))
$driveArray[$counter] | Add-Member -MemberType NoteProperty -Name sizeGB -Value ([int]($drive.Size / 1Gb))
$driveArray[$counter] | Add-Member -MemberType NoteProperty -Name freespaceGB -Value ([int]($drive.FreeSpace / 1Gb))
$counter +=1
$driveArray | Format-Table

A slimmed down version for the Powershell console can be found here: Console Script

I also made a system monitoring script, free for you to use.

If you found this useful, subscribe to my blog.

PowerGUI – Powershell SQL Server Database Information Script Node

This short script will prompt you for the name or IP of the server you wish to connect to. As long as you have the correct rights to that server you can use this script. Hope this helps Fernando!

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’) | Out-Null
$name = [Microsoft.VisualBasic.Interaction]::Inputbox(“Enter the IP or Name of the server:”)
$server = New-Object(‘Microsoft.SqlServer.Management.Smo.Server’) $name
$server.Databases | format-table Name,Size,RecoveryModel,PrimaryFilePath -AutoSize

If you found this useful, subscribe.