PowerCLI Patch Aging Reports

[akoptan]

Hello, 

Please i would like to know what would be the best way to generate a Patch History and patch aging reports for vCeneter 6.5 environment patched by the Update Manager, using Power CLI.

or if a Scripting Example could be provided that directs the output to a CSV file for ex, that would be great.

Thank you

Amr Koptan

Please let me know what would be the best way to generate a Patch 

History and Patch Aging reports for vCeneter 6.5 environment patched by the Update Manager.

[ryanpatel2]

Here's what I wrote that helps us maintain visibility in Excel. You'll need to populate lines 1 & 6 with your specific info.

$vCenters = Get-Content 'XXX.csv'

foreach ($vCenter in $vCenters){

#$vCenter = (Read-Host "Please enter the name of the vCenter:")

Connect-VIServer $vCenter -WarningAction SilentlyContinue

$LogPath = "XXXXXXXXX"

$xlXLS = 56

$xlsfile = $LogPath + $vCenter + ".csv"

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)

$Sheet.Cells.Item(1,1) = "Server"

$Sheet.Cells.Item(1,2) = "Release"

$Sheet.Cells.Item(1,3) = "Version"

$Sheet.Cells.Item(1,4) = "Build"

$Sheet.Cells.Item(1,5) = "Baseline"

$Sheet.Cells.Item(1,6) = "Status"

$intRow = 2

$WorkBook = $Sheet.UsedRange

$WorkBook.Interior.ColorIndex = 19

$WorkBook.Font.ColorIndex = 11

$WorkBook.Font.Bold = $True

$compliant = "Compliant"

$notcompliant = "Not Compliant"

$unknown = "Unknown Status"

$baseline = Get-PatchBaseline -Name '*Patches*' -WarningAction SilentlyContinue

$vmhosts = Get-VMHost | Where-Object {$_.ConnectionState -eq "Connected"}

foreach ($vmhost in $vmhosts) {

  $vmhostview = get-vmhost $vmhost | Get-view

  $compliance = $vmhosts | get-compliance -Baseline $baseline -Detailed

  $Sheet.Cells.Item($intRow, 1) = [String]$vmhost

  $Sheet.Cells.Item($intRow, 2) = $vmhostview.Config.Product.name

  $Sheet.Cells.Item($intRow, 3) = $vmhostview.Config.Product.version

  $Sheet.Cells.Item($intRow, 4) = $vmhostview.Config.Product.build

  $Sheet.Cells.Item($intRow, 5) = $compliance.baseline.name

  if($compliance.status -eq 0) {

    $Sheet.Cells.Item($intRow, 6) = [String]$compliant

    $Sheet.Cells.Item($intRow, 6).Interior.ColorIndex = 4

    $Sheet.Cells.Item($intRow, 1).Interior.ColorIndex = 4

  }

  elseif($compliance.status -eq 1) {

    $Sheet.Cells.Item($intRow, 6) = [String]$notcompliant

    $Sheet.Cells.Item($intRow, 6).Interior.ColorIndex = 3

    $Sheet.Cells.Item($intRow, 1).Interior.ColorIndex = 3

  }

  else {

    $Sheet.Cells.Item($intRow, 6) = [String]$unknown

    $Sheet.Cells.Item($intRow, 6).Interior.ColorIndex = 48

    $Sheet.Cells.Item($intRow, 1).Interior.ColorIndex = 48

  }

  $intRow++

}}

$WorkBook.EntireColumn.AutoFit()

sleep 5

$Sheet.SaveAs($xlsfile,$xlXLS)

Disconnect-VIServer $vCenter -confirm:$false

[akoptan]

your contribution is much appreciated!

Thank you very much

[LucD]

If install Doug's excellent ImportExcel module, you can generate such reports without counting cells.

And you don't even have to install Excel on the machine where you run the script.

Try this one, it reports for all connected vCenters and all baselines.

#Requires –Modules ImportExcel

$report = ".\patch-report-$(Get-Date -Format 'yyyyMMdd-HHmm').xlsx"

Remove-Item -Path $report -ErrorAction Ignore

foreach($vc in $global:DefaultVIServers){

    Get-PatchBaseline -PipelineVariable baseline -Server $vc |

        ForEach-Object -Process {

            Get-VMHost -Server $vc -PipelineVariable esx | where{$_.ConnectionState -eq 'Connected'} |

            ForEach-Object -Process {

                Get-Compliance -Entity $_ -Baseline $baseline -Detailed -Server $vc |

                Select @{N='vCenter';E={([System.Uri]$vc.ServiceUri).Host}},

                    @{N='Server';E={$esx.Name}},

                    @{N='Release';E={$esx.ExtensionData.Config.Product.Name}},

                    @{N='Version';E={$esx.ExtensionData.Config.Product.Version}},

                    @{N='Build';E={$esx.ExtensionData.Config.Product.Build}},

                    @{N='Baseline';E={$baseline.Name}},

                    @{N='Status';E={$_.Status}}

        } |

        Export-Excel -Path $report -WorkSheetname "$($vc.Name)-$($baseline.Name)" -AutoNameRange -ConditionalText $(

            New-ConditionalText -Range Status -ConditionalType BeginsWith 'Compliant' -BackgroundColor green

            New-ConditionalText -Range Status -ConditionalType BeginsWith 'NotCompliant' -BackgroundColor red

            New-ConditionalText -Range Status -ConditionalType BeginsWith 'Incompatible' -BackgroundColor yellow

            New-ConditionalText -Range Status -ConditionalType BeginsWith 'Unknown' -BackgroundColor cyan

        )

    }

}

Invoke-Item -Path $report

[akoptan]

Hello LucD

Thank you very much for your reply, I shall try the script

Thanks

Amr