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

Comments

  • Oh. The Mysql statement was copied from the report log.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity