/build/static/layout/Breadcrumb_cap_w.png

k1000 Asset History and Asset mysql join help

I've been slowly teaching myself mysql for the past couple years and have usually been successful in building reports I need. I'm currently trying to build a new report for accounting that shows changes to location and status. I'm able to make reports that show all the asset information I want, and reports that show all the asset history information I want, but having difficulty joining the two.

I'm looking for have a report that has the standard asset information (name, type, serial, etc) with asset history so I can track changes to status or location (ex, recycled or spare) 

This is the standard asset history report that currently works:
SELECT TYPE_NAME,
NAME,
DATE_FORMAT(TIME,'%W %m-%d-%Y') as TIME,
USER_TEXT,
CHANGE_TYPE,
FRIENDLY_FIELD_NAME,
VALUE1 as "Previous Status",
VALUE2 as "Current Status"

FROM ASSET_HISTORY

WHERE FRIENDLY_FIELD_NAME = 'STATUS' AND TIME > DATE_SUB(NOW(), INTERVAL 168 HOUR)

ORDER BY NAME

And this is my modified report:
SELECT ASSET.NAME AS "Asset Name",
ASSET_HISTORY.TYPE_NAME AS "Asset Type",
ASSET_DATA_5.FIELD_818 AS "Model Name",
ASSET_DATA_5.FIELD_816 AS "Serial Number",
ASSET_DATA_5.FIELD_817 AS "Product Number",
DATE_FORMAT(TIME,'%W %m-%d-%Y') as "Change Date",
USER_TEXT AS "Changed by",
ASSET_HISTORY.CHANGE_TYPE AS "Change Type",
ASSET_HISTORY.FRIENDLY_FIELD_NAME AS "Changed Field",
ASSET_HISTORY.VALUE1 as "Previous Status",
ASSET_HISTORY.VALUE2 as "Current Status"

#FROM ASSET_HISTORY

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 J25 ON J25.ASSET_ID = ASSET.ID AND J25.ASSET_FIELD_ID=25
LEFT JOIN ASSET A25 ON A25.ID = J25.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_1 AD25 ON AD25.ID = A25.ASSET_DATA_ID
LEFT JOIN ASSET_HISTORY ON ASSET_HISTORY.ID = ASSET_DATA_5.ID AND ASSET.ASSET_TYPE_ID=5

WHERE ((ASSET_HISTORY.CHANGE_TYPE = 'Modification') AND (ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'status'))

