SMS 2003 - Verify WMI Stability Backport on Windows XP/Server 2003 with this report SQL (deploy to them with this collection query WQL)

BACKGROUND (click HERE to skip to the summary)

I've been looking into our client health a lot deeper recently and something that I keep coming back to is how fragile WMI seems.  After some great conversations at MMS with some really bright folks, I started really trying to fix those unhealthy clients.  I got to reading a blog that pointed to a blog what pointed to some Microsoft articles which gave me some good information.  I'd like to share some of that information because it showed us that we're not doing all we can when it comes to WMI and perhaps you're not either.

When Microsoft developed Vista and Server 2008, they updated WMI to be more stable and less prone to corruption.  Well, in October of 2007, Microsoft backported the WMI stability updates to Windows XP and Server 2003.

(See the original KB's here-->http://support.microsoft.com/kb/933061 and here-->http://support.microsoft.com/kb/933062...one is for XP, one is for Server 2003)

<tangent>
I know...I've seen all the arguments from some of the internal WMI developers stating how it's not WMI that's the problem and it's all the fault of us end users who push crappy applications onto Windows which mess with WMI.  Well, I don't know...it seems like if you give a kid a hammer and a glass house to play in, he's going to break stuff.  Do you blame the kid, the person who gave him the hammer and set him in the glass house, or the person that made the house out of glass? 

That is what WMI is to me.  We're the kid, the application development is the hammer and WMI is the glass house.

Sure, an argument could be made that the kid or his guardian shares some of the blame, but it seems to me if you make the hammer a rubber mallot and make the house out of concrete then the kid is going to have fun and the house will still be standing when he's done.

Tell me I'm wrong...
</tangent>

Anyway, I just assumed that those KB articles with the WMI stability stuff were part of some ITMU updates or a rollup or something, and as we gradually upgrade to XP SP3 it'll be a part of that...the problem is, SP3 is 364MB (push that to 200K clients without Nomad and you're sending 70TB of data), and it had a couple of problems out of the gate, so our managers are a little hesitant to pull the trigger on SP3 enterprise wide.  We're fine pushing the updates separately as they come in because we still get the security benefits, but no 364MB hit.  In the meantime, I'd personally like to see WMI as solid as it can be since that's the glass house that this kid is playing in.  So I want to make sure those backports are on my systems.

IDENTIFY UPDATED/NON-UPDATED MACHINES

STEP 1) Inventory REPDRVFS.DLL in %WINDIR%\System32\WBEM

According to those KB articles, the file that gets updated is REPDRVFS.DLL.  So to know if we've got the updated WMI backport, we need to know what versions of that file are on our machines. The first thing you need to do is make sure your software inventory is inventorying REPDRVFS.DLL. We don't want to inventory the copies that are in the service pack uninstall folder or any other place on the hard drive, so limit it to %WINDIR%\System32\wbem.  This works on my Server 2003 x64 box as well so I think it should be fine for most machines.  I don't have an XP x64 box to test so I don't know if it's someplace different there.

STEP 2) Create a report that will count the REPDRVFS.DLL files and versions

First, let's just simply find all of the versions of that file and count the distinct machines that have it

SNIPPET #1 - Query to count machines with each version of REPDRVFS.DLL

SELECT
   COUNT(DISTINCT sf.resourceID) AS [Count],
   sf.fileVersion AS [Version]
FROM
   dbo.v_GS_SoftwareFile AS sf
WHERE
   sf.fileName = 'Repdrvfs.dll'
GROUP BY
   sf.fileVersion
ORDER BY
   sf.fileVersion

OUTPUT #1

OK, so there's all the versions we have at one of our sites.  There's a lot so, let's modify it based on the information in those KBs to show us which versions are the latest. 

Server 2003 Versions
5.2.3790.2936 - For SP1
5.2.3790.4080 - For SP2

Windows XP Versions
5.1.2600.3138

Using that information, let's add a CASE statement to the SELECT in order to put an X next to those versions that indicate the update has been applied.

(oh yeah, any version 6 or higher indicates Vista/Server 2008 and thus already has the WMI stability update built-in)

