October 2007 - Posts
Summary: John Nelson has improved on a query I posted in a recent blog posting. You might have missed that, so I want to highlight his comments.
As a programmer / scripter from way back, any time I see a lot of very similar code, I expect there must be a better way. That was true in this case but I wasn't familiar with the technique John ponts out, and the query was quick enough for me. But a great query is certainly better than a good one.
When I started this blog one of my greatest hopes was that it would stimulate discussion about some of my favorite computer management topics. That hasn't happened as much as I would like, so I imagine many of you won't be checking the comments. That's definitely worth doing in this case. Even better is to check out John's blog posting on the topic, since he goes into great detail to educate us on how to do such queries in a much more efficient manner: http://myitforum.com/cs2/blogs/jnelson/archive/2007/10/09/turning-rows-into-columns-using-pivot.aspx
And for the record, I'm more than pleased to learn from others in any forum. Working here at Microsoft provides many opportunities for that (I have many great co-workers). That's half the fun of being a techie - the opportunity to learn all the time. So I hope you'll comment as well, on any of my postings so far, or yet to come. That's what really advances the state of the art.
Summary: I don't have much in the way of details, but the blog below has details for a Live Meeting where the details will be explained for SCCM and SCOM certifications.
So do you want my opinion of certifications? I think they're great for rounding out one's expertise in secondary technologies. For example, for a SCCM techie, having certifcations on SQL Server and AD would be wonderful. The SCCM certification itself is good for a relatively junior SCCM techie. If such a person applies for a job where they'd be the only SCCM techie, or in a medium-sized shop where there'd be one of two of three SCCM techies, then the certification would help the hiring manager to believe that the guy (male or female) probably has the basics down enough to be able to do the job. But in a large shop, the skills required are rarely addressed by the certification training or test, so then it would not be nearly as useful.
I've had the pleasure of interviewing at least 50 SMS techies over the years, and the few that have proudly announced their SMS ceritifcation have actually made me nervous - do they really think that's good enough to be able to run SMS on a large scale? So in a large shop I look for a variety of skills, attitudes, and experiences, but SMS certification is low on the priority list. On the other hand, I find it hard to find people with good background in those ways AND decent SQL or scripting or other skills. So someone with good a good SMS background and certifications in those technologies would definitely get my attention.
So I definitely encourage certification, but there's more to being a great SMS techie than high certification scores.
Summary:computer management is an always evolving art, done in multiple locations. That means you're going to have various computer management software versions in your environment, and each site will be different.
How do you monitor at that level of detail? The following query might help. It will produce output like so:
The query is a bit complicated, but not as bad as some. If you're not comfortable with SQL, the best bet is to read it in sections. Certainly you'll have to adjust it for your site's sitecodes. And the first column's version descriptiosn will not be useful for anyone that didn't do a lot of SCCM 2007 beta testing, so you'll have to adjust that part for the versiosn you use.
And you will notice some details about my SCCM environment. Sitecodes are not particularly secret, but the mix of client counts might be a little surprisng. Basically we're taking our production operations more seriously than ever, so upgrading sites is going slower than in past dogfooding cycles, just to be safe. And 'EMA' was just recently moved to SCCM, so its client versions will upgrade for SCCM over the next week or so, but aren't there yet.
when client_version0='2.50.4160.2000' then 'v3 SP2'
when client_version0='2.50.4138.2000' then 'v3 SP2 RC'
when client_version0='2.50.4050.2000' then 'v3 SP2 Beta'
when client_version0='2.50.3174.1018' then 'v3 SP1'
when client_version0='2.50.3174.1152' then 'v3 SP1 with ITMU pre-reqs'
when client_version0='4.00.5135.0000' then 'v4 Beta 1'
when client_version0='4.00.5125.0000' then 'v4 Beta 1 Escrow'
when client_version0='4.00.5221.0000' then 'v4 Beta 1Refresh Escrow'
when client_version0='4.00.5224.0000' then 'v4 Beta 1Refresh'
when client_version0='4.00.5571.0000' then 'v4 Beta 2 Escrow'
when client_version0='4.00.5574.0000' then 'v4 Beta 2 Escrow update'
when client_version0='4.00.5578.0000' then 'v4 Beta 2'
when client_version0='4.00.5578.0002' then 'v4 Beta 2 with a hotfix?'
when client_version0='4.00.5790.0000' then 'v4 RC0 Preview'
when client_version0='4.00.5815.0000' then 'v4 RC0'
when client_version0='4.00.5924.0000' then 'v4 RTM escrow'
when client_version0='4.00.5931.0001' then 'v4 RTM'
else client_version0end 'SMS client versions',
client_version0 'version number',(select count(distinct name0) from v_R_System sys2 join v_RA_System_SMSAssignedSites ass2 on sys2.resourceID=ass2.resourceID and sys2.client_version0=sys1.client_version0 and SMS_Assigned_Sites0='NAM' and client0=1 and obsolete0=0) 'NAM',
(select count(distinct name0) from v_R_System sys2 join v_RA_System_SMSAssignedSites ass2 on sys2.resourceID=ass2.resourceID and sys2.client_version0=sys1.client_version0 and SMS_Assigned_Sites0='PSB' and client0=1 and obsolete0=0) 'PSB',(select count(distinct name0) from v_R_System sys2 join v_RA_System_SMSAssignedSites ass2 on sys2.resourceID=ass2.resourceID and sys2.client_version0=sys1.client_version0 and SMS_Assigned_Sites0='PSC' and client0=1 and obsolete0=0) 'PSC', (select count(distinct name0) from v_R_System sys2 join v_RA_System_SMSAssignedSites ass2 on sys2.resourceID=ass2.resourceID and sys2.client_version0=sys1.client_version0 and SMS_Assigned_Sites0='EMA' and client0=1 and obsolete0=0) 'EMA',
(select count(distinct name0) from v_R_System sys2 join v_RA_System_SMSAssignedSites ass2 on sys2.resourceID=ass2.resourceID and sys2.client_version0=sys1.client_version0 and SMS_Assigned_Sites0 not in ('NAM','PSB','PSC','EMA') and client0=1 and obsolete0=0) 'other',
count(distinct name0) 'total'
from v_R_System sys1 join v_RA_System_SMSAssignedSites ass1 on sys1.resourceID=ass1.resourceID
where client0=1 and obsolete0=0
group by sys1.client_version0
order by 2 desc
p.s. Apologies again for the time between blog postings. The summer is finally over, but I've also been working on a couple of 'special projects' that didn't lend themselves to blogging (just yet).