I have assigned each asset a department. I am trying to create a report that displays the UserID, ComputerName, OS Version, CPU, RAM installed and version of Microsoft Office installed for all computers in a given department.

Thanks,

Mike
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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?
Answered 01/14/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 01/14/2011 by: mzastrow
Senior Yellow Belt

Please log in to comment
0
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.
Answered 01/14/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I tried this and the results were blank. I also tried changing "Department" to a department name and it was still blank.
Answered 01/14/2011 by: mzastrow
Senior Yellow Belt

Please log in to comment
0
I forgot to put in your department field ID -- mine was 59 yours is 28. I fixed the SQL above.
Answered 01/14/2011 by: GillySpy
Seventh Degree Black Belt

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