Using powershell to gather disk used into a sql table

Question

I have a script which connects to a db to get a list of servers to query and then queries them for disk space using a wmi query. Now I'd like to get these results into a table. I've run into a few issues.

invoke-sqlcmd is a pain in rear to use with a stored procedure or parameters - couldn't get it to work. Hash tables are on the list to be implemented in the next version. I'm planning on doing this many times for a monitoring system and the sql method just looks very clumsy.

I could also export to a csv and import periodically import, but the way the structure of the code is working out I'm having trouble writing to one single csv - not sure how to set up a data structure to hold the hash table holding my $drives result.

Any ideas on how to make this work and do some somewhat easily/simply?

$servers = Invoke-Sqlcmd -Query "SELECT SERVERHardwareName FROM SERVERLIST_Hardware WHERE EXCLUDE = 0 and activenode = 1 or activenode is null;" -ServerInstance "sqlserver" -Database "dba_rep"

$ExportPath = 'c:\temp\psout\Server-DiskSpace.csv'

# going through, server by server, querying.
foreach($server in $servers)
{

    $server =  $server.SERVERHardwareName + '.fullyqualified.domain'
    Write-Output $server

    $Drives = Get-WmiObject -ComputerName $server -Query 'SELECT * from Win32_LogicalDisk WHERE DriveType=3' `
    | select SystemName,DeviceId,DriveType, VolumeName,Description,
    @{Label="FreeSpace(GB)"; `
    Expression={"{0:N2}" `
    -f ($_.FreeSpace/1GB)}},`
    @{Label="Size(GB)"; `
    Expression={"{0:N2}" `
    -f ($_.Size/1GB)}} 

    # So here we have a hash table of drives for one server
    foreach($Drive in $Drives)
    {
        $Drive | Export-Csv -Path $ExportPath -NoTypeInformation

    }


}

Answer 1

$servers = Invoke-Sqlcmd -Query "SELECT SERVERHardwareName FROM SERVERLIST_Hardware WHERE EXCLUDE = 0 and activenode = 1 or activenode is null;" -ServerInstance "sqlserver" -Database "dba_rep" | foreach {$_.SERVERHardwareName}
$ExportPath = 'c:\temp\psout\Server-DiskSpace.csv'  
Get-WmiObject -ComputerName $servers -Query 'SELECT * from Win32_LogicalDisk WHERE DriveType=3' ` 
| select SystemName,DeviceId,DriveType, VolumeName,Description, @{Label="FreeSpace(GB)";Expression={"{0:N2}" -f ($_.FreeSpace/1GB)}},`
  @{Label="Size(GB)";Expression={"{0:N2}" -f ($_.Size/1GB)}} | Export-Csv -NoTypeInformation -Path $exportpath

You can simplify quite a bit by loading your lists of servers into an array and passing the array to get-wmiobject. If you look at get-help get-wmiobject you'll notice the computername parameter is string[], whenever you see string[] this means it take an array.

Answer 2

You could also create an SMO object for your initial query of servers and then re-use the SMO server connection to perform the insert (of disk info) back into the same server.