Counting clients - the SQL details
Summary: there are plenty of ways to count SMS clients - which is the best way?
I actually use SQL Server Management Studio more than I use the SMS Administrator console. Partially that's because I'm not on the frontlines, but it's also because I have or get a lot of questions every day, and the answers to the questions are often provided by queries (an imminent blog topic). Most of those questions revolve around client counts. So what do those queries look like?
Well, the basic, and quite sufficient form of those queries is:
SELECT count(*) from v_R_System where client0=1 and obsolete0=0
Of course I almost always join in some other views or clauses but that basic format does a pretty good job. We are talking clients, so client0=1 makes sense. I've seen postings about people not seeing their clients with that flag set, but I find that if heartbeat discovery is frequent enough (2 days in our case) and everything else is flowing properly, then that column gets set propertly. Obsolete0 is a wonderful column, especially at a very dynamic environment like Microsoft - computers get rebuilt and so a machine that was a client is now replaced by another.
To be really accurate I'll change the count(*) to count(distinct name0). That reduces the client counts by about 2.4% in my very dynamic environment. So not enough to be worth typing for those quick 'what if' questions, but definitely worth doing for the 'let's build the CIO scorecard' queries. That helps with computers that change so much that they aren't recognized by SMS as being the same client but they haven't changed their computer name. It doesn't help with those that also change their names (in fact then we get double counting) but client health trending (another blog topic yet to come) allows us to account for that.
You might note that I don't include the active0 column. It sounds really useful, but that column is only relevant if you want to know which clients have seen data in the last week (or whatever your cycle is set to). As per the post linked above we at Microsoft IT count SMS clients on a 30 day cycle (which is taken care of by the client purging cycle - if the site hasn't seen heartbeat discovery data from a client in 30 days, then it's purged out of the database, and thus won't be counted).
In the olden days (2 or 3 years ago) I used to also include "obsolete0 is null" or clients that were assigned to sites (as opposed to unassigned), but with SMS 2003 SP2 (and possibly earlier) I don't find those issues to be significant (in fact, no clients meet those criteria anymore for us).
Some great members of the team I work in are considering whether hardware ID might be a good indicator of client uniqueness. Historically it hasn't been, in my experience, but as the product has changed over time I'm reconsidering that option. Even then the hardware ID column may be best for answering the computer count question, as opposed to the client count question, as per the link mentioned above. Check back in about 6 months for a blog on that topic.
Another consideration is clients that are returning data, such as inventory or status messages. That's cool, but for me "healthy" vs. unhealthy clients is a seperate issue from client counts. For example, Garth Jones, in a recent post, based his data on hardware inventory (but not including discovery data). For me that gives a 0.5% discrepency from my normal reporting. Good, but to me a computer that reports inventory but isn't a resource (i.e. isn't in v_R_System) isn't really a client. The closeness of the counts seems like just a coincidence. If I do join the views, so that I'm looking at clients that haven't reported hardware inventory, with my very dynamic environment and one week hardware inentory cycles, I see a 3.1% discrepency.That query is:
select count(DISTINCT Name0) from v_R_System sys full join v_GS_WORKSTATION_STATUS WS ON WS.resourceID=sys.resourceID where client0=1 and obsolete0=0
When I want to work along the lines of the same idea of data-generating clients but using discovery / resource data (the basis of v_R_System) but not include AD discovery data (which may be stale), then I use the following query (credit for this one goes to the SMS product group, but I'm afraid I forget who):
select count(DISTINCT Name0) from v_R_System sys full join (select ResourceId, MAX(AgentTime) as AgentTime from v_AgentDiscoveries where agentname<>'SMS Discovery Data Manager' AND agentname not like '%!_AD!_System%' ESCAPE'!' group by ResourceId) disc on disc.resourceid=sys.resourceid where client0=1 and obsolete0=0
The final option is to include Client Health Tool data, which I do, but that's tricky enough that it deserves a seperate post.
So there's my thoughts on counting clients. More importantly, what are your thoughts? Please post your comments.
p.s. My reference above to Garth's queries should not be taken to imply any criticism. In fact I have great respect for Garth, and I know him quite well. I know him so well, in fact, that I will be sure to never irritate him while he's anywhere near a chainsaw, but that's another story...