/build/static/layout/Breadcrumb_cap_w.png

Need help reporting on all software installed on all machines

I have been asked to run a report that shows all software on all 700 machines grouped by Machine and then by Software category.  I used the wizard to create th efollowing but it crashes the Kace VM each time I run it.  Help?

 

SELECT MACHINE.NAME AS SYSTEM_NAME, SC.DISCONNECT_TIME, CLIENT_VERSION, SC.SMMP_VERSION, MACHINE.IP, MACHINE.KUID, GROUP_CONCAT(DISTINCT IF(LABEL.NAME NOT LIKE 'HDN_LABEL_%', LABEL.NAME, NULL) SEPARATOR '\n') AS LABEL_NAME, LAST_SYNC, MACHINE.MAC, SC.CLIENT_CONNECTED, MANUAL_ENTRY, SYSTEM_DESCRIPTION, OS_ARCH, (CONCAT(SUBSTRING_INDEX(UPTIME, ',', 1), ' days, ', SUBSTRING(UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', UPTIME) - LOCATE(',', UPTIME) - 1), ' hours, ', SUBSTRING_INDEX(UPTIME, ':', -1), ' minutes')) AS UPTIME, OS_INSTALLED_DATE, LAST_REBOOT, LAST_SHUTDOWN, OS_BUILD, OS_MAJOR, OS_MINOR, OS_NAME, OS_VERSION, SERVICE_PACK, SYSTEM_DIRECTORY, MACHINE.USER_NAME, USER_LOGGED, USER_DOMAIN, USER_FULLNAME, BIOS_DESCRIPTION, BIOS_IDENTIFICATION_CODE, BIOS_MANUFACTURER, BIOS_NAME, BIOS_SERIAL_NUMBER, BIOS_VERSION, CHASSIS_TYPE, CS_DOMAIN, CS_MANUFACTURER, CS_MODEL, PROCESSORS, REGISTRY_MAX_SIZE, REGISTRY_SIZE, RAM_TOTAL, RAM_USED, CDROM_DEVICES, AVG(MACHINE_DISKS.PERCENT_USED) AS MACHINE_DISKS_PERCENT_USED, GROUP_CONCAT(DISTINCT MACHINE_DISKS.DISK_FREE SEPARATOR '\n') AS MACHINE_DISKS_DISK_FREE_GROUPED, GROUP_CONCAT(DISTINCT MACHINE_DISKS.NAME SEPARATOR '\n') AS MACHINE_DISKS_NAME_GROUPED, SUM(MACHINE_DISKS.DISK_SIZE) AS MACHINE_DISKS_DISK_SIZE, SUM(MACHINE_DISKS.DISK_USED) AS MACHINE_DISKS_DISK_USED, MONITOR, MOTHERBOARD_PRIMARY_BUS, MOTHERBOARD_SECONDARY_BUS, SOUND_DEVICES, VIDEO_CONTROLLERS, MACHINE_DAILY_UPTIME.DAY, MACHINE_DAILY_UPTIME.HOURS, MACHINE.NOTES AS MACHINE_NOTES, PRINTERS, GROUP_CONCAT(DISTINCT SOFTWARE.PUBLISHER SEPARATOR '\n') AS SOFTWARE_PUBLISHER_GROUPED, GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED, ASSET.NAME AS ASSET_NAME, ASSET.ID AS ASSET_ID, GROUP_CONCAT(DISTINCT A3.NAME SEPARATOR '\n') AS FIELD_3, MACHINE.ID as TOPIC_ID FROM MACHINE LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID) LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID) LEFT JOIN MACHINE_DAILY_UPTIME ON (MACHINE_DAILY_UPTIME.MACHINE_ID = MACHINE.ID) LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J3 ON J3.ASSOCIATED_ASSET_ID = ASSET.ID AND J3.ASSET_FIELD_ID=3

                                                 LEFT JOIN ASSET A3 ON A3.ID = J3.ASSET_ID WHERE ((ASSET.NAME like 'a%') AND (OS_NAME like '%Windows%')) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME


1 Comment   [ + ] Show comment
  • Oh. The Mysql statement was copied from the report log. - damonh 10 years ago

Answers (0)

Be the first to answer this question

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