ConfigMgr – SCCM Advertisement, Package Type and Program Information with Target Count

select CollectionID, COUNT(*) as MemberCount into #TempTable from v_FullCollectionMembership group by CollectionID

SELECT dbo.v_Advertisement.AdvertisementID,
‘PackageType’ =
when v_Package.PackageType = 0 Then ‘Software Distribution Package’
when v_Package.PackageType = 3 Then ‘Driver Package’
when v_Package.PackageType = 4 Then ‘Task Sequence Package’
when v_Package.PackageType = 5 Then ‘Software Update Package’
when v_Package.PackageType = 6 Then ‘Device Setting Package’
when v_Package.PackageType = 7 Then ‘Virtual Package’
when v_Package.PackageType = 8 Then ‘Application’
when v_Package.PackageType = 257 Then ‘Image Package’
when v_Package.PackageType = 258 Then ‘Boot Image Package’
when v_Package.PackageType = 259 Then ‘Operating System Install Package’
v_Collection.Name as CollectionName,
dbo.v_Package.Name as PackageName,

FROM dbo.v_Advertisement INNER JOIN
dbo.v_Program ON dbo.v_Advertisement.PackageID = dbo.v_Program.PackageID AND
dbo.v_Advertisement.ProgramName = dbo.v_Program.ProgramName INNER JOIN
dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
dbo.v_Collection ON dbo.v_Advertisement.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
#TempTable ON dbo.v_Advertisement.CollectionID = #TempTable.CollectionID

Leave a Reply

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

You are commenting using your 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