Report with computer and software Data
Hi I'm trying to get a Computer List report going with mostly Computer Categories but i need also need to add a column that lists all the software each computer has. I understand the formatting will be ugly but for my purposes it should be ok.
This is the Code i have so far without adding the software column
SELECT MACHINE.NAME AS SYSTEM_NAME,SYSTEM_DESCRIPTION,BIOS_SERIAL_NUMBER,CS_MANUFACTURER,OS_NAME,SERVICE_PACK,CS_MODEL,PROCESSORS,RAM_TOTAL,SUM(MACHINE_DISKS.DISK_SIZE) AS MACHINE_DISKS_DISK_SIZE FROM MACHINE LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
Below is the code i beleive i need to add but I'm not sure how or where to add it:
SELECT DISPLAY_NAME FROM SOFTWARE WHERE (NOT SOFTWARE.IS_PATCH)
Answers (2)
I think this will do what you need. I took your SQL string and added a couple lines to list installed software but exclude patches.
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, CS_MODEL, BIOS_MANUFACTURER, BIOS_SERIAL_NUMBER, PROCESSORS, RAM_TOTAL, SUM(MACHINE_DISKS.DISK_SIZE) AS MACHINE_DISKS_DISK_SIZE, OS_NAME, SERVICE_PACK, GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED FROM MACHINE LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.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) WHERE SOFTWARE.IS_PATCH = 0 GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
Dave
Comments:
-
Thanks! - Papo28 12 years ago
The following post should help:
http://www.itninja.com/question/custom-inventory-report-or-sql-query-help-needed
My answer there shows how to include the software table as a join in your report.
