Query Machines With Two Specific Software Titles Installed
Hello Ninjas!
I have a simple request that appears to requires some SQL knowledge that I do not have. I am working on creating a SQL report that will show all machines that have TWO specific software titles installed (Adobe + BlueBeam). I have a report from ITNINJA that we use to query machines with a particular software title installed, but it only queries ONE title at a time. The code is below:
Select
MACHINE.NAME as Computer_Name,
SOFTWARE.DISPLAY_NAME as Application_Name,
Display_Version,IP,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED,
LAST_SYNC
from
(SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE)
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like 'BlueBeam%'
order by Display_Version * 1 descThe above code will search for all machines with BlueBeam installed, but I need it to search for BlueBeam and Adobe. I have tried adding another line to it to below, but that comes up with 0 lines returned:Select
MACHINE.NAME as Computer_Name,
SOFTWARE.DISPLAY_NAME as Application_Name,
Display_Version,IP,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED,
LAST_SYNC
from
(SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE)
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like 'BlueBeam%'
and SOFTWARE.DISPLAY_NAME like 'Adobe Acrobat%'
order by Display_Version * 1 descMy understanding is that I need to have two queries, one searching for Adobe, the other searching for BlueBeam, and then compare the two? I unfortunately don't know how to do that in SQL. Please Obi-Wan Kenobi, you're my only hope.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
Here is one way to do it:
SELECT DISTINCT(MACHINE.NAME) as Computer_Name,
IP,
USER_LOGGED,
LAST_SYNC,
GROUP_CONCAT(SOFTWARE.DISPLAY_NAME) as Installed_Software
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT MSJT on MSJT.MACHINE_ID = MACHINE.ID
LEFT JOIN SOFTWARE on SOFTWARE.ID = MSJT.SOFTWARE_ID
GROUP BY MACHINE.NAME
HAVING Installed_Software like "%BlueBeam%"
and Installed_Software like "%Adobe Acrobat%"
