Let’s learn in this blog post how to create SCCM Dynamic report link to get specific application details installed on systems.
Most of the SCCM administrators might receive requests to provide the report, like a specific application installed on how many devices. Then as an admin, you have to go to the SQL database, write a query, extract a report, and share.
This is a daily task for the SCCM admin. So I feel let’s automate this task and create a custom query link. I will share my experience on how to create a Dynamic report link to get the specific applications installed on the machines.
After creating the custom report link, you can share it with respective members to get the details easily.
How to Create Dynamic Report Link
Before creating a custom report link, create a folder where you need to keep all the custom links. There are options available in ConfigMgr Console and SQL server report link to create custom reports.
In the Configuration Manager console, go to the Monitoring tab -> Reporting and right-click create a report. Provide the name of the report and browse the path where you want to save the report.
Click next to complete the task. Then edit the report in report builder.
Also, you can browse the SQL server report link, go to the folder location and select the report name you created in the ConfigMgr console. Edit the report with report builder.
You see this below screen as we already integrated the Power BI with our lab SCCM environment. But in your case, the below would not be the same.
Note: If you have already installed the report builder in the ConfigMgr box, then you can edit the report in the same box.
Once you edit the report in report builder it will show as below screen.
Expand the Datasets Select the dataset and Right-click. Then click ‘Dataset Properties‘.
Paste the below query in the Query field.
select VRS.Name0 as 'Machine Name',
ARP.DisplayName0 as 'Application Name',
ARP.Version0 as 'Version',
ARP.Publisher0 as 'Publisher',
ARP.InstallDate0 as 'Application Installed Date'
from v_Add_Remove_Programs ARP
join v_R_System VRS on ARP.ResourceID=VRS.ResourceID
where ARP.DisplayName0 like @ProgramName
After pasting the query, once you click on the ‘Ok’ button, it will ask you to provide the user name and password to connect to the data source. If you have full permission, you can choose the ‘Use the Current Windows User‘ option. Else you can use your user name and password.
Once you submit the Ok button, you will see all the fields will be created below Datasets. Now go to the ‘Insert’ tab and Create table according to the requirement. Do the formatting and select the appropriate fields in each column.
For example, I’m using the Report Name, Folder Path, Report URL, etc., on the report page.
To create such a field, create another table and specify the name you want to use in the table. In each field Right-Click on the column and select ‘Expression.’
Go to ‘Built-in Fields‘ and choose the appropriate ‘Item‘ according to the table. Copy the field from the Example and paste it into the Value Expression Tab. Then click Ok.
Result of the Dynamic Report
To check the report, click on the report link and open it on the browser. Put the application name you want to look for and click on View Report to get the results.
Resources
- https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-report-server-urls-ssrs-configuration-manager?view=sql-server-ver15
- SCCM Primary Last Logon User Report | SQL Query | ConfigMgr
- SQL Query for SCCM Configuration Baselines Results | ConfigMgr
Author
Debabrata Pati has more than 7+ years of experience in IT. Skilled in MEMCM, Azure, and Powershell. More than five (5) years of experience in MEMCM (SCCM) administration, OSD, and Troubleshooting for the environment with more than 100K client devices.
Very useful report it helpt me to extract the exact number of machines impacted after Docker changed the ULA agreement, Thank you