none
Need Attention! Missing multiple security updates RRS feed

  • Question

  • I am looking in Azure Analysis Services and we have 50+ devices that are 'Missing multiple security updates'

    I am trying to drill down into this to see which specific security updates they are missing, but unsure how to edit the powerBI query

    I have checked a few of the devices in SCCM for the latest ADR, and they are compliant. A few of the devices are inactive, and haven't been used for some time, which makes sense. But for the ones that seem to be compliant, and are in use in the wild, how do I tell specifically which updates they are missing?

    Tuesday, September 10, 2019 3:00 AM

Answers

  • Have you had a look at the built in Software Updates Compliance reports? 


    Martin Bengtsson | Blog: www.imab.dk | Twitter: @mwbengtsson
    If a post helps to resolve your issue, please remember to click Mark as Answer.

    • Marked as answer by Dean Sullings Thursday, September 12, 2019 2:56 AM
    Tuesday, September 10, 2019 7:43 AM

All replies

  • Have you had a look at the built in Software Updates Compliance reports? 


    Martin Bengtsson | Blog: www.imab.dk | Twitter: @mwbengtsson
    If a post helps to resolve your issue, please remember to click Mark as Answer.

    • Marked as answer by Dean Sullings Thursday, September 12, 2019 2:56 AM
    Tuesday, September 10, 2019 7:43 AM
  • Try the below SQL query , it will show you missing security update for specific collection. (change the collection ID)

    select vrs.name0, CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted, 
    ui.bulletinid [BulletinID],ui.articleid [ArticleID], ui.Title, 
        Targeted=(case when ctm.ResourceID is not null then '*' else '' end), 
        IsRequired=(case when css.Status=2 then '*' else '' end), 
        'IsInstalled/NotRequired'=(case when css.Status=3 then '*' else '' end), 
        ui.InfoURL as InformationURL, 
        ui.dateposted [Date Posted] , 
        Deadline=cdl.Deadline 
        from V_UpdateComplianceStatus  css 
        join v_UpdateInfo ui on ui.CI_ID=css.CI_ID 
        left join v_CITargetedMachines  ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID 
        INNER join v_CICategories_All catall2 on catall2.CI_ID=css.CI_ID 
        INNER  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID 
        and catinfo2.CategoryTypeName='UpdateClassification' 
        join v_ClientCollectionMembers ccm on ccm.ResourceID=css.ResourceID
        JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID 
           outer apply ( 
           select Deadline=min(a.EnforcementDeadline) 
           from v_CIAssignment  a 
           join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID 
           ) cdl 
       WHERE 
       ui.Severity IN (8, 10) --this is for security and critical updates 
       and ccm.CollectionID='sms00001' and css.Status=2 --2 is for missing/Required patches
    ORDER BY 1

    Tuesday, September 10, 2019 8:48 AM
  • anyway to do this in the Kusto language in Azure itself?
    Wednesday, September 11, 2019 9:16 PM
  • Thursday, September 12, 2019 11:18 AM
    Moderator