Paul Thomsen at myITforum.com

Summary: computer management requires many skills, including what I would call computer administration skills. Amongst those is shell scripting. It's important to keep an eye open to the most recent solutions, but sometimes older solutions, such as shell scripting, are the right ones for the job at hand.

Yesterday I had a co-worker come to me with a problem related to backups where he wanted to keep 3 versions of the relevant files - no more and no less. He wanted to do it as a batch file, and I couldn't imagine how to do it (I could imagine how to do it with vbscript, but that requires a level of scripting support that wasn't appropriate for the problem). Fortunately, we have another coworker, Levi Stevens, who amongst his many great skills is a Windows shell scripting guru. Within an hour he had the solution that was needed. And even those of us that aren't guru shell scripters can readily maintain it (it's easier to edit than it is to create, as all scripters know, and it's especially easy to edit when the language is simialr to something you know, like regular Windows command statements).

These days we have PowerShell on the Windows platforms to provide a very sophisticated level of shell scripting. Everyone knows how to write basc .BAT files. Those from a *nix background will know Perl. And those with even older backgrounds will be familiar with OpenVMS DCL, or similar languages from the IBM mainframe world. There are lots of options, and each has their virtues, but sometimes the 'close to least common demoninator' is the right solution. Windows shell scripting may be that solution for your problem. There's a learning curve if you're creating the scripts, but it might be much smaller than for other scripting approaches (maybe this blog entry will tell you everything you need to know (no guarantees!))

The script Levi produced itself is wonderous enough, but the real lessons to be learned are:

• It's best to learn from the experts, which in this case especially includes Tim Hill’s book, "Windows NT Shell Scripting". ISBN 1578700477
• In many cases, the FOR command is one of your most powerful Windows shell scripting assets. At a command prompt, enter "For /?" to get the details.
• Similarly, the SET command has a surprising amount of power. Again, at the command prompt, enter "Set /?" to get the details.

 Taking it to the next level, some of Levi's insights are:

• When it comes to using the FOR command, sometimes you need to filter the output you want to parse. Use the escape character (AKA carrot - ^) on the pipe inside the FOR command. For example, here is a script that gives you the HAL type:

Set REGHAL=HKLM\Hardware\ResourceMap\Hardware Abstraction Layer
For /F "Tokens=5 Delims=\" %%i In ('reg.exe query "%REGHAL%" /s ^| Find /i "Abstraction"') Do Set HAL=%%i
Echo HAL: %HAL%

• To replace characters from a string, here's another example (with the first line being the string that needs correcting) :
  
    Set _Tmp=c:/bad/formed(dfd)/url
    Set _Tmp=%_Tmp:/=-%
    Set _Tmp=%_Tmp:\=-%
    Set _Tmp=%_Tmp:(={%
    Set _Tmp=%_Tmp:)=}%
  
• Often you will need to increment a number, for example in FOR loops (In this example the "Goto :EOF" will exit the current block when 10 iterations have occurred.):

Set /a LOOP+=1
If %LOOP% GEQ 10 Goto :EOF

p.s. I can't resist - here's one bit of Levi's script that particularly impressed me (the FOR loop):

@ECHO OFF

For /f "Tokens=2,3,4 Delims=/ " %%i In ("%Date%") Do @(
  Set Month=%%i& Set Day=%%j& Set Year=%%k
)

echo %month%
echo %day%
echo %year%

 

Summary: computer management done on any significant scale and long enough will create lots of objects that need to be cleaned up. Manually doing that cleanup would be prohibitively laborious and tedious, so scripts are a great solution. But how do we write and run such scripts safely?

There was a great discussion in the last couple of days on the SMS mailing list at myITforum.com on the subject of deleting collections.It's easy to write a vbscript or SQL script that deletes SMS collections themselves. And those scripts can be driven by input from the administrator as to which collections should be deleted. But how does the administrator know? If he's going to do all the research to figure out which collections are safe to delete, he might as well run the collection deletion wizard in the console, one collection at a time. So I believe the logic for safely deleting computer management objects should be part of the script.

