Solved

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

Posted on 2010-09-23
15
7,494 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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