none
Report of what updates are needed by server

    Question

  • I am looking to get a report for all windows servers i have.

    I am looking to get an export of what windows updates are needed each server.

    I have tried the Compliance 4 'Updates by Vendor month Year' but it's output give the  individual updates and what servers they are missing from. I am looking for 

    SERVERA needs
    KB123123
    KB3213421
    KB 213213

    SERVERB needs

    KB123123
    KB3213421
    KB 213213

    Hope you can help

    Monday, October 26, 2015 7:35 AM

All replies

  • I use the reports here and they work very well.  https://gallery.technet.microsoft.com/SCCM-2012-Software-Updates-5aa05da4

    Just sort the report by what is installed and you can see what they need.  Otherwise, just look at the deployment status for your Software Update Groups.


    Best, Jacob I'm a PC.

    Monday, October 26, 2015 6:47 PM
  • You might see if these fit the bill a little better.  http://ninet.org/2012/04/sccm-patch-compliance-reports/

    Best, Jacob I'm a PC.

    Monday, October 26, 2015 6:48 PM
  • Thanks Jacob - Is there a SQL Query I can run to get this info instead of using the RDL files? I have access to the SQL box.
    Tuesday, October 27, 2015 1:33 AM
  • I tried the following from another site. It works but it pulls in all updates for all applications and windows 

    How could I modify to only give me the updates needed for 

    Windows 2008 R2 and Windows 2012?

    

    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))

    Tuesday, October 27, 2015 5:24 AM
  • here is the SQL query ,i use normally to get list of patches required by specific computer ,I will put this in my blog for reference. customise this to your needs.

    select 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),

                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 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 vrs.Name0='Eskonr1234' and

    ui.Severity IN (8, 10) --this is for security and critical updates

    AND css.Status=2  --for required

    ORDER BY 1


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

    Tuesday, October 27, 2015 6:53 AM
  • You can always extract the query from those RDL files if you like.  Look for the Command tag in the XML of the file.  Just make sure you declare the variables since you won't be passing them via the report. 

    Best, Jacob I'm a PC.

    Tuesday, October 27, 2015 4:11 PM
  • Jacob -

    I have DBA access ,so permissions is not an issue, the client is also installed on all servers and I know I have over 500 windows 2008 servers each with 60+ updates needed. I also have the date correct form 10-10-2015 so it's capturing everything previous to that.

    What am I missing to get an output all windows updates (Critical & Important), needed on all servers? because the below only returns 200 rows and a total of 15 servers out of a possible 500+

    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, '2015-10-10 00:00:00', 102)) and Title like '%Security Update for Windows Server 2%'

    Wednesday, October 28, 2015 4:22 AM
  • have you tried the query that i posted to get list of updates needed by client ?

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

    Wednesday, October 28, 2015 6:58 AM
  • Eswar,

    Thanks for the query.

    I have tried it, but  the query only returns 200 rows. I have at least 500 Windows 2008 servers and each with 60+ updates needed. It also doesn't tell me what servers require what updates unfortunately. 

    Wednesday, October 28, 2015 9:41 PM
  • the query which i posted is only for 1 server ,if you want for list of servers with required patches,you may have to edit the query ,supply the inputlist or append the list of servers using condition IN . To show the server name in the query,you must add vrs.Name0 to the query .

    you can try replacing WHERE vrs.Name0='Eskonr1234' with WHERE vrs.Name0 in ( 'PC1','PC2','PC3') for list of 500 servers.


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

    Thursday, October 29, 2015 1:09 AM
  • Eswar,

    I tried that, i included all 500+ servers and it runs ok but get nothing back ((0 row(s) affected)

    I actually Forgot to mention that your script only works if I change the  WHERE vrs.Name0= to WHERE  Title like '%Security Update for Windows Server 2008 R2 x64 Edition%' but even them i only get 200 rows

    ********************************************************************************

    select 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),

                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 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  Title like '%Security Update for Windows Server 2008 R2 x64 Edition%' and

    ui.Severity IN (8, 10) --this is for security and critical updates

    AND css.Status=2  --for required 

    ORDER BY 1

    ********************************************************************************


    Thursday, October 29, 2015 4:46 AM
  • Eswar,

    I tried that, i included all 500+ servers and it runs ok but get nothing back ((0 row(s) affected)

    I actually Forgot to mention that your script only works if I change the  WHERE vrs.Name0= to WHERE  Title like '%Security Update for Windows Server 2008 R2 x64 Edition%' but even them i only get 200 rows


    what are you trying to do using this query ? the change you did is completly wrong. You cannot simply change the input computer name to something like software update ,you must also make the necessary changes to the query as well based on your inputs. what is your requirement ?

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


    Thursday, October 29, 2015 7:41 AM
  • Eswar -

    I am trying to get a list of all Critical & Important updates needed on all servers.

    Thursday, October 29, 2015 11:48 PM
  • Eswar -

    I am trying to get a list of all Critical & Important updates needed on all servers.

    then what is wrong with the query i posted ? have you given the PC name and ran the report to get list of patchew required ? You must edit the query to supply more computer names .Here is what i tried with list of servers,it worked good for me.Focus on dbo.fn_SplitString(@file,',' ).

    DECLARE @file varchar(8000);

    SET @file='PC1,PC2,PC3,PC4'

    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),

                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 dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID

                right JOIN dbo.fn_SplitString(@file,',' ) AS fss ON vrs.Name0 = fss.substring

                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 css.Status=2  --for required

    ORDER BY 1


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

    Friday, October 30, 2015 7:08 AM
  • Eswar, wouldn't it help him out if you could query for the members of a certain collection instead of providing each of the 500 servers manually in an array? I'm not good at this, therefor I'm only asking :)

    Nickolaj Andersen | www.scconfigmgr.com | @NickolajA

    Friday, October 30, 2015 8:17 AM
  • Eswar, wouldn't it help him out if you could query for the members of a certain collection instead of providing each of the 500 servers manually in an array? I'm not good at this, therefor I'm only asking :)

    Nickolaj Andersen | www.scconfigmgr.com | @NickolajA

    Oh .yeah Nikolaj ,that would be nice idea to have all these 500 PC's into collection but i didnt expect to input 500 servers and if the list of servers keeps changing every time,would be difficult to modify or create collections rather input the servers (expected to be few but not high) into SQL Server.

    Based on Nickolaj idea ,you can use this SQL query to run agaist the collection.

    declare @COLLID varchar(50);
    set @COLLID='SMS00001' --change the coll 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),
                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 dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
                join v_FullCollectionMembership fcm on fcm.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 css.Status=2  --for required
    and fcm.CollectionID=@COLLID
    ORDER BY 1


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

    Monday, November 2, 2015 4:03 AM
  • Hello Eswar,

    I am running below query and getting few of the servers rather to get all the servers ( we are having 11500 servers)

    Please help me to get all the servers in report... becuase of this report task my project is stuck 

    Select Name0 as Machinename , 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, 
           IsRequired=(case when css.Status=2 then 'YES' else '' end), 
        ui.InfoURL as InformationURL, 
        ui.dateposted [Date Posted],
        fcm.Collectionid
        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_FullCollectionMembership fcm on fcm.resourceid=css.resourceid    
        INNER  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID 
        and catinfo2.CategoryTypeName='UpdateClassification' 
        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 
       css.Status=2 
    and fcm.Collectionid= @Collection 
    ORDER BY vrs.Name0

    However, I have created another dataset where I have following query to prompt for collection

    Select CollectionID, Name from v_Collection Order by Name

    PLEASE HELP!!!!!!

    Wednesday, December 28, 2016 4:02 AM
  • I am running below query and getting few of the servers rather to get all the servers ( we are having 11500 servers)


    This is hard to tell here in the forums without being able to work with live data from your database. You would have to examine which criteria or joind filters out the remaining servers.

    Torsten Meringer | http://www.mssccmfaq.de

    Wednesday, December 28, 2016 7:48 AM
    Moderator