When deleting collections in particular, you have to remember to not only delete the collection itself (class SMS_Collection) but also the link to the collection from where you found the collection (class SMS_CollectToSubCollect).

More importantly, you really should check whether the collection is used in any of the following ways (even the collection deletion wizard probably doesn't check all these details, but that doesn't mean your script can't or shouldn't):

• secondary parent collections to the collection (other SMS_CollectToSubCollect parent entries)
• child collections to the collection (children in SMS_CollectToSubCollect)
• advertisement target collections
• subcollection to an advertisement target collection
• collection limiting collection for another collection
• subselect rule in a collection's query
• collection limiting collection for a console query (if you use console queries)
• subselect rule in a console query
• SMS web report query select or subselect
• other kind of reporting query select subselect (if you use any reporting method other than SMS web reports)

And when you upgrade to SCCM 2007, add these to the list:

• DCM baselines
• DCM templates
• SUM (patch mgmt) deployments
• maintenance windows that should still be used
• any still useful collection details, such as collection variables

That's quite a set of issues to worry about, but I don't want to discourage you from scripting. These are very manageable challenges - the point is to take them seriuosly, not to avoid them.

As to whether to delete and change objects via SQL Server or WMI, the official answer (which I do 99.9% of the time) is to do it via WMI. There's three main reasons for that: all the ofifical documentation is based on the WMI approach, the WMI objects are least likely to change from version to version, and features such as security are only enforced via WMI. The ultimate answer is that WMI is the only supported means to make such changes, unless CSS specificially says otherwise (see the discussion below about supportability).

Michael Neihaus rightly observes that there are SQL triggers on the SMS objects that will cause many related details to be automatically deleted when you delete an SMS object. For example, his observation is that when deleting a collection the security instances and collection rules will automatically be deleted, so your script doesn't have to do those deletions. And in fact the console will allow you to delete objects without verifying any details it won't try to clean up all references in any case. So if the console can do things in a 'messy' way (my terminology, not Michael's) then it's not unreasonable for our scripts to do the same. I can agree with that, but when the wizard lists details that will be affected by the deletion of a collection I believe it's implying that they should be cleaned up first (by the administrator), and if I'm going to make the effort to clean up collections, then I don't want to cause messes elsewhere.

With some thought, observing what the product does, and some decent scripting you'll handle most of the issues in deleting the computer management object. But how do you know that you've covered all the issues? TESTING! Obviously that means that you should look at all the effects and side effects of your script, as evidenced by normal console checking and operations. But you should also check the database - if the data looks the same (in all relevant views) no matter whether you delete via the console or via the script, then you've probably got it.

So ultimately my advise is to "be careful out there". These things are often not quite as simple as they seem (a theme often repeated in my blog). They're not impossible - just tricky.

p.s. A related topic is how to ensure your script is supportable by Microsoft (or whoever your computer management software vendor is). I've been a public advocate (zealot?) for scripts for many years now and have often heard this question. But I don't work for CSS (formerly PSS) and thus I don't have support responsibilities. Even though I work for the company, I'm not in any position to make official statements about supportability. So call your TAM or CSS representative and get the official story. But it's reasonable that they can't support every script - there's just too many possibilities. Many different ways to write the same script, different languages, secondary points you may or may not include, etc. So maybe you'll get some support, but you can't reasonably expect complete support for all scripts. Does that mean that you should never touch any script, for fear of losing any support? I would say not - otherwise there's no point in Microsoft producing SDK's. Instead, if you ever hit an issue that might involve a script, try to reproduce it without the script. If you can, then support shouldn't be an issue. If it only reproduces with the script, then there's a good chance it is the script.

p.p.s. Another related topic is that you may want to know what Microsoft's suggestions are for how to address such challenges (as purging old objects), and why don't guys like me just give you the scripts we use? There's a few answers to that, but one of the core ones is that we don't have the time and manpower to test and support such solutions in all the variations that they would likely be used in. That would be a huge effort. Sure we can offer the solutions with a ton of disclaimers (as we often offer other things), but the reality is that people still have at least an implied level of quality for anything labeled as coming from "Microsoft". So people will assume that such solutions are better than they may actually be. So we can offer code samples, and concepts, to help you build what you need, but only under very specific circumstances can we provided true 'solutions'.

Summary: a few simple queries can give you a quick update on your latest advertisement. You can then react as needed or get on to more important tasks.

I love looking over the shoulder of fellow techies - it may well be the best way to learn truly useful new techniques. Often I'm asking some random question and they do what they have to in order to get the answer. But the techniques they use are probably based on years of experience, and their own insights, most of which are going to different from my own - we're different individuals, after all. Those differences may lead them to approach a problem differently from what I would do. That's my chance to learn!

I had the pleasure of having a temporary officemate lately and he did that in reverse - he was surprised by something I take for granted. So I'll share it with you. That's one of the strengths of blogs - we get to look over each others' shoulders no matter the deltas in time, expertise, primary language, background, etc.

In our case we were deploying some software and I wanted to see how it was going. I suppose the usual approach (as it was his) would be to go to the SMS web reports, which means calling up IE and navigating to the SMS reporting point), finding the relevant report, telling it which advertisement you want to know about, clicking Display, and then waiting for the response.

