In-Depth

Getting the Most out of SMS

Tips and tricks from an SMS veteran to help you recoup your investment in Microsoft's System Management Server.

Complex but powerful, Microsoft's Systems Management Server has matured well since its release in the late '90s. Originally a buggy and often troublesome software inventory and deployment tool, it has developed into a comprehensive management package for Windows administrators.

Yet that comprehensive nature is often the biggest stumbling block for the budding SMS administrator. A tool that can inventory every software and hardware component on a network, deploy software and patches to any host, watch any process for use and misuse, and even deploy whole operating systems without touching the client, is clearly going to have a steep learning curve.

The good news is that plenty of people have fallen in love with the beast, and are willing to share their best tips and tricks to tame it. Here we collect a few of the tried and true.

Collect Backward and Get Smart
SMS's software installation component works for enterprises by breaking down each change event in your network into three separate components: The Collection, the Package and the Advertisement.

Collections are groups of machines gathered through a query while the Package is the software that does the install on each machine. The Advertisement designates the date and time when the Package should be installed on the machines defined in the Collection.

Using either the natural language interface or by writing the SQL query directly, your Collections are limited only by your imagination. Want to know about the laptops on the 10.1.3.0 subnet that currently run Microsoft Project 2003? Build a query like the one in Figure 1.

Figure 1. This Collection queries for laptops on the 10.1.3.0 net that have Microsoft Project installed.
Figure 1. This Collection queries for laptops on the 10.1.3.0 net that have Microsoft Project installed. (Click image to view larger version.)

This flexibility in creating Collections sounds great, but it takes some know-how to create them correctly. For software installations, the idea is to get the software onto machines where it isn't installed. Using the query in Figure 1 won't do that—what you need is an inverse query, meaning a query that looks for where the software isn't (see Figure 2 for an example).

Figure 2. This Collection queries for systems
Figure 2. This Collection queries for systems “not in” the previous list of systems. (Click image to view larger version.)

As we've already shown, it's easy to build a Collection that simply identifies the laptops on the 10.1.3.0 subnet and just push to that Collection. However, it's more useful to deploy software to an inverse Collection. As machines in your inverse Collection successfully complete the software installation, they will then have the WINPROJ.EXE file in their software inventory. Consequently, SMS will remove them from the Collection at the next inventory cycle.

Since our inverse method only pushes to laptops that don't have Project, we need only watch and wait for the Collection membership to shrink to zero. When no machines exist in the Collection, you know all machines are updated and the deployment is 100-percent successful.

The downside of inverse Collections is the definitions get a little more complicated, requiring the administrator to build "subselect statements" and relate them to the "not in" operator. As you can see in Figure 2, these subselect statements are created using SQL rather than the natural language.

There is a trick, however: First create your subselect statement using the natural language interface. Then, click the Show Query Language button to view the SQL string. Copy and paste this SQL string into the subselect box of your main Criterion Properties window. Change the first part of the string from Select * to Select Name and set the operator to "not in" to create the inverse collection.

One final tip when using inverse Collections is to set the advertisement schedule to recur the installation every few days or every day. Doing this ensures that clients which attempted but failed to install the package will retry at a later time. Sometimes a software installation just doesn't "take" the first time. Recurring the installation with an inverse Collection gives it another chance for success.

The Black Art of Writing Packages
The biggest difficulty with SMS lies in the need for the administrator to rewrite software installations for a deployment. SMS provides a convenient interface for packaging Microsoft patches, but there is no interface or centralized clearinghouse for writing software installation packages. Indeed, the writing of such packages is something of a black art.

The general process is thus: Locate the installation media for a software installation. Then, through a combination of trial-and-error, luck, and skill, repackage the installation to prevent the dialog boxes from appearing. This repackaging process is intended to make the installation run silently so that a single command line can fully install the software.

Finding that command line is the hard part. Sometimes it can be found by invoking the software's Setup.exe or Install.exe file with the /? switch. Doing this will often pop up a window detailing the available installation options and reveal the method for running the installation silently. Many times, the magic silent mode switches can be found on the vendor's Web site.

