Started rolling out MS office 2013 to Users few weeks ago.For this ,created collection to get computers with MS office 2010 ,created application for MS office 2013 upgrade and deploy to collection .This collection consists of computers from different departments,business units etc.
Colleague of mine asked me if there is way to get report for deployment status of specific application for specific OU. The reason for this requirement is that ,he wanted to get the deployment status per business unit (location)for the deployed application. OUs in AD are named with location names .
A single collection (MS office 2013 upgrade ) consist of computers from several business units and he wants to see the status per business unit for reporting.
The default report ‘All Application Deployment (basic)’ from software distribution—Application monitoring category works on specific collection and this collection contains the computers from different business units (Locations which are nothing but OU’s ) did not work for this requirement. Must create new report to get the deployment status results for specific application for specific business unit.
So I started looking out for the Configmgr 2012 R2 SQL tables/sql views using the Configmgr 2012 R2 SQL views spread sheet .
I created report which can be used to find the deployment status of any application (but not packages) for specific OU.
Note: This report will list the computers on which the deployment ran or atleast the computer received policy about the deployment. For ex: An OU contains 200+ computers but the collection contains only 50 Computers from this specific OU so when you run this report,you may only see results for these 50 computers but not 200+. Also remember that,this report will not give you the error description for the computer when did the deployment is failed . It requires little more work to get the description from dll files in SSRS and will update this post when am done.
When you run the report ,you will have to select the OU ,you will see list of all OU’s from your AD .It may look ugly with all OUs and sub OU etc but you can fine tune the SQL query this to get the child OU only.
Report is based on the Deployment status :
Success
In Progress
Requirements Not Met
Unknown
Error
This report contains 2 sub reports 1)Overall deployment status results with nice pie chart for specific application for specific OU 2) sub report to get the list of computers with specific specific for the selected application .
Deployment chart summary for specific application :
Sub report to list computers with specific status for application for OU (click on the Pie chart to get list of computers with specific status):
Download the RDL files from TechNet gallery here ,upload the RDL files to your SSRS Folder (keep both these files in same folder as they are linked),change the Data source and run it.
40 Comments
So I just downloaded this report, and found that when I click on the pie chart, it changes the OU, so for instance if I choose in the Deployment results for Specific Application Per OU report Domain\Office\Location it shows all the installs for the OU, but when I click on the chart to see the list the OU changes to DOmain\Office\Location\A1\Computers
is there a way to fix this?
Hi,
The fix is to modify the SQL code for the Pie chart.
I will update the code when time permits.
Thanks,
Eswar
HI Eswar,
i need sql query for Patch deployment status report for collection based
1.Compliant Servers
2.Server with Install Pending (downloaded update)
3.Server with Pending Restart,
with this 3 status kindly help me for this query creation, i have tried lot but i am not able to create the query.
All this information you can get it if you are on Configmgr current branch 1802 and above .There is pending restart column available for clients .
Thanks,
Eswar
Hi Eswar i need to create the report and its publish in dashboard actaully we have many collection and software update group so i have compainded all system in one collection i need the last enforcement report for that collection please help me. i have already created the query for one system but not able to create for collection kindly help me.
select TOP 1
vSMS_R_System.Netbios_Name0,
vSMS_Update_ComplianceStatus.LastEnforcementMessageID,
vSMS_Update_ComplianceStatus.LastEnforcementMessageName,
vSMS_Update_ComplianceStatus.LastEnforcementMessageTime
from vSMS_R_System JOIN vSMS_Update_ComplianceStatus
on vSMS_Update_ComplianceStatus.MachineID = vSMS_R_System.ItemKey
where Name0 in ('Hostname') ORDER BY LastEnforcementMessageTime DESC
When running the report it says "OU is missing a value"
Which report you have issues ? is it linked report or parent report ? if you are running linked report directly ,it wont work as the prompt is hidden and it comes from its parent report.
Thanks,
Eswar
is there a way to add errorcode descriptions? Needed for techsupport
Hi Eswar,
Please pardon my stupidity but, I am kind of new to SCCM. Can you please guide where is SSRS folder located and how to change the data source.
Thanks in advance.
you can try this link on how to change SSRS datasource https://www.enhansoft.com/blog/how-to-change-the-ssrs-datasource
Thanks,
Eswar
Hi Eswar,
Do you have any idea on how to get top 25 successful deployment of Apps and packages.
You can get but what do you mean by top 25 successful ? is it based on the number of success or success% of the deployment .
Regards,
Eswar
Hi Eswar...I guess there is some issue with above report it show be below error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
Incorrect syntax near '='
Any suggestion !!!
I tried it ,it works for me .Can you edit the RDL file for SQL query and try it in SQL server management studio.
Regards,
Eswar
Ola Eswar
Thanks a lot for your blog , i used to forward internally to others SCCM admins your tricks , very helpful
there are typo on your query (eg IIf instead of If) , but even after cleaned that i can't get the query to work , i am on SCCM 2012 R2 SP1
Can you double-check your query please ?
Thanks,
XT
Hi Extenue,
I just ran the report ,it works fine for me.What error do you get after running report ? Below is the SQL used in the report which might help you to run in SQL management studio.
Add Displayname and OU in the SQL query before you run.
SELECT vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],vru.department0,
IIf([EnforcementState]=1001,'Already Present',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress',
IIf([EnforcementState]>=3000 And [EnforcementState]<4000,'Requirements Not Met ',
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown',
IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status,
vrs.Last_Logon_Timestamp0,
vgws.LastHWScan
FROM dbo.vAppDeploymentResultsPerClient ADRP
inner JOIN dbo.v_RA_System_SystemOUName SOU ON sou.ResourceID = ADRP.ResourceID
right JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.ci_id=ADRP.CI_ID
inner JOIN dbo.v_R_System vrs ON vrs.ResourceID = ADRP.ResourceID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws ON vgws.ResourceID=vrs.resourceid
left JOIN dbo.v_R_User AS vru ON vru.User_Name0 = vrs.User_Name0
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos ON vgos.ResourceID = vrs.ResourceID
WHERE lac.DisplayName=''
AND SOU.System_OU_Name0 LIKE '%%'
GROUP BY vrs.Name0 ,vgos.Caption0 ,vrs.User_Name0 ,vru.department0,vrs.Last_Logon_Timestamp0,
vgws.LastHWScan,EnforcementState
ORDER BY 5
Regards,
Eswar
Can this SQL report be modified to get the specific Collection ID instead of OU ???
yes you can modifiy the OU with collection .all you need is resource ID from v_fullcollectionmembership but there is already default reports for collection.
Regards,
Eswar
Eswar,
Thanks for all your support and your time you give for your blog's ... I am familiar with the built in report but it is a pain in the butt to get what you want and that is why I am looking for a easier straight forward method to get the info needed. If you can create something simialiarthat would be Awesome ...
Once again Thank you for all you do !!!
Hi,
Here is the modified SQL query ,you can take this and customize in SSRS.
SELECT distinct
vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
IIf([EnforcementState]=1001,'Already Present',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress',
IIf([EnforcementState]>=3000 And [EnforcementState]<4000,'Requirements Not Met ',
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown',
IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status,
vrs.Last_Logon_Timestamp0,
vgws.LastHWScan
FROM dbo.v_R_System AS vrs INNER JOIN (dbo.vAppDeploymentResultsPerClient
INNER JOIN v_CIAssignment ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID) ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws ON vgws.ResourceID=vrs.resourceid
INNER JOIN v_FullCollectionMembership coll ON coll.ResourceID = vrs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos ON vgos.ResourceID = vrs.ResourceID
WHERE lac.DisplayName='application name'
Regards,
Eswar
Hello Eswar
I dont see any default reports in our environment to check the status of an application , We have reports to check the status for packages . Can you tell me how to import them in sccm .Also i need a seperate report to check the status of both applications and packages .
Hi Rajni,
What version of SCCM are you running ? if you are on CM12 and above, there are some default reports with category 'Software Distribution - Application Monitoring' . To import this report, download the RDL file, upload to your SSRS report folder ,change the datasource and run the report.
Regards,
Eswar
Thanks Eswar for the reply , We are currently running SCCM 2012 R2 , where can we download those reports(RDL files ) . I am pretty new to reporting .
Hi Rajini,
Run the Configmgr reports URL ,configmgr_sitecode ,look for category 'Software Distribution - Application Monitoring' . If you do not see it ,check with your Configmgr admin for this default reports.
Regards,
Eswar
I see "All application deployments (advanced) " report under "software distribution - application monitoring " but it does not give any results , I need a report which gives me the status of the application .I am not sure if we have reports which we can import or do we need to write it completely. .
I also need a report which will give status of a machine for both applications and packages as we use applications and packages , Default report "All application deployments (advanced)" will not provide any time stamp .
For applications and packages ,they work altogether differently and their SQL tables/views also different .You already have default reports for both packages and application status.
Regards,
Eswar
Eswar,
Thanks for all your support and your time you give for your blog's ... I am familiar with the built in report but it is a pain in the butt to get what you want and that is why I am looking for a easier straight forward method to get the info needed. If you can create something simialiarthat would be Awesome ...
Once again Thank you for all you do !!!
Hi Eswar
can you help
On the Deployment chart for specific application per OU ...i dont get any application on the select the application
On the List Computers Deployment results for Specific Application Per OU ..it goes The 'OU' parameter is missing a value
thanks
It works good for me and others as well. Do you have any applications at all and are they deployed to clients ? if you have ,can you check the apps from view called dbo.fn_ListApplicationCIs(1033) ,The prompt used in the report is pulling the information from this view.
You can open the RDL file using report builder ,take the SQL query used in dataset called App and try the Query in SQL management studio.
Do you known, how to obtein the results, when a deployment was eliminated??
what do you mean by eliminated ? Removed ? if so ,you cannot get the status for removed deployment.
Is there a report that show when the application was installed? Thanks
you can add installeddate0 from v_gs_add_remove_programs? installeddate value stored from this view.
Hello,
have you tested this ?
for me it cannot work as it does not exist a relationship between the application and the software , how are you linking all together ?
Thanks
XT
what do you mean by application and software ? This report only works for Applications but not for package(legacy). If you do not see any applications in the prompt, Open the RDL file using report builder or visual studio ,try the SQL Query used in App dataset into SQL Server management studio.
Take a look at the SQL query to understand the linking and how it works.
what i meant was , have you succeed on getting installation date of an application (and not software)
for me database only tell you date of deployment (so same date for all collection members) but not date of the installation of that application
my management want a report of apps install made by sccm per month , i am just not able to do that
ok,got it. Nope,this report will not tell you when was the application installed since,Application deployment evaluation cycle will run as per the schedule and it always report success if install already .So the best way would be to query it from v_add_remove_programs if install date exist in this view.
Any chance you have this for collection based?
application deployment status report for collection based is available in default reports. have you looked at report --->All application deployments (basic) under category Software Distribution - Application Monitoring ?