ConvertTo-Html varying colours depending on value

I’ve a report that pulls info from Zabbix and produces an email with a System.Data.DataTable presented in HTML using ConvertTo-Html.

The report is basically what jobs are running and how long they’ve been running. I want to format the rows in different colours depending on the job “RunTime”, which is how long the job has been running. How can I do that?

The table is created like this:

LogWrite "Creating DataTable"
$table = New-Object System.Data.DataTable
$table.Columns.Add("CheckTime","string")
$table.Columns.Add("Network","string")
$table.Columns.Add("Client","string")
$table.Columns.Add("JobName","string")
$table.Columns.Add("RunTime","string")
$table.Columns.Add("JobStartTime","string")
$table.Columns.Add("Host","string")

…Style is:

$a = "<style>"
$a = $a + "BODY{background-color:#FFFFFF;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "</style>"

$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px;}"
$style = $style + "TD{border: 1px solid black; padding: 5px;}"
$style = $style + "</style>"

…and then passed to

<body>$($Table | ConvertTo-Html -Property Network , Client , JobName, RunTime, JobStartTime, CheckTime, Host -Head $style | Out-String)</body>

 

 

I don’t think that you can with that cmdlet. Don Jones made a module that can do much more: https://www.powershellgallery.com/packages/EnhancedHTML2/

Otherwise you must do it yourself. Something like:

*Deleted*

I can see in the topics list there should be an answer but I cannot see it here … anyway - there is a free ebook what might help you.

Creating HTML Reports in Windows PowerShell

I was trying to post code with html tags. It didn’t go that well. After x number of edits, the post vanished. It might be waiting to be approved… :slight_smile:

Ah … ok, thanks. Sometimes I think there should be a better option for a forum software for a Powershell / Scripting forum. :wink:

Thanks Olaf, I’ve read that one before, and twice today, still no idea how to achieve what I’m wanting to achieve.

I wrote a monitor to check free diskspace and alert on certain thresholds. Below are some snippets that should get you going in the right direction. All of this information was obtained reading the E-book mentioned above. Please keep in mind this is not functional code in and of itself. These are the relevant sections from the solution I crafted that should provide you with enough of an example to achieve your desired result.

# Report name
$reportPath = 'C:\Temp\'
$dateTime = [DateTime]::Now.ToString("MM-dd-yyyy_HHmmss")
$reportName = "DiskSpaceRpt_$dateTime.html";

# Path and Report name together
$diskReport = $reportPath + $reportName

#Set colors for table cell backgrounds
$redColor = "#FF0000"
$orangeColor = "#FBB917"
$whiteColor  = "#FFFFFF"
$greenColor  =  "#00FF00"


