CODEWITHSUNDEEP
sql-server
Saku
Saku

Reputation: 35

SQL Server Management Studio - Add columns containing software name and version to SQL Query for systems with specific software

I am using Microsoft SQL Server Management Studio to query for computers with a specific software in SQL Server database. Unfortunately, I am completely new to SQL so I am trying to learn on the fly. I was able to create a basic query thanks to help on Prajwal's site.

However I would like to refine it a little bit. I want to have two extra columns which display the software name, and the software version.

To start, here is my code so far:

SELECT DISTINCT
    sys.Netbios_Name0 AS [Computer Name],
    sys.User_Name0 AS [User Name]
FROM
    v_R_System sys
JOIN 
    v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
    sys.ResourceID IN (SELECT sys.ResourceID
                       FROM v_R_System sys
                       JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
                       WHERE DisplayName0 LIKE '%Adobe Reader%')

This returns the following output:

Computer Name | User Name
--------------+-----------
COMPUTER001   | Jane Doe  
COMPUTER002   | John Doe

However I would like output like the below example, with the software name from the where statement as well as its version:

Computer Name | User Name | Software     | Version
--------------+-----------+--------------+---------
COMPUTER001   | Jane Doe  | Adobe Reader |  v.XXX
COMPUTER002   | John Doe  | Adobe Reader |  v.XXX

I have tried adding to my Select statement these properties:

SELECT DISTINCT
    sys.Netbios_Name0 AS [Computer Name],
    sys.User_Name0 AS [User Name],
    arp.DisplayName0 AS [Software],
    arp.Version0 AS [Version]

But this returns EVERY software a computer has, like so:

Computer Name | User Name | Software     | Version
--------------+-----------+--------------+--------
COMPUTER001   | Jane Doe  | Adobe Reader |  v.XXX
COMPUTER001   | Jane Doe  | Firefox      |  v.XXX
COMPUTER001   | Jane Doe  | Chrome       |  v.XXX

I need it just to give the specific software I query. I have searched on the internet to see if I could find anything to help me, and started learning some SQL basics, but haven't had much luck so far. I am going to continue researching and working on it, but if anyone can offer me any tips or advice, I would really appreciate it!

By the way, I have managed to create the query successfully in SCCM, but I am not so sure how to translate WQL into SQL and work wants me to make the query in SSMS.

Upvotes: 0

Views: 157

Answers (1)

Alex Brohshtut
Alex Brohshtut

Reputation: 2060

If I understand correctly, you don't really need an inner query (which is BTW bad practice and should be avoided at all costs, since this query runs on every row). You query can be simplified to code below to achieve what you need.

SELECT DISTINCT
    sys.Netbios_Name0 AS [Computer Name],
    sys.User_Name0 AS [User Name],
    arp.DisplayName0 AS [Software],
    arp.Version0 AS [Version]
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE arp.DisplayName0 LIKE '%Adobe Reader%'

Upvotes: 1

Related Questions