Forum Discussion

DonRoberto's avatar
DonRoberto
New Member
1 year ago

Sorting out Compliance State

Hi,

 

I'm using MECM for patchmanagement and use a powerbi report for monitoring. The table I'm talking about is the following:

AssignmentDeviceStatusStatusTime
UpdateAssignment OS1Device1Waiting for Maintenance Window18.02.25 01:00
UpdateAssignment OS2Device1Waiting for Maintenance Window18.02.25 01:01
UpdateAssignment OS3Device1Waiting for Maintenance Window18.02.25 01:03
UpdateAssignment Office1Device1Compliant18.02.25 03:00
UpdateAssignment Office2Device1Compliant18.02.25 03:01

 

In the last month I switched the filter from "Max Status"(A-Z) to "Max Date" by using a helping table. This month all devices(where no office is installed) are reporting as compliant, because they evaluated the office updates as latest. Beginning with their maintenance window the switch the state to e.g. "Installing Updates".

 

Max Status doesn't work from time to time, because some devices doen't evaluate the the OS assignment which they not belong to.

 

Does anyone have a idea how I can filter this out?

 

THX

Robert

 

Need Help

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi DonRoberto ,

     

    Your issue arises because devices are reporting compliance based on Office updates rather than OS updates, leading to incorrect status filtering. Since some devices do not evaluate OS assignments they do not belong to, relying on "Max Status" is inconsistent. The solution is to ensure that OS updates take precedence over Office updates when determining compliance.

    First, create a calculated column to classify updates as either OS-related or Office-related. You can achieve this with the following DAX expression:

    UpdateType = 
IF( CONTAINSSTRING('Table'[Assignment], "OS"), "OS", "Office")

    Once the updates are classified, define a measure that retrieves the latest OS update status if available. If no OS updates exist for a device, the measure should fall back to the latest overall update status. This can be accomplished using:

    Latest OS Status = 
VAR LatestOSStatus = 
    CALCULATE(
        MAX('Table'[Status]),
        'Table'[UpdateType] = "OS"
    )

VAR LatestOverallStatus = 
    MAX('Table'[Status])  

RETURN
    IF(NOT(ISBLANK(LatestOSStatus)), LatestOSStatus, LatestOverallStatus)

    By applying this measure in your Power BI report, you ensure that devices are evaluated based on OS update status first. If a device has OS updates, the measure will return the most recent OS status. If a device only has Office updates, it will return the latest Office update status. This approach prevents Office updates from incorrectly marking devices as compliant before OS updates are processed.

     

    Best regards,

    • DonRoberto's avatar
      DonRoberto
      New Member
      1 year ago

      Hi DataNinja777 ,

       

      thanks for your quick response. I see two problems:

       

      1. The devices need to install both OS & Office Updates. We are talking about a server infrastructure where only 1% has installed Office. "LatestOSStatus" will never be blank. So the expression will always return the OS status only.

       

      2. You say "Max Status" is inconsistent. I'm with you, because it's depending on the alphabet. Shouldn't we use "Max Date" instead?

       

      Additional Information:

      It could be possible that the table is looking like this:

      AssignmentDeviceStatusStatusTime
      UpdateAssignment OS1(Server2025)Device1Compliant18.02.25 01:00
      UpdateAssignment OS2(Server2016)Device1Waiting for Maintenance Window18.02.25 01:01
      UpdateAssignment OS3(Server2019)Device1Waiting for Maintenance Window18.02.25 01:03
      UpdateAssignment Office1(Office2016)Device1Compliant18.02.25 03:00
      UpdateAssignment Office2(Office365)Device1Waiting for Maintenance Window18.02.25 03:01

       

      Do you know what I mean? 🙂

       

      Best regards

      Robert

      • DataNinja777's avatar
        DataNinja777
        Super User
        1 year ago

        Hi DonRoberto ,

         

        Since devices need to install both OS and Office updates, and we should determine the latest status based on StatusTime rather than Status, the best approach is to find the most recent StatusTime for each device and return the corresponding status. Using MAX(StatusTime), we identify the latest update time for each device, and then TOPN(1) ensures we select only the most recent status entry. This method ensures that neither OS nor Office updates are prioritized arbitrarily and that the device's latest update determines the compliance status.

        Latest Update Status =
VAR LatestTime = CALCULATE(
    MAX('Table'[StatusTime]), 
    ALLEXCEPT('Table', 'Table'[Device])
)

RETURN CALCULATE(
    SELECTCOLUMNS(
        TOPN(1, 'Table', 'Table'[StatusTime], DESC), 
        "LatestStatus", 'Table'[Status]
    ),
    ALLEXCEPT('Table', 'Table'[Device])
)

        This formula ensures that the most recent update—whether OS or Office—is always considered, preventing scenarios where an older OS update is mistakenly prioritized over a newer Office update. If the latest update for a device is an Office update, it will correctly return that status, and vice versa. Let me know if this works better for your use case!

         

        Best regards,