Get Update Compliance Status

select su.bulletinid, ui.description, ui.title,count(status) NumberOfMachinesInState, sn.statename, ui.daterevised

from v_UpdateComplianceStatus UCS

join vSMS_SoftwareUpdate SU on SU.CI_ID = ucs.CI_ID

join v_StateNames sn on ucs.status = sn.stateid

join v_updateinfo ui on ui.ci_id = ucs.ci_id


where (1=1)

and
su.isSuperseded = 0

and sn.topictype = 300

group by ucs.ci_id, status, su.issuperseded, sn.statename,su.bulletinid, ui.description, ui.title, ui.daterevised

order by daterevised, title, description,bulletinid

[updated: 10/27/11 – 15:22)
So this is great and all, but there’s a better table that has all the summary information that you need, here’s the statement:

select 

su
.bulletinid,

ui
.description,

ui
.title,

ui
.daterevised,

UCS.LastSummaryTime,

UCS.NumTotal,

UCS.NumUnknown,

UCS.NumPresent,

UCS.NumInstalled,

UCS.NumFailed,

UCS.NumNotApplicable,

UCS.NumMissing

from v_Update_ComplianceSummary UCS

join vSMS_SoftwareUpdate SU on SU.CI_ID = ucs.CI_ID

join v_updateinfo ui on ui.ci_id = ucs.ci_id


where (1=1)

and
su.isSuperseded = 0

order by daterevised, title, description,bulletinid

One thought on “Get Update Compliance Status

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s