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
28 Replies
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Here are the icon 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">'

Running with the following produced the desired results minus the Backup lines

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')){

The last script returned identical icons for all lines.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

I'm lost I'm afraid.


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

Reply
0 Kudos
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.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

I don't see the difference why this would work and my code doesn't.


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

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

LucD, here is the result of your script:

2019-09-24_15-12-42.png

Here is the results of my modifications of your original:

2019-09-24_15-20-51.png

I can't explain why yours didn't return the expected results.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

I think I can, there is a line missing.

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'}

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

        if('Replicated System','DEV-QA System' -contains $caValue){

            $icon_green

        }

        else{

            $icon_red

    }}}


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

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Returned Red on all results not explicitly defined like DEV-QA.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

And my last attempt

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'}

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

        if('Replicated System' -eq $caValue -or 'DEV-QA System' -eq $caValue){

            $icon_green

        }

        else{

            $icon_red

    }}}


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

Reply
0 Kudos
BrianDGeorge
Enthusiast
Enthusiast
Jump to solution

Sorry LucD, no joy.  You got everything to where it needed to be, I just made it an ugly baby.

Reply
0 Kudos