Function Find-Compliance { param ( [Parameter(Mandatory)] $StatusID )
$category_list = Import-Excel "C:\pATCHING\pATCHES.xlsx"
foreach ($category in $category_list) {
if ($category.status -eq $StatusID) {
Write-Host "$($category.Parameter), whose status is $($Category.status)." } else { Write-Host "System is fully compliant in all parameter" -ForegroundColor Green } } } Find-Compliance -StatusID 'Non-Compliant'

Tried above code but it's giving multiple output.

CodePudding user response：

I would recommend you to save this in a variable while looping, and when it's finished it should determine based on that variable if everything is compliant and if it's not, print out all that are not compliant:

Function Find-Compliance {
    param ( [Parameter(Mandatory)] $StatusID )
    $category_list = Import-Excel "C:\pATCHING\pATCHES.xlsx"
    $isCompliant = $True
    $nonCompliantParameters = @()
    foreach ($category in $category_list) {
        if ($category.status -eq $StatusID) {
            Write-Host "$($category.Parameter), whose status is $($Category.status)." 
            $isCompliant = $False
            $nonCompliantParameters  = $category
        }
    } 
    If ($isCompliant) {
        Write-Host "System is fully compliant in all parameters" -ForegroundColor Green
    } else {
        Write-Host "System is not compliant in all parameters, non compliant parameters:" -ForegroundColor Red
        Write-Host $nonCompliantParameters
    }
    
} 
Find-Compliance -StatusID 'Non-Compliant'

CodePudding user response：

Here's code that also writes to the .xlsx file:

Function Find-Compliance { 

  param ( [Parameter(Mandatory)] $StatusID )

  $category_list = Import-Excel "G:\Test\Patches.xlsx"

  #Remove-Item "G:\Test\Patches.xlsx" -force

  #Clear Overall Compliance Status!
  ($Category_list[-1]).Status = ""

  $Compliant = $True
  foreach ($Entry in $category_list) {
    if (($Entry.status) -eq $StatusID)   {
      "$($Entry.Parameter), whose status is $($Entry.status)." 
      $Compliant = $False
    } 
  }
  If ($Compliant) {
    Write-Host "System is fully compliant in all parameters" -ForegroundColor Green
    ($Category_list[-1]).Status = "Fully-Compliant"
  }
  else {
    Write-Host "System is NOT fully compliant in all parameters" -ForegroundColor RED
    ($Category_list[-1]).Status = "Non-Compliant"
  }

  $Category_List   #Debugging Only!

  $Category_list | Export-Excel "G:\Test\Patches.xlsx"

} #End Function Find-Compliance   
 
#------ Main Program -----

Clear-Host
Find-Compliance -StatusID 'Non-Compliant'

Output in Console:

FORCEPOINT ONE ENDPOINT, whose status is Non-Compliant.
System is NOT fully compliant in all parameters

Parameter                        Status       
---------                        ------       
Microsoft Windows 11 Enterprise  Compliant    
Patching                         Compliant    
Encryption                       Compliant    
Antivirus                        Compliant    
Firewall                         Compliant    
DLP                              Compliant    
Tanium Client 7.4.8.1042         Compliant    
FORCEPOINT ONE ENDPOINT          Non-Compliant
EDM                              Compliant    
BeyontTrust Privilege Management Compliant    
Mircosoft O365                   Compliant    
Cofense Reporter                 Compliant    
Pulse Secure 9.1                 Compliant    
Google Chrome                    Compliant    
Microsoft Edge                   Compliant    
Email Validation Tool            Compliant    
Overall Compliance:-             Non-Complient

Excel File:

UPDATE: I found a quirk that I can't explain but here's what happens. When Export-Excel writes a file it sets the file permissions so that Full Control & Modify are not checked for users. Thus, when you try modify the Excel file in Excel you get sharing violations! If however you open Excel as an Administrator you do not get these errors. Likewise, if you modify the permissions on the file for users you can start Excel normally and you will not get the sharing violations.

CodePudding user response：

Why not simply use Where-Object ?

$category_list = Import-Excel "C:\pATCHING\pATCHES.xlsx"
$nonCompliant = @($category_list | Where-Object {$_.Status -ne 'Compliant'})
if ($nonCompliant.Count) {
    Write-Host "System is not compliant in all parameters, non compliant parameters:`r`n" -ForegroundColor Red
    Write-Host ($nonCompliant.Parameter -join [environment]::NewLine)
}
else {
    Write-Host "System is fully compliant in all parameters" -ForegroundColor Green
}