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:
- 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 - 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.
- In order to determine if the WMI stability updates are on your machines, you need to inventory REPDRVFS.DLL in %WINDIR%\System32\wbem
- 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) - 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. - COMPUTE gives you totals/subtotals in your SQL query, but you can't change the column names which they display.
- The SQL code for the report is here --> WMIStability.txt (save and rename to .SQL)
- 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
