Patching Related End to end – Collections and reports :-

Below is for a compliance report based on SQL

declare @CollectID AS  varchar(8)
SET @CollectID= 'SMS0001'

declare @CollectionListID AS  varchar(90)
SET @CollectionListID='ScopeId_5432f432-F885-4A98-B666-5432134122/AuthList_F15C63EA-B655-4940-A250-654323fd432'

declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@CollectionListID

declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0)

from v_ClientCollectionMembers ccm where ccm.CollectionID=@CollectID

select
    CollectionName=vc.Name,
    'Update List'=al.Title,
    Status=sn.StateName,
    NumberOfComputers=count(*),
    PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif(@CollCount, 0), 1))),
    CollectionID=@CollectID,
    AuthListID=@CollectionListID
from v_Collection vc right join v_ClientCollectionMembers cm on vc.CollectionID=cm.CollectionID
join v_UpdateListStatus_Live cs on cs.CI_ID=@CI_ID and cs.ResourceID=cm.ResourceID
left join v_StateNames sn on sn.TopicType=300 and sn.StateID=isnull(cs.Status, 0)
left join v_AuthListInfo al on cs.CI_ID=al.CI_ID
where cm.CollectionID=@CollectID
group by vc.Name, sn.StateName, al.Title
order by sn.StateName

________________________________________________________________________________________________________________________
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
________________________________________________________________________________________________________________________
<<<<<<<---WQL based query----->>>>> systems will be automatically part of the collection with the name of "2011 Jan Updates" Deployment

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System  WHERE        SMS_R_System.ResourceID IN (     SELECT        SMS_UpdateComplianceStatus.MachineID        FROM         SMS_UpdateComplianceStatus        JOIN SMS_UpdateDeploymentSummary           ON    SMS_UpdateComplianceStatus.CI_ID = SMS_UpdateDeploymentSummary.CI_ID     WHERE           SMS_UpdateComplianceStatus.Status = "2"        AND SMS_UpdateDeploymentSummary.AssignmentName    = "2011 Jan Updates")

______________________________________________________________________________________________________________________________
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
______________________________________________________________________________________________________________________________

Patching Related End to end – Collections and reports :-