SCCM SQL query to get all Package deployment status to specific collection

Select
Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'PackageName',
Ds.ProgramName 'Program_name',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown'
from v_DeploymentSummary Ds
join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID
Where Ds.FeatureType = 2 and ds.CollectionID='Collectionid'
order by Ds.DeploymentTime desc

SCCM SQL query to get all Package deployment status to all collection

Select
Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'PackageName',
Ds.ProgramName 'Program_name',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown'
from v_DeploymentSummary Ds
join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID
Where Ds.FeatureType = 2
order by Ds.DeploymentTime desc

SCCM SQL query to get all application deployment status to Specific collection

Select
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown',
Vaa.CreationTime as 'CreationTime',
Vaa.LastModificationTime as 'LastModifiedTime',
Vaa.LastModifiedBy as 'LastModifiedBy'
from v_DeploymentSummary Ds
left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
Where Ds.FeatureType = 1 and ds.CollectionID='CollectionID'
order by Ds.DeploymentTime desc

SCCM SQL query to get all application deployment status to all collection

Select
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown',
Vaa.CreationTime as 'CreationTime',
Vaa.LastModificationTime as 'LastModifiedTime',
Vaa.LastModifiedBy as 'LastModifiedBy'
from v_DeploymentSummary Ds
left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
Where Ds.FeatureType = 1
order by Ds.DeploymentTime desc