RSS .92| RSS 2.0| ATOM 0.3
  • Home
  •  

    Reporting of baseline compliance with PowerCLI and Update Manager cmdlets

    The following code will generate a lovely excel spreadsheet to report compliance of ESX(i) hosts against their current baseline as applied from Update Manager.

    You will need Excel, Powershell, PowerCLI and the Update Manager cmdlets installed.  The framework is borrowed from an excellent Powershell script at http://ict-freak.nl/2009/07/01/powercli-generate-an-excel-sheet-with-vm-info/

    $xlCSV = 6
    $xlXLS = 56
    $csvfile = "compliance.csv"
    $xlsfile = "compliance.xls"
    
    $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"
    
    $vmhosts = get-vmhost
    foreach ($vmhost in $vmhosts) {
    
      $vmhostview = get-vmhost $vmhost | get-view
      $compliance = get-compliance -entity $vmhost
    
      $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 = $intRow + 1
    }
    $WorkBook.EntireColumn.AutoFit()
    
    sleep 5
    
    $Sheet.SaveAs($xlsfile,$xlXLS)
    $Sheet.SaveAs($csvfile,$xlCSV)
    

    The output will show:
    Server | Release (ESX or ESXi) | Version of ESX(i) | Build Number | Attached Baseline | Status (Compliant, Not, or Unknown

    2 responses to “Reporting of baseline compliance with PowerCLI and Update Manager cmdlets”

    1. 90210red says:

      Hi Phil,

      Does this script work with vCenter 5 .. and ESXi 5 hosts?

      as when i run i get “unknown status” reported back

      Great script by the way ..

      Many Thanks

    Leave a Reply

    Your email address will not be published. Required fields are marked *