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)
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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

Answered 09/24/2012 by: dmaximoff
Orange Senior Belt

Please log in to comment

Answers

1

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.

Answered 09/24/2012 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity