How to get logical drive information

As a DBA you might want to get storage information of a server. It may be a local server, remote server or multiple servers. 


Lets assume you want to create a report with the format given below;


[DeviceID, VolumeName, Size, FreeSpace]


If it is single server you may be able to prepare the report easily by going though the "My Computer". What if the report needs to prepared for 20 servers. 


You can create a powershell script for this task. I've created the below script to extract logical drive information for a given server and put the output in an Excel file. 



You can modify the script to work with multiple servers too.


$Exl = New-Object -comobject Excel.Application


$Exl.visible = $True


$wb = $Exl.workbooks.add()


$ws = $wb.worksheets.item(1)


$ws.Cells.Item(1,1) = "DeviceID"
$ws.Cells.Item(1,2) = "VolumeName"
$ws.Cells.Item(1,3) = "Size_GB"
$ws.Cells.Item(1,4) = "FreeSpace_GB"
$row = 2 `


gwmi -query ("select * from Win32_LogicalDisk where driveType=3") | `


foreach-object `
{ `
    
    $ws.Cells.Item($row,1) = $_.DeviceID; `
    $ws.Cells.Item($row,2) = $_.VolumeName; `
    $ws.Cells.Item($row,3) = $_.Size/1GB; `
    $ws.Cells.Item($row,4) = $_.Freespace/1GB; `
    
    $row++ `
} `


$Exl.Save("D:\Lab\Powershell\StorageInfo.xls")