Solved

SCCM 2007 Reports - Count of OS / Service Pack

Posted on 2015-02-17
10
274 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

863 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

19 Experts available now in Live!

Get 1:1 Help Now