If you use HP Asset Manager software (AM), at some point you may be asked to query historical records of an asset. As long as the field is configured to keep history, all changes are kept in the amHistory table along with the old and new values of the field, modified date, what table is affected, etc.
The problem is amHistory table by itself is hardly useful without being linked to other tables where we usually start our search (i.e., machine’s serial number, person’s name, etc.). amHistory is linked to a table whenever Keep history: option is turned on on any field of that particular table. The trick is to understand how amHistory is linked to different tables within AM.
Since amHistory can be linked to any tables, it uses one field, LHISTOBJID to populate main Id from the linked tables. So anytime we want to link amHistory to a table such as amPortfolio for example, then need to get the main Id of amPortfolio’s table (i.e., LPORTFOLIOITEMID) and link it with LHISTOBJID. Similarly, if we want to join amHistory with amAsset table, we should use LASTID as the key to match to LHISTOBJID.
These two SQL examples below should be self-explanatory:
Joined with amPortfolio Table
SELECT * FROM amPortfolio p INNER JOIN amHistory h ON p.LPortfolioItemId = h.LHistObjId WHERE p.AssetTag = 'SN1234';
Joined with amAsset Table
SELECT * FROM amAsset a INNER JOIN amHistory h ON a.LAstId = h.LHistObjId WHERE a.AssetTag = 'SN1234';
Hope it helps.
Leave a Reply