Solved

SCCM 2007 Reports - Count of OS / Service Pack

Posted on 2015-02-17
10
298 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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