/build/static/layout/Breadcrumb_cap_w.png
09/16/2016 1280 views
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 desc

The 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 desc
My 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.
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

2
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%"

Answered 09/16/2016 by: chucksteel
Red Belt