Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

    }


}
share|improve this question

2 Answers 2

up vote 1 down vote accepted
$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.

share|improve this answer
    
That seems like a good idea to me - I'll look into that. Thanks Chad! –  Sam Apr 7 '11 at 15:22
    
From looking at this code, There is "foreach {$_.SERVERHardwareName}" and then setting $ExportPath and Export-CSV. Wouldn't this result in the csv only containing the last SERVERHardwareName's information? –  Sam Apr 7 '11 at 16:18
    
No because the only foreach I'm using is to load the Server variable. And even there I could have used "| select -expand SERVERHardwareName" instead of "| foreach {$_.SERVERHardwareName}" –  Chad Miller Apr 7 '11 at 18:59
    
Thanks very much for your explanation and answer - perfect! –  Sam Apr 7 '11 at 23:04

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.