Last updated on August 2nd, 2022 at 10:57 am
In the forums I see a lot of people asking questions about how to query for OS names. Simply taking the Operating System Names for the v_R_System SQL view in an SQL Server Reporting Services (SSRS) report will not distinguish between different OS versions such as Windows 7 Professional, Enterprise, or Ultimate.
As a Microsoft Endpoint Manager Configuration Manager (MEMCM / MECM / SCCM ) admin, knowing the true OS name is important. But far too many examples out on the web will show the OS Name from the v_R_System which use the old style NT names and version. However, with a small change to this query, you will be able to get the exact Windows version that each PC is running.
How to Add the Operating System Names to your SQL Query
Using the following query as an example, you can see that that the OS Caption will give you the “true” OS name, which is what you want. In the example, I am listing only those PCs running Windows 7 (Microsoft Windows NT Workstation 6.1) but you can adjusted it as needed.
|
Select R.Name0, R.Operating_System_Name_and0, OS.Caption0 From dbo.v_R_System r left outer join dbo.v_GS_OPERATING_SYSTEM OS on R.ResourceID = OS.ResourceId Where R.Active0 = 1 and R.client0 = 1 and R.Operating_System_Name_and0 = ‘Microsoft Windows NT Workstation 6.1’ Order by R.Operating_System_Name_and0 |
Notice within the results that v_R_System SQL view (R.Operating_System_Name_and0) does not distinguish between Ultimate and Professional, but the v_GS_OPERATING_SYSTEM (OS.Caption0) does make this distinction.
With only a small change to your query you can display the true Operating System Names (OS.Caption0) for each PC.
Don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.