Often, however, software won't give up its silent secrets. In that case, Microsoft supplies a free, but limited, version of InstallShield's AdminStudio on the SMS 2003 media that can take a snapshot of a standard workstation image. Once that's done, the administrator installs the new software and reruns the AdminStudio image capture utility. The tool will determine the files and Registry keys that changed as part of the installation and package those changes into an .MSI file for installation.

The problem with the free version of AdminStudio is that, unlike with the full version, once the MSI is created it's impossible to edit or customize it without creating a separate .MST transform file.

A useful tool for getting around that problem is the old SMS Installer on the SMS 2.0 media. Although it's getting on in years, this tool is still available for free on the Microsoft Web site, and its easy interface feels more like scripting. You can still capture a system's state and create a package from the differences, but here the completed package can be edited by dragging installation steps from the list in the left pane to the script in the right pane. The script runs from top to bottom, so it's easy to sequentially see what the package will do to the system.

On the other side, the full versions of AdminStudio and others like Wise Package Studio greatly assist with this process of writing packages. Though expensive, these tools provide robust capture-and-compare capabilities, feature-rich repackaging tools, and database-driven change management controls, which are a must for large scale software packaging.

As a special case, software—especially older software—packaged with InstallShield is often difficult to reverse engineer and repackage because running SETUP.EXE /? gives you little information. Though not immediately obvious, InstallShield-based software installations require the use of the SETUP.EXE –R command to launch the installation in "record mode." While the installation is running and asking questions, this record mode records your answers to the install questions in a file called SETUP.ISS in the C:\WINDOWS or C:\WINNT folder.

Once that SETUP.ISS file is created, you can later rerun the installation silently by running:

SETUP.EXE /f1C:\WINNT\SETUP.ISS

Note that there is no space between /f1 and C:\WINNT\SETUP.ISS. This also assumes that you also copy the SETUP.ISS file to the WINNT folder on the destination machine.

Changes to SMS in 4.0

At the Microsoft Management Summit last summer, Microsoft released a single slide overviewing the updates to SMS in the next release. This release, which has no publicized date of release and is currently named simply SMS 4.0, includes features surrounding four major points. Many of these additional features in the next version of SMS come directly from the requests of the SMS community.

Unified OS Deployment
Security
Simplicity
Desired Configuration
Longhorn and Office 12 Upgrade Assessment
The ability to identify and resolve hardware and software incompatibilities with Windows and Office before upgrading
Vulnerability Reporting
Enterprise-wide vulnerability reporting
Full Functionality Out-of-the-Box
Simple MSI setup that ends with a fully functional SMS site.
Proactive Best Practice Evaluation and Notification
Notification of any deviation from a desired configuration for a system or an application.
Machine Replacement
The ability to migrate desktops and servers from old hardware to new hardware while preserving state.
Quarantine integration for patching and vulnerabilities
Prevent workstations from accessing corporate resources when they are not properly patched or when they have vulnerable configurations.
Simplification of SMS Distribution Hierarchy
Leveraging workstations as distribution points can reduce infrastructure and cost.
Regulatory Compliance Verification
Notification of any deviation from regulatory compliances such as Sarbanes-Oxley or HIPPA.
Disconnected / Remote Deployment
Enable administrators and users to deploy Windows via CD set or DVD with or without network connectivity.
Internet-facing Scenarios
Software distribution, asset management, patch management, and desired configuration across the Internet without requiring a VPN
Task-based UI
Simple and intuitive task-based administrator interface for patching, quarantine, OS deployment, and desired configuration monitoring.
Ability to Create and Edit Configuration Definitions Easily
Use the knowledge provided by your vendor, or customize and create your own.
  Patching Improvements
Selectively downloading only the patches that apply to a given system reduces network traffic and closes with WSUS gap.
Install in a Time Window
Allow administrators to install software in designated time windows.
 

Little Orphan Annie
For many enterprises, verifying the health of the SMS client presents a major problem. Clients sometimes have issues and become orphaned, meaning they no longer talk with the site server. In large environments with thousands of clients, it's difficult to keep track of which machines have become orphaned. Such cases require an automated mechanism for finding orphaned clients and verifying their health.

Microsoft's SMS Client Health Monitoring Tool provides a suite of applications that integrates with, but operates outside, the SMS database. Working off its own database, the Client Health Monitoring Tool performs "ping" and "pulse" tests on clients to find ones that haven't reported inventory data on-cycle or have been off-net for an extended period of time.

The ping test checks to see if a client is on the network. Once the client is found, it also checks the status of its SMS and BITS services. The pulse test checks for SMS policy requests from the client to its management point. The results from these tests are dropped into a separate SQL database for later reporting.

To set up the tool, you'll need to run its installation executable, configure a Client Health Agent account and set up the Client Health SQL database. Once this is done, you'll need to configure its boundaries and schedule, its reporting parameters and how many days between heartbeats constitute an unhealthy client.

Using the Health Monitoring Tool means constantly monitoring its reports to look for clients not functioning properly. Actually doing something about those failed clients is up to you, because no shrink-wrapped "fix" tool has yet been developed.

However, Microsoft's internal IT department released the logic used in their internal tool last April at the Microsoft Management Summit in Las Vegas. This logic can be used to create a log-in script written in VBScript that validates the client's health and fixes problems (see chart below). Since every machine has to log in to the domain to access resources, the login script is an excellent mechanism for conducting tests and fixing problems.

Microsoft IT Global Client Software SMS Logon /  Computer Startup Script
(Click image to view larger version.)

Heads-Up Display
One of the main selling points of SMS is its reporting capabilities, an area where the SMS Dashboard comes into play. Little more than a side-by-side, heads-up display of multiple SMS reports, the real beauty of the SMS Dashboard lies in the ability to drill down to more specific information through a single click.

If you're using the SUS Feature Pack, a simple but powerful dashboard can line up every Microsoft patch and your site's compliance information into a single page. Clicking the icon to the left of any patch shows information about the specific machines missing that patch.

To create a patch compliance dashboard, you need to create a main report and link it to an existing report. Right-click the Reports node in the SMS Administrator Console and choose All Tasks | Create Report. The main report, adapted from the SUS Feature Pack documentation and titled "Patch Compliance Matrix" will use this SQL query:

SELECT
QNumber,
Bulletin,
(100 * Installed / (Applicable + Installed + AdminApplicable)) as '% Compliant',
Installed,
Applicable,
Installed + Applicable + AdminApplicable as Total
FROM
(SELECT
v_GS_PATCHSTATE.QNumbers0 as QNumber,
v_GS_PATCHSTATE.ID0 as Bulletin,
v_GS_PATCHSTATE.Title0 as Issue,
sum (case when v_GS_PatchState.Status0 = 'Installed' then 1 else 0 end) as Installed,
sum (case when v_GS_PatchState.Status0 = 'Applicable' then 1 else 0 end) as Applicable,
FROM v_GS_PATCHSTATE
INNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_PATCHSTATE.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
INNER JOIN v_R_System ON v_GS_WORKSTATION_STATUS.ResourceID = v_R_
System.ResourceID
WHERE (v_GS_WORKSTATION_STATUS.LastHWScan >= GETDATE()-3)
AND (V_GS_PATCHSTATE.ID0 <> 'None')
GROUP BY QNumbers0, ID0, Title0, LocaleID0 ) as ps
ORDER BY Bulletin DESC

The second report is one of the SMS default reports. To create the "drill down" effect in the dashboard, you'll want to link these reports together. This will allow you to click on any patch number in the main report and learn the specific machines missing the patch. To do this, select the properties for the Patch Compliance Matrix report and choose the Links tab. Under Link Type, link this report to the Computers where a Specific Software Update is Available report. It's also handy to set the refresh interval to five minutes, so the page auto-refreshes while being viewed.

