In this example I explain how to use the Count function. I anticipate that you already by now know how to create the report in Configuration Manager and how to start SQL Server Management Studio. This report will group all computer objects by Vendor and Model with a count of each unique model.

 

 

I’ve started SQL Server Management Studio, navigated to the SMS_Sitecode database, and created a new View. For information about those steps please refer to Part II of this post.

clip_image001

Select V_GS_Computer_System, click Add and Close.

clip_image001[4]

Select the columns Manufacturer(), Model() and Model() – the Model column is selected twice as we need the one column with a name and the second column with a count.

In Alias type Total for the second Model() column.
image

From the Query Designer menu, select Add Group By.
image

Select Count in the second Model() column. Your SQL statement should look like this:

SELECT     Manufacturer0, Model0, COUNT(Model0) AS Total
FROM         dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0, Model0
image

Execute the SQL Statement. The result from my test environment is displayed in the left column. Now copy the SQL statement to a new report in Configuration Manager as explained in Part II of this post.