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
12 Comments
Hi Eswar,
I need some assistance on the above report, first will this work on SCCM 2012 SP1 and the second how to get the "2 prompts (start time and end time)", any help is much appreciated.
Regards
Anil
haven't tested in Configmgr 2012 ,if at all works,this is only for packages but not for applications. Try the SQL Query in your Configmgr 2012 ,change the dates as you required ,run it. About creating SQL prompt ? where do you want to create ? in SSRS ? if so ,you dont need to create any as the query itself create prompts automatically with manual input the entries. for ex: yyyy-mm-dd
Hi Eswar,
Thanks for your reply, Any guidance or a way of getting a report about all the installations done for all the application in a month in a single view. For e.g. in Jan 2016 total installation of adobe reader = 100 etc, something on these lines I have checked the default reports but those provide me for single application which in turn I have to drill down, which will make my life hell while reporting. Any help / guidance is appreciated.
Regards
Anil
i dont think so its possible for applications due to application deployment evaluation cycle. When you deploy an application to 100 Pc's ,though it is installed, this evaluation cycle keeps checking whether the application is installed or not and send the status message ,so it is not really easy to track on that matter but you can generate inventory report with application install date (v_add_remove_programs) to check the installation date <30 days or so.
Hi Eswar,
Need some in the above mentioned report, will this report work for sccm 2012 sp1 and the second is how do get the sql prompt
Thanks
haven't tested in Configmgr 2012 ,if at all works,this is only for packages but not for applications. Try the SQL Query in your Configmgr 2012 ,change the dates as you required ,run it. About creating SQL prompt ? where do you want to create ? in SSRS ? if so ,you dont need to create any as the query itself create prompts automatically with manual input the entries. for ex: yyyy-mm-dd
Hi - thanks for replying. I've just been through the process again and bizarrely enough, its pasted in ok! So looks like its working fine, thanks.
Do you have any thoughts on adding some further reporting per system?
So what I'm looking at doing is returning a report for a complete site deployment of about 500 pc's. I want to return the success rate per pc of package deployments. Each PC is a member of a particular deployment group. The group is assigned to a set of packages as a query. So for example group 1 is assigned via query to packages a,b,c,d,e, and pc1, pc2, pc3 are members of this group. We have something like 20 groups.
Report wise, I'd want to return something in for format of
Computer Name Packages Status
PC00001 Packagename1 Sucesss
Packagename2 Failed
PC00002 Packagename2 Failed
PC00003 Packagename1 Success
etc
Many Thanks
Ian
Hi - I have an issue importing the MOF - doesn't seem to be appearing in the console - I have rights to import.
Alternatively, I can manually enter the query into a report, but it errors out with what looks like a syntax error.
Can you help?
what error do you see ? can you try to paste the query into report by creating new report ?
Hallo,
the report does not work for me. I only get an exclamation without an specific error message after entering the query.
I copied the query directly from the txt. Can you help me?
Thanks and regards
Michael
hi,
you need to replace the fancy quotes and try again...
Pingback: Bồn nước