Solved

SCCM 2007 Reports - Count of OS / Service Pack

Posted on 2015-02-17
10
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 61
getting error Subquery returned more  than 1 value 6 22
SQL 2014 missing dll from Bin? 3 31
SQL Syntax 6 28
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

762 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