• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

SCCM 2007 Reports - Count of OS / Service Pack

Hi,

I have a report that group computers by OS / SP.  I need a little bit help to complete that report.  The result return is :

Operating System                                                                      Service Pack                Count

Microsoft Windows 2000 Professional                                      Service Pack 4      14
Microsoft Windows 2000 Professionnel                                      Service Pack 4      11
Microsoft Windows 7 Enterprise                                              Service Pack 1      2
Microsoft Windows 7 Professional                                                                            5
Microsoft Windows 7 Professional                                              Service Pack 1      1396
Microsoft Windows 7 Professionnel                                      Service Pack 1      1146
Microsoft Windows 7 Ultimate                                                      Service Pack 1      1
Microsoft Windows XP Professional                                      Service Pack 2      2
Microsoft Windows XP Professional                                      Service Pack 3      4197
Microsoft Windows XP Professionnel                                      Service Pack 2      8
Microsoft Windows XP Professionnel                                      Service Pack 3      14620
Microsoft(R) Windows(R) XP Professional x64 Edition              Service Pack 2      1


It does distinction between French and English version.  I would like to display the OS name as

"Windows 7"             for           Microsoft Windows 7 Professionnel / Professional / Ultimate / Enterprise
"Windows XP"          for           Microsoft Windows XP Professionnel / Professional
"Windows 2000"       for          Microsoft Windows 2000 Professionnel / Professional

So that way, i hope to group the Service Pack without language distinction.


my SQL statement is:

SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OS,v_R_System SYS
INNER JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
WHERE FCM.CollectionID = 'SMS00004' and OS.Resourceid=SYS.Resourceid
GROUP BY Caption0,CSDVersion0
ORDER BY Caption0,CSDVersion0


There must be a trick.... like the CASE function, but i did not figure it out yet.... :(
0
mldaigle1
Asked:
mldaigle1
  • 6
  • 4
1 Solution
 
Lee SavidgeCommented:
It gets tricky but use a CTE to make things a little cleaner.


with t as (
SELECT case
             when Caption0 like '%2000%' then 'Windows 2000'
             when Caption0 like '%Windows 7%' then 'Windows 7'
             when Caption0 like '%XP%' then 'Windows XP'
       end as 'Operating System',
       CSDVersion0 as 'Service Pack'
FROM v_GS_OPERATING_SYSTEM OS,v_R_System SYS
INNER JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
WHERE FCM.CollectionID = 'SMS00004' and OS.Resourceid=SYS.Resourceid
)
select [Operating System],
       [Service Pack],
       count(*) as VersionCount
from t
GROUP BY [Operating System],
         [Service Pack]

ORDER BY [Operating System],
         [Service Pack]

Open in new window

0
 
mldaigle1Author Commented:
Hello Lee,

Unfortunately, it does not work... it does return me the same info, it did not group the OS as per your statement.

:(
0
 
Lee SavidgeCommented:
What is the output if you do this:

with t as (
SELECT case
             when Caption0 like '%2000%' then 'Windows 2000'
             when Caption0 like '%Windows 7%' then 'Windows 7'
             when Caption0 like '%XP%' then 'Windows XP'
       end as 'Operating System',
       CSDVersion0 as 'Service Pack'
FROM v_GS_OPERATING_SYSTEM OS,v_R_System SYS
INNER JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
WHERE FCM.CollectionID = 'SMS00004' and OS.Resourceid=SYS.Resourceid
)
select [Operating System],
       [Service Pack]
from t

ORDER BY [Operating System],
         [Service Pack]
                                          

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mldaigle1Author Commented:
An error occurred when the report was run. The details are as follows:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 319
0
 
Lee SavidgeCommented:
What version of SQL are you using?
0
 
Lee SavidgeCommented:
I have a table called testout which contains 2 columns. One called Caption0 and the other called CSDVersion0.

If I replace the FROM, JOIN and WHERE clause in my test SQL script and put: FROM TestOut

My script works and gives me the correct counts. I get:

Operating System	Service Pack	VersionCount
Windows 2000	Service Pack 4	160
Windows 7		16
Windows 7	Service Pack 1	1042
Windows XP	Service Pack 2	321
Windows XP	Service Pack 3	257

Open in new window

0
 
mldaigle1Author Commented:
humm... maybe i did not understand your instruction.  I simply copy and paste your statement in me SCCm report.... Am i missing something?
0
 
Lee SavidgeCommented:
It might be that you need to create it as a stored procedure that you can call from SCCM.
0
 
mldaigle1Author Commented:
Thanks Lee,

I will continue on my side with "how to work with stored procedure call from SCCM" since i do not have any idea of what it is.  I will search information on it an apply your statement after.


Thanks again and have a great day!

:)
0
 
Lee SavidgeCommented:
Try this in SQL Management Studio:

if exists (select * from sysobjects where name = N'CountOSes' and type = N'P')
  drop procedure CountOSes
go

create procedure CountOSes
as
begin
with t as (
SELECT case
             when Caption0 like '%2000%' then 'Windows 2000'
             when Caption0 like '%Windows 7%' then 'Windows 7'
             when Caption0 like '%XP%' then 'Windows XP'
       end as 'Operating System',
       CSDVersion0 as 'Service Pack'
FROM v_GS_OPERATING_SYSTEM OS,v_R_System SYS
INNER JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
WHERE FCM.CollectionID = 'SMS00004' and OS.Resourceid=SYS.Resourceid
)
select [Operating System],
       [Service Pack],
       count(*) as VersionCount
from t
GROUP BY [Operating System],
         [Service Pack]

ORDER BY [Operating System],
         [Service Pack]
end
go

Open in new window


Then in your report, execute the stored procedure called CountOSes. That should do it but I'm running a bit blind from this point on :)

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now