I always have SQL Server 2005 Enterprise Manager open (I'm always running queries), so I went to Recent Files, selected "adstatus.sql", and clicked Ctrl-E (execute). Instantaneously we had the results we needed and were checking the status. That's not a LOT simpler - we're talking 2 seconds vs. 15 seconds (I'm guessing), but when you do it a lot, it makes a difference. A bunch of efficiencies like this make for a happy techie.

So these are the queries I ran:

declare @adID varchar(8)

set @adID='XXX200YY' -- you'll need to know this ahread of time, but then can it be saved in this line (so you don't have to enter it every time)

select AdvertisementName, CollectionID, PresentTime from v_Advertisement where AdvertisementID=@adID

select count(*) 'Target size' from v_FullCollectionMembership where CollectionID=(select CollectionID from v_Advertisement where AdvertisementID=@adID) and IsClient=1 and (IsObsolete=0 or IsObsolete=null)

select count(*) 'Heard from' from v_ClientAdvertisementStatus where AdvertisementID=@adID and laststatename<>'No Status'

select count(*)*100 / (select count(*) from v_FullCollectionMembership where CollectionID=(select CollectionID from v_Advertisement where AdvertisementID=@adID) and IsClient=1 and (IsObsolete=0 or IsObsolete=null)) '% heard from' from v_ClientAdvertisementStatus where AdvertisementID=@adID and laststatename<>'No Status'

select LastStateName, count(*) 'Clients' from v_ClientAdvertisementStatus where AdvertisementID=@adID group by laststatename order by count(*) desc

select LastStateName 'Failure or retry', LastStatusMessageIDName 'Reason', LastExecutionResult, count(*) 'Clients' from v_ClientAdvertisementStatus where AdvertisementID=@adID and (LastStateName='Failed' or LastStateName='Retrying') group by LastStateName, LastStatusMessageIDName, LastExecutionResult order by count(*) desc

select LastStatusMessageIDName 'Waiting reason', count(*) 'Clients' from v_ClientAdvertisementStatus where AdvertisementID=@adID and LastStateName='Waiting' group by LastStatusMessageIDName order by count(*) desc

The one caveat is that if the target collection is a parent collection then you won't get the true target audience. And if that collection is empty then the "% heard from" will error out. But the rest of the report is still useful in that case, so that limiation can be ignored.

