/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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