Show all Pending Content Distribution

The following SQL query display all pending content distribution to all distribution points. This will display all packages that are not currently in state 0.

DPstatus

/* All Content in Flight to All DPs */
select p.pkgid as 'PKGID', s.installstatus as 'Content Status', p.name as 'Content Name',(p.SourceSize/1024) as 'Content Size(MB)', s.state as 'Content State', Count(substring(servername,0,charindex('.',servername))) as 'Count'
from v_PackageStatusDistPointsSumm s 
join smspackages p on s.packageid = p.pkgid 
join v_DistributionPointInfo dist on s.ServerNALPath = dist.NALPath
where s.state !=0
Group by p.pkgid, s.installstatus, p.name, p.SourceSize, s.state
Order by Count desc, s.installstatus, p.name