SNIPPET #2 - Versions of REPDRVFS.DLL with update indicator 
SELECT
   COUNT(DISTINCT sf.resourceID) as [Count],
   CASE
      WHEN  sf.FileVersion LIKE '5.2.3790.2936%'
        OR  sf.FileVersion LIKE '5.2.3790.40[8-9][0-9]%'         --4080-4099
        OR  sf.FileVersion LIKE '5.2.3790.4[1-9][0-9][0-9]%'     --4100-4999
        OR  sf.FileVersion LIKE '5.2.3790.[5-9][0-9][0-9][0-9]%' --5000-9999
        OR  sf.FileVersion LIKE '5.1.2600.313[8-9]%'             --3138-3139
        OR  sf.FileVersion LIKE '5.1.2600.31[4-9][0-9]%'         --3140-3199
        OR  sf.FileVersion LIKE '5.1.2600.3[2-9][0-9][0-9]%'     --3200-3999
        OR  sf.FileVersion LIKE '5.1.2600.[4-9][0-9][0-9][0-9]%' --4000-9999
        OR  sf.FileVersion LIKE '[6-9]%' THEN 'X'
      ELSE ''
   END AS [Has Update],
   sf.fileVersion AS [Version]
FROM
   dbo.v_GS_SoftwareFile AS sf
WHERE
   sf.fileName = 'Repdrvfs.dll'
   AND sf.resourceID in (select resourceID from dbo.v_SDE_GeneralInfo)
GROUP BY
   sf.fileVersion
ORDER BY
   [Has Update],
   sf.fileVersion

OUTPUT #2

OK, now we can see the number of machines that have the update and the machines that don't...crap...that's like 80% don't have it and 20% do.

WHOA, WHOA, WHOA, WHAT'S ALL THAT '5.2.3790.[5-9][0-9][0-9][0-9]%'NONSENSE?

Sorry, let me go back and explain that CASE statement before I move on...

What we're trying to do is find REPDRVFS.DLL with versions like

5.1.2600.3138%
5.2.3790.2936%
5.2.3790.4080%

* but we'll also consider a version as having the update if the last 4 digits are higher than the last 4 digits in these:

5.1.2600.3138%
5.2.3790.4080%

Consider the case of XP SP3.  The version of that file on SP3 is 5.1.2600.5512%.  Well, I don't want to specify every possible version that could exist between 3138 and 5512 one-at-a-time, so we'll use a wildcard expression involving brackets and numbers to get us the results we want.

By the way, you might be tempted to use sf.FileVersion > '5.1.2600.3138' ... don't be.  This is a varchar field not a numeric field.  SQL will perform a dictionary sort not a numeric sort so you can run into all kinds of problems if you're trying to use greater than with the file version like that. 

So the options are, extract the string and convert it to a number and deal with that (I mention that tactic HERE), or use some fancy wildcards.  I've already demonstrated the string extraction method in a previous article so I'd like to mention the wildcard method.  Plus in this case, having to do a bunch of function calls and string manipulation for every record would probably be a lot more work for the SQL engine than adding some fancy wildcards because SQL is built for pattern matching and is very efficient at it compared to the overhead involved in function calls and string manipulation.

Let's quickly talk about wildcards...a wildcard character you're probably all familiar with is the % sign.  It basically means match 0 or more of any characters.  Then there's the underscore _ which means match any single character.  But there's also these brackets [ ] which let you specify a range (where [0-9] means a single character matching the number 0 through 9 or [a-g] means a single character matching any letter from a through g) or a set (where [135] means a single character matching a 1, 3 or 5 and [adf] means any single character matching a, d or f).  You can also put those brackets next to each other to specify multiple ranges (like [0-9][0-9] representing every number between 00 and 99].  I think that bracket wildcard can come in handy.  

Well then, let's take our file version example above and try to use these wildcards.  We know that version 5.1.2600.3138% indicates the update is installed, but so does 5.1.2600.3139%, 5.1.2600.3140%, and every other number up to 5.1.2600.9999%.  So, we need a way to represent 3138 and every number up to 9999.

[0-9][0-9][0-9][0-9] would represent every number from 0000 to 9999 which is too many.
[3-9][1-9][3-9][8-9] would represent 3138 to 9999, but miss 0-7 in the 1's column, 0-2 in the 10's column and 0 in the 100's column, which isn't enough.

