CODEWITHSUNDEEP
htmlpowershellpowershell-2.0
Sunny
Sunny

Reputation: 8312

Color a cell on the basis of another cell value

I have a file output.txt having below content:

SERVER_NAME MOUNT_POINT TOTAL_SPACE USED_SPACE USED_PERCENTAGE   AVAILABLE_SPACE
Server_1           /dev       1200G       537G             54%              464G
Server_2           /dev        600G       490G             85%               94G
Server_3           /dev        600G       402G             69%              181G
Backup_server     /storage     800G       682G             72%              278G

Using following script, I formatted my output.txt file to get a notification via email. Now I 'm trying to compare last two colums (USED_PERCENTAGE and AVAILABLE_SPACE) in such a way that If USED_PERCENTAGE value is greater than 90% for a particular cell then corresponding AVAILABLE_SPACE cell's background should come in RED color.

Please can you tell me how I can do this in my following script ?

$smtpServer = "xyz.bla.com"
$smtpFrom = "[email protected]"
$smtpTo = "[email protected]"
$messageSubject = "Space notification"

$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true 

$data = Get-Content D:\output.txt | select -Skip 1 | ForEach-Object{
    $splitLine = $_ -split '\s+'
    [pscustomobject] @{
        'SERVER_NAME' = $splitLine[0]
        'MOUNT_POINT' = $splitLine[1]
        'TOTAL_SPACE' = $splitLine[2]
        'USED_SPACE' = $splitLine[3]
        'USED_PERCENTAGE' = $splitLine[4]
        'AVAILABLE_SPACE' = $splitLine[5]
    }
}

$headerStyle = 'style = "border: 1px solid black; background: #dddddd; padding: 5px;"'
$normalDataStyle =  'style = "border: 1px solid black; padding: 5px;text-align:center;"'
$backupRowStyle = 'bgcolor="#81F7F3"'
$normalRowStyle = 'bgcolor="#D0F5A9"'
$body = "<table style=`"border: 1px solid black; border-collapse: collapse;`">`r`n"
$body = $body + "<tr><th $headerStyle>SERVER_NAME</th><th $headerStyle>MOUNT_POINT</th><th $headerStyle>TOTAL_SPACE</th><th $headerStyle>USED_SPACE</th><th $headerStyle>USED_PERCENTAGE</th><th $headerStyle>AVAILABLE_SPACE</th></tr>`r`n"

$data | ForEach-Object{
    Switch($_.CLUSTER_NAME){
        "Server_1"{$rowStyle = $normalRowStyle}
        "Server_2"{$rowStyle = $normalRowStyle}
        "Server_3"{$rowStyle = $normalRowStyle}
        "Backup_server"{$rowStyle = $backupRowStyle}
        default{$rowStyle = ""}
    }
    $body = $body + "<tr $rowstyle><td $normalDataStyle>$($_.SERVER_NAME)</td><td $normalDataStyle>$($_.MOUNT_POINT)</td><td $normalDataStyle>$($_.TOTAL_SPACE)</td><td $normalDataStyle>$($_.USED_SPACE)</td><td $normalDataStyle>$($_.USED_PERCENTAGE)</td><td $normalDataStyle>$($_.AVAILABLE_SPACE)</td></tr>`r`n"
}

$body = '<body>' + $body + '</table><br/><br/><b>NOTE:</b> This is an automatically generated email, please do not reply to it.</body>'
$message.Body = $Body

$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)

Upvotes: 0

Views: 83

Answers (1)

Vesper
Vesper

Reputation: 18747

You should format your HTML result with a different style if there's a condition. To do that, you declare a variable for data style that should be equal to $normalDataStyle if your condition is false, and a special style if it's true.

$redDataStyle='style = "border: 1px solid black; background: #c00000; padding: 5px;text-align:center;"'
# add "background" parameter to normal style
$data | ForEach-Object{
    # <switch skipped>
    $spaceStyle=$normalDataStyle
    $percent = $_.USED_PERCENTAGE.substring(0,$_.USED_PERCENTAGE.indexOf("%")
    #strips "%" off percentage, making it a number
    if ($percent -gt 89) { $spaceStyle=$redDataStyle}
    $body = $body + "<tr $rowstyle><td $normalDataStyle>$($_.SERVER_NAME)</td><td $normalDataStyle>$($_.MOUNT_POINT)</td><td $normalDataStyle>$($_.TOTAL_SPACE)</td><td $normalDataStyle>$($_.USED_SPACE)</td><td $spaceStyle>$($_.USED_PERCENTAGE)</td><td $spaceStyle>$($_.AVAILABLE_SPACE)</td></tr>`r`n"
}

Upvotes: 2

Related Questions