Custom OS Name and OS Revision Report

Below is a customized report to display the OS Name, OS Revision, Client Name, and OS build number.
This report works for both all PCs managed via Altiris, regardless of OS.

SELECT DISTINCT 
T1.[OS Name] AS 'OS Name', 
T1.[OS Revision] AS 'OS Revision', 
T1.[Name] AS 'Name', 
T1.[OS Build Number] AS 'OS Build Number', 
T0.[Guid] AS 'Guid' 
FROM [vResourceEx] T0 
 INNER JOIN [Inv_AeX_AC_Identification] T1 
  ON T0.[Guid] = T1.[_ResourceGuid] 
   WHERE (T1.[OS Revision] LIKE '%' 
    AND (T0.[IsManaged] = 1 
    AND T1.[OS Name] LIKE '%'))

With some modifications this custom report serves as an excellent base to create custom collections for software distribution. This can be accomplished by modifying the following report entries and then generating a collection from the reports SQL

 
WHERE (T1.[OS Revision] LIKE '%' 
and
T1.[OS Name] LIKE '%'
 

For example: By changing the steps below, you can narrow the report to include only PCs with Service Pack 2

Find the following entry in the SQL query:
WHERE (T1.[OS Revision] LIKE '%' 
Change the entry to
WHERE (T1.[OS Revision] LIKE 'Service Pack 2'

Below is an example of a custom report for Windows XP SP3 clients that can be turned directly into a collection

SELECT DISTINCT   
T1.[OS Name] AS 'OS Name',   
T1.[OS Revision] AS 'OS Revision',   
T1.[Name] AS 'Name',   
T1.[OS Build Number] AS 'OS Build Number',   
T0.[Guid] AS 'Guid'   
FROM [vResourceEx] T0   
 INNER JOIN [Inv_AeX_AC_Identification] T1   
  ON T0.[Guid] = T1.[_ResourceGuid]   
   WHERE (T1.[OS Revision] LIKE 'Service Pack 3'  
    AND (T0.[IsManaged] = 1   
    AND T1.[OS Name] LIKE 'Microsoft Windows XP'))