SMS report to list number of devices in a collection 2

[owenhawk]

My SMS came with a report to list all devices in a collection as follows:

FROM v_FullCollectionMembership
WHERE CollectionID = "SMS000ES"
ORDER BY Name

But I want the report to list just the number of devices in the collection. There are only 2 canned reports that come with SMS fro collection and I don't see any views/columns to get this info. Any assitance is greatly appreciated.

 

[tbrennans]

...Not sure what you mean. Like right clicking a collection and selecting show count? You want that info or something else?

 

[owenhawk]

When I run the report to display all devices in a collection, in the upper right corner is the number of devices that are listed below. I want to just display the number of devices. I have a report that displays just the number of devices that have a certain app in ARP, but now I want to display the number of devices in a collection. Thank you for thinking about this.

 

[tbrennans]

Don't think you will get that out of a collection other than what i suggested by right clicking on the collection and selecting "show count". There just inst a place for it to show up. exporting it to excel would tell you, but thats a lot of effort when you could do the suggestion above in a second.

Tim

 

[ncotton]

Here is quick solution this will give you

---------------------------
| System Status | Count |
---------------------------
| OK | 1422 |
---------------------------
etc

select v_GS_Computer_System.Status0 AS SystemStatus, count (v_GS_Computer_System.Status0) AS Count FROM v_GS_Computer_System WHERE v_GS_Computer_System.Status0='OK' GROUP BY v_GS_Computer_System.Status0

That should work for ALL systems

If you have specific collections you want to query it isn't that simple, but pretty easy as they are WMI queries that create the collections. Go into your collection and go into the rules, get the query that creates the collection and replace the select v_GS with the collection query. Should work. Not tried it though.

Neil J Cotton
njc Information Systems
Systems Consultant
HND, BSc HONS, CCNA, BCS, IETF, DMTF

 

[tbrennans]

That works for me :-D

 

[owenhawk]

The first sql code worked for all devices. I am trying the specfic collection suggestion, doesn;t cooperate yet. Ill keep trying. Thank you very much.

 

[ncotton]

just replace the v_gs_Computer_system.Status with _GS_SMS000005.A common value - im at home now and don't have a system to look at.

all you need is a table view that contains an accessor to the contents of the collection....all i can remember is it starts with _ rather than v_ and has the SMS0005 etc. There will be a property in there that is the same for all them, probably sitecode or something. I will have a look tomorrow morning if you haven't resolved it.

Neil J Cotton
njc Information Systems
Systems Consultant
HND, BSc HONS, CCNA, BCS, IETF, DMTF

 

[ncotton]

In regard to that last comment, I just realised that won't work as the SMS Report Agent doesn't have access to the specified views _RES_COL_SMS00xxx so you would have to use my suggestion under my first post, where you use the query that forms the collection from the standard hardware inventory invormation. Your best bet is to use the v_GS_Computer_System view.

something like, this is PseudoCode, bare in mind Im not a SQL dude, barely use the thing, so this is just an idea.
Say for all machines in the collection

select v_GS_ComputerSystem.ReoccuringProperty AS REOCCURINGPROPERTYNAME, count (v_GS_Computer_System) WHERE ([insert whole collection query minus the initial select]) GROUP BY v_GS_ComputerSystem.ReoccuringProperty

I'll let you dwell on that and see if I can get some answers for you. The above should work, would just have to tweat it for your specific requirements. I don't think there is anyway to report directly the contents of a collection. (in a simple form)

Neil J Cotton
njc Information Systems
Systems Consultant
HND, BSc HONS, CCNA, BCS, IETF, DMTF

 

[ncotton]

Here you go, courtesy of Mark Carriere

Against the views: 
SELECT COUNT(v_FullCollectionMembership.Name) AS Expr1, vCollections.CollectionName 
FROM v_FullCollectionMembership INNER JOIN 
vCollections ON v_FullCollectionMembership.CollectionID = vCollections.SiteID 
GROUP BY vCollections.CollectionName 

Against the tables: 
SELECT COUNT(CollectionMembers.Name) AS Total, Collections.CollectionName 
FROM CollectionMembers INNER JOIN 
Collections ON CollectionMembers.SiteID = Collections.SiteID 
GROUP BY Collections.CollectionName

You may need to alter your permissions as these tables or views don't have SELECT permissions set for SMS Admins user group. Just assign them in the DB on the table or view. You can use either. I recommend view....as to not brake the encapsulated concept of using views.

HTH

Neil J Cotton
njc Information Systems
Systems Consultant
HND, BSc HONS, CCNA, BCS, IETF, DMTF