Custom Report for Windows OS with YYHX Update Versions

Products

• IT Management Suite

Issue/Introduction

How to create a custom report in IT Management Suite (ITMS) to show Windows computers with specific OS update versions (e.g., 22H2, 23H2)

Users may find that standard inventory reports in ITMS display general Operating System (OS) names but do not explicitly show the specific Windows update version (such as 22H2 or 23H2) in a single combined string for easy exported reporting.

Environment

 

• IT Management Suite (ITMS) 8.x
• Symantec Management Platform (SMP) 8.x
• Windows 10 and Windows 11 Endpoints

 

Cause

The specific update version (Release ID or Display Version) is stored in the Inv_AeX_AC_Identification table, while general computer naming and OS information are typically pulled from the vComputer view. A custom SQL join is required to concatenate these values into a single readable column.

Resolution

Follow these steps to create a custom SQL report that displays the Computer Name, OS Name, Display Version, and a combined "OS Version" string.

 

1. Open the Symantec Management Console.
2. Navigate to Reports > All Reports.
3. Right-click the folder where you want to store the report and select New > Report > Base Report, choose SQL Report as a Report Type and Create.
4. Click on Edit  button to edit the report.
5. In the Report Settings tab, provide a Name (e.g., Windows OS Update Version Report) and an optional Description.
6. In the Parameterized Query section, delete any existing text and paste the following SQL query:
   

   DECLARE @v1_TrusteeScope nvarchar(max)
      SET @v1_TrusteeScope = N'%TrusteeScope%'
   SELECT
      vc.[Guid] AS [_ItemGuid],
      vc.[Name],
      vc.[OS Name],
      iai.[DisplayVersion], 
      CONCAT(vc.[OS Name],' ', iai.[DisplayVersion]) as [OS Version]   
   FROM
      [vComputer] AS vc
   INNER JOIN Inv_AeX_AC_Identification iai on iai._ResourceGuid=vc.Guid
   WHERE
      (
         (vc.[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
      )

7. Click Save Changes.
8. Click Run to view the results. The report will now show a column named OS Version with entries such as "Microsoft Windows 11 Education 23H2".