SAPIEN Forums

Code: Select all

[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}}