SCCM 2007 - Report for all Windows Updates installed

[thanke]

Hello togehter,

 

i am new in SCCM 2007 and have read many articles in the internet.

I search for an report that list every update that is installed on our servers.

 

Can someone helfp me with this issue?

 

Thanks a lot.

 

Regards

 

Thorsten

[Eswar Koneti]

why dont you use builtin reports.

I found that,software updates that are installed on server(2008) will not be listed in add and remove programs .so you will not have the info in sccm database to pull.If you are looking for patches on server 2003,please try this http://eskonr.com/2010/01/sccm-report-for-applications-installled-on-computers-with-without/ .try to modify what you need from the report.

Try using DCM feature listed here on http://blog.coretech.dk/kea/specific-updates-installed/

[thanke]

Hello

 

the problem is with the builtin reports that i cant find any that is working for my needs.

i need one for example all deployed windows updates for example. all systems that begins with "SRV".

Is there any possible way to get this solved?

 

Thanks

Thorsten

[thanke]

Hello,

 

one question again about the reports. in the moment i use the following which displays me the compliance report of all systems:

 

SQL:

Select Deploymentname, Available, Deadline,

cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + '%' AS '% Compliant',

[Compliant],

[Enforcement state unknown],

[successfully installed update(s)],

[Failed to install update(s)],

[installing update(s)],

[Waiting for another installation to complete],

[Pending system restart],

[Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

[Enforcement state unknown],

[successfully installed update(s)],

[Failed to install update(s)],

[installing update(s)],

[Waiting for another installation to complete],

[Pending system restart],

[Downloading update(s)])

) AS pvt

 

What i now want is to show me the status of all updates in the updatelists call Windows Server 2003, Windows Servern 2008 and Windows Server 2008R2. Is this possible and if yes can someone help me please?

 

PS: i didnt want to type in something manually instead all should be displayed without any popup if possible

 

Thanks a lot.

 

 

Thorsten

 

Edit:

 

Hello again, here i have an new report but still problems:

set nocount on

declare @FromDate datetime; set @FromDate=convert(datetime, @Year+'-'+case when @MonthNumber<>'' then @MonthNumber else '01' end+'-01', 20)

declare @ToDate datetime; set @ToDate=dateadd(month, case when @MonthNumber<>'' then 1 else 12 end, @FromDate)

declare @VendorID int; if @Vendor='Microsoft' select @VendorID=Microsoft from v_CategoryInfo where CategoryTypeName='Microsoft' and CategoryInstanceName='Microsoft'

declare @ProductID int; if @Product='Windows Server 2003' set @ProductID=0 else select @ProductID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Windows Server 2003' and CategoryInstanceName=@Product

declare @ClassID int; if @UpdateClass='' set @ClassID=0 else select @ClassID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='UpdateClassification' and CategoryInstanceName=@UpdateClass

declare @CI table(CI_ID int primary key)

insert @CI(CI_ID)

select ci.CI_ID

from v_UpdateCIs ci

where ci.IsHidden=0 and ci.DateRevised>=@FromDate and ci.DateRevised<@ToDate

and (@VendorID=Microsoft or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID='Microsoft'))

and (@ProductID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ProductID))

and (@ClassID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))

select

Vendor=ven.CategoryInstanceName,

UpdateClassification=cls.CategoryInstanceName,

ArticleID,

BulletinID,

Title,

Approved=case when exists(select 1 from v_CITargetedCollections where CI_ID=ci.CI_ID and CollectionID='SMS000FS) then '*' else '' end,

Present=NumPresent,

Missing=NumMissing,

NotApplicable=NumNotApplicable,

Unknown=NumUnknown,

Total=NumTotal,

PCompliant=convert(numeric(5,2), isnull((NumPresent+NumNotApplicable)*100.0/nullif(NumTotal, 0), 100)),

PNotCompliant=convert(numeric(5,2), isnull((NumMissing)*100.0/nullif(NumTotal, 0), 0)),

PUnknown=convert(numeric(5,2), isnull((NumUnknown)*100.0/nullif(NumTotal, 0), 0)),

CollectionID='SMS000FS',

UniqueUpdateID=CI_UniqueID,

InformationURL=InfoURL

from @CI ci

left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID='SMS000FS'

left join v_UpdateInfo ui on ui.CI_ID=ci.CI_ID

left join v_CICategoryInfo_All ven on ven.CI_ID=ci.CI_ID and ven.CategoryTypeName='Company'

left join v_CICategoryInfo_All cls on cls.CI_ID=ci.CI_ID and cls.CategoryTypeName='UpdateClassification'

order by 1, 2, 3

 

i want that the Collection ID, the Vendor and the product are filled automatically with an fixed data.

 

si this possible?

Edited November 28, 2011 by thanke

[stuart2981]

Hi

 

What is best Report for deploy software update in a Collection?

 

Regards.

Stuart Saavedra