# Create and write HTML Header of report
$titleDate = Get-Date -uformat "%m-%d-%Y - %A %H:%M:%S"
$header = "
  <html>
  <head>
  <meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
  <title>DiskSpace Report</title>
  <STYLE TYPE='text/css'>
  <!--
  td {
   font-family: Calibri;
   font-size: 12px;
   border-top: 1px solid #999999;
   border-right: 1px solid #999999;
   border-bottom: 1px solid #999999;
   border-left: 1px solid #999999;
   padding-top: 0px;
   padding-right: 0px;
   padding-bottom: 0px;
   padding-left: 0px;
  }
  body {
   margin-left: 5px;
   margin-top: 5px;
   margin-right: 0px;
   margin-bottom: 10px;
   table {
   border: thin solid #000000;
  }
  -->
  </style>
  </head>
  <body>
  
<font face='calibri' color='#003399' size='4'>Daily Morning Report for $titledate</font>
" Add-Content -Path $diskReport -Value $header # Create and write Table header for report $tableHeader = " " Add-Content -Path $diskReport -Value $tableHeader switch ($percentFree) { {$_ -lt $percentWarning} {$color = $orangeColor;$i++} {$_ -lt $percentCritcal} {$color = $redColor;$i++} Default {$color = $greenColor} } # Create table data rows $dataRow = " " Add-Content -Path $diskReport -Value $dataRow;

I hope you find this information helpful.

Server Drive Drive Label Total Capacity(GB) Used Capacity(GB) Free Space(GB) Freespace %
$computer $deviceID $volName $sizeGB $usedSpaceGB $freeSpaceGB $percentFree

The “code formatting” didn’t like the HTML in my example… Here is just the straight text. I apologize as I know that this is not best practice for these forums.

Report name

$reportPath = ‘C:\Temp’
$dateTime = [DateTime]::Now.ToString(“MM-dd-yyyy_HHmmss”)
$reportName = “DiskSpaceRpt_$dateTime.html”;

Path and Report name together

$diskReport = $reportPath + $reportName

#Set colors for table cell backgrounds
$redColor = “#FF0000
$orangeColor = “#FBB917
$whiteColor = “#FFFFFF
$greenColor = “#00FF00

Create and write HTML Header of report

$titleDate = Get-Date -uformat “%m-%d-%Y - %A %H:%M:%S”
$header = "
<html>
<head>
<meta http-equiv=‘Content-Type’ content=‘text/html; charset=iso-8859-1’>
<title>DiskSpace Report</title>
<STYLE TYPE=‘text/css’>
<!–
td {
font-family: Calibri;
font-size: 12px;
border-top: 1px solid #999999;
border-right: 1px solid #999999;
border-bottom: 1px solid #999999;
border-left: 1px solid #999999;
padding-top: 0px;
padding-right: 0px;
padding-bottom: 0px;
padding-left: 0px;
}
body {
margin-left: 5px;
margin-top: 5px;
margin-right: 0px;
margin-bottom: 10px;
table {
border: thin solid #000000;
}
–>
</style>
</head>
<body>

<font face='calibri' color='#003399' size='4'>Daily Morning Report for $titledate</font>
" Add-Content -Path $diskReport -Value $header

Create and write Table header for report

$tableHeader = "

" Add-Content -Path $diskReport -Value $tableHeader

switch ($percentFree)
{
{$_ -lt $percentWarning} {$color = $orangeColor;$i++}
{$_ -lt $percentCritcal} {$color = $redColor;$i++}
Default {$color = $greenColor}
}

Create table data rows

$dataRow = "

" Add-Content -Path $diskReport -Value $dataRow;
Server Drive Drive Label Total Capacity(GB) Used Capacity(GB) Free Space(GB) Freespace %
$computer $deviceID $volName $sizeGB $usedSpaceGB $freeSpaceGB $percentFree

I give up. Send me an email if you want the example code.

Yep, many of these Q&A/Forums, are not designed to handle all things HTML/JSON etc., in general.
There is always the option to post to PasteBin, GitHub, etc. and simply share the link to your code.

[quote quote=131874]Yep, many of these Q&A/Forums, are not designed to handle all thing HTML/JSON etc., in general.

There is always the option to post to paste in, GitHub, etc. and simply share the link to your code.[/quote]

Good idea! I will post the example to my website then post a link to this thread over the weekend. Cant do stuff like that while I am at work :frowning:

Thanks Logan.

Look forward to seeing your example.

Here you go, Iain.

https://github.com/LBo98684/colorHTMLReport/blob/master/Example

https://stackoverflow.com/questions/4559233/technique-for-selectively-formatting-data-in-a-powershell-pipeline-and-output-as/

A different way to do it, without harmful html tags. :slight_smile:

[void]$(
$table = New-Object System.Data.DataTable
$table.Columns.Add("CheckTime","string")
$table.Columns.Add("Network","string")
$table.Columns.Add("Client","string")
$table.Columns.Add("JobName","string")
$table.Columns.Add("RunTime","string")
$table.Columns.Add("JobStartTime","string")
$table.Columns.Add("Host","string")
)

# generate some data
1..40 | ForEach-Object {
    [void]$table.Rows.Add((
        "checktime",
        "network",
        "client",
        "jobname $_",
        (Get-Random -Minimum 10 -Maximum 100), # some random runtime
        "jobstarttime",
        "host"))
}

$html = $table | ConvertTo-Html -Property Network, Client, JobName, RunTime, JobStartTime, CheckTime, Host -Head $style

$xml = [System.Xml.Linq.XDocument]::Parse($html)
if($Namespace = $xml.Root.Attribute("xmlns").Value) {
    $Namespace = "{{{0}}}" -f $Namespace
}

# Find the index of the runtime column
$runtimeIndex = [Array]::IndexOf($xml.Descendants("${Namespace}th").Value, "RunTime")

$rows = $xml.Descendants("${Namespace}tr")

foreach($row in $rows) {
    $cells = @($row.Descendants("${Namespace}td"))
    if(!$cells){ continue }

    # the runtime cell
    $cell = $cells[$runtimeIndex]

    # value is a valid int in my example
    $value = [int]$cell.Value

    # if less than 50, green...
    if($value -lt 50) { $row.SetAttributeValue("style", "background: green;") }
    else { $row.SetAttributeValue("style", "background: orange;") }
}

$xml.Save("$pwd/test999.html")
ii test999.html

Hi Ian

I am using the one of cookie monster script to achieve the result which you are looking for.

https://gallery.technet.microsoft.com/scriptcenter/PowerShell-HTML-Notificatio-e1c5759d

You can define arguement and value ( argument is your column name ) and you can define the value and its colour . Please take a look .

Something like below

[pre]

       #Add yellow, orange and red shading 
            $handleHTML = Add-HTMLTableColor -HTML $handleHTML -Argument 1500 -attrValue "background-color:#FFFF99;" @params 
            $handleHTML = Add-HTMLTableColor -HTML $handleHTML -Argument 2000 -attrValue "background-color:#FFCC66;" @params 
            $handleHTML = Add-HTMLTableColor -HTML $handleHTML -Argument 3000 -attrValue "background-color:#FFCC99;" @params 

[/pre]

Logan, Suresh, thanks very much, I appreciate your help.