1 2 3 Previous Next 36 Replies Latest reply on Oct 7, 2020 11:30 AM by JCTan Branched to a new discussion.

    Export Compliance Reports

    elihuj Enthusiast

      We are looking to get a report of missing patches and such from our hosts. I found this document from bbambalova, and wanted to see if anyone could assist me with it. From what I can tell, right now this script will run against ALL hosts found via the $vmHosts = Get-VMHost variable. We want to be able to run this against a specific set of hosts or even select clusters only; not the entire environment. I have tested with the following:

       

      $vmHosts = Get-Content "C:\esxihosts.txt"

       

      Within that text file, I have a few hostnames that I'd like to run the report against. When I run the script (ExportCompliance.ps1 $vmHOsts "C:\Reports\"), I get the following error:

       

      Cannot process argument transformation on parameter 'inventoryItems'. Cannot convert the "esx1.host.com" value of type "system.String" to type "VMware.VimAutomation.ViCore.Types.V1.Inventory.InventoryItem[]".

       

      Is there any way I can set this up to run as I'd like? Or is there a better way to go about accomplishing my goal? Thank you.

        • 1. Re: Export Compliance Reports
          LucD Guru
          vExpertCommunity WarriorsUser Moderators

          Nearly there, you will have to provide the script with the actual objects, not the names.

          So try it like this

          ExportCompliance.ps1 (Get-VMHost -Name $vmHosts) "C:\Reports\"

          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Export Compliance Reports
            elihuj Enthusiast

            Oh cool! Thank you LucD!

             

            Now when I run this, nothing terrible will happen correct? This will ONLY run the report against whatever I have in the esxihosts.txt file right?

            • 3. Re: Export Compliance Reports
              LucD Guru
              Community WarriorsUser ModeratorsvExpert

              Correct

              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Export Compliance Reports
                elihuj Enthusiast

                Thanks again LucD, it worked perfectly

                 

                As a followup, do you know of a way to modify the powershell script to include only certain patch types? For example, the output now will show the Patch Compliance column with values of Compliant, Not Compliant, Not Applicable, etc. Similar to what is found when looking at the summary in Update Manager. Can this be filtered to show say Compliant and Not Compliant only?

                • 5. Re: Export Compliance Reports
                  obireb Novice

                  Thanks LucD. The script worked.

                  • 6. Re: Export Compliance Reports
                    LucD Guru
                    User ModeratorsvExpertCommunity Warriors

                    Unfortunately the ComplianceStatus parameter only accepts 1 value.

                    But you can filter the ones you require after the Get-Compliance cmdlet with a Where-clause.

                    Something like this

                    $compliances = Get-Compliance -Entity $inventoryItem -Detailed |

                         Where {"Compliant","NonCompliant" -contains $_.Status}

                    Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                    • 7. Re: Export Compliance Reports
                      elihuj Enthusiast

                      I added in the code you suggested, but it unfortunately still outputs the same. Any idea what else I could try? Thank you.

                      • 8. Re: Export Compliance Reports
                        LucD Guru
                        Community WarriorsvExpertUser Moderators

                        Strange, can you try (just take whatever entity for which you know the status is not Compliant or NonCompliant.

                        Then do

                        Get-Compliance -Entity $entity | Select Status

                        Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                        • 9. Re: Export Compliance Reports
                          elihuj Enthusiast

                          Thanks again LucD. I think I will leave that script as-is for a detailed view.

                           

                          You did get me thinking though. If I run:

                          $Get-Compliance -Entity servername -Detailed
                          
                          
                          
                          

                           

                          I get a decent output showing a summary view. This may actually be ideal for what I'm doing. How can I output this into a text file?

                           

                          Another followup! Am I correct in this script will only output results if there is an existing scan?

                          • 10. Re: Export Compliance Reports
                            LucD Guru
                            vExpertUser ModeratorsCommunity Warriors

                            You can just direct it to a text file with the Out-File cmdlet.

                            Get-Compliance -Entity servername -Detailed  | Out-File C:\compliance.txt

                            But that will also give you the default output (selected properties, the way they are displayed...).

                            If you want more control over the output, you can first direct the objects to the Select-Object cmdlet and then write them for example to a CSV file.

                            For example

                            Get-Compliance -Entity $esx -Detailed |

                            Select @{N="EntityName";E={$_.Entity.Name}},

                                @{N="Baseline";E={$_.Baseline.Name}},

                                Status |

                            Export-Csv C:\compliance.csv -NoTypeInformation -UseCulture

                            Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                            • 11. Re: Export Compliance Reports
                              elihuj Enthusiast

                              You've been really helpful here LucD, I really appreciate your help and patience In addition to getting the detailed view, I'm also working on this code:

                               

                              Get-Compliance -Entity $inventoryItem -Detailed | Sort-Object -Property Entity | Format-Table -AutoSize | 
                              Out-String -Width 5000 | Out-File "$path\PatchComplianceReport_$CurrentDate.txt"
                              

                               

                              That outputs great in a text file. However, is there any way that I can output that to a .csv? I've tried changing Out-File to Export-Csv, but it did not like that.

                              • 12. Re: Export Compliance Reports
                                LucD Guru
                                User ModeratorsvExpertCommunity Warriors

                                That is in fact what my 2nd example in my previous answer is doing.

                                You only need to output the object to the Select-Object cmdlet, where you specify which properties to show, and then the result goes to the CSV file with the Export-Csv cmdlet.

                                 

                                Coming back to your TXT file lines, you can simplify that a bit.

                                Get-Compliance -Entity $inventoryItem -Detailed |

                                Out-File "$path\PatchComplianceReport_$CurrentDate.txt" -Width 5000

                                The Format-Table cmdlet is intended for screen output.

                                 

                                The Sort is not needed on Entity, since the Get-Compliance can only accept one entity on each call.

                                You could sort on the Basline name.

                                Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                                • 13. Re: Export Compliance Reports
                                  elihuj Enthusiast

                                  I feel like I'm getting closer!

                                   

                                  For the first one, I've added this code:

                                  Get-Compliance -Entity $inventoryItem -Detailed |
                                    Select @{N="EntityName";E={$_.Entity.Name}},
                                    @{N="Baseline";E={$_.Baseline.Name}},
                                    @{N="Compliant Patches";E={$_.CompliantPatches.Name}},
                                    @{N="Not Compliant Patches";E={$_.NotCompliantPatches.Name}},
                                    @{N="Unknown Patches";E={$_.UnknownPatches.Name}},
                                    @{N="Not Applicable Patches";E={$_.NotApplicablePatches.Name}},
                                    Status |
                                    Export-Csv "$path\PatchComplianceReport_$CurrentDate.csv" -UseCulture
                                  

                                   

                                  When I do that, it will output to a CSV file with the correct headings. However there are no numerical values

                                   

                                  And for the plain text file, when I simplify it as you mention it truncates the text. Is there a way to have it display in formatted columns?

                                   

                                  Thank you!

                                  • 14. Re: Export Compliance Reports
                                    LucD Guru
                                    vExpertCommunity WarriorsUser Moderators

                                    To get the numbers you will have to use the Measure-Object cmdlet.

                                    Like this

                                    Get-Compliance -Entity $inventoryItem -Detailed |

                                    Select @{N="EntityName";E={$_.Entity.Name}},

                                    @{N="Baseline";E={$_.Baseline.Name}},

                                    @{N="Compliant Patches";E={$_.CompliantPatches | Measure-Object | Select -ExpandProperty Count}},

                                    @{N="Not Compliant Patches";E={$_.NotCompliantPatches | Measure-Object | Select -ExpandProperty Count}},

                                    @{N="Unknown Patches";E={$_.UnknownPatches | Measure-Object | Select -ExpandProperty Count}},

                                    @{N="Not Applicable Patches";E={$_.NotApplicablePatches | Measure-Object | Select -ExpandProperty Count}},

                                    Status |

                                    Export-Csv "c:\compliance.csv" -UseCulture

                                    The problem with the text file is most probably caused by the .ps1xml file that controls the formatting of these properties (but that is another story).

                                    So add the Format-Table again.

                                    Get-Compliance -Entity $inventoryItem -Detailed |

                                    Format-Table -AutoSize |

                                    Out-File  -Width 255 -FilePath "c:\compliance.txt"

                                    Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                                    1 person found this helpful
                                    1 2 3 Previous Next