ConvertTo-Html varying colours depending on value

Welcome Forums General PowerShell Q&A ConvertTo-Html varying colours depending on value

This topic contains 15 replies, has 6 voices, and was last updated by

 
Participant
2 weeks, 6 days ago.

  • Author
    Posts
  • #131804

    Participant
    Points: 182
    Rank: Participant

    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 = ""
    
    

    ...and then passed to

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

     

     

  • #131811

    Participant
    Points: 192
    Helping Hand
    Rank: Participant

    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*
  • #131840

    Participant
    Points: 421
    Helping Hand
    Rank: Contributor

    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

    • #131846

      Participant
      Points: 192
      Helping Hand
      Rank: Participant

      I can see in the topics list there should be an answer but I cannot see it here

      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... ūüôā

    • #131850

      Participant
      Points: 421
      Helping Hand
      Rank: Contributor

      Ah ... ok, thanks. Sometimes I think there should be a better option for a forum software for a Powershell / Scripting forum. ūüėČ

    • #131853

      Participant
      Points: 182
      Rank: Participant

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

  • #131861

    Participant
    Points: 235
    Helping Hand
    Rank: Participant

    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 = "
      
      
      
      DiskSpace Report
      
      
      
      
    Daily Morning Report for $titledate
    " 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 hope you find this information helpful.

  • #131862

    Participant
    Points: 235
    Helping Hand
    Rank: Participant

    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 = "



    DiskSpace Report


    Daily Morning Report for $titledate

    "
    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

  • #131867

    Participant
    Points: 235
    Helping Hand
    Rank: Participant

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

  • #131874

    Participant
    Points: 514
    Helping Hand
    Rank: Major Contributor

    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.

  • #131882

    Participant
    Points: 235
    Helping Hand
    Rank: Participant

    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.

    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 ūüôĀ

  • #131984

    Participant
    Points: 182
    Rank: Participant

    Thanks Logan.

    Look forward to seeing your example.

  • #132188

    Participant
    Points: 192
    Helping Hand
    Rank: Participant

    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. ūüôā

    [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
  • #132251

    Participant
    Points: 6
    Rank: Member

    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

    
    
           #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 
    
  • #132366

    Participant
    Points: 182
    Rank: Participant

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

     

You must be logged in to reply to this topic.