VMware Cloud Community
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

PowerCLI Convert-HTML add a column when criteria is $null

Hello all.  I have a report that I am working on for the Backup Team to assist in the countless audit we receive.  The report runs great (a little slow but functional) and my Convert-HTML adds a red button for everything instead of a green for Networker Policy being populated for a VM and red for a null entry.  The other thing it does is add the Status Column to my csv output with the following: <img src="https://cdn0.iconfinder.com/data/icons/gloss-basic-icons-by-momentum/32/bullet-red.png"> which makes the csv ugly.  I guess my problem is two-fold.  The HTML output and the CSV.  I would like the Status Column in the CSV to not appear at all. I apologize for the length and wish I could figure out how to paste as a pretty PowerShell code.

pastedImage_0.png

#HTML Variables

icon_green  = '<img src="https://cdn2.iconfinder.com/data/icons/fugue/icon/tick_circle.png">'

$icon_red    = '<img src="https://cdn0.iconfinder.com/data/icons/gloss-basic-icons-by-momentum/32/bullet-red.png">'

#Text to the HTML file

Function Create-HTMLTable

{

param([array]$Array)

$arrHTML = $Array | ConvertTo-Html

$arrHTML[-1] = $arrHTML[-1].ToString().Replace(‘</body></html>’,"")

Return $arrHTML[5..2000]

}

$output = @()

$output += ‘<html><head></head><body>’

$output +=

‘<style>table{border-style:double solid;border-width:1.5px;font-size:8pt;background-color:#085965;width:100%;}th{text-align:left;}td{background-color:#f9f9f9;width:20%;border-style:double so

lid;border-width:1.5px;}body{font-family:verdana;font-size:12pt; color:000000;}h1{font-size:16pt; color:db3827; font-weight:bold;}h2{font-size:10pt;}</style>’

$output += '<p><img src=https://.com/wp-content/uploads/2018/05/logo-new.svg width=447, height=81></p>'

$output += ‘<H1>Monthly VM Backup Report</H1>’

$output += ‘<H2>Date and time</H2>’,$date

#

$report = @()

$report += Get-View -ViewType VirtualMachine -Filter @{'Runtime.PowerState'='poweredOn'}|

Select Name,

    @{N='Datacenter';E={

        $parent = Get-View -Id $_.Parent -Property Name,Parent

        while($parent -isnot [VMware.Vim.Datacenter] -and $parent){

            $parent = Get-View -Id $parent.Parent -Property Name,Parent

        }

        if($parent){

            $parent.Name

        }}},

    @{N='Cluster';E={

        $parent = Get-View -Id $_.ResourcePool

        while($parent -isnot [VMware.Vim.ClusterComputeResource] -and $parent){

        $parent = Get-View -Id $parent.Parent -Property Name,Parent

        }

        if($parent){

        $parent.Name}}},   

    @{N='OS';E={$_.Config.GuestFullName}},

    @{N="Status";E={if($_.'Networker Policy' -eq $null){$icon_red}else{$icon_green}}},

       @{N="Networker Policy";E={$viewThisVM = $_; ($viewThisVM.CustomValue | ?{$_.Key -eq ($viewThisVM.AvailableField | ?{$_.Name -eq "Last EMC vProxy Backup"}).Key}).Value}}

   

#Output

if ($GridView -eq "yes") {

$report | Sort Name | Out-GridView }

if ($CreateCSV -eq "yes") {

$report | Sort Name | Export-Csv $FileCSV -NoTypeInformation }

