By Pierre Dumoulin
I recently needed to identify and remediate all business-critical applications in my area for our coming Windows XP migration. When I ran queries against our Microsoft Systems Management Server (SMS) 2.0 databases, I came up against one of its greatest flaws: the software inventory. To solve my dilemma, I created a script, which you can download here. If you’re having a similar problem, read through this description of my situation and see whether you can tweak the script for your needs.
More about my dilemma
SMS is so thorough that it picks up every single executable on a client's hard drive and attempts to identify it. Unfortunately, this thoroughness makes the information almost worthless. SMS will read the binary's file header looking for an appropriate description of the executable, assuming the developers of an application have bothered specifying one properly at all.
Many applications also consist of more than one executable, so gathering all of them manually under the appropriate banner is painstaking work. The bottom line: My initial SMS export came up to around 3,600 entries that were of little use to me unless I was ready to spend hours manually data mining.
It then came to me that a major improvement of this system, for starters, would be to uniquely identify each application. The best way to do this would be to import the Add/Remove Programs list from my workstations. My first scan ran on roughly 250 workstations and yielded an initial result of some 900 applications; with 600 of them, there were two or fewer copies. Upon closer inspection, many of these were either downloads, plug-ins, or user-installed, unsupported apps.
Scripting: An alternative solution
One major advantage I had right off the bat was that our workstation-naming convention is based on a sequential numbering scheme (e.g., WKS1000 to WKS1999). Since I could easily build a loop that would cycle through each node name, I basically just needed to devise a script to scan one machine remotely.
If you do not use a comparable naming convention, I suggest exporting every valid node name to a text file (or at least enough for a statistically significant sample, if that's all your looking for), each on a single line, and having the script read the file and retrieve the appropriate value for each loop cycle.
The Add/Remove Programs information is located in the registry here: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall
Subkeys contain a string value labeled DisplayName (but not all). This is what is listed in the Add/Remove Programs list and constitutes the value that must be retrieved. The bit that does the actual dirty work is made up of two major components: connecting to the registry remotely and enumerating all Uninstall subkeys so that they each can be cycled through looking for a DisplayName string.
Another important part of this script is the error handling. As your script cycles through your list of workstations, some will undoubtedly be unavailable (laptops traveling, computer switched off, permissions issues, unassigned node names, etc.). The first step toward dealing with this is the On Error Resume Next command. This allows identifying and manipulating returned error codes in the form of Err.Number, Err.Description, and Err.Source values. The logging of these errors can be useful in refining the script during your initial limited test runs.
Using standard file system objects, I created a log file in comma-separated value (CSV) format that identifies the machine name and the DisplayName of the application. You might want to use a semicolon as a separator, since some application names include commas. You also might want to pick up the username for the workstation’s owner—or at least the last logon username, if your workstation deployment method does not label the registry with the owner's username. This could allow you, for example, to easily build a mailing list with the username and a list of applications located on his or her computer.
After perhaps filtering out standard apps, you could reach each user in an OS migration scenario by building a simple Outlook form asking which applications really need to be upgraded, saving your company thousands of dollars in software upgrades.
This script could also be used on a daily basis to scan for blacklisted applications, such as peer-to-peer file-sharing programs, or to check whether any of your workstations had their antivirus or desktop firewall software uninstalled by the user (although you'd probably be better off scanning for the service's status, since it may still be installed but disabled).
It's not a perfect world
Although this script has been a big help, it's obviously not perfect. For example, if the version of the software is not listed in the DisplayName, you may have to cross-reference with other info, such as SMS queries, to find it. Also, certain entries are still linked to existing applications and will need to be filtered out manually.
Even though the majority of my scans revealed mostly isolated applications, some of these may still be business critical or may simply have a slightly different spelling of an existing application. When you sift through all this information, you may want to go through them sorted by both application name and by number of instances of each. These numbers can be generated easily in Excel using pivot table reports.
The two sorts will reveal your most popular applications (and the most obscure ones), and the proximity of similar application names in the alphabetical sort will reveal slightly different spellings of the same applications.
I also suggest modifying the script to ping a workstation before scanning and ignore the workstations that don’t respond. Although you may miss a few laptops with misconfigured desktop firewalls, you won’t have to wait a minute per nonresponding machine for the request to time out.
SMS picks up executables of applications that may not actually be installed, but this method will pick up all registry entries, regardless of whether the application directory is actually there or not.
You could also easily add password masking to this script, allowing you to use it without divulging the administrator password for these workstations to any spectators.
It never ceases to amaze me how 60-odd lines of scripting and a few hours’ work can deliver on some levels what enterprise-level management software fails to deliver. In some ways, the scripting community could be considered a bit of an offshoot of the open source community because of all the information and code sharing. There are a lot of extremely useful scripts out there that are freely distributed and easy to assemble, like the proverbial Legos that they are.