SAPIEN Solutions

[reflection.assembly]::loadwithpartialname("System.Data") | Out-Null

function Get-DriveInventory {
PROCESS {
#get drives from WMI
$drives = gwmi win32_logicaldisk -comp $_ -filter "drivetype=3"

#construct output objects
foreach ($drive in $drives) {
$obj = New-Object psobject
$obj | Add-Member NoteProperty ComputerName $_
$obj | Add-Member NoteProperty DriveLetter $drive.deviceid
$free = $drive.freespace/1MB -as [int]
$obj | Add-Member NoteProperty AvailableSpace $free
$total = $drive.size/1MB -as [int]
$obj | Add-Member NoteProperty TotalSpace $total

#write output object
Write-Output $obj
}
}
}

function Write-DriveInventory {
BEGIN {
#open database connection
$conn = New-Object System.Data.OleDb.OleDbConnection
#will need to adjust the path
$connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DriveInventory.accdb;Persist Security Info=False;"
$conn.connectionstring = $connstr
$conn.open()

#create database command
$cmd = New-Object system.Data.OleDb.OleDbCommand
$cmd.connection = $conn
}
PROCESS {
#construct SQL statement using input objects' properties
$now = Get-Date -form g
$sql = "INSERT INTO DriveInventory (ComputerName,DriveLetter,AvailableMB,TotalMB,LastUpdated) VALUES ("
$sql += "'" + $_.ComputerName + "',"
$sql += "'" + $_.DriveLetter + "',"
$sql += "'" + $_.AvailableSpace + "',"
$sql += "'" + $_.TotalSpace + "',"
$sql += "'" + $now + "')"
$cmd.commandtext = $sql
$cmd.executenonquery() | Out-Null
}
END {
$conn.close()
}
}

function Get-FileName {
$computer = Read-Host "Filename of computer names?"
return $computer
}

# get the filename
$f = Get-FileName

# Use this command-line for task #1
Get-Content $f | Get-DriveInventory | Write-DriveInventory

# Use this command-line for task #2
Get-Content $f | Get-DriveInventory | Export-Csv C:\DriveInventory.csv

# Use this command-line for task #3
Get-Content $f | Get-DriveInventory | Format-Table ComputerName,DriveLetter,AvailableSpace,TotalSpace,@{Label="PercentFree";Expression={$_.AvailableSpace/$_.TotalSpace*100}}