if ($HTML -eq "yes") {

$output += ‘<p>’

$output += ‘<H2>Report Virtual Machines & Results</H2>’

$output += ‘<p>’

$output += Create-HTMLTable $report |foreach { if ($_ -match 'img' ) { [System.Net.WebUtility]::HtmlDecode($_) } else { $_ } }

$output += ‘</p>’

$output += ‘</body></html>’

Reply
0 Kudos
1 Solution

Accepted Solutions
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Well LucD, it isn't pretty but it is functional and working.

Get-View -ViewType VirtualMachine -Filter @{'Runtime.PowerState'='poweredOn'}|

Select Name,

    @{N="Status";E={

        if(($script:policy -eq 'Replicated System') -or

        ($script:policy -eq 'DEV-QA System') -or

        ($script:policy -eq 'Client Backup') -or

        ($script:policy -eq 'Restore/Rebuilt System') -or

        ($script:policy -eq 'Decommed') -or

        ($script:policy -eq 'Replication Machines') -or

        ($script:policy -eq 'NetWorker Proxy (Do Not Backup)') -or

        ($script:policy -eq 'Restored Machine') -or

        ($script:policy -eq 'Desktop System') -or

        ($script:policy -like 'Backup*')){

            $icon_green

            }

        else{

            $icon_red

    }}}

-------------------------------------------------------------------------------------------------------------------------------

Was it helpful? Let us know by completing this short survey here.

View solution in original post

Reply
0 Kudos
28 Replies
LucD
Leadership
Leadership
Jump to solution

You can select the properties explicitly and leave out the Status one.

Something like this

if ($CreateCSV -eq "yes") {

    $properties = $report[0].PSObject.Properties.Name | where{$_ -ne 'Status'}

    $report | Select -Property $properties | Sort Name | Export-Csv $FileCSV -NoTypeInformation

}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

LucD, changed the script and the CSV is now blank.

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Figured it out there was a dash added in front of the csv file variable.  That portion now works flawlessly.  Any ideas on the HTML export with the if/else for input?

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

So I have tried every variation of null that I can think of and am trying the following:

@{N="Status";E={if($_.NetworkerPolicy -ne "" -or $_.NetworkerPolicy -ne $null){$icon_green}else{$icon_red}}}

based on the results of:

@{N="NetworkerPolicy";E={$viewThisVM = $_; ($viewThisVM.CustomValue | ?{$_.Key -eq ($viewThisVM.AvailableField | ?{$_.Name -eq "Last EMC vProxy Backup"}).Key}).Value}},

Am I referencing this correctly?  Can the results of one line even be referenced inline to another line? All I get is the same red or green icon for every column in status.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

I also had a typo, I didn't use $properties.

I corrected the code above.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

I am getting closer to a solution:

If i run the following for OS it works perfectly adding the column and correct Green icon for Server 2008 and Red for all other OSs.

Get-View -ViewType VirtualMachine |

select -First 5 Name,

@{N='OS';E={$_.Config.GuestFullName}},

@{N="Status";E={if($_.Config.GuestFullName -like "Microsoft Windows Server 2008 R2 (64-bit)"){$icon_green}else{$icon_red}}} | ft -AutoSize

If I run the following for my Annotation property, I get Green icon for all results

Get-View -ViewType VirtualMachine |

select -First 5 Name,

@{N="NetworkerPolicy";E={$viewThisVM = $_; ($viewThisVM.CustomValue | ?{$_.Key -eq ($viewThisVM.AvailableField | ?{$_.Name -eq "Last EMC vProxy Backup"}).Key}).Value}},

@{N="Status";E={

     if({$viewThisVM = $_; ($viewThisVM.CustomValue |

     ?{$_.Key -eq ($viewThisVM.AvailableField |

     ?{$_.Name -eq "Last EMC vProxy Backup"}).Key}).Value} -like "Replicated System")

     {$icon_red}else{$icon_green}}} | ft -AutoSize

Please help.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

For a calculated property you don't need to have the 'Format-Table -Autosize' at the end.

That is only used for output going to the console.

For the Annotation, would this work?

Get-View -ViewType VirtualMachine |

Select-Object -First 5 Name,

@{N="NetworkerPolicy";E={

    $script:policy = $_.Summary.CustomValue |

        Where-Object {$_.Key -eq 'Last EMC vProxy Backup'} |

        Select-Object -ExpandProperty Value

    $script:policy}},

@{N="Status";E={

    if($script:policy -eq 'Replicated System'){

        $icon_green

    }

    else{

        $icon_green

    }}}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Thank you for the response LucD.  Thanks for catching the FT -Autosize, that isn't in my final script only for testing purposes.  I was not able to get the "Networker" query to return any results so the following just returned the 'else'.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Try it this way.

Get-View -ViewType VirtualMachine |

Select-Object -First 5 Name,

@{N="NetworkerPolicy";E={

    $caEntry = $_.CustomValue | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

    $script:policy = ($_.CustomValue | where{$_.Key -eq $caEntry.Key}).Value

    $script:policy

}},

@{N="Status";E={

    if($script:policy -eq 'Replicated System'){

        $icon_green

    }

    else{

        $icon_red

}}}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Thanks LucD, but no results for the "NetworkerPolicy".

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Can you show me an example of how this Custom Attribute is used on a VM?

I'm not sure anymore we are looking at the same thing.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

I hope this helps

2019-09-20_16-43-36.png

2019-09-24_8-26-40.png

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

I made a typo, that should be AvailableField instead of CustomValue

Get-View -ViewType VirtualMachine |

Select-Object -First 5 Name,

@{N="NetworkerPolicy";E={

    $caEntry = $_.AvailableField | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

    $script:policy = ($_.CustomValue | where{$_.Key -eq $caEntry.Key}).Value

    $script:policy

}},

@{N="Status";E={

    if($script:policy -eq 'Replicated System'){

        $icon_green

    }

    else{

        $icon_red

}}}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

WooHoo!!! You did it LucD!  Now it works fantastic for looking at one clause but if I add an -or operator and another value it wrecks.  Ideally I would search for a $null and mark that as red else green.

Get-View -ViewType VirtualMachine -Filter @{'Runtime.PowerState'='poweredOn'}|

Select -First 10 Name,

    @{N="NetworkerPolicy";E={

        $caEntry = $_.AvailableField | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

        $script:policy = ($_.CustomValue | where{$_.Key -eq $caEntry.Key}).Value

        $script:policy

    }},

    @{N="Status";E={

        if($script:policy -eq 'Replicated System' -or 'DEV-QA System'){

            $icon_green

        }

        else{

            $icon_red

    }}}

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

You can't do an OR operation like that.

Better could be to use -contains against all the 'good' values

Get-View -ViewType VirtualMachine -Filter @{'Runtime.PowerState'='poweredOn'}|

Select -First 10 Name,

    @{N="NetworkerPolicy";E={

        $caEntry = $_.AvailableField | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

        $script:policy = ($_.CustomValue | where{$_.Key -eq $caEntry.Key}).Value

        $script:policy

    }},

    @{N="Status";E={

        if('Replicated System','DEV-QA System' -contains $script:policy){

            $icon_green

        }

        else{

            $icon_red

    }}}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

I ran the report and got green all the way down.

2019-09-24_10-27-43.png

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

And what if we use the -or

Get-View -ViewType VirtualMachine -Filter @{'Runtime.PowerState'='poweredOn'}|

Select -First 10 Name,

    @{N="NetworkerPolicy";E={

        $caEntry = $_.AvailableField | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

        $script:policy = ($_.CustomValue | where{$_.Key -eq $caEntry.Key}).Value

        $script:policy

    }},

    @{N="Status";E={

        if($script:policy -eq 'Replicated System' -or $script:policy -eq 'DEV-QA System'){

            $icon_green

        }

        else{

            $icon_red

    }}}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

No joy LucD.  I even tried putting parens around each variable and running as individual operations.

if(($script:policy -eq 'Replicated System') -or ($script:policy -eq 'DEV-QA System') -or ($script:policy -eq $script:policy))

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Ok, one last try.
Let's eliminate the $script variable altogether, and replace the -eq with a -match.

Get-View -ViewType VirtualMachine -Filter @{'Runtime.PowerState'='poweredOn'}|

Select -First 10 Name,

    @{N="NetworkerPolicy";E={

        $caEntry = $_.AvailableField | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

        ($_.CustomValue | where{$_.Key -eq $caEntry.Key}).Value

    }},

    @{N="Status";E={

        $caEntry = $_.AvailableField | Where-Object {$_.Name -eq 'Last EMC vProxy Backup'}

        if($caEntry -match 'Replicated System' -or $caEntry -match 'DEV-QA System'){

            $icon_green

        }

        else{

            $icon_red

    }}}

On another note, you are sure that $icon_green and $icon_red contain different values?
You could try to put the value instead of the $variable.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos