ITNinja

mzastrow, are any of the fields above coming from asset fields that you populate? Or are they all fields that come from inventory? The reason I ask is that asset-base fields are different on every KBOX.

Secondly, you obviously have a field in computer asset that relates the department. Can you run this query and report the results here:

select ID, FIELD_NAME, FIELD_TYPE from ASSET_FIELD_DEFINITION WHERE ASSET_TYPE_ID=5

Thirdly, did you create a department asset type or did you use the OEM type that comes with the box?

The department field is one that I populate and it is from a list of Departments that were created as Asset Types.

Here are the results of the query.

Description:

# ID FIELD_NAME FIELD_TYPE

1 19 Location ASSET_1

2 28 Department ASSET_2

3 23 Vendor ASSET_4
4 21 Purchase Date date

5 56 Machine Type multiselect

6 22 Purchase Order text
7 29 Vendor Invoice text

8 30 Cap Ex Number text

9 57 Serial Number text

10 58 Order Number text
11 60 Invoice Amount text

12 61 Cap Ex Amount text

13 27 Assigned To user

Assuming that you only care about machines with a department and office installed....

select DA.NAME "Department", M.USER, M.NAME,CONCAT( M.OS_NAME,' ', M.OS_NUMBER, ' ',M.SERVICE_PACK) OS, PROCESSORS AS CPU, RAM_TOTAL as RAM,
GROUP_CONCAT(DISPLAY_VERSION ORDER BY DISPLAY_VERSION ASC SEPARATOR ', ') "Office Version"
from MACHINE M
JOIN MACHINE_SOFTWARE_JT MS ON MS.MACHINE_ID=M.ID
JOIN (select 'Microsoft Office' MO,SOFTWARE.* from SOFTWARE )S ON S.ID=MS.SOFTWARE_ID
JOIN ASSET CA ON CA.MAPPED_ID=M.ID
JOIN ASSET_ASSOCIATION AA ON CA.ID=AA.ASSET_ID and AA.ASSET_FIELD_ID=28 /* the id of your department field */
JOIN ASSET DA ON DA.ID=AA.ASSOCIATED_ASSET_ID
WHERE S.DISPLAY_NAME LIKE '%microsoft%office%'
GROUP BY S.MO
ORDER BY DA.NAME

If you're machine is like others I have seen office will list many entries so you have to figure out which ones you want. The query above aggregates them all and lists all the versions.

I tried this and the results were blank. I also tried changing "Department" to a department name and it was still blank.

I forgot to put in your department field ID -- mine was 59 yours is 28. I fixed the SQL above.