SCCM report to count collection members is returning junk

Question

I have written a report to give me the total member count on multiple collections at once. It executes, but when it does, only the first collection in the list returns its actual membership. The others return junk data. This is an SCCM 1610 environment, on a Server 2008R2 VM. I'm building the report in SQL Server Report Builder 3.0. My testing is running the execute command in the Query Designer, then comparing the results to the membership information of that collection in the Config Manager Console. To explain my issue more fully, here is the code for one collection:

SELECT
    v_Collection.Name,
    v_Collection.Comment,
    v_Collection.CollectionID,
    COUNT(*) AS [Members]

FROM
    v_Collection,
    v_FullCollectionMembership

WHERE
    v_Collection.CollectionID = v_FullCollectionMembership.CollectionID
    AND v_Collection.CollectionID LIKE 'XXXXXX01'

GROUP BY
    v_Collection.Name,
    v_Collection.Comment,
    v_Collection.CollectionID

ORDER BY
    v_Collection.Name

The above code will return this:

One Collection Result

If I add an OR statement, and another collection like this:

SELECT
    v_Collection.Name,
    v_Collection.Comment,
    v_Collection.CollectionID,
    COUNT(*) AS [Members]

FROM
    v_Collection,
    v_FullCollectionMembership

WHERE
    v_Collection.CollectionID = v_FullCollectionMembership.CollectionID
    AND v_Collection.CollectionID LIKE 'XXXXXX01'
    OR v_Collection.CollectionID LIKE 'XXXXXX02'

GROUP BY
    v_Collection.Name,
    v_Collection.Comment,
    v_Collection.CollectionID

ORDER BY
    v_Collection.Name

This is the result:

Two Collection Result

CollectionID XXXXXX02 does not have over 900,000 members. It has less than 200. Looking at the collection in ConfigMgr shows this, and I'm reasonably confident we don't have a million rogue devices in this collection.

The most confusing part of this to me is when I add successive collections, every one after the first has the same number, and its slightly larger the more collections in the list. So for example, without more screenshots, if I add a third collection, the results are as follows:

CollID   - MemberCount
XXXXXX01 - 1944
XXXXXX02 - 940519
XXXXXX03 - 940519

If I add a fourth collection, this is the result:

CollID   - MemberCount
XXXXXX01 - 1944
XXXXXX02 - 940568
XXXXXX03 - 940568
XXXXXX04 - 940568

I would like to note that these numbers are not increasing by the collection count each time. The difference between the bad number with three collections and the one with four collections is 49, but the fourth collection has a couple thousand members.

I'm certain this is a matter of my ignorance of both SQL and/or general coding, but even on a team of experienced SysAdmins, I'm one of the only guys who will try to write something like this without reams of experience, and nobody has reams of experience with SQL or Queries in our team, so I'm the de facto SCCM Reporting Guy. I can get by most of the time cannibalizing code from existing reports and the internet, but this one has me stumped. Thank you for taking the time to review my issue.

score 0 · Accepted Answer · answered Jan 05 '18 at 07:38

0

Have you tried just using the MemberCount field from v_Collection instead of your COUNT(*)? That way you don't need the "Group By" clause and it should make it easier to debug.

Have a look at the link below to download the full list of SQL Views, will help with constructing queries later.

https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b

Cheers

Joe

answered Jan 05 '18 at 07:38

JaeBee

136
3

Thank you, that was what I needed. It can be hard to know what fields are in a table without browsing to it in SQL Management Studio. Do you know a good tool for finding out all the fields without doing that? I've approached the Google Oracle for knowledge, but not found anything really useful for my level of work. – Eric Jan 17 '18 at 20:39
You can try here: https://technet.microsoft.com/en-au/library/dn581978.aspx. It gives you a brief description of each view. It's for SCCM 2012 but most of the views are the same. There's a Current Branch version but I can't find it at the moment. – JaeBee Jan 19 '18 at 07:59

score 0 · Answer 2 · edited Sep 06 '18 at 01:24

I updated your query so that it will work correctly.

SELECT
    Coll.Name as 'Collection Name',
    Coll.Comment as 'Comment',
    Coll.CollectionID as 'Collection ID',
    COUNT(*) AS 'Members'
FROM
    dbo.v_Collection Coll
    join dbo.v_FullCollectionMembership FCM on Coll.CollectionID = FCM.CollectionID
WHERE
    Coll.CollectionID LIKE 'XXXXXX01'
    OR Coll.CollectionID LIKE 'XXXXXX02'
GROUP BY
    Coll.Name,
    Coll.Comment,
    Coll.CollectionID
ORDER BY
    Coll.Name

SCCM report to count collection members is returning junk

2 Answers2