Solved

SCCM 2007 Reports - Count of OS / Service Pack

Posted on 2015-02-17
10
254 Views
Last Modified: 2015-02-23
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
Comment
Question by:mldaigle1
  • 6
  • 4
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40625439
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
 

Author Comment

by:mldaigle1
ID: 40625504
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
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 40625509
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
 

Author Comment

by:mldaigle1
ID: 40625626
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40625662
What version of SQL are you using?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40625666
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
 

Author Comment

by:mldaigle1
ID: 40625708
humm... maybe i did not understand your instruction.  I simply copy and paste your statement in me SCCm report.... Am i missing something?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40625715
It might be that you need to create it as a stored procedure that you can call from SCCM.
0
 

Author Closing Comment

by:mldaigle1
ID: 40625775
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40625811
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now