Need help with an SQL query to search for machines with specific software
Hello,
I need some help in making a report of the list of computers on a specific domain name, with the specific OS, and with a specific software.
So for example, searching for all the computers in the "domain1" with the OS "Microsoft Office" and with the software "Microsoft Office%"
This is currently possible via the filters, but I need a CSV report which shows the Computer name, Domain, OS, and software installed on all the computers.
Running a report via the search filters show everything else, beside the software installed.
Here is the SQL:
select MACHINE.NAME as MNAME,MACHINE.IP,MACHINE.DOMAIN,MACHINE.MAC,
MACHINE.NOTES,OS_NAME,MACHINE.OS_VERSION,MACHINE.SERVICE_PACK,
MACHINE.SYSTEM_DIRECTORY,MACHINE.USER_LOGGED,MACHINE.USER_DOMAIN,
MACHINE.USER,MACHINE.CS_MANUFACTURER,MACHINE.CS_MODEL,MACHINE.CS_DOMAIN,
MACHINE.BIOS_NAME,MACHINE.BIOS_VERSION,MACHINE.BIOS_MANUFACTURER,MACHINE.BIOS_DESCRIPTION,
MACHINE.BIOS_IDENTIFICATION_CODE,MACHINE.BIOS_SERIAL_NUMBER,MACHINE.PROCESSORS,
MACHINE.RAM_TOTAL,MACHINE.REGISTRY_SIZE
,GROUP_CONCAT(MACHINE_DISKS.NAME) as DNAME from ORG1.MACHINE left join MACHINE_DISKS on MACHINE.ID = MACHINE_DISKS.ID
LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1
where (((( CS_DOMAIN like 'domain%') AND (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like 'microsoft office%')) ) AND OS_NAME like '%microsoft%')) GROUP BY MACHINE.ID
Can someone modify this sql so that when a report is run, there is another column to show the software installed? Even though it is searching for specific softare installed on computers (SOFTWARE.DISPLAY_NAME like 'microsoft office%') it does not seem to show it in the report.
This is how the current report output shows, but would like to add a column to show the software as well.
Thank you
Answers (1)
This is a variation I use for csv. I do it this way because of the line breaks in a csv, and to me it's cleaner. Be sure to change you domain on the 4TH from last line. SELECT DISPLAY_NAME, DISPLAY_VERSION, GROUP_CONCAT(DISTINCT M.NAME SEPARATOR '\n') AS MACHINE_NAME, GROUP_CONCAT(DISTINCT M.USER_LOGGED SEPARATOR '\n') AS User_Logged, M.DOMAIN, M.OS_NAME FROM SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID) LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) WHERE (NOT S.IS_PATCH) AND M.DOMAIN = 'DOMAIN.ORG' AND S.DISPLAY_NAME LIKE '%MICROSOFT OFFICE%' GROUP BY S.DISPLAY_NAME, M.NAME ORDER BY S.DISPLAY_NAME
This is a variation I use for csv. I do it this way because of the line breaks in a csv, and to me it's cleaner. Be sure to change you domain on the 4TH from last line.
SELECT DISPLAY_NAME, DISPLAY_VERSION, GROUP_CONCAT(DISTINCT M.NAME SEPARATOR '\n') AS MACHINE_NAME, GROUP_CONCAT(DISTINCT M.USER_LOGGED SEPARATOR '\n') AS User_Logged, M.DOMAIN, M.OS_NAME FROM SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID) LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) WHERE (NOT S.IS_PATCH) AND M.DOMAIN = 'DOMAIN.ORG'
AND S.DISPLAY_NAME LIKE '%MICROSOFT OFFICE%' GROUP BY S.DISPLAY_NAME, M.NAME ORDER BY S.DISPLAY_NAME
Comments:
-
Thank you very much, that worked perfectly! - awaisk 11 years ago
