locked
Security update reports for all desktop -last installed patch details and date RRS feed

  • Question

  • Hi

    There is an audit  going in my organisation and they are asking me to give a report for all desktops with its last instaled patch details and date.

    I am using SCCM 2007 R2,please help me to generate this report.

    hope we can create a custome query for this.

    Regard

    Tom

    Monday, August 30, 2010 12:46 PM

Answers

  • Try this out:will give you computers with all the above information.

    select distinct sys.Netbios_Name0,ip.IPAddress0,
    summ.ID,summ.title,summ.QNumbers,
    DATEADD(ss,@__timezoneoffset,ps.LastStatusTime) as LastStatusTime, ps.LastStatusMessageIDName
    from v_R_System sys
    join v_GS_NETWORK_ADAPTER_CONFIGUR ip on sys.ResourceID=ip.ResourceID
    join v_GS_PatchStatusEx ps on sys.ResourceID=ps.ResourceID
    join v_ApplicableUpdatesSummaryEx summ on
          ps.UpdateID=summ.UpdateID and ipenabled0='1'

    group by Netbios_Name0,ip.IPAddress0,LastStatusTime,
    LastStatusMessageIDName,LastExecutionResult,summ.ID,
    summ.title,summ.QNumbers
    order by Netbios_Name0

     


    Koneti @ http://eskonr.wordpress.com/
    Tuesday, August 31, 2010 7:37 AM

All replies

  • Hi Tom,

    Check this report from Garth http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx


    Kent Agerlund | http://scug.dk/members/Agerlund/default.aspx | The Danish community for System Center products
    Monday, August 30, 2010 1:09 PM
  • Try to create new report with the following syntax gives you installed patche with date(last statustime):you can even customise more upon the requirement.

    select distinct sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, fcm.SiteCode, ws.LastHWScan,summ.ID,summ.title,summ.QNumbers,
    DATEADD(ss,@__timezoneoffset,ps.LastStatusTime) as LastStatusTime, ps.LastStatusMessageIDName, ps.LastExecutionResult
    from v_R_System sys
    join v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
    join v_GS_WORKSTATION_STATUS ws on sys.ResourceID=ws.ResourceID
    join v_GS_PatchStatusEx ps on sys.ResourceID=ps.ResourceID
    join v_ApplicableUpdatesSummaryEx summ on
          ps.UpdateID=summ.UpdateID
    group by Netbios_Name0, user_Domain0,user_Name0,SiteCode,LastHWScan,LastStatusTime,LastStatusMessageIDName,LastExecutionResult,summ.ID,summ.title,summ.QNumbers
    order by Netbios_Name0

    Also check out this link here http://eskonr.wordpress.com/2010/03/25/monthly-patch-statistics-reports-to-show-up-to-the-management-in-a-simplified-manner/ .

    Koneti @ http://eskonr.wordpress.com/

    Monday, August 30, 2010 1:15 PM
  • nothing is working for me.. they need report as

    system name -Ip address -last intalled pach name- installed Date

    Regards

    Tom

    Tuesday, August 31, 2010 5:31 AM
  • Try this out:will give you computers with all the above information.

    select distinct sys.Netbios_Name0,ip.IPAddress0,
    summ.ID,summ.title,summ.QNumbers,
    DATEADD(ss,@__timezoneoffset,ps.LastStatusTime) as LastStatusTime, ps.LastStatusMessageIDName
    from v_R_System sys
    join v_GS_NETWORK_ADAPTER_CONFIGUR ip on sys.ResourceID=ip.ResourceID
    join v_GS_PatchStatusEx ps on sys.ResourceID=ps.ResourceID
    join v_ApplicableUpdatesSummaryEx summ on
          ps.UpdateID=summ.UpdateID and ipenabled0='1'

    group by Netbios_Name0,ip.IPAddress0,LastStatusTime,
    LastStatusMessageIDName,LastExecutionResult,summ.ID,
    summ.title,summ.QNumbers
    order by Netbios_Name0

     


    Koneti @ http://eskonr.wordpress.com/
    Tuesday, August 31, 2010 7:37 AM
  • Thanks Eswar.. for you r help

     Ihave tried this qry i Reports but its not  able to load, I tried in SQL qury and it shows the error @ "@__timezoneoffset.I have changed the that option and executed the list,Now its listing  all patches installed on systems.

    Regards

    Vivin

    • Proposed as answer by Garth JonesMVP Wednesday, July 27, 2011 1:07 AM
    Tuesday, August 31, 2010 8:31 AM
  • Gald you made it work.The above query should work on new report without any issues .
    Koneti @ http://eskonr.wordpress.com/
    Tuesday, August 31, 2010 3:17 PM
  • Hi Eswar,

    I have same issue I am not able run this SQL query.

    My Audit team is requesting to generate a report with followings;

    Host Name, OS Details and date when the last patch was installed. I have to run this report on a specific collection.

    This query is not working as I have SQL 2014 and SCCM 2012R2 1702. could you please help.

    Asif

    Monday, July 16, 2018 8:10 AM
  • Hi Eswar,

    I have same issue I am not able run this SQL query.

    My Audit team is requesting to generate a report with followings;

    Host Name, OS Details and date when the last patch was installed. I have to run this report on a specific collection.

    This query is not working as I have SQL 2014 and SCCM 2012R2 1702. could you please help.

    Asif

    That report doesn't work because some of the sql views (v_GS_PatchStatusEx  and v_ApplicableUpdatesSummaryEx) do not exist or replaced in configmgr 2012/current branch.

    you can download SQL views from https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b and get the report.


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

    Monday, July 16, 2018 2:34 PM
  • This does create some duplicates because multiple patches report as installed at same time.  As close as I could get to requirement:

    select distinct vrs.Name0 as Name, vrs.Operating_System_Name_and0 as OS, nac.IPAddress0 as IP, ui.Title, li.LastInstalled from v_r_system vrs --
    left join v_GS_NETWORK_ADAPTER_CONFIGUR nac on vrs.ResourceID = nac.ResourceID and nac.IPAddress0 is not null
    join (select distinct ucs.ResourceID, max(ucs.LastLocalChangeTime) as LastInstalled
     from v_Update_ComplianceStatus ucs join v_UpdateInfo ui on ucs.CI_ID = ui.CI_ID and ui.CIType_ID = 8 and ucs.Status = 3  
      Group by ucs.ResourceID) as LI on vrs.resourceid = LI.resourceid
    join v_Update_ComplianceStatus ucs on LI.resourceid = ucs.resourceid and LI.LastInstalled = ucs.LastLocalChangeTime
    join v_UpdateInfo ui on ucs.CI_ID = ui.CI_ID and ui.CIType_ID = 8 and ucs.Status = 3
    order by vrs.name0
    


    Tuesday, July 17, 2018 1:03 AM