/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
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
Posted by: dugullett 11 years ago
Red Belt
3

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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