So let's start with 3138.  If we start all the way on the right and use a wildcard to roll the 1's over like an odometer which starts at 3138, we'll get

sf.FileVersion LIKE '5.1.2600.313[8-9]%'  

which matches 3138 and 3139.  Then we move to the left and put a wildcard there too to roll the 10's and 1's over starting at the next number (3140)

sf.FileVersion LIKE '5.1.2600.31[4-9][0-9]%'

which matches 3140 to 3199.  Then we move to the left again and put a wildcard to roll the 100's 10's and 1's over starting at the next number (3200)

sf.FileVersion LIKE '5.1.2600.3[2-9][0-9][0-9]%'

which matches 3200 to 3999.  Now we do it one more time to the left and roll the 1000's, 100's, 10's and 1's over starting at the next number (4000)

sf.FileVersion LIKE '5.1.2600.[4-9][0-9][0-9][0-9]%'

which matches 4000-9999.  Those 4 lines together now give us every number between 3138 and 9999.

OK, BACK TO THE QUERY

So we've go our counts, our versions and our flag to determine which versions indicate having the update.  Now let's fancy it up to get percentages and remove decommissioned/obsolete non-clients.

SNIPPET #3 - (Final Product) Versions of REPDRVFS.DLL with indicator, percentages and totals
(This can all go into an SMS Web Report)

DECLARE @Total INT
SELECT
   @Total = COUNT(DISTINCT resourceID)
FROM
   dbo.v_GS_SoftwareFile
WHERE
   fileName = 'Repdrvfs.dll'
   AND resourceID in (select resourceID from dbo.v_R_System WHERE Decommissioned0 = 0 and Obsolete0 = 0 and client0 = 1)


SELECT
   COUNT(DISTINCT sf.resourceID) as [Count],
   100.0 * COUNT(DISTINCT sf.resourceID) / @Total as Percentage,
   CASE
      WHEN  sf.FileVersion LIKE '5.2.3790.2936%'
        OR  sf.FileVersion LIKE '5.2.3790.40[8-9][0-9]%'         --4080-4099
        OR  sf.FileVersion LIKE '5.2.3790.4[1-9][0-9][0-9]%'     --4100-4999
        OR  sf.FileVersion LIKE '5.2.3790.[5-9][0-9][0-9][0-9]%' --5000-9999
        OR  sf.FileVersion LIKE '5.1.2600.313[8-9]%'             --3138-3139
        OR  sf.FileVersion LIKE '5.1.2600.31[4-9][0-9]%'         --3140-3199
        OR  sf.FileVersion LIKE '5.1.2600.3[2-9][0-9][0-9]%'     --3200-3999
        OR  sf.FileVersion LIKE '5.1.2600.[4-9][0-9][0-9][0-9]%' --4000-9999
        OR  sf.FileVersion LIKE '[6-9]%' THEN 'X'
      ELSE ''
   END AS [Has Update],
   sf.fileVersion AS [Version]
FROM
   dbo.v_GS_SoftwareFile AS sf
WHERE
   sf.fileName = 'Repdrvfs.dll'
   AND sf.resourceID in (select resourceID from dbo.v_R_System WHERE Decommissioned0 = 0 and Obsolete0 = 0 and client0 = 1)
GROUP BY
   sf.fileVersion
ORDER BY
   [Has Update],
   sf.fileVersion
COMPUTE
   SUM(COUNT(DISTINCT sf.resourceID)),
   SUM(100.0 * COUNT(DISTINCT sf.resourceID) / @Total) BY [Has Update] 

 

 OUTPUT #3

 

So the points of interest in snippet #3 is a preceding query that gets the total number of unique, valid machines; a percentage calculation in the SELECT and totals using a COMPUTE clause; and a subselect in both queries that limits the results to only non-decommissioned, non-obsolete valid clients.

BTW, you don't have any control over how the COMPUTE clause names the total/subtotal columns.  If you've got a SUM, the total/subtotal column will say SUM. You can't change that. In our case, the SUM on the left is the count and the SUM on the right is the percentage.  (percentages might not = 100% exactly due to rounding/precision)

A COLLECTION QUERY (Untested WQL)

I'll leave finishing this up to you, but if you wish to target devices that don't have the latest update, you need to create yourself a collection with a subselect. The subselect should use the file versions in the CASE statement, first finds all of the machines that HAVE the update and then performs a NOT IN...something like this:

SELECT
   sys.ResourceID,
   sys.ResourceType,
   sys.Name,
   sys.SMSUniqueIdentifier,
   sys.ResourceDomainORWorkgroup,
   sys.Client
FROM  
   SMS_R_System AS sys
   INNER JOIN SMS_G_System_SoftwareFile AS sf
     ON sys.ResourceID = sf.ResourceID
WHERE  sf.FileName = "REPDRVFS.DLL"
       AND sf.resourceID NOT IN (SELECT
                                   resourceID
                                 FROM  
                                   SMS_G_System_SoftwareFile AS sf2
                                 WHERE  sf2.FileName = "REPDRVFS.DLL"
                                        AND (sf2.FileVersion LIKE "5.2.3790.2936%"
                                             OR sf2.FileVersion LIKE "5.2.3790.40[8-9][0-9]%"
                                             OR sf2.FileVersion LIKE "5.2.3790.4[1-9][0-9][0-9]%"
                                             OR sf2.FileVersion LIKE "5.2.3790.[5-9][0-9][0-9][0-9]%"
                                             OR sf2.FileVersion LIKE "5.1.2600.313[8-9]%"
                                             OR sf2.FileVersion LIKE "5.1.2600.31[4-9][0-9]%"
                                             OR sf2.FileVersion LIKE "5.1.2600.3[2-9][0-9][0-9]%"
                                             OR sf2.FileVersion LIKE "5.1.2600.[4-9][0-9][0-9][0-9]%"
                                             OR sf2.FileVersion LIKE "[6-9]%"))
       AND sys.decommissioned = 0
       AND sys.obsolete = 0
       AND sys.client = 1


SUMMARY AND NEXT STEPS

This article discusses:

  1. There are WMI stability updates that went into Vista and Server 2008 and have been backported to XP & Server 2003.
    http://support.microsoft.com/kb/933061
    http://support.microsoft.com/kb/933062
  2. The latest service packs should have these WMI stability updates in them, but they are big and for those of us who don't want to rush to deploy them, we need to make sure we have the updates in place.
  3. In order to determine if the WMI stability updates are on your machines, you need to inventory REPDRVFS.DLL in %WINDIR%\System32\wbem
  4. When writing a SQL query to count machines having/missing the updates, you need to look for said file with the file version LIKE
    5.1.2600.3138% (or last 4 digits higher)
    5.2.3790.2936%
    5.2.3790.4080% (or last 4 digits higher)
  5. One way to search a version string for a range of numbers is to use the bracket [ ] wildcard which lets you specify
    A RANGE: [0-9] or [a-g] which means any single character matching 0 though 9 or a through g
    A SET: [135] or [adf] which means any single character matching 1, 3, 5 or a, d, f.
  6. COMPUTE gives you totals/subtotals in your SQL query, but you can't change the column names which they display.
  7. The SQL code for the report is here --> WMIStability.txt  (save and rename to .SQL)
  8. The WQL code for machines missing the update is here --> WMIStabilityWQL.txt (save and rename to .SQL)

Next Steps

At this point you can put the SQL code into a web report and find out how many machines have the WMI stability updates.  To find WHICH machines these are, you'll have to write your own drill-down report which joins to v_R_System and doesn't GROUP BY and COUNT but rather just displays the machines.  Be careful here, if you have a lot of machines this could timeout trying to enumerate them all.

And test that collection query, it works for me and the numbers look about right, but I may be missing something.

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
Add to Google

 

 

Published Wednesday, June 04, 2008 8:05 PM by jnelson
Filed under: , , ,

Comments

# SMS 2003 - Verify WMI Stability Backport on Windows XP/Server 2003 with this report SQL (deploy to them with this collection query WQL) - Number2 Blog - MyITForum.com

Pingback from  SMS 2003 - Verify WMI Stability Backport on Windows XP/Server 2003 with this report SQL (deploy to them with this collection query WQL) - Number2 Blog - MyITForum.com

Powered by Community Server (Commercial Edition), by Telligent Systems