0

I'm trying to get drive space for multiple servers

$servers = Get-Content "\\SQL-Server1\e$\Scripts\DBInfo\DBInfo_DiskfreeComputers.txt"
foreach ($Server in $Servers)
{

$results = Get-CimInstance -ClassName Win32_LogicalDisk
$DriveLetter = $results.DeviceID
$FreeSpace = $results.FreeSpace
$TotalSpace = $results.Size
$CollectionDate = $([System.Datetime]::Now.ToString("dd/MM/yy HH:mm:ss"))
Write-Host "Insert into SQLSERVER.dbo.DBInfo_DiskSize(ServerName, DiskLetter, FreeSpace,TotalSpace,CollectionDate)
            Values $Server, $DriveLetter, $FreeSpace, $TotalSpace, $CollectionDate"
}

This is giving me:

Insert into SQLSERVER.dbo.DBInfo_DiskSize(ServerName, DiskLetter, FreeSpace,TotalSpace,CollectionDate)
            Values SQL-SERVER, C: E: F: Z:, 13104119808 244906799104  16500219904, 214260355072 297121345536  246957469696, 04/05/22 16:00:48

How would I get this to format to do an insert for each disk, instead of all on this one statement - for instance this server has 4 disks I'd like to insert 4 rows into the table one per disk

1 Answer 1

1

The issue is your Get-CimInstance produces an object, but you assign individual properties to variables that produce these 'space separated strings' in the variables.

The simplest solution is to install the SqlServer PowerShell module and use the Write-SqlTableData function to insert the data for you:

$servers = Get-Content "\\SQL-Server1\e$\Scripts\DBInfo\DBInfo_DiskfreeComputers.txt"
foreach ($Server in $Servers)
{
  $results = Get-CimInstance -ClassName Win32_LogicalDisk | Select @{n="ServerName"; e={$ServerName}}, @{n="DiskLetter"; e={$_.DeviceID}}, @{n="FreeSpace"; e={$_.FreeSpace}}, @{n="TotalSpace"; e={$_.Size}}, @{n="CollectionDate"; e={[System.Datetime]::Now.ToString("dd/MM/yy HH:mm:ss")}}

  $results | Write-SqlTableData -ServerInstance "<SQL server name>" -DatabaseName "<database name>" -TableName "DBInfo_DiskSize"
}

If you just want to output the insert command to the console, you need to iterate through the results from Get-CimInstance and build the output string, you can use something like below should work:

$servers = Get-Content "\\SQL-Server1\e$\Scripts\DBInfo\DBInfo_DiskfreeComputers.txt"
foreach ($Server in $Servers)
{
    $CollectionDate = $([System.Datetime]::Now.ToString("dd/MM/yy HH:mm:ss"))
    $output = "Insert into SQLSERVER.dbo.DBInfo_DiskSize(ServerName, DiskLetter, FreeSpace,TotalSpace,CollectionDate)`r`nVALUES "

    $results = Get-CimInstance -ClassName Win32_LogicalDisk | For-EachObject {
            $DriveLetter = $_.DeviceID
            $FreeSpace = $_.FreeSpace
            $TotalSpace = $_.Size
            
            $output += "('$(Server)', '$($DriveLetter)', '$($FreeSpace)', '$($TotalSpace)', '$($CollectionDate)',`r`n"
        }
    Write-Host $output.Trim().TrimEnd(',')
}
3
  • Thank you for the answer, HandyD... I get the following error: Unable to cast object of type 'System.Management.Automation.PSObject' to type 'System.IConvertible'.Couldn't store <12752400384> in FreeSpace Column. Expected type is UInt64. -- The column is a INT column, I tried to change it to just varchar, but still get the same error, in the database those fields just go in as null. May 4, 2022 at 23:33
  • Int64 is equivalent to a BIGINT in SQL. You can change the data type in the table, or you could convert the FreeSpace and Size values from the current Bytes value to maybe Kilobytes or Megabytes. Easy conversion is to change e={$_.FreeSpace} to e={[math]::Round($_.FreeSpace\1MB)}. Note: it has to be rounded to store as an Int, otherwise it is a Decimal value
    – HandyD
    May 5, 2022 at 0:28
  • Thank you - e={[math]::Round($_.FreeSpace\1MB)} does not appear to be valid for VisualStudio Code. I will try to look at it again in the morning, but getting all the red Squigglys. May 5, 2022 at 1:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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