Query for Software Updates Reports Install Date for only Some of the Installed Updates

We use the following query to retrieve a list of software updates for a specific collection and software update group.  There are many cases where the installation status of a software update is 'installed' but there is no corresponding 'install date'.  Can anyone tell me why?

declare   @AuthListLocalID as int
declare @CollID as varchar(8)
Select @CollID=CollectionID
from v_Collection
WHERE CollectionID='ZSV0000A'
select @AuthListLocalID=CI_ID from   v_AuthListInfo
where    CI_UniqueID='ScopeId_EAD5EB80-CC39-4EA2-B08F-A45BB84F1D76/AuthList_4DEAD110-5740-495E-8BD8-467A55C50A0C'
select
rs.Name0 as ServerName,
catinfo.CategoryInstanceName as Vendor,
catinfo2.CategoryInstanceName as   UpdateClassification,
ui.BulletinID as BulletinID,
ui.ArticleID as ArticleID,
Case ui.Severity WHEN 10 THEN 'Critical' WHEN 8 THEN 'Important' ELSE LTRIM(ui.Severity) END as Rating,
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,            
CAST(CASE   IsNull(AddRem.InstallDate0,CAST('1/1/1900' as smalldatetime)) WHEN   CAST('1/1/1900' as smalldatetime) THEN AddRem64.InstallDate0 ELSE   AddRem.InstallDate0 END as Date) as 'Install Date',
ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
inner join v_UpdateInfo ui on   ui.CI_ID=css.CI_ID
inner join v_CIRelation cir  on ui.CI_ID=css.CI_ID and cir.ToCIID =   ui.CI_ID -- add this
inner join v_CICategories_All catall on   catall.CI_ID=ui.CI_ID 
inner join v_CategoryInfo catinfo on   catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and   catinfo.CategoryTypeName='Company' 
inner join v_CICategories_All catall2 on   catall2.CI_ID=ui.CI_ID 
inner join v_CategoryInfo catinfo2 on   catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
and   catinfo2.CategoryTypeName='UpdateClassification'
left outer join v_CITargetedMachines ctm   on ctm.CI_ID=css.CI_ID
and ctm.ResourceID = css.ResourceID
left outer join v_GS_ADD_REMOVE_PROGRAMS   AddRem on css.ResourceID = AddRem.ResourceID and ui.Title =   AddRem.DisplayName0
left outer join   v_GS_ADD_REMOVE_PROGRAMS_64 AddRem64 on css.ResourceID = AddRem64.ResourceID   and ui.Title = AddRem64.DisplayName0
left outer join (select atc.CI_ID
,CreationTime=min(a.CreationTime)
,ExpirationTime=min(a.ExpirationTime)
,StartTime=min(a.StartTime)
,EnforcementDeadLine=min(a.EnforcementDeadLine)
,LastModificationTime=min(a.LastModificationTime)
,Deadline=min(a.EnforcementDeadline)
from v_CIAssignment a
inner join v_CIAssignmentToCI atc on   atc.AssignmentID=a.AssignmentID
group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
left outer join v_R_System rs ON   css.ResourceID=rs.ResourceID
where    css.ResourceID in (
Select vc.ResourceID
FROM v_FullCollectionMembership vc
WHERE vc.CollectionID=@CollID)
and cir.FromCIID=@AuthListLocalID
and cir.RelationType=1  
order by
catinfo.CategoryInstanceName
, catinfo2.CategoryInstanceName
, ui.ArticleID
January 7th, 2014 3:20am

Hi Mate,

Its really difficult to do get the installed date of the patches as there is no table available in SCCM  DB to pull this info i had a requirement in the past but i was unable to do had done it via work around.

This can be done after the editing of sms_def.mof file or else using the power-shell script in windows server 2008 where the powershell is a feature by default.

H/W inventory: 

For the 2000 servers this has to be installed but if we enable this its mentioned it may utilize upto 100 % of the Memory during the h/w inventory you can check that in your SMS_DEF.MOF  -

QuickFixEngineering you can see the warning.uickFixEngineering

http://support.microsoft.com/kb/279225/en-us

Powershell script:

http://msmvps.com/blogs/richardsiddaway/archive/2011/10/23/1760058.aspx

the warning for the quick fix engineering is not there on 2012 but there on 2007 hope the info help you to achieve your output.

Free Windows Admin Tool Kit Click here and download it now
January 7th, 2014 1:45pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics