Color coding Invoke-SQL in Powershell

Question

Using invoke-sql I have a PowerShell script that returns results from a SQL Query. That query has the status of several data points local I want to colour code the entire row if the column of Status returns different values. Is this possible as I know you can colour code Charts in PowerShell but not sure how a larger return query would work?

    param(
    [string] $dataSource = "SQLName",
    [string] $database = "DatabaseName",
    [string] $sqlCommand = $("Select tbldatafeed.datafeed_name,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbldatafeedhistory.status_id,1,'Running'),2,'Completed'),3,'Faulted'),4,'Warning'),5,'Terminating'),6,'Terminated'),7,'Pending') AS Status,DateDiff(MINUTE,tbldatafeedhistory.start_time, tbldatafeedhistory.end_time) As 'Run Time(minutes)'
    from tblDataFeedHistory
    left Join tblDatafeed on tbldatafeedhistory.datafeed_id = tbldatafeed.datafeed_id
    inner join 
    (
        Select max(start_time) as LatestDate, [datafeed_id]
        from tblDataFeedHistory
        Group by datafeed_id
    ) SubMax 
    on tblDataFeedHistory.start_time = SubMax.LatestDate
    and tblDataFeedHistory.datafeed_id = SubMax.datafeed_id 
    WHERE tbldatafeed.is_active = 1
    Order by tbldatafeed.datafeed_id")
    )

    $connectionString = "Data Source=$dataSource; " + "Integrated Security=SSPI; " + "Initial Catalog=$database"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    write-output $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables
    }

    $FeedID = Invoke-SQL
    $FeedID

The output would be a red highlighted line if the column returned Failed, yellow if the Column returns Warning, Green if the column returns Completed.

postanote · Accepted Answer · 2019-06-13T04:46:18.420

I've not personally found or seen a reason to do this with SQL data generally, nor do I have any record in my stash of anyone who has.

Anything you write to the screen can have color. It's one of the primary focuses of the …

[Write-Host][1]

Or [Console]::ForegroundColor]

... cmdlet or .Net class.

However, if you plan to use that data on the pipeline, or elsewhere, then don't use it. Especially if you are not on the newest PowerShell versions. Legacy PowerShell, Write-Host empties the buffer, later PowerShell versions, send it to the Information Stream. As talked by PowerShell inventor Jeffery Snover here:.

http://www.jsnover.com/blog/2013/12/07/write-host-considered-harmful

... Jeffrey Snover change his stance on this as of May 2016.

With PowerShell v5 Write-Host no longer "kills puppies". data is captured into info stream ...

https://twitter.com/jsnover/status/727902887183966208

https://learn.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Utility/Write-Information?view=powershell-5.1

However, there are PowerShell modules you can leverage to get really creative with color.

Find-Module -Name '*color*' | Format-Table -AutoSize

You also have several scripts to leverage as is or tweak for your use case.

Format Table Colors in PowerShell Format the output table colors in PowerShell host with/without conditional colorful formatting.

Download : Write-PSObject.ps1

You do have another option. If you want to call attention, with color to a given screen output line, you can use the ...

Write-Warning 
Write-Error

... cmdlets and just accept its default color.

Yet, your question could almost be a duplicate of these Q&A and its accepted answer.

Powershell - Output Color for certain results

Is there a way to specify a font color when using write-output

If you are saying, that you are sending this to a form, then you have to handle color in the form properties code.

Color coding Invoke-SQL in Powershell

1 Answers1