ConvertTo-HTML conditional formatting

PowerShell Help
1

Hi, I am trying to do the below.

  1. Read through a SQL server list from a text file
  2. Connecting to each server and select some fields from SQL Login properties
  3. Putting each server output to a .out file and finally concatenating all .out to one file.
  4. Export the .out file to CSV
  5. Convert the .CSV to html using ConvertTo-HTML.

I want to conditionally format the html table according to the String value in column 4 . This table has 6 columns and many rows.
I want to change the color of table data cell or entire row, if the value is “LOGIN EXPIRED”.

Example of the html table

Server Login PasswordLastSet DaystoExpire PasswordChecked Client

Test1 ab_67 1/25/2021 LOGIN EXPIRED YES C1

Test1 ab_63 1/25/2020 LOGIN EXPIRED YES C1

Test1 bb_67 5/25/2012 NEVER EXPIRE YES C1

Test1 ab_87 1/25/2012 NEVER EXPIRE YES C1

Test1 ab_47 1/25/2012 LOGIN EXPIRED YES C1

I am struggling to change the color of the cell if LOGIN EXPIRED found.

Any insight would be greatly helpful.

Thank you

2

Hi, welcome to the forum :wave:

Here’s a great article on generating HTML reports with PowerShell:

Essentially, you want to replace <td>LOGIN EXPIRED</td> with <td class="loginStatus">LOGIN EXPIRED</td>

Then use CSS to define your colours for the loginStatus class:

.loginStatus {
    color: #ff0000;
}
3 Likes
3

Hello Matt,

Thank you for guiding me on the right direction towards the solution.
I have tried this, I am not sure what to pass or Pipe in $test =

Currently I am using the below code.

Import-Csv $curlocation$RunOutFile | ConvertTo-HTML - Head $css -Body “

SQL Login Report

'n
Generated on $(get-Date)
” | Out-File $curlocation\Report.html

$test = * | select ServerName, LoginName, PasswordLastSetTime, DaysUntilExpiration,
PasswordExpireChecked, Tenant | ConvertTo-HTML - -Head $css
$test = $test -Replace ‘LOGIN EXPIRED’,‘ class =“loginStatus”>LOGIN EXPIRED’
Set-Content -Value $test $curlocation\Report.html

4

Something like this:

# Begin Example CSV Data
# This section would be replaced by your Import-CSV command

$data = @'
Server,Login,PasswordLastSet,DaystoExpire,PasswordChecked,Client
Test1,ab_67,1/25/2021,LOGIN EXPIRED,YES,C1
Test1,ab_63,1/25/2020,LOGIN EXPIRED,YES,C1
Test1,bb_67,5/25/2012,NEVER EXPIRE,YES,C1
Test1,ab_87,1/25/2012,NEVER EXPIRE,YES,C1
Test1,ab_47,1/25/2012,LOGIN EXPIRED,YES,C1
'@

$csv = $data | ConvertFrom-Csv

# End Example CSV Data

$head = @'
<style>
    .loginStatus {
        color: #ff0000;
    }
</style>
'@

$output = $csv | ConvertTo-Html -Head $head

$output = $output -replace '<td>LOGIN EXPIRED</td>','<td class="loginStatus">LOGIN EXPIRED</td>'

$output | Set-Content E:\Temp\Files\report.html
2 Likes
5

Thank you so much. That worked :grinning:

1 Like
6

Great, happy to have helped :+1:

1 Like
7

Hi Matt,

Does this have to be on the same column? or would the below should work?

$output = $output -replace ‘LOGIN EXPIRED’,'LOGIN EXPIRED

‘$output = $output -replace ‘YES’,’YES

8

Ravinda

when you post code please format it as code using the “preformatted text” button (</> ).

Thanks in advance.

How much effort would it be for you to test it by yourself?
Regardless of that - you try to replace a string with the same string. That wouldn’t make any sense!? :face_with_raised_eyebrow:

9 
$output = $output -replace '<td>LOGIN EXPIRED</td>','<td class="loginStatus">LOGIN EXPIRED</td>'
$output = $output -replace '<td>YES</td>','<td class="PasswordStatus">YES</td>'

Thank you for showing me the correct way to post code. Yes I have tried it myself and it’s only working for data on the same column. When I tried to replace td data on a different column it does not work.

10

Did you check the html file? The replacement actually worked. But you did not define a style for your class “PasswordStatus”.

Extend the style definition like this …

$head = @'
<style>
    .loginStatus {
        color: #ff0000;
    }
    .PasswordStatus {
        color: #ff00ff;
    }
</style>
'@
1 Like
11

I have defined a css class called .passwordStatus same as what you have in the code.
Strange it is not working on other column.

12

With the sample code from Matt it did work for me. Could you please share the code you used? … the original code … :wink:

13

I am using the below code to replace the string

$result = Import-Csv $curlocation\$RunOutFile

$output = $result | ConvertTo-HTML  -Property ServerName,LoginName,PasswordLastSetTime,DaysUntilExpire,PasswordExpireChecked,Tenant  -Fragment

$output = $output -replace '<td>LOGIN EXPIRED</td>','<td class="loginStatus">LOGIN EXPIRED</td>'
$output = $output -replace '<td>YES</td>','<td class="passwordStatus">YES</td>'

$Report = ConvertTo-HTML  -Head $css  -Body "<h1>Login Report</h1>" | Out-File $curlocation\Report.html
14

I found the issue, when I checked the source code for the html file. there was a trailing character before Y in Yes. It is working now.

Thank you

1 Like
15

Great. I’m glad you figured it out. :slight_smile: :+1:t4:

1 Like