By: Richard Vantrease | Comments (6) | Related: > PowerShell
Problem
I work in an environment where getting new software approved is often quite difficult. We have a disk space monitor in place, but it doesn't provide the type of monitoring my team was looking for. Monitoring disk space is critical for DBAs In addition, we use mount points, which is not supported by our existing disk space monitor.
Solution
My solution is PowerShell centric. Many of you may wonder why a DBA would use PowerShell. I tend to use the following guidelines to determine whether I will use PowerShell or TSQL to perform a task.
- TSQL - If the primary function of the task is set based (Select, Insert, Update, Delete), I use TSQL
- PowerShell - If the task I am trying to perform has a significant amount of flow control, or needs to access objects that are not SQL, I tend to use PowerShell
Get Volume Information
Let's go get the information for the disks. I'm going to assume you have PowerShell 2.0 at a minimum installed on your server or workstation. If you don't have PowerShell installed, check here for instructions from the Scripting Guy Blog. Let's use the Integrated Scripting Environment (ISE) to do our work. You can find the shortcut in Start|All Programs|Accessories|Windows PowerShell. For Windows 8, search for "PowerShell ISE". The PowerShell 2.0 ISE will open up with a script window, a command window, and an output window. The PowerShell 3.0 ISE will open up with a script window, and a combined command/output window.
PowerShell does a fantastic job of accessing WMI objects. We are going to take advantage of this fact to get information about the volumes. Type the following command in PowerShell:
get-WMIObject Win32_Volume
Notice that you get a ton of information about the different volumes. This can be a bit overwhelming. Information about the information returned by Win32_Volume can be seen here. We can improve on the information displayed by piping it through the format-table Cmdlet.
get-WMIObject Win32_Volume | format-Table Name, Label, Freespace, `
Filesystem, BlockSize, Capacity -Auto
This is a little better. By the way, notice the line continuation character which is the (`) usually above the tab key. We can still improve further, to do so we are going to use a filter.
$Filter = @{Expression={$_.Name};Label="DiskName"}, `
@{Expression={$_.Label};Label="Label"}, `
@{Expression={$_.FileSystem};Label="FileSystem"}, `
@{Expression={[int]$($_.BlockSize/1KB)};Label="BlockSizeKB"}, `
@{Expression={[int]$($_.Capacity/1GB)};Label="CapacityGB"}, `
@{Expression={[int]$($_.Freespace/1GB)};Label="FreeSpaceGB"}
Get-WmiObject Win32_Volume | Format-Table $Filter -AutoSize
Here is a sample of the data I got by running the above:
DiskName Label FileSystem BlockSizeKB CapacityGB FreeSpaceGB -------- ----- ---------- ----------- ---------- ----------- C:\ NTFS 4 195 85 D:\ NTFS 4 98 68 G:\ DataVol2012 NTFS 4 736 630 E:\ Filler NTFS 4 50 50 F:\ Backups NTFS 64 85 0
Now let's turn the code into a simple function that we can use later. You will notice a couple of new things. First of all, I added a couple of parameters; ComputerName and Raw. One nice feature of Get-WmiObject is that you can run the command remotely against another computer. I also added a default for ComputerName to default to the local computer if no parameter is given.
The second parameter Raw is sort of a standard of mine. It allows you to either run the function to get information in a formatted table, or in raw form. Raw form is handy if you want to pass the data along to another function or script.
function get-DiskVolumes
{
param(
[string]$ComputerName=$env:COMPUTERNAME,
[switch]$Raw
)
$Filter = @{Expression={$_.Name};Label="DiskName"}, `
@{Expression={$_.Label};Label="Label"}, `
@{Expression={$_.FileSystem};Label="FileSystem"}, `
@{Expression={[int]$($_.BlockSize/1KB)};Label="BlockSizeKB"}, `
@{Expression={[int]$($_.Capacity/1GB)};Label="CapacityGB"}, `
@{Expression={[int]$($_.Freespace/1GB)};Label="FreeSpaceGB"}
if($Raw){Get-WmiObject Win32_Volume -ComputerName $ComputerName | Select-Object $Filter}
else{Get-WmiObject Win32_Volume -ComputerName $ComputerName | Format-Table $Filter -AutoSize}
}
Next Steps
- Run the above script to load the function into memory.
- Check the list of functions by typing dir function:
- Run the function by typing get-DiskVolumes in the PowerShell_ISE command window.
- Work with the function using the -ComputerName parameter to run on a remote computer.
- Look at the difference in the output when using the -Raw switch parameter.
- Coming Soon: Add the function to a module so you can load it easier.
About the author
Richard Vantrease is a lead SQL Server DBA for a large company. His specialties include architecture, the data engine and automation.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
