Modifying a custom report to include additional data
I need to produce a report of all Archived Assets that includes Make, Model & Serial number. I know the data still exists in the asset archive because I can drill down and manually gather that data. From the Assets menu I can View by Archived and the create a report that will show ID, Asset Name, Asset type etc. Based off some searches here I should be able to modify this query KACE created to include Make, Model & Serial Number.
IF anyone can point me int the the right direction on how to modify this please. Here is the query KACE created. Thank you in advance.
SELECT ASSET.ID, ASSET.NAME AS ASSET_NAME, T.NAME AS ASSET_TYPE, COALESCE(AC.NAME, 'None') AS ASSET_SUBTYPE,
IF(U.FULL_NAME != '', U.FULL_NAME, U.USER_NAME) AS USER,
T.MAPPED_TABLE, T.DETAIL_PAGE, ASSET.ASSET_DATA_ID, ASSET.MAPPED_ID,
M.NAME AS MACHINE_NAME,
ASSET.ASSET_TYPE_ID,
L.NAME AS LOCATION,
GROUP_CONCAT(DISTINCT D.NAME) AS DEPARTMENT,
GROUP_CONCAT(DISTINCT C.NAME) AS COST_CENTER,
ARCHIVE_ASSET_DEVICE.NAME AS ARCHIVED_MACHINE_NAME,
ASSET_STATUS.NAME AS ASSET_STATUS FROM ASSET
INNER JOIN ASSET_TYPE T ON T.ID = ASSET.ASSET_TYPE_ID
LEFT JOIN ASSET_CLASS AC ON AC.ID = ASSET.ASSET_CLASS_ID
LEFT JOIN USER ASSET_OWNER ON ASSET_OWNER.ID = ASSET.OWNER_ID
LEFT JOIN ASSET ASSET_OWNER_LOCATION ON ASSET_OWNER_LOCATION.ID = ASSET_OWNER.LOCATION_ID
LEFT JOIN USER_ROLE ASSET_OWNER_ROLE ON ASSET_OWNER_ROLE.ID = ASSET_OWNER.ROLE_ID
LEFT JOIN KBSYS.LOCALE_BROWSER ASSET_OWNER_LOCALE ON ASSET_OWNER_LOCALE.ID = ASSET_OWNER.LOCALE_BROWSER_ID
LEFT JOIN USER U ON U.ID = ASSET.OWNER_ID
LEFT JOIN ASSET L ON L.ID = ASSET.LOCATION_ID
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ARCHIVE_ASSET_DEVICE ON ASSET.ID = ARCHIVE_ASSET_DEVICE.ASSET_ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_FIELD_DEFINITION F5 ON F5.ASSET_TYPE_ID = ASSET.ASSET_TYPE_ID AND F5.FIELD_TYPE = 'ASSET_2'
LEFT JOIN ASSET_ASSOCIATION J5 ON J5.ASSET_FIELD_ID = F5.ID AND J5.ASSET_ID = ASSET.ID
LEFT JOIN ASSET D ON D.ID = J5.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_FIELD_DEFINITION F6 ON F6.ASSET_TYPE_ID = ASSET.ASSET_TYPE_ID AND F6.FIELD_TYPE = 'ASSET_3'
LEFT JOIN ASSET_ASSOCIATION J6 ON J6.ASSET_FIELD_ID = F6.ID AND J6.ASSET_ID = ASSET.ID
LEFT JOIN ASSET C ON C.ID = J6.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_FIELD_DEFINITION F1 ON F1.ASSET_TYPE_ID = ASSET.ASSET_TYPE_ID AND F1.FIELD_TYPE = 'ASSET_1'
LEFT JOIN ASSET_ASSOCIATION J1 ON J1.ASSET_FIELD_ID = F1.ID AND J1.ASSET_ID = ASSET.ID
LEFT JOIN ASSET LA ON LA.ID = J1.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_BARCODE_JT ABJT ON ABJT.ASSET_ID = ASSET.ID
LEFT JOIN BARCODE ON BARCODE.ID = ABJT.BARCODE_ID
LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID
WHERE 1=1 AND ASSET.ID != 1 AND ASSET.ASSET_TYPE_ID NOT IN (10000,10001,10200,7,10002) AND ASSET.ARCHIVE IN('PENDING', 'COMPLETED') GROUP BY ASSET.ID order by ASSET_NAME
Answers (1)
Make and Model are in the ASSET_DATA_5 table, so you will need to add a join to that table:
LEFT JOIN ASSET_DATA_5 DATA on DATA.ID = ASSET.ASSET_DATA_ID
I'm fairly certain that the make a model fields are part of the standard device asset type and on my appliance those fields are FIELD_34 and FIELD_35, respectively. You need to add those to the select portion of the query:
DATA.FIELD_34 as 'Make',
DATA.FIELD_35 as 'Model',
On my appliance, the asset's name is the serial number, if that isn't the case on yours, then did you add a custom field to store it? If so, then you need to determine what the field ID is for your serial number column in the database. This query will show you the fields for the device asset type:
SELECT * FROM ORG1.ASSET_FIELD_DEFINITION WHERE ASSET_TYPE_ID = 5;
Once you have identified the column name, e.g. FIELD_12345, you can add that to the select portion of the main report query:
DATA.FIELD_12345 as 'Serial Number'
Comments:
-
Thank you for this response! The asset name in my org isn't always the SN because a naming convention wasn't established until I started here. So moving forward, yes, the asset name does now contain the SN.
The purpose of this report is for accounting and compliance to be able to pull and audit easily. So having this data in one report will have tremendous value.
I will dig into your suggestions and modify the SQL query. - JonPillon 4 years ago-
We also have our mapped inventory field set to BIOS_SERIAL_NUMBER and the matching asset field to NAME. This links the asset to the inventory based on the serial number which isn't going to change. To me this makes more sense because the asset is a physical piece of equipment that is unlikely to change, whereas the inventory record is a combination of hardware and software that will change over time. - chucksteel 4 years ago
-
I downloaded a trial of Toad Data point to look into each database and see where the data I wanted to produce was at. Asset table has Archive, Archive Date and Archive Reason.
Machine Table has CS_Manufacturer, CS_Mode & BIOS Serial Number.
I wanted to go a bit further and see if I could create the query in Toad and dump it into the SMA reporting into a new SQL query. I was able to get it to produce a report, but it was blank.
Im VERY new to SQL BTW, Im just trying to wrap my head around it all. - JonPillon 4 years ago -
I highly recommend MySQL Workbench for looking at the tables, it works well and is free.
The MACHINE table won't work for you because it only contains devices that are in inventory currently. - chucksteel 4 years ago
-
Thanks Chuck, After using some KB articles I was able to get MySQL connected. I now can see a pile of tables. I would never have known that Asset_Data_5 had model data because the column names are FIELD_9921 - 9939.
I am way out of my element here so maybe you can point me to some kind of resource that can help me understand these tables and queries. YouTube has some stuff but nothing of help to me so far. How do you find out this particular table contains this data? There are 7 tables of asset data. - JonPillon 4 years ago-
Most of my knowledge comes from looking at the tables and creating custom reports. The tables are actually laid out very well (compared to other systems I have used) so following data from one place to another is generally easy.
For instance, naming is very consistent between tables. If tableA references a row in tableB it will have a column named tableB_ID, so join statements are easy to figure out:
join tableB on tableB.ID = tableA.tableB_ID
For further explanation of joining tables: https://mariadb.com/kb/en/library/join-syntax/
As for the asset tables, those make sense, too. For every asset type there is an associated table that contains the data for that type. Devices are type 5, so the data is in ASSET_DATA_5. You can find the definitions of asset types in ASSET_TYPE. Similarly, you can find the definitions of asset fields in ASSET_FIELD_DEFINITION.
I don't have any good recommendations for learning this stuff because I learn by doing. There are tons of resources online if you prefer to learn by books or videos, that's just not my style. I tend to Google a lot and reference the MariaDB website frequently. - chucksteel 4 years ago
