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.
#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>’
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.
You can select the properties explicitly and leave out the Status one.
Something like this
$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
LucD, changed the script and the CSV is now blank.
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?
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.
I also had a typo, I didn't use $properties.
I corrected the code above.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
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?
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
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'.
Try it this way.
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
Thanks LucD, but no results for the "NetworkerPolicy".
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
I hope this helps
I made a typo, that should be AvailableField instead of CustomValue
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
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
}}}
You can't do an OR operation like that.
Better could be to use -contains against all the 'good' values
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
I ran the report and got green all the way down.
And what if we use the -or
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
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))
Ok, one last try.
Let's eliminate the $script variable altogether, and replace the -eq with a -match.
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
