Jeff,
I have the very script you're looking for that does exactly what you want.
I pieced it together using a couple functions I found online and it works very well.
-It pulls a list of servers from the text file
-creates temporary data table
-creates function to retrieve data
-executes function which stores data in data table
-imports data table into sql server table
replace values in <> with your variables.
#define servers to be monitored
$server = get-content "<path>.txt"
#data table to hold results
Function out-DataTable
{
$dt = new-object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
if ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
elseif ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
return @(,($dt))
}
#function to retrieve disk information
Function Get-DisksSpace ([string]$Servername, $unit= "GB")
{
$measure = "1$unit"
Get-WmiObject -computername $serverName -query "
select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label
from Win32_Volume
where DriveType = 2 or DriveType = 3" `
| select @{Label="SystemName";Expression={$serverName.ToUpper()}} `
, Name `
, @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Capacity/$measure)}} `
, @{Label="FreeIn$unit";Expression={"{0:n2}" -f($_.freespace/$measure)}} `
, Label
}
#execute the functions
foreach ($s in $server)
{
Get-DisksSpace $s
$dataTable = Get-DisksSpace $s | where {$_.name -like "E:\*" -or $_.name -like "C:\*" -or $_.name -like "F:\*"} | out-DataTable
$connectionString = "Data Source=<server\instance>; Integrated Security=True;Initial Catalog=<databaseName>;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "<schema.table>"
$bulkCopy.WriteToServer($dataTable)
}
As a side note, I use Win32_Volume instead of LogicalDisk because the volume class returns mount points whereas the logicaldisk class returns only drive letters.