Solved

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

Posted on 2010-09-23
15
7,422 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
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
This is a fairly complicated script that will install the required prerequisites to install SCCM 2012 R2 on a server.  It was designed under the functional model in order to compartmentalize each step required, reducing the overall complexity.  The …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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