I am trying to write an asset report with most, if not all of the information that we have stored in the Assets area of the K1000, in it.  I can't seem to find a field to pull the last user ID.  Can anyone tell me what the field is called?  Or is there somewhere that I can get a list of the databases and tables in the K1000?

This is what I have come up with so far:

SELECT ASSET.ID AS ASSET_ID,ASSET_DATA_5.FIELD_25 AS FIELD_25,A29.NAME AS FIELD_29,ASSET_DATA_5.FIELD_30 AS FIELD_30,A27.NAME AS FIELD_27,ASSET_DATA_5.FIELD_32 AS FIELD_32,USER.FULL_NAME AS FIELD_31,A20.NAME AS FIELD_20,A19.NAME AS FIELD_19,A28.NAME AS FIELD_28,ASSET.NAME AS ASSET_NAME,ASSET_DATA_5.FIELD_26 AS FIELD_26,A23.NAME AS FIELD_23,ASSET_DATA_5.FIELD_34 AS FIELD_34,ASSET_DATA_5.FIELD_33 AS FIELD_33 FROM ASSET_DATA_5  LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J29 ON J29.ASSET_ID = ASSET.ID AND J29.ASSET_FIELD_ID=29
                             LEFT JOIN ASSET A29 ON A29.ID = J29.ASSOCIATED_ASSET_ID
                             LEFT JOIN ASSET_DATA_13 AD29 ON AD29.ID = A29.ASSET_DATA_ID
                              LEFT JOIN ASSET_ASSOCIATION J27 ON J27.ASSET_ID = ASSET.ID AND J27.ASSET_FIELD_ID=27
                             LEFT JOIN ASSET A27 ON A27.ID = J27.ASSOCIATED_ASSET_ID
                             LEFT JOIN ASSET_DATA_2 AD27 ON AD27.ID = A27.ASSET_DATA_ID
                              LEFT JOIN USER USER ON USER.ID = ASSET.OWNER_ID LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20
                             LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID
                             LEFT JOIN ASSET_DATA_1 AD20 ON AD20.ID = A20.ASSET_DATA_ID
                              LEFT JOIN ASSET_ASSOCIATION J19 ON J19.ASSET_ID = ASSET.ID AND J19.ASSET_FIELD_ID=19
                             LEFT JOIN ASSET A19 ON A19.ID = J19.ASSOCIATED_ASSET_ID
                             LEFT JOIN ASSET_DATA_4 AD19 ON AD19.ID = A19.ASSET_DATA_ID
                              LEFT JOIN ASSET_ASSOCIATION J28 ON J28.ASSET_ID = ASSET.ID AND J28.ASSET_FIELD_ID=28
                             LEFT JOIN ASSET A28 ON A28.ID = J28.ASSOCIATED_ASSET_ID
                             LEFT JOIN ASSET_DATA_12 AD28 ON AD28.ID = A28.ASSET_DATA_ID
                              LEFT JOIN ASSET_ASSOCIATION J23 ON J23.ASSET_ID = ASSET.ID AND J23.ASSET_FIELD_ID=23
                             LEFT JOIN ASSET A23 ON A23.ID = J23.ASSOCIATED_ASSET_ID
                             LEFT JOIN ASSET_DATA_8 AD23 ON AD23.ID = A23.ASSET_DATA_ID
                                ORDER BY FIELD_27,ASSET_ID

 

1 Comment   [ + ] Show Comment

Comments

  • Have you tried using flyspeed to browse the database? I tend to use this when creating complex queries.
Please log in to comment

Answers

1

You will need to JOIN the Machine table. I prefer MACHINE.USER_LOGGED since this gives domain\username. 

There's not really a standard way to join this table. I've used the serial number in the past since it was in both the Asset table, and the Machine.

LEFT JOIN ASSET  A ON A.MAPPED_ID = MACHINE.ID AND A.ASSET_TYPE_ID=5

LEFT JOIN ASSET_DATA_5 A5 ON A5.ID = A.ASSET_DATA_ID

 

I would also download the MySQL workbench. You can then browse the tables.

http://dev.mysql.com/downloads/tools/workbench/

Answered 07/11/2013 by: dugullett
Red Belt

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

Share