For some reason, the report will show the change type info for any items that show up with 'modification', but when I add AND (ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'status' I get nothing in my report. Any ideas?

Thanks in advance for the help.


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 7 years ago
Red Belt
0
In order to make your query work with my assets I had to strip out some of the fields. When troubleshooting this sort of query I generally find it helpful to strip away things anyway and then build back up. I believe the problem lies in your join statements:
LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5
I changed this to:
LEFT JOIN ASSET ON ASSET.ASSET_DATA_ID =  ASSET_DATA_5.ID
I generally put the table I'm joining first, I believe that changes with left and right join behavior. Also, the limit on the asset type is redundant since we are looking at specific assets.

Next:
LEFT JOIN ASSET_HISTORY ON ASSET_HISTORY.ID = ASSET_DATA_5.ID AND ASSET.ASSET_TYPE_ID=5
ASSET_HISTORY.ID is the ID of each entry in that table, it doesn't equal the asset's ID, that is in ASSET_HISTORY.ASSET_ID. I used this instead:
JOIN ASSET_HISTORY ON ASSET_HISTORY.ASSET_ID = ASSET.ID

Since there will be more than one entry in the history table per asset I did not use a left join in this case.

I don't have any entries for 'status' as a friendly field name in my ASSET_HISTORY table so I'm not sure what the issue is with that part. However, if you run the query below you should be able to check the results and identify which friendly field value you need to add to your where clause.
SELECT ASSET.NAME AS "Asset Name",
ASSET_HISTORY.TYPE_NAME AS "Asset Type",
DATE_FORMAT(TIME,'%W %m-%d-%Y') as "Change Date",
USER_TEXT AS "Changed by",
ASSET_HISTORY.CHANGE_TYPE AS "Change Type",
ASSET_HISTORY.FRIENDLY_FIELD_NAME AS "Changed Field",
ASSET_HISTORY.VALUE1 as "Previous Status",
ASSET_HISTORY.VALUE2 as "Current Status"
#FROM ASSET_HISTORY
FROM ASSET_DATA_5
LEFT JOIN ASSET ON ASSET.ASSET_DATA_ID =  ASSET_DATA_5.ID
JOIN ASSET_HISTORY ON ASSET_HISTORY.ASSET_ID = ASSET.ID
WHERE ASSET_HISTORY.CHANGE_TYPE = 'Modification'


Comments:
  • I added the join, and it works a bit better so thank you. However the next issue is that this is only showing comptuers, not all assets. I'm sure I need to add a join to the hardware listings but it hasn't worked so far. When I run this I do get two hardware items that show up, but they show up as ipads instead of docking stations.

    #Asset status change query
    SELECT ASSET.NAME AS "Asset Name",
    ASSET_HISTORY.TYPE_NAME AS "Asset Type",
    #T.NAME AS ASSET_TYPE,
    ASSET_DATA_5.FIELD_818 AS "Model Name",
    ASSET_DATA_5.FIELD_816 AS "Serial Number",
    ASSET_DATA_5.FIELD_817 AS "Product Number",
    DATE_FORMAT(TIME,'%W %m-%d-%Y') as "Change Date",
    #USER_TEXT AS "Changed by",
    #ASSET_HISTORY.CHANGE_TYPE AS "Change Type",
    #ASSET_HISTORY.FRIENDLY_FIELD_NAME AS "Changed Field",
    ASSET_HISTORY.VALUE1 as "Previous Status",
    ASSET_HISTORY.VALUE2 as "Current Status"

    FROM ASSET_DATA_5
    LEFT JOIN ASSET ON ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID
    LEFT JOIN ASSET_HISTORY ON ASSET_HISTORY.ASSET_ID = ASSET.ID

    WHERE ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'status' AND TIME > DATE_SUB(NOW(), INTERVAL 168 HOUR)))

    #Not showing hardware, just showing computers. Why?
    #Shows two hardware, but has them listed at "ipad" instead of the dock they are. - PBAWAC 7 years ago
    • Asset type 5 is the computer asset type in the assets module. This report is only pulling information for that type of asset. If you want to pull different kinds of assets then things will get much more complicated. - chucksteel 7 years ago
      • I get that it will complicated, that is why I posted as it is currently beyond my skillset. Really all I want to build is an asset history report that does what it does now, which is grabs everything that has a status change, and add in the associated model type, model name and serial number. Maybe I'm just going about this the wrong way. - PBAWAC 7 years ago
      • I can help you with that, I just need to know the ID of the other asset types that need to be included and the ID numbers of the fields that contain the model type, model name and serial number for each of those asset types. - chucksteel 7 years ago
      • Sorry its taken me 3 months to get back to this due to end of year stuff, but I've recently had time to start working on this again and no matter what I do, it still pulls some hardware asset numbers, and combines them with computer assets (IE hardware asset number, but the rest of the info is computer/serial). Not sure why that is since hardware assets are stored under asset_data_88, but I'm guessing the join must not totally be correct. Any ideas why it would mix up the data? - PBAWAC 7 years ago
      • Can you post your updated query? - chucksteel 7 years ago
      • It seems to happen when trying to join asset_data_5 and asset_history. Its showing asset names for docking stations, but showing all the rest of the data as an ipad/pc.

        SELECT ASSET_HISTORY.NAME AS "Asset Name",
        #ASSET_HISTORY.TYPE_NAME AS "Asset Type",
        FIELD_28 AS 'Asset Type',
        FIELD_818 AS 'Model Name',
        FIELD_816 AS 'Serial Number',
        FIELD_817 AS 'Product Number',
        DATE_FORMAT(TIME,'%W %m-%d-%Y') as "Change Date",
        ASSET_HISTORY.FRIENDLY_FIELD_NAME AS "Changed Field",
        ASSET_HISTORY.VALUE1 as "Previous Status",
        ASSET_HISTORY.VALUE2 as "Current Status"

        FROM ASSET_DATA_5
        LEFT JOIN ASSET ON ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID
        JOIN ASSET_HISTORY ON ASSET_HISTORY.ASSET_ID = ASSET.ID


        WHERE ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'Location' AND TIME > DATE_SUB(NOW(), INTERVAL 8 DAY))) OR ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'Status' AND TIME > DATE_SUB(NOW(), INTERVAL 8 DAY))) - PBAWAC 7 years ago
      • Try making the join to ASSET_HISTORY a left join. - chucksteel 7 years ago
      • The problem ended up being that we needed to add AND ASSET.ASSET_TYPE_ID=5 to the end of the asset join. It works correctly now that I have added that. I believe I've finished the report, going to have the users test tomorrow.

        (SELECT ASSET.NAME AS 'ASSET_NAME',
        ASSET_DATA_5.FIELD_28 AS 'Asset Type',
        ASSET_DATA_5.FIELD_818 AS 'Model Name',
        ASSET_DATA_5.FIELD_816 AS 'Serial Number',
        ASSET_DATA_5.FIELD_817 AS 'Product Number',
        DATE_FORMAT(TIME,'%W %m-%d-%Y') as 'Change Date',
        ASSET_HISTORY.FRIENDLY_FIELD_NAME AS 'Changed Field',
        ASSET_HISTORY.VALUE1 as 'Previous Status',
        ASSET_HISTORY.VALUE2 as 'Current Status'

        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_HISTORY ON ASSET.ID = ASSET_HISTORY.ASSET_ID

        WHERE ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'Location' AND TIME > DATE_SUB(NOW(), INTERVAL 8 DAY))) OR ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'Status' AND TIME > DATE_SUB(NOW(), INTERVAL 8 DAY)))

        ORDER BY ASSET.NAME asc, ASSET_HISTORY.FRIENDLY_FIELD_NAME
        )

        UNION

        #Hardware Asset status change query
        (SELECT ASSET.NAME AS 'Asset Name',
        A796.NAME AS 'Asset Type',
        ASSET_DATA_88.FIELD_781 AS 'Model Name',
        ASSET_DATA_88.FIELD_795 AS 'Serial Number',
        ASSET_DATA_88.FIELD_841 AS 'Product Number',
        DATE_FORMAT(TIME,'%W %m-%d-%Y') AS 'Change Date',
        ASSET_HISTORY.FRIENDLY_FIELD_NAME AS 'Changed Field',
        ASSET_HISTORY.VALUE1 AS 'Previous Status',
        ASSET_HISTORY.VALUE2 AS 'Current Status'

        FROM ASSET_DATA_88
        LEFT JOIN ASSET ON ASSET_DATA_88.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=88
        LEFT JOIN ASSET_HISTORY ON ASSET.ID = ASSET_HISTORY.ASSET_ID
        LEFT JOIN ASSET_ASSOCIATION J796 ON J796.ASSET_ID = ASSET.ID AND J796.ASSET_FIELD_ID=796
        LEFT JOIN ASSET A796 ON A796.ID = J796.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_91 AD796 ON AD796.ID = A796.ASSET_DATA_ID

        WHERE ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'Location' AND TIME > DATE_SUB(NOW(), INTERVAL 168 HOUR))) OR ((ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'Status' AND TIME > DATE_SUB(NOW(), INTERVAL 168 HOUR)))

        ORDER BY ASSET.NAME asc, ASSET_HISTORY.FRIENDLY_FIELD_NAME
        ) - PBAWAC 7 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