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

So that one is great and all, but here’s one that presents a little more detail

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