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

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
ggarateAsked:
Who is Participating?
 
TarekIsmailConnect With a Mentor Commented:
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
 
TarekIsmailCommented:
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
 
ggarateAuthor Commented:
And only the instances of SQL (2000, 2005 and 2008)?

Thanks
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
TarekIsmailCommented:
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
 
ggarateAuthor Commented:
It does not work, there are machines with SQL 2008, not reported
0
 
TarekIsmailCommented:
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
 
ggarateAuthor Commented:
Here you have it, Thanks
SMSReportResults-a-enviar.csv
0
 
TarekIsmailCommented:
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
 
TarekIsmailCommented:
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
 
ggarateAuthor Commented:
Well, working all but the SQL2000 server does not see them all, there are a cluster and the nodes are not
0
 
TarekIsmailCommented:
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
 
ggarateAuthor Commented:
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
 
TarekIsmailCommented:
very strange for me , service will not give you the SQL version
0
 
ggarateAuthor Commented:
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
 
ggarateAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.