How to create a custom report that shows all Mac's with specified application installed?

Symptoms

How to create a custom report that shows all Mac's with specified application installed?

Resolution

Sometimes it is required to have a SCCM Report that shows a list of Mac's that have this or that application installed. Since Parallels Mac Management stores Mac application data differently than SCCM, it is required to create a custom report. Please follow the next steps in order to create a custom report:

1. Open SCCM Console and navigate to Monitoring -> Overview -> Reporting -> Reports -> Create Report:

   

2. In the Information page of Create Report Wizard:

   • Choose SQL-based Report in Type field.

   • Fill in Name field (e.g. Mac's with specified application).

   • Click Browse... near the Path field and choose the folder you want to store the report in.

   • Click Next.

     

3. Click Next on Summary page of Create Report Wizard.

4. Wait for report deploying is finished and click Close on Completion page of Create Report Wizard.

5. When you finish, you should notice that SQL Server Report Builder should now open (if it's not opened, locate the report you created, right-click it and choose edit). Provide privileged user credentials when asking.

6. Click Table or Matrix in opened Microsoft SQL Server Report Builder:

   

7. In the opened New Table of Matrix:

   • Choose Create a dataset and click Next on Choose a dataset page.

   • Choose Data Source Connection and click Next on Choose a connection to a data sorce page (if required provide user credentials and click OK in opened Enter Data Source Credentials dialog ).

   • Expand dbo -> Views -> v_GS_COMPUTER_SYSTEM and select e.g. the following fields in Database view pane:

     • Name0

     Note: You can use dbo -> Views -> v_R_SYSTEM and choose e.g. ResourceID and NetBios_Name0 or any other views and fields depending on what data you want the report to contain.

   • Expand dbo -> Views -> v_GS_ADD_REMOVE_PROGRAMS and select e.g. the following fields in Database view pane:

     • DisplayName0
     • InstallDate0
     • Publisher0
     • Version0
   • Expand Relationships section if it is minimized.
   • Click Auto Detect option (uncheck it if checked).

   • Click Add Relationship button .

     

   • Click on cell in Left Table column and choose Tables in Current Query -> v_GS_ADD_REMOVE_PROGRAMS

     

   • Click on cell in Right Table column and choose Tables in Current Query -> v_GS_COMPUTER_SYSTEM (or v_R_SYSTEM, depending on your choice in step above):

     

   • Double click on cell in Join Fields column.

   • Click Add Field in Edit Related Fields dialog.

   • Click on cell in Left Join Filed column and choose RecourceID from opened list.

   • Click on cell in Right Join Filed column and choose RecourceID from opened list:

     

   • Click OK to close Edit Related Fields dialog.

   • Click Run Query to view the result and check that query works.

   • Click Next on Design a query page of New Table or Matrix dialog.

   • Select all fields in Available fields box and drag and drop them into Values box on Arrange fields page.

     

   • Click Next on Arrange fields page.

   • Click Next on Choose the layout page.

   • Choose style and click Finish > > on Choose a style page.

   At this point we have a report that will show all applications installed on all Mac's. To make this report show all Mac's that have a specified application installed, please continue editing:

8. Right-click Parameters in the left pane of Microsoft SQL Server Report Builder > Add Parameter...:

   

9. In General tab of Report Parameter Properties dialog, specify the Name, e.g. app_name, specify the Prompt, e.g. Application name:

   

10. Click OK to close Report Parameter Properties dialog.

11. Expand Datasets in the left pane and right-click the dataset > Query:

    

12. Click on Edit as Text button to modify the query manually:

    

13. Paste the following the following conditions in the end of the query (to make a query select Mac's with specified applications installed):

    WHERE
       v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%' + @app_name + '%'
    AND
       (v_GS_COMPUTER_SYSTEM.Description0 LIKE 'mac%' OR v_GS_COMPUTER_SYSTEM.Description0 LIKE 'OS%')
    

    

14. Click OK to close Query Designer.

15. Click Save button on Microsoft SQL Server Report Builder dialog to save the created report and close it.

Mac's with specified application report has been created. Now you can run it, specify an application and view all Mac's that have this application installed: