-- Author: Number2 (John Nelson) - http://number2blog.com
-- Disclaimer: This code is offered as-is and is not provided for any particular purpose
--             I can't guarantee that it works, nor can I guarantee it won't break something.
--             However, it is to the best of my understanding, pretty good code.

-- Modify it to meet your needs and TEST TEST TEST!
--
-- Description:   This query can go right into an SMS web report. It is intended to
--                show counts of machines that have the latest WMI stability updates
--                and counts of machines that are missing said updates.
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]