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

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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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
Answered 03/12/2013 by: dugullett
Red Belt

  • Thank you very much, that worked perfectly!
Please log in to comment
Answer this question or Comment on this question for clarity