Count computers by model number
I am trying to write a query that will return a list of all the distinct manufacturer/model numbers for all the computers in our environment and a count of each type. So far, the count is what I can't get working.

My query looks like this:

select SMS_G_System_COMPUTER_SYSTEM.Manufacturer, 
       SMS_G_System_COMPUTER_SYSTEM.Model
from   SMS_R_System 
inner join SMS_G_System_COMPUTER_SYSTEM 
on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 
group by  SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model

Which returns a list that looks similar to this:
Manufacturer Model
Dell Inc. Latitude E6500
Dell Inc. OptiPlex 760
Hewlett-Packard HP Z800 Workstation
Hewlett-Packard HP Elite 7100 Microtower PC
IBM 8215EKU
IBM 8215E7U
LENOVO 8808CTO
LENOVO 9964ABU

As soon as I add a count function:

select SMS_G_System_COMPUTER_SYSTEM.Manufacturer, 
	   SMS_G_System_COMPUTER_SYSTEM.Model,
	   COUNT(*) as Quantity
from   SMS_R_System 
inner join SMS_G_System_COMPUTER_SYSTEM 
on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 
group by  SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model

It fails. Can anyone tell me how to add a third "Quantity" Column onto the table above?

January 20th, 2012 5:20pm

Hi!

Are you writing a report? Then the report SQL query should be something like this:

select v_GS_Computer_system.Manufacturer,  v_GS_Computer_system.Model, COUNT(*) as Quantity from  v_GS_Computer_System group by v_GS_Computer_system.Manufacturer, v_GS_Computer_system.Model


The collection queries use WMI but the one you use can not be used in any SQL/web report. 

As far as I know the WMI query does not support counting.

 

Jan


  • Edited by JanJaap53 Friday, January 20, 2012 6:27 PM
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2012 6:18pm

Great report!

I would like to have a modified version of this report that would allow me to click on each of the models in the report and be able to view a list of all the computers of that model. How would I accomplish that?

Thanks!

  • Proposed as answer by ersatyle Thursday, March 28, 2013 5:56 PM
  • Unproposed as answer by ersatyle Thursday, March 28, 2013 5:56 PM
September 5th, 2012 5:37pm

Hi,

Try this: http://ccmexec.com/2010/04/sccm-report-count-computer-models-per-collection/

/Jrgen

Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2015 3:53am

How would we be able to use this query but limit it to only a specific collection?  I am having a really hard time getting this information.

Just need exactly the information above (Manufacturer/model/count) but need to limit it to specific collections.

Any help would be appreciated.

May 27th, 2015 9:58am

Add a join statement:

LEFT OUTER  JOIN  v_FullCollectionMembership fcm ON sys.ResourceID = fcm.ResourceID


Add a variable in the where clause:

and fcm.CollectionID = @Collection

Define a dataset for the variable:

select
  CollectionID,
  Name
from v_Collection
where CollectionType = 2
order by Name

Lastly configure your parameter to use the dataset:

Free Windows Admin Tool Kit Click here and download it now
May 27th, 2015 11:20am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics