?
Solved

Report for SCCM with SQL Server machines and version of sql installed

Posted on 2010-09-23
15
Medium Priority
?
7,789 Views
Last Modified: 2013-11-21
I have this SQL statement


SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where ARPDisplayName0 like '%SQL%'  

Gives the correct result, but I need to filter only by SQL Server, either version 2000, 2005 or 2008
0
Comment
Question by:ggarate
[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
  • 8
  • 7
15 Comments
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33746614
for SQL 2000
---------------
SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE '%SQL%') AND (ARPDisplayName0 LIKE '%2000%')

for SQL 2005
---------------
SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE '%SQL%') AND (ARPDisplayName0 LIKE '%2005%')

for SQL 2008
-----------------

SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE '%SQL%') AND (ARPDisplayName0 LIKE '%2008%')




0
 

Author Comment

by:ggarate
ID: 33746797
And only the instances of SQL (2000, 2005 and 2008)?

Thanks
0
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33746871
can we try this for SQL 2008

SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE 'Microsoft SQL Server 2008 (64-bit)') or (ARPDisplayName0 LIKE 'Microsoft SQL Server 2008')
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:ggarate
ID: 33746971
It does not work, there are machines with SQL 2008, not reported
0
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33747249
could you give me the result of the query below , so i can get selection critria for your request

SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE '%SQL%')
0
 

Author Comment

by:ggarate
ID: 33747363
Here you have it, Thanks
SMSReportResults-a-enviar.csv
0
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33747808
HI,
we can divide it in 3 types as below:

Microsoft SQL Server 2000
Microsoft SQL Server 2000 (64 bits)

Microsoft SQL Server 2005
Microsoft SQL Server 2005 (64 bits)

Microsoft SQL Server 2008
Microsoft SQL Server 2008 (64-bit)
Microsoft SQL Server 2008 R2 (64-bit)

so the query will be for SQL 2008 as below







SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                      
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE 'Microsoft SQL Server 2008 (64-bit)') or (ARPDisplayName0 LIKE 'Microsoft SQL Server 2008')or (ARPDisplayName0 LIKE 'Microsoft SQL Server 2008 R2 (64-bit)')

Open in new window

0
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33747842
for SQL 2000
============
SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE 'Microsoft SQL Server 2000') or (ARPDisplayName0 LIKE 'Microsoft SQL Server 2000 (64 bits)')


for SQl 2005
==========

SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE 'Microsoft SQL Server 2005') or (ARPDisplayName0 LIKE 'Microsoft SQL Server 2005 (64 bits)')
0
 

Author Comment

by:ggarate
ID: 33747983
Well, working all but the SQL2000 server does not see them all, there are a cluster and the nodes are not
0
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33748244
try to use this one , i am sure it will give you what you have for 'Microsoft SQL Server 2000'
---------

SELECT     v_GS_SYSTEM.Name0, v_GS_SYSTEM.SystemRole0, v_GS_SYSTEM.SystemType0, v_GS_SYSTEM.Domain0, v_HS_INSTALLED_SOFTWARE.ARPDisplayName0
                     
FROM         v_HS_INSTALLED_SOFTWARE INNER JOIN
                      v_GS_SYSTEM ON v_HS_INSTALLED_SOFTWARE.ResourceID = v_GS_SYSTEM.ResourceID
 where (ARPDisplayName0  LIKE 'Microsoft SQL Server 2000')
0
 

Author Comment

by:ggarate
ID: 33748424
I have looked at the nodes of the cluster of SQL2000 and SQL server does not appear as installed software : ( in add / remove programs could you report based on any service? for example
0
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33748467
very strange for me , service will not give you the SQL version
0
 

Author Comment

by:ggarate
ID: 33748691
But the SQL2000 service, which is the instance, is totally different from sql2005 and 2008, I will see how it does not exist in add / remove programs, and we solved this by, thank you very much for your help;) once you ask for help again
0
 
LVL 6

Accepted Solution

by:
TarekIsmail earned 2000 total points
ID: 33748861
HI ,
again i have check the issue , it seems there is some missing keys in the registry and the link below will help you to view the program in Add/Remove program

Program Is Not Listed in Add/Remove Programs After Installation
http://support.microsoft.com/kb/247515

compare the registry between the two nodes and add the missing one
0
 

Author Closing Comment

by:ggarate
ID: 33748996
Thanks
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Back in July, I blogged about how Microsoft's new server pricing model, combined with the end of the Small Business Server package, would result in significant cost increases for many small businesses (see SBS End of Life: Microsoft Punishes Small B…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

764 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