/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Query Machines With Two Specific Software Titles Installed

09/16/2016 1646 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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