Home Forums Programming Powershell Write PowerShell Output to SQL Server Table RE: Write PowerShell Output to SQL Server Table

  • calvo (6/17/2013)


    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.

    Thank you, Sir. I was hoping to avoid the out-datable (God bless the "Scripting Guy"!) function because I may have several columns in the table that won't be populated by PowerShell. I just might have to resort to that (can always use a staging table), though, because it seems like the $bulkCopy would probably write to the server more quickly than individual inserts (If that's what Gary's code ends up doing).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)