locked
SCCM 2012 Query | Required MS Updates with Machine name RRS feed

  • Question

  • SCCM 2012, The below query gives a syntax error

    What i need is, a query, that can give a output From a collection, List of machines next to the machine name the MS update that are required on the machine.

    There are many reports in SCCM 2012, but they are very time consuming for collection with 1500 machines. got the query on internet - i get syntax error on the very first line.. Can you help me with a query to generate the list of machines with MS update required ??

    SELECT     v_R_System.Name0, v_Update_ComplianceStatus.Status, v_UpdateInfo.BulletinID, v_UpdateInfo.ArticleID, v_UpdateInfo.Description, v_UpdateInfo.Title,
                          v_UpdateInfo.DatePosted
    FROM         v_R_System INNER JOIN
                          v_Update_ComplianceStatus ON v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID INNER JOIN
                          v_UpdateInfo ON v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
    WHERE     (v_Update_ComplianceStatus.Status = 2) AND (v_UpdateInfo.DatePosted <= CONVERT(DATETIME, '2013-01-01 00:00:00', 102) 


    • Edited by GovG10 Friday, November 27, 2015 1:11 PM
    Friday, November 27, 2015 1:06 PM

Answers

  • Dear Sir,

    It's SQL Query that exactly like yours but works well. Contains conditions (Required? Post time? Superseded? for which OS?) which you can decide to use or not.

    SELECT     dbo.v_R_System.Name0, dbo.v_UpdateComplianceStatus.Status, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Description,
                          dbo.v_UpdateInfo.Title, dbo.v_UpdateInfo.DatePosted
    FROM         dbo.v_R_System INNER JOIN
                          dbo.v_UpdateComplianceStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateComplianceStatus.ResourceID INNER JOIN
                          dbo.v_UpdateInfo ON dbo.v_UpdateComplianceStatus.CI_ID = dbo.v_UpdateInfo.CI_ID
    WHERE    
    (dbo.v_UpdateComplianceStatus.Status = 2) AND -- Required
    (dbo.v_UpdateInfo.DatePosted <= CONVERT(DATETIME, '2015-10-10 00:00:00', 102)) AND -- Post time
    (dbo.v_UpdateInfo.IsSuperseded = 0) AND  -- Superseded or not
    (dbo.v_UpdateInfo.Title LIKE N'%Security Update for Windows Server 2008%') OR -- Update for OS
    (dbo.v_UpdateInfo.Title LIKE N'%Security Update for Windows Server 2012%')

    Best regards

    Frank

    • Proposed as answer by Frank Dong Monday, December 7, 2015 4:13 AM
    • Marked as answer by Frank Dong Tuesday, December 8, 2015 9:37 AM
    Monday, November 30, 2015 3:19 AM

All replies

  • Here's the query corrected but it's not giving exactly what you need.

    SELECT     v_R_System.Name0, v_Update_ComplianceStatus.Status, v_UpdateInfo.BulletinID, v_UpdateInfo.ArticleID, v_UpdateInfo.Description, v_UpdateInfo.Title,v_UpdateInfo.DatePosted
    FROM         v_R_System
    INNER JOIN v_Update_ComplianceStatus ON v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID 
    INNER JOIN v_UpdateInfo ON v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
    WHERE     (v_Update_ComplianceStatus.Status = 2) AND (v_UpdateInfo.DatePosted <= CONVERT(DATETIME, '2013-01-01 00:00:00', 102))

    We designed a report that list exactly what you need :

    http://www.systemcenterdudes.com/sccm-2012-software-updates-deployment-report/


    Benoit Lecours | Blog: System Center Dudes

    Friday, November 27, 2015 2:42 PM
  • Select sys.Name0,
    ui.Title, ui.DatePosted, cica.CategoryInstanceName
    From v_Update_ComplianceStatusReported ucs
    JOIN v_UpdateInfo ui on ucs.CI_ID = ui.CI_ID
    JOIN v_R_System sys on sys.ResourceID=ucs.ResourceID
    JOIN v_FullCollectionMembership fm on sys.Name0=fm.Name
    JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryTypeName = ‘UpdateClassification’ on ucs.CI_ID = cica.CI_ID
    Where ucs.status =2 and CollectionID='CollectionID'
    AND ui.IsSuperseded=0

    use this query may be this will help you. replace with your collectionID


    • Edited by AshSCCM Saturday, November 28, 2015 8:30 AM
    Saturday, November 28, 2015 8:26 AM
  • Dear Sir,

    It's SQL Query that exactly like yours but works well. Contains conditions (Required? Post time? Superseded? for which OS?) which you can decide to use or not.

    SELECT     dbo.v_R_System.Name0, dbo.v_UpdateComplianceStatus.Status, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Description,
                          dbo.v_UpdateInfo.Title, dbo.v_UpdateInfo.DatePosted
    FROM         dbo.v_R_System INNER JOIN
                          dbo.v_UpdateComplianceStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateComplianceStatus.ResourceID INNER JOIN
                          dbo.v_UpdateInfo ON dbo.v_UpdateComplianceStatus.CI_ID = dbo.v_UpdateInfo.CI_ID
    WHERE    
    (dbo.v_UpdateComplianceStatus.Status = 2) AND -- Required
    (dbo.v_UpdateInfo.DatePosted <= CONVERT(DATETIME, '2015-10-10 00:00:00', 102)) AND -- Post time
    (dbo.v_UpdateInfo.IsSuperseded = 0) AND  -- Superseded or not
    (dbo.v_UpdateInfo.Title LIKE N'%Security Update for Windows Server 2008%') OR -- Update for OS
    (dbo.v_UpdateInfo.Title LIKE N'%Security Update for Windows Server 2012%')

    Best regards

    Frank

    • Proposed as answer by Frank Dong Monday, December 7, 2015 4:13 AM
    • Marked as answer by Frank Dong Tuesday, December 8, 2015 9:37 AM
    Monday, November 30, 2015 3:19 AM
  • Hi Frank,

    I tried the query suggested by you in SCCM 2012 Query. But i get a syntax error.

    SELECT     dbo.v_R_System.Name0, dbo.v_UpdateComplianceStatus.Status, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Description, 
                          dbo.v_UpdateInfo.Title, dbo.v_UpdateInfo.DatePosted
    FROM         dbo.v_R_System INNER JOIN
                          dbo.v_UpdateComplianceStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateComplianceStatus.ResourceID INNER JOIN
                          dbo.v_UpdateInfo ON dbo.v_UpdateComplianceStatus.CI_ID = dbo.v_UpdateInfo.CI_ID
    WHERE     
    (dbo.v_UpdateComplianceStatus.Status = 2) AND -- Required
    (dbo.v_UpdateInfo.DatePosted <= CONVERT(DATETIME, '2010-10-10 00:00:00', 102)) AND -- Post time
    (dbo.v_UpdateInfo.IsSuperseded = 0) AND  -- Superseded or not
    (dbo.v_UpdateInfo.Title LIKE N'%Security Update for Windows Server 2008%') OR -- Update for OS
    (dbo.v_UpdateInfo.Title LIKE N'%Security Update for Windows Server 2012%')

    Image:

    Also:

    Also, in the collection, i have machines with all kind of OS, Windows Server 2008 R2, 2012 and 2012 R2. It would be great if this query can get list the machines of a collection, that are in need of MS Udpates. "Required" Ms Updates.

    Wednesday, February 3, 2016 12:14 PM
  • The query is for reports not for collections. Why do you want a collection query?

    Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ

    Wednesday, February 3, 2016 1:51 PM
  • Yes, i am looking for collection Query. That can list the computer names, of a collection, the computers which have MS updates "required". No other conditions needed. 

    Wednesday, February 3, 2016 1:54 PM
  • Yes, i am looking for collection Query. That can list the computer names, of a collection, the computers which have MS updates "required". No other conditions needed. 


    But why?? What is wrong with using the built-in reports for this?

    Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ

    Wednesday, February 3, 2016 2:12 PM
  • I have a list of 800 Machines in a Collection.

    I want to know, which of these 800 machines have MS Updates in "Required" Status. I just need the list of Computer names. Noting more.

    I am aware of the Below Built-IN Reports, but they do not help much.

    • Management 2 - Updates required by not Deployed: This gives me list of all the required Updates, which is good, but not my requirement. I need list of Computer names
    • Compliance 5 - Specific Computer: This is good, but again, i cannot do it for 800 machines, this will consume a lot of time. and again, i need to consolidate the report after generating the reports

    None of these, are practically helping me to generate the report i have to share with Management.


    • Edited by GovG10 Wednesday, February 3, 2016 2:45 PM
    Wednesday, February 3, 2016 2:44 PM
  • And what is wrong with Compliance 2 - Specific software update? and Compliance 8 - Computers in a specific compliance state for an update
    (secondary) ?

    Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ

    Wednesday, February 3, 2016 3:01 PM
  • i agree, I can achieve the list of computers. However i have to choose each MS Updates to get that report. i can choose the maximum required MS Update's KB and get the list of all computers. However, there is a possibility that few computers are missing from this KB's list, but need other KB's.

    My range of KB's are "all required MS Updates" I think it starts from 2013 MS Updates until 2016 Jan.

    I have used these Reports in the past to satisfy management. But these have caused me miss the computers which require ms updates. I have asked this Question in Nov 2015. Post that i have been trying to use all built-in reports. But none of them give me a single list.

    Hence decided to see, if i can get a collection Query, using which i can generate this list of ms updates.

    Wednesday, February 3, 2016 3:14 PM
  • Do you know that ,if you consider this requirement ,you get huge number of rows and that will be a BIG mess ,what are you going to do with this results ? each PC must surely produce 200+ rows and if your collection has 800+ clients ? 200*800 ,who is going to read all those ?

    Do consider the default software update compliance reports to identify the clients which are non-compliant(if atleast one patch is missing that becomes non-complaint) and take action on them rather generating report for all irrespective of installed/Not required,missing.

    Take a look at this blog, it has couple of nice patch compliance reports that may help you to start with.http://eskonr.com/?s=patch+compliance+reports&x=0&y=0

    if you still want to generate the report based on your needs, here is the SQL query: 

    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='XXXXXXXX' and css.Status=2 --2 is for missing/Required patches
    ORDER BY 1

    Via https://social.technet.microsoft.com/Forums/en-US/465da98b-0fa5-40c2-befb-dffe2a5d597e/looking-for-sccm2012-query-for-custom-compliance-report-with-patch-installed-data?forum=configmanagersecurity#f2951947-1cfa-44ee-8e9e-892d5f9aacbf


    Eswar Koneti | Configmgr Blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: eskonr

    Wednesday, February 3, 2016 3:16 PM
  • Hi Eswar,

    From the query, i have only replaced XXXX with my collection ID.

    I get a message Invalid View. Before that i got a syntax error.


    Wednesday, February 3, 2016 3:25 PM
  • Hence decided to see, if i can get a collection Query, using which i can generate this list of ms updates.

    A collection is not the right place for this.

    It sound what you need it a custom report. Custom SU reports are among the hardest to create.

    Take a look at this. http://www.enhansoft.com/blog/februarys-free-ssrs-report-is-patch-compliance-progression-by-collection


    Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ

    Wednesday, February 3, 2016 3:35 PM
  • I get a message Invalid View. Before that i got a syntax error.

    Remember this is a report not a collection query.

    Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ

    Wednesday, February 3, 2016 3:35 PM
  • Hi Eswar,

    From the query, i have only replaced XXXX with my collection ID.

    I get a message Invalid View. Before that i got a syntax error.


    It is SQL query and run it from SQL Server Management Studio. It is not WQL Query to use it in Collections.

    Eswar Koneti | Configmgr Blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: eskonr

    Wednesday, February 3, 2016 4:56 PM