Hello everyone!

There is a way to show the installation date of security updates installed on a specific computer ?

For listing all updates I found in ConfigMgr reports under "Software Updates - A. Compliance" a report: "Compliance 6 - Specific Computer" but the problem is that when is listed in web reports this doesn't contain a column with installation date. I tried to modify it to add another column, but my knowledge in ms sql is very poor.

Can you help me to modify the code below and add the Install Date column for the security updates?

declare @RscID int; 

select @RscID=ResourceID from v_R_System where ((Name0 = @MachineName) and (Active0 = 1));

select 

catinfo.CategoryInstanceName as Vendor,

catinfo2.CategoryInstanceName as UpdateClassification,

            ui.BulletinID as BulletinID,

            ui.ArticleID as ArticleID,

            ui.Title as Title,            

            Targeted=(case when ctm.ResourceID is not null then '*' else '' end),

            Installed=(case when css.Status=3 then '*' else '' end),

            IsRequired=(case when css.Status=2 then '*' else '' end),

            Deadline=cdl.Deadline,

            ui.CI_UniqueID as UniqueUpdateID,

ui.InfoURL as InformationURL

from v_UpdateComplianceStatus css

join v_UpdateInfo ui on ui.CI_ID=css.CI_ID

join v_CICategories_All catall on catall.CI_ID=ui.CI_ID 

join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 

join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 

left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID

left join (

                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a

                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID

                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID

where  css.ResourceID = @RscID 

and ((css.Status=2) or (css.Status=3))

and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)

and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)

order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

And maybe you have some link to ms sql tutorials and links to the free sql editing tools. 

Another question: Is possible to find when a sccm client comunicate for the first time with the server?(Different from the installation date)

Thanks in advance. Good day!

I did a quick search of the SU reports and I don’t seen any of them that display this details, Where are you seeing the Install date of a SU?

Also take a look at this blog post.

http://smsug.ca/blogs/garth_jones/archive/2008/08/12/learning-tsql.aspx

I did a quick search of the SU reports and I don’t seen any of them that display this details, Where are you seeing the Install date of a SU?

When you are logged into a computer the install date of software updates shows in ARP, seems like you could get it outta SCCM somehow but honestly I've never tried.

It is mostly true that the date is within the ARP view but exactly how do you link the ARP name to a SU?  There is no direct relationship between them, you can HOPE that the SU name is EXACTLY the same as the ARP name but that is the best that you can do.

How about just leave SU out of it and get the info from ARP? Not elegant but may work.

SELECT dbo.v_R_System.Name0, dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, dbo.v_GS_ADD_REMOVE_PROGRAMS.Publisher0,
CONVERT(datetime,installdate0,101)as [Install Date]
FROM     dbo.v_R_System 
INNER JOIN dbo.v_GS_ADD_REMOVE_PROGRAMS ON dbo.v_R_System.ResourceID = dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE   (dbo.v_R_System.Name0 = 'COMPUTER1' or 
dbo.v_R_System.Name0 = 'COMPUTER2') 

AND (dbo.v_GS_ADD_REMOVE_PROGRAMS.Publisher0 = 'Microsoft Corporation') 
AND (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Update%')
and lefT(CONVERT(datetime,installdate0,101),12) > GETDATE() - 90

ORDER BY dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0

Thank you for your answers. I will try this Monday because now here it's almost the end of the day and I still had something else to do. During this weekend I will try to get familiar with sql. 

Have a nice weekend!

Unfortunately this query will only work with XP and Win2k3. Plus not all SU have the word “update” in the ARP table.. Hence the issue with join the data.

Ah, that explains it. We are all XP here and I've used that to find something before.

Emilian, not sure if you found an answer to this yet but I think this will give you what you need.  LastStatusChangeTime is essentially the InstallDate if the patch was actually installed.  If it was never installed, it will give you the last time the update scan was run.

v_UpdateComplianceStatus css

v_UpdateInfo ui on ui.CI_ID=css.CI_ID

v_CICategories_All catall on catall.CI_ID=ui.CI_ID

v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company'

v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID

v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'

join v_Update_ComplianceStatusAll UCSA on css.CI_ID=UCSA.CI_ID and css.ResourceID=UCSA.ResourceID

join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID

join

(

css.ResourceID = @RscID

((css.Status=2) or (css.Status=3

))

(@Vendor = '' or catinfo.CategoryInstanceName = @Vendor

)

(@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass

)

by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

• Edited by HeyAdmin Tuesday, January 07, 2014 8:19 PM

• Edited by HeyAdmin Tuesday, January 07, 2014 8:19 PM

I compared the 'install date' from the Add/Remove Programs list of Microsoft updates on a specific machine to the contents of dbo.v_UpdateComplianceStatus.LastStatusChangeTime and they didn't correspond at all.  I don't think the LastStatusChangeTime column is applicable here.

What about SCCM 2012?  Any better options there?