BTW, another reason I like to run my queries via Enterprise Manager is that I can instantly improve them as I get new ideas. If you get a better idea for your reports while running them via SMS web reporting then you have to work on the query, edit the report, refresh it, etc. and that's going to take awhile. Sure, it's prettier, but most of my reports are for my own benefit, and I don't require that much prettiness. And maybe that's what he liked about my report - I had tweaked it so that it showed all the crucial details we needed (I don't even know what the out-of-the-box SWD web reports give).

p.s. Yes, I know I'm due to post more about client health reporting, but variety is good. 

p.p.s. I've since gone on to write a quick vbscript to serve as a wrapper for the queries I need for this particular deployment. That way I get the goodness of SQL with even more formatting and consolidation. That may still take a second to run but it only takes a second or less to invoke the report - thus leaving me lots of time for more important things, like blogging ;-)

Summary: starting at the basics, we need to know how much activity we're seeing from our computer management clients, and thus how much we're likely to see from them in the near future. These queries will get you started.

In the past we've talked about the different kinds of client health business problems, and thus the different kinds of reports that are needed. In this case we'll focus on how much activity we're seeing from our current clients. So that's not a question of how many clients we should have, or how many are truly broken. We just want to know how many have done the various core SMS activities (such as inventory collection, heartbeat discovery, or status) in the last week. With a quick tweak you can tell how many have done the activties in the last two weeks. Chances are that the next week or two will be very similar, and so with that information you can now predict how comprehensive your patch deployments are going to be, or how complete your asset management reports are going to be, in the next week or two. That is crucial information for all computer management activities, so these queries are a key point to start building your client activity reports.

select count(DISTINCT Name0) from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID where client0=1

set @olddate=DATEADD(day,-8, getdate())

set @NullVal = CONVERT(datetime,'1/1/1980')

select count(DISTINCT Name0) 'disc - 1 week' from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID 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 IsNULL(AgentTime,@NullVal)>@olddate

select count(DISTINCT Name0) 'hwinv - 1 week' from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID full join v_GS_WORKSTATION_STATUS WS ON WS.resourceID=sys.resourceID where client0=1 and IsNULL(LastHWScan,@NullVal)>@olddate

On a medium to large scale these queries can be resource intensive, so the 'set transaction' statement is a good way to keep things running efficiently while the queries run. It tells SQL to not lock the relevant tables, since we just want a quick idea of the current state of the data - we don't intend to change any of it. Also, change the DATEADD to subtract 15 days, instead of 8, and now you have two week activity (I add a day to 1 week and 2 week in order to account for latency in the data flow (important on big hierarchies, less so on smaller ones)). And an important trick you may notice with the discovery query is that it excludes AD discovery data - that data is going to be as active as the frequency of your AD discovery jobs, which is not representative of the activity of the clients themselves.

From there it's easy to build a query for software inventory activity. Status is a little trickier to query on, since that view doesn't use resourceID's, so that query is:

select count(DISTINCT Name0) 'status - 1 week' from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID full join v_StatusMessage stat ON sys.name0=stat.machinename where client0=1 and IsNULL(Time,@NullVal)>@olddate

Software distribution activity is just a varient on that, where you look for status messages with messageID=10005 

BTW, you can use the SMS Client Health Tool, as downloaded from www.microsoft.com/SMS, to produce similar reports. That may be the better way to go if you don't want to build your own client activity reporting system. But these queries actually predate that tool - some shameless SMS product team developer (you know who you are) stole them as inspiration for that tool!  ;-)  Actually, I partially jest, of course. The product team helped to refine these queries, so they were a joint effort. And we love to inspire the product team!

p.s. Sorry for yet another long delay between blog postings. Summer months, coupled with intense work, make for bad blogging. I've got plenty of ideas, soon to come (as I too often say)... Next time we'll save the data to a custom database so that it can be graphed later (the graphing is the really tricky part).