System Management Compendium

In SCCM, it’s can come in handy to use a query to select a number of machines meeting some requirement in one table, then add another query onto it for another table and show the resulting table without using the JOIN statement when there’s no sensible way of creating a JOIN.

This method of selecting a number of machines is called a subselect query. Using subselects you can select for any logic combination of the following pseudo :

Select all Windows update agent version numbers from all machines running a specific OS type

When creating subselect queries, you need to know what what entry to join to what other entry. First of all, start by putting up a pseudo description of what you want to do. Then, find the nouns in the description, in this case it would be ‘version numbers’ and ‘os type’, or extend them if you will. Then, look for tables in the query builder in the reporting module and find tables representing the nouns. Use those tables and determine which table is superior. One of either tables should return all rows nevertheless, in the example it would be the ‘ WUA’ table. So, the WUA table would be mentioned in the first FROM statement, then the COMP table would be joined upon it. Try to follow this logic and create your own table join.

This would return all machines from the v_GS_WINDOWSUPDATEAGENTVERSIO table (since all machines have at least some version of this component v_GS_COMPUTER_SYSTEM with a specific value (OS type equals the prompted value). The query that would construct this would be the following :

SQL SubSelect example

SELECT DISTINCT COMP.Name0, WUA.Version0
FROM v_GS_WINDOWSUPDATEAGENTVERSIO AS WUA
INNER JOIN v_GS_COMPUTER_SYSTEM AS COMP
ON COMP.ResourceID = WUA.ResourceID
WHERE WUA.ResourceID IN
(
SELECT OS.ResourceID
FROM v_GS_OPERATING_SYSTEM AS OS
WHERE OS.Caption0 LIKE @Type
)
ORDER BY WUA.Version0 DESC

Note; you can also use subselect statements in WQL queries, but note that WQL is not equal to SQL and copy/pasting and altering table names to match the WQL ones will not work.