Once your reports are created, navigate to the Dashboards node and create a new Dashboard. Give the Dashboard a name, a height of 500 pixels, one column and one row. Link the Patch Compliance Matrix to the single cell and you're done.

Since this is your first dashboard, you can quickly view your patch compliance information from a single web page by linking to http:////Dashboard.asp?DashboardId=1. Other dashboard designs exist on the Internet that can extend your viewing and drill-down capabilities.

I Hate GUIs!

SMS does provide a GUI that enables you to control its various functions, but some admins hate all things GUI, or need more granular control over the interface. SMS 2003 provides what you need. With scripting interfaces both on the server and on the SMS client, you'll have the capability to override site policies, speed up software distribution, rerun advertisements and enumerate plenty of data on client health.

Recently posted to the SMS page at www.microsoft.com/sms, the SMS Scripting Guide provides 40 useful scripts for interfacing with the site server and the client.

Many scripts begin with a call to the SWbemLocator object, followed by a connection to the site server. You'll want to replace and below with values that make sense for your organization:

Set objSWbemlocator = CreateObject("Wbem
Scripting.SWbemLocator")
Set objSWbemServices = _
objSWbemlocator.ConnectServer(,
"root\sms\site_")


As a slimmed down example from the SMS Scripting Guide with error checking removed, let's show the code you can run against your site server to parse the contents of a collection to a CSV file. In the example below, you'll also want to change the value for strQueryID to the collection ID you want to query.

On Error Resume Next
strQueryID = "SMS040"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objTextFile = fso.OpenTextFile("RESULTS.csv", 2, True)
Set objSWbemlocator = CreateObject("Wbem
Scripting.SWbemLocator")
Set objSWbemServices = _
objSWbemlocator.ConnectServer(,
"root\sms\site_")

Set objQuery=objSWbemServices.Get("SMS_Query.QueryID='
"+ strQueryID +"'" )

Set colQueryResults=objSWbemServices.ExecQuery

(objQuery.Expression)
For Each objResult In colQueryResults
objTextFile.WriteLine objResult.Name
Next


The connection to the SWbemLocator object is used to connect to the site server. Using the Get method, the query syntax is mapped to objQuery, which is then run by the ExecQuery method.

If you're moderately familiar with VBScript scripting and you can decode the recipes available in the Scripting Guide and from other locations like myitforum.com and Microsoft.com, then you're well on your way to scripting SMS.

— G.S.

Eliminate Wasted Licenses
In addition to inventorying the software and hardware on your systems, SMS can provide metrics on minute-by-minute software use. Think you've got too many Microsoft Project licenses for your own good? Write an SMS Metering Rule on all versions of Winproj.exe. The SMS client on each workstation and server on your network will start reporting how often that process is running, how many minutes it is used, and the times and dates of use.

Canned software metering reports exist to show computers that have run a specific program, total usage trend analysis, and installed base for all software programs, among others. You can even drill down to the individual machines on your network. After pulling the data for a few weeks or months, write a report that shows which machines haven't even started Project over that period of time. You'll be surprised with the results, and you'll save money during your annual Microsoft True-Up period.

SMS is quite a bit more than just an expensive tool to remotely access workstations around the network. In terms of stability, functionally, and efficiency, it's grown considerably in the past six years. Best of all, it's the tool in your systems administrator quiver that has the greatest promise for keeping you from actually walking from computer to computer all over your network. And never having to leave your ivory tower is really what being a systems admin is all about, right?

comments powered by Disqus

Reader Comments:

Wed, Nov 19, 2008 Johan Netherlands

Nice, but i can't get anything inside my dashboard..ther eis no info,.what am i doing wrong

Wed, Apr 5, 2006 Mike G Williston, ND

With a few mods, I got it to work. Here's the SQL statement I used (note I added Product and Locale, and just my All Systems collection so I could see which systems require updates):

SELECT
QNumber,
Bulletin,
(100 * Installed / (Applicable + Installed)) as '% Compliant',
Installed,
Applicable,
Installed + Applicable as Total,
'SMS00001' as Collection,
'' as Product,
'' as Locale
FROM
(SELECT
v_GS_PATCHSTATE.QNumbers0 as QNumber,
v_GS_PATCHSTATE.ID0 as Bulletin,
v_GS_PATCHSTATE.Title0 as Issue,
sum (case when v_GS_PatchState.Status0 = 'Installed' then 1 else 0 end) as Installed,
sum (case when v_GS_PatchState.Status0 = 'Applicable' then 1 else 0 end) as Applicable

FROM (v_GS_PATCHSTATE INNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_PATCHSTATE.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID)
INNER JOIN v_R_System ON v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceID
WHERE (v_GS_WORKSTATION_STATUS.LastHWScan >= GETDATE()-14)
AND (V_GS_PATCHSTATE.ID0 'None')
GROUP BY QNumbers0, ID0, Title0, LocaleID0 ) as ps
ORDER BY Bulletin DESC

Thu, Mar 16, 2006 mbain Anonymous

Missing the definition for column "AdminApplicable" in the query.

Wed, Mar 1, 2006 Anonymous Anonymous

There should be a little more detail

Mon, Feb 6, 2006 Anonymous Anonymous

Good article, but like many people pointed out the code isn’t quite right for the patch compliance dashboard. To make the dashboard work I added a case statement for AdminApplicable, changed the LastHWScan >= GETDATE( )-3 date to match our hardware scan days (7). In addition the view v_GS_PatchState is incorrect, in SMS 2003 the correct view is v_GS__PatchStateEX.

Thu, Jan 19, 2006 Chris B Richmond

Any response from the author of this article. The Dashboard sounds great, but like the others here have found out, it doesn't work.

Wed, Jan 11, 2006 Daniel D Minneapolis, MN

Good Article but AdminApplicable is not defined - it would appear that another line similiar to the 'case' statements would need to be there thus the bad puctuation (the extra ',') with some reference to an LocaleID as seen in the group by clause. It would be helpful to see the entire SQL Statement...

Thu, Jan 5, 2006 Brad Giles Raleigh, NC

Can you add a link to the online article with the fix to the dashboard issues (listed above)?

I was excited to see someone actually come up with a dashboard that might be of use. Since SMS 2003 came out, I have not seen one dashboard that is of any use to any of my clients.

Once the SQL query typos are corrected, the report throws an error # -2147217900 (Invalid column name 'AdminApplicable').

Being able to demonstrate creation of useful Dashboards would of significant value to my clients.

Thanks in advance.

Mon, Dec 26, 2005 Owen Gilmore San Mateo, CA

I am an application integrator and packager, but not having touched SMS for some time (version 2.0) , I would have loved to see more on application packaging and how SMS integrates MSI files. I notice that Wise can even generate .SMS files with command lines prepared. Of course the advantages of using Windows Installer instead of a setup.exe installation are myriad, and (thankfully) setup.exe type installations are fewer as time goes on and application vendors "get religion" and write proper MSIs. I think the subject of Windows Installer integration is worth another article!

Also, the FindIT code posted in the print version for the VBscript did not work.

Sincerely,

Owen Gilmore
MCP

Wed, Dec 21, 2005 Sandy Anonymous

Great article - I've been an SMS admin for 3 years and I learned quite a bit from your piece. I would love to get my hands on a copy of the startup script that Microsoft IT uses on their systems.

Fri, Dec 9, 2005 Quentin Gurney Kokomo IN.

Nice article, however the sample code doesn't work. An extra comma needs to be removed after "applicable" in line 14, and the table name
v_R_system .resourceid. Also there is no definition of "AdminApplicable, so it isn't clear where that comes from. If you remove it, the query runs but has rather strange output.

Wed, Dec 7, 2005 Monia Anonymous

Loved your article. Very informative to the point and delightful to read... Monia

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.