SLA report for SCCM Software Distribution

Have you been asked by your boss saying can you send me the report for the applications that are deployed last month with Success rates /failures? You will be wonder after looking at the success rate 🙂 and start troubleshooting why is this .

We will be deploying number of applications per day using SCCM and difficult to track the status of each application and export to document . This report will give you all the advertisements that are deployed with in specific date/time.

Create a new report with the below SQL query with 2 prompts (start time and end time)

select adv.AdvertisementName as SoftwareDistributionEvent,
sum(case LastState when 0 then 0 else 1 end) as Accepted,
sum(case LastState when 13 then 1 else 0 end) as Successful,
sum(case LastState when 11 then 1 else 0 end) as Failed,
sum(case when LastState >= 8 AND LastState <=10 then 1 when LastState = 12 then 1 when LastState = -1 then 1 else 0 end) as NotCompleted,

round(100.0*sum(case LastState when 13 then 1 else 0 end)/count(*),1) as SuccessRate,
adv.PresentTime as DistributionDate,
pkg.Name as PackageName, pgm.ProgramName, coll.Name as CollectioName,
--count(*) as C005,
adv.AdvertisementID, adv.SourceSite
from v_Advertisement adv
join v_Package pkg on adv.PackageID=pkg.PackageID
join v_Program pgm on adv.PackageID=pgm.PackageID and adv.ProgramName=pgm.ProgramName
join v_Collection coll on adv.CollectionID=coll.CollectionID
join v_ClientAdvertisementStatus stat on adv.AdvertisementID=stat.AdvertisementID
where (adv.PresentTime > @StartDate) AND (adv.PresentTime < @EndDate) and
(adv.AssignedScheduleEnabled != 0 or adv.AdvertFlags & 0x720 != 0)
group by adv.AdvertisementID, adv.AdvertisementName, adv.PresentTime,
pkg.Name, pgm.ProgramName, adv.SourceSite, coll.Name
order by adv.AdvertisementName

Create a prompt for  Startdate and end date:

Enter the information shown in below screen shot

Below is the report how it looks like :

Try it out and let me know ur comments on this. Good luck in getting agreed SLA success rate on distribution :).

Download the MOF file from here and import into your SCCM 2007 console