sccm 2012 SQL Query Get software updates that are downloaded but not in any software update group

Software update groups in Configmgr 2012 simplifies lot of administrative work ,which simply  contains list of updates (must be downloaded before you add any updates) merged to single deployment (can have multiple deployments for single software update group).

Coming to the subject line, It is necessary (during cleanup activity) to find software updates that are downloaded but not in any software update group (I said software update group but not deployment group ..there is little difference with these two).

If you want to get software updates that are downloaded but not in any deployment group ,you can refer Stephen blog here.

so how do I write SQL query here  ? I used sub-selected query to get the updates .

1) first get list of software updates that are downloaded and part of software update groups

2) filter the software updates that are not in First query.

So here is the complete SQL Query for you:

If you are smart enough in SQL,you can use this SQL ,to create different reports like updates not member of particular software update group or list all updates from specific software update group etc.

select ui.Title, ui.ArticleID, ui.BulletinID,
case when ui.IsSuperseded=0 then 'No' else  'Yes' end as [IsSuperseded],
case when ui.IsExpired=0 then 'No' else  'Yes' end as [IsExpired]
FROM v_updateinfo ui
INNER JOIN V_UpdateContents uc ON uc.CI_ID=ui.CI_ID
WHERE ui.CI_ID NOT IN (SELECT upd.CI_ID from vSMS_CIRelation as cr
INNER JOIN fn_ListUpdateCIs(1033) upd ON upd.CI_ID = cr.ToCIID
INNER JOIN V_UpdateContents CC ON cc.CI_ID=upd.CI_ID
INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
where CC.ContentProvisioned='1')
AND uc.ContentProvisioned='1'
AND (ui.CIType_ID=1 OR ui.CIType_ID=8)
GROUP BY
ui.Title, ui.ArticleID, ui.BulletinID,ui.IsSuperseded,ui.IsExpired
order by 2

If you need further information about the Patch URL,Description etc,you can get it from view v_updateinfo.