How to create a custom report that shows all applications installed on specified Mac?

Symptoms

How to create a custom report that shows all applications installed on specified Mac?

Resolution

Sometimes it is required to have a SCCM Report that shows a list of applications installed on this or that Mac. 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 applications).

   • 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_COMPUTER_SYSTEM (or v_R_SYSTEM, depending on your choice in step above):

     

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

     

   • 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 applications installed on a specified Mac, please continue editing:

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

   

9. In Query tab of Dataset Properties dialog choose Use a dataset embedded in my report option, choose Data source and click on fx button:

   

10. Paste the following query (this query will select all Mac hosts) in Expression windows and click OK to close Dataset Properties dialog:

    SELECT
       v_GS_COMPUTER_SYSTEM.Name0
    FROM
       v_GS_COMPUTER_SYSTEM
    WHERE
       v_GS_COMPUTER_SYSTEM.Description0 LIKE '%macOS%'
    

    

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

    

12. In Report Parameter Properties:

    • In General tab specify the Name, e.g. mac_name, specify the Prompt, e.g. Mac Name:

      

    • In Available Values tab choose Get values from a query, choose the latest created DataSet, set Value field and Label field to Name0 and click OK:

      

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

    

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

    

15. Paste the following strings in the beginning of the query (this will declare a @ResourceID integer variable that will contain Mac ResourceID depending on the host name you specify):

    DECLARE
       @ResourceID int
    
    SELECT
       @ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
    FROM
       v_GS_COMPUTER_SYSTEM
    WHERE
       v_GS_COMPUTER_SYSTEM.Name0 = @mac_name
    

    and add the following condition in the end of the query (to make a query select applications only for specified Mac):

    WHERE
       v_GS_COMPUTER_SYSTEM.ResourceID = @ResourceID
    

    

    NOTE: Parameter @mac_name can be different depending on the one you specified in step 12.

16. Click OK to close Query Designer.

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

Mac applications report has been created. Now you can run it, select Mac and view installed applications: