Hello Everyone,
We have a dashboard where we are displaying KPI's based on Patches and Related Computers.
In both patches and computers relationship is many to many.
Here I am trying to calculate all the compliance and non compliance computers and patches.
where in compliance computer means: if on a particular day if all the approved/related patches to a computer is installed on this computer then it is considered compliance computer, if any of the approved/related patch is not installed then it will be considered as non-compliance.
Similarly for compliance patch: if a patch is installed on all the computers then it will be considered as compliance patch otherwise non-compliance.
So the formula I am using to calculate compliance computers is:
=Count({<[Report Date Num]={'$(vMaxCalendarDate)'},ComputerID=E({1<[UpdateStatus]={'NotInstalled','Downloaded'}>})>} Distinct ComputerID)
where in MaxCalendarDate is the last day of the selected duration. UpdateStatus contains all the Patch related statuses like "Install", "Downloaded" and we consider all status as compliance except "notinstalled" and "downloaded".
So the issue I am facing here is lets say on a particular day if a Server has multiple patches out of which 1 is installed and other one is not-installed then above code is considering this computer under compliance instead of non-compliance.
This computer should be considered as non-compliance as not all patches are installed on this.
Same kind of issue is with compliance patches.
Any help would be really appreciated.
Regards,
tauceef
