Trying to insert results of Get-CimInstance to SQL table



  • 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



  • 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 https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps 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 "" -DatabaseName "" -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(',')
    

    }




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2