I was searching for something to find the software update complaince to see if machines requires something to patch .as i couldnt find any such report from default reports ,so created one like below and gives me the compelet complaicne of software updates.
SQL Statement
select distinct
sys.Name0,
ui.BulletinID as BulletinID,
ui.ArticleID as ArticleID,
ui.Title as Title
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_R_System sys on css.ResourceID=sys.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=sys.ResourceID
where css.Status=2 and ccm.CollectionID=@CollID
order by sys.Name0, ui.ArticleID
Prompts
Name: CollID
Prompt text: Collection ID
Provide a SQL statement:
begin
if (@__filterwildcard = ”)
select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
else
select CollectionID as CollectionID, Name as CollectionName from v_Collection
WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
order by Name
end
