Forum Discussion

prj102's avatar
prj102
Icon for Helper I rankHelper I
7 years ago

Systems missing patches for multiple months

Need a bit of help.  I'm trying to find the best way to calculate if a system is missing patches from multiple months.  Consecutive and non-consecutive.  

 

I'm using SCCM to pull in patch information with patch status (compliant or not) and I'm hoping to create two columns that will look for patching missing from multiple months.  

 

Any ideas would be helpful.

 

 

Update - More info:

 

My patch info table looks something like this.  

 

machineidci_idstatusPatchDate
system1patch1compliant3/15/2019
system2patch1not compliant3/15/2019
system3patch1not compliant3/15/2019
system1patch2compliant2/13/2019
system2patch2not compliant2/13/2019
system3patch2compliant2/13/2019
system1patch3compliant1/9/2019
system2patch3compliant1/9/2019
system3patch3not compliant1/9/2019

 

 

I'm looking to create a calculated column that will tell me something like the following.

 

machineidstatus
system1compliant
system2missing consecutive months
system3missing multiple months

 

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi prj102 ,

     

    In my opinion, I'd like to suggest you except function to compare with current list and full list. Can you please share some sample data with expect result?

     

    Notice: do mask on sensitive data.

     

    Regards,

    Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI prj102 ,

        You can try to use following measure formula:

        Measure =
VAR _statuscount =
    CALCULATE (
        COUNTA ( T1[status] ),
        FILTER ( ALLSELECTED ( T1 ), [status] = "compliant" ),
        VALUES ( T1[machineid] )
    )
VAR _datecount =
    COUNTROWS ( VALUES ( T1[PatchDate] ) )
RETURN
    IF (
        ISINSCOPE ( T1[machineid] ),
        IF (
            _statuscount = _datecount,
            "compliant",
            "Miss " & _datecount - _statuscount & " Patchs"
        )
    )

        Regards,

        Xiaoxin Sheng