Hi there This query (using the pivot function) was originally written by John Nelson(number 2) of Wells Fargo, in order to find the agent version running on clients and get a full count. I got help fixing it for SQL 2000, however I still get an error. I assume due to a different table names. Where can I find the SMS 2003\SQL2000 database structure, since I want to use the following query to do the same on an SMS 2003 DB. <<Begin Query>> SELECT CASE WHEN client_version0 = '2.50.4160.2000' THEN 'v3 SP2' WHEN client_version0 = '2.50.4138.2000' THEN 'v3 SP2 RC' WHEN client_version0 = '2.50.4050.2000' THEN 'v3 SP2 Beta' WHEN client_version0 = '2.50.3174.1018' THEN 'v3 SP1' WHEN client_version0 = '2.50.3174.1152' THEN 'v3 SP1 with ITMU pre-reqs' WHEN client_version0 = '4.00.5135.0000' THEN 'v4 Beta 1' WHEN client_version0 = '4.00.5125.0000' THEN 'v4 Beta 1 Escrow' WHEN client_version0 = '4.00.5221.0000' THEN 'v4 Beta 1Refresh Escrow' WHEN client_version0 = '4.00.5224.0000' THEN 'v4 Beta 1Refresh' WHEN client_version0 = '4.00.5571.0000' THEN 'v4 Beta 2 Escrow' WHEN client_version0 = '4.00.5574.0000' THEN 'v4 Beta 2 Escrow update' WHEN client_version0 = '4.00.5578.0000' THEN 'v4 Beta 2' WHEN client_version0 = '4.00.5578.0002' THEN 'v4 Beta 2 with a hotfix?' WHEN client_version0 = '4.00.5790.0000' THEN 'v4 RC0 Preview' WHEN client_version0 = '4.00.5815.0000' THEN 'v4 RC0' WHEN client_version0 = '4.00.5924.0000' THEN 'v4 RTM escrow' WHEN client_version0 = '4.00.5931.0001' THEN 'v4 RTM' ELSE client_version0 END AS [SMS Client Versions], pvt.client_version0 AS [Version Number], [XYZ], [OTHER], [XYZ]+[OTHER] AS TOTAL FROM ( select Sites.Client_Version0, count (case when Sites.AssignedSites = 'XYZ' then Sites.Name0 end) as XYZ, count (case when Sites.AssignedSites = 'Other' then Sites.Name0 end) as Other from ( SELECT DISTINCT sys.name0, sys.client_version0, CASE WHEN sites.sms_assigned_sites0 IN ('XYZ') THEN sites.sms_assigned_sites0 ELSE 'OTHER' END AS AssignedSites FROM dbo.v_ra_system_smsassignedsites AS sites JOIN dbo.v_r_system AS sys ON sites.resourceid = sys.resourceid WHERE sys.client0 = 1 AND sys.obsolete0 = 0) AS sites group by Sites.Client_Version0) pvt <<End Query>> TIA

Hi, You can use the same query with Configmgr. I just ran you sql query and it works great.Kent Agerlund | http://scug.dk/members/Agerlund/default.aspx | The Danish community for System Center products

I got help fixing it for SQL 2000, however I still get an error. Which error are you talking about?