Solved

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

Posted on 2010-09-23
15
6,934 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
Comment Utility
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
Comment Utility
And only the instances of SQL (2000, 2005 and 2008)?

Thanks
0
 
LVL 6

Expert Comment

by:TarekIsmail
Comment Utility
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
 

Author Comment

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

Expert Comment

by:TarekIsmail
Comment Utility
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
Comment Utility
Here you have it, Thanks
SMSReportResults-a-enviar.csv
0
 
LVL 6

Expert Comment

by:TarekIsmail
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:TarekIsmail
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
very strange for me , service will not give you the SQL version
0
 

Author Comment

by:ggarate
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

15 Experts available now in Live!

Get 1:1 Help Now