This article is intended to generate a report having fields from multiple tables in the database. When you use two or more tables at the same time for pulling data, there needs to be a unique id that should connect the tables. That is when we use inner joins, where records from two tables are combined in a query's results only if values in the joined fields meet a specified condition. Here is an example showing the effective usage of inner join:
I want to pull the details of all the computers having Acrobat Reader 6.0 installed on it. My details should show Computer Name, Operating system, Software name and the model of the computer.
Since these details are spread across different tables, we need to make a connection between the tables using a unique id. The resource GUID is the unique id we can select from all the tables. Here are the steps to generate the report using Query Builder:
The following objects need to be selected for your report:
Use the join selection window and drop down each object on left and right pane. You can see that GUID will be selected automatically as Altiris will recognize it as the unique id on both.
Now you are joining first two tables, vResourceEX table and Inv_Aex_AC_Identification using the unique id selected above.
Now select the second and third tables, ie, Inv_AeX_AC_Identification and Inv_AeX_OS_Add_Remove_Programs
Click Inner button as you can see the Unique ID is already selected by the program.
Now select the tables Inv_AeX_OS_Add_Remove_Programs and Inv_AeX_HW_Serial_Number. Click Inner button as you can see the Unique ID is already selected by the program.
You can see the string under Join String window. Click Add.
You can see the SQL based on the steps we performed above now.
Hit Next and use the Table selector to select the fields you want to see in the report.
Hit Next and provide the conditions in the Conditionals window. I am giving the condition to display entries of Acrobat Reader 6.0 from Add Remove Programs.
Select the sorting and grouping as you wish in the next window.
Hit Next and you can see the SQL query there. Hit Run to test it.
The output will be displayed as below:
