KACE Product Support

My organization has been using KACE for years now.  I started here recently and was asked to create some reports outside of KACE.  I have connected to with ODBC and am attempting to write some queries to create some simple Reports/Dashboards from the reports that are generated within KACE using the wizard.  I am fairly new to using K1000 but have used SQL before and understand the basics of query writing.

I am having issues getting the information I need from ORG1 because all the generically named 'ASSET_DATA_' tables and 'FIELD_' column names. We have over 50 asset tables labelled ASSET_DATA_1 to ASSET_DATA_56, each containing fields named "FIELD_#", where #  is an actual number.

On top of this, the SQL generated for the report aliases the fields as the same FIELD name like this, for example:   "SELECT FIELD_23 AS "FIELD_23" FROM ASSET_DATA_32..."  or "...LEFT JOIN ASSET A174  ON  A174.ID = J174.ASSOCIATED_ASSET_ID..." .  See the SQL at the end for a full example.

I know this is a long shot, but is there a query or table I could use (join) to translate these field / tables names to something more user friendly?   The SQL I am working from is super complex and time consuming to go through.  Maybe someone has already addressed this issue, but I can't find anything on it.  Any help or guidance is appreciated.

EXAMPLE OF SQL:

SELECT    A174.NAME                 AS FIELD_174,
          ASSET_DATA_22.FIELD_159   AS FIELD_159,
          A161.NAME                 AS FIELD_161,
          A363.NAME                 AS FIELD_363,
          ASSET_DATA_22.FIELD_160   AS FIELD_160,
          A162.NAME                 AS FIELD_162,
          ASSET.ID                  AS ASSET_ID,
          ASSET_DATA_22.FIELD_10036 AS FIELD_10036,
          A173.NAME                 AS FIELD_173,
          A177.NAME                 AS FIELD_177,
          ASSET_DATA_22.FIELD_171   AS FIELD_171,
          ASSET_DATA_22.FIELD_10035 AS FIELD_10035,
          A163.NAME                 AS FIELD_163,
          A164.NAME                 AS FIELD_164,
          A10038.NAME               AS FIELD_10038,
          ASSET.NAME                AS ASSET_NAME,
          ASSET_DATA_22.FIELD_167   AS FIELD_167,
          ASSET_DATA_22.FIELD_176   AS FIELD_176,
          ASSET_DATA_22.FIELD_170   AS FIELD_170,
          A172.NAME                 AS FIELD_172,
          ASSET_DATA_22.FIELD_175   AS FIELD_175,
          ASSET_DATA_22.FIELD_10022 AS FIELD_10022,
          AD161.FIELD_92            AS A161_FIELD_92,
          AD161.FIELD_320           AS A161_FIELD_320,
          AD161.FIELD_93            AS A161_FIELD_93,
          AD161.FIELD_106           AS A161_FIELD_106,
          A161_P.NAME               AS A161_FIELD_374,
          AD161.FIELD_105           AS A161_FIELD_105,
          AD161.FIELD_100           AS A161_FIELD_100,
          AD161.FIELD_99            AS A161_FIELD_99,
          AD161.FIELD_240           AS A161_FIELD_240,
          AD161.FIELD_98            AS A161_FIELD_98,
          AD161.FIELD_96            AS A161_FIELD_96,
          AD161.FIELD_97            AS A161_FIELD_97,
          AD161.FIELD_94            AS A161_FIELD_94,
          AD161.FIELD_95            AS A161_FIELD_95,
          AD162.FIELD_157           AS A162_FIELD_157,
          AD163.FIELD_77            AS A163_FIELD_77,
          AD164.FIELD_101           AS A164_FIELD_101,
          AD10038.FIELD_10037       AS A10038_FIELD_10037,
          AD363.FIELD_369           AS A363_FIELD_369,
          AD363.FIELD_371           AS A363_FIELD_371,
          AD172.FIELD_80            AS A172_FIELD_80,
          AD172.FIELD_85            AS A172_FIELD_85,
          AD172.FIELD_81            AS A172_FIELD_81,
          AD172.FIELD_79            AS A172_FIELD_79,
          A172_A356.NAME            AS A172_FIELD_356,
          AD172.FIELD_102           AS A172_FIELD_102,
          AD172.FIELD_86            AS A172_FIELD_86,
          AD172.FIELD_88            AS A172_FIELD_88,
          AD172.FIELD_84            AS A172_FIELD_84,
          AD172.FIELD_82            AS A172_FIELD_82,
          AD172.FIELD_87            AS A172_FIELD_87,
          AD172.FIELD_83            AS A172_FIELD_83,
          AD173.FIELD_148           AS A173_FIELD_148,
          AD174.FIELD_152           AS A174_FIELD_152,
          AD174.FIELD_151           AS A174_FIELD_151,
          AD177.FIELD_239           AS A177_FIELD_239,
          A177_A10032.NAME          AS A177_FIELD_10032,
          AD177.FIELD_236           AS A177_FIELD_236,
          AD177.FIELD_230           AS A177_FIELD_230,
          AD177.FIELD_232           AS A177_FIELD_232,
          AD177.FIELD_234           AS A177_FIELD_234,
          AD177.FIELD_233           AS A177_FIELD_233,
          A177_A231.NAME            AS A177_FIELD_231,
          AD177.FIELD_238           AS A177_FIELD_238,
          @LIMITCT                  AS LIMIT_CT,
          ASSET.ID                  AS TOPIC_ID
FROM      ASSET_DATA_22
LEFT JOIN ASSET
ON        ASSET_DATA_22.ID = ASSET.ASSET_DATA_ID
AND       ASSET.ASSET_TYPE_ID=22
LEFT JOIN ASSET_ASSOCIATION J174
ON        J174.ASSET_ID = ASSET.ID
AND       J174.ASSET_FIELD_ID=174
LEFT JOIN ASSET A174
ON        A174.ID = J174.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_20 AD174
ON        AD174.ID = A174.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J161
ON        J161.ASSET_ID = ASSET.ID
AND       J161.ASSET_FIELD_ID=161
LEFT JOIN ASSET A161
ON        A161.ID = J161.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_1 AD161
ON        AD161.ID = A161.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J363
ON        J363.ASSET_ID = ASSET.ID
AND       J363.ASSET_FIELD_ID=363
LEFT JOIN ASSET A363
ON        A363.ID = J363.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_53 AD363
ON        AD363.ID = A363.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J162
ON        J162.ASSET_ID = ASSET.ID
AND       J162.ASSET_FIELD_ID=162
LEFT JOIN ASSET A162
ON        A162.ID = J162.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_21 AD162
ON        AD162.ID = A162.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J173
ON        J173.ASSET_ID = ASSET.ID
AND       J173.ASSET_FIELD_ID=173
LEFT JOIN ASSET A173
ON        A173.ID = J173.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_19 AD173
ON        AD173.ID = A173.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J177
ON        J177.ASSET_ID = ASSET.ID
AND       J177.ASSET_FIELD_ID=177
LEFT JOIN ASSET A177
ON        A177.ID = J177.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_29 AD177
ON        AD177.ID = A177.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J163
ON        J163.ASSET_ID = ASSET.ID
AND       J163.ASSET_FIELD_ID=163
LEFT JOIN ASSET A163
ON        A163.ID = J163.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_12 AD163
ON        AD163.ID = A163.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J164
ON        J164.ASSET_ID = ASSET.ID
AND       J164.ASSET_FIELD_ID=164
LEFT JOIN ASSET A164
ON        A164.ID = J164.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_17 AD164
ON        AD164.ID = A164.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J10038
ON        J10038.ASSET_ID = ASSET.ID
AND       J10038.ASSET_FIELD_ID=10038
LEFT JOIN ASSET A10038
ON        A10038.ID = J10038.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_56 AD10038
ON        AD10038.ID = A10038.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION J172
ON        J172.ASSET_ID = ASSET.ID
AND       J172.ASSET_FIELD_ID=172
LEFT JOIN ASSET A172
ON        A172.ID = J172.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_4 AD172
ON        AD172.ID = A172.ASSET_DATA_ID
LEFT JOIN ASSET_HIERARCHY A161_H
ON        A161_H.CHILD_ASSET_ID = A161.ID
LEFT JOIN ASSET A161_P
ON        A161_P.ID=A161_H.PARENT_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION A172_J356
ON        A172_J356.ASSET_ID = A172.ID
AND       A172_J356.ASSET_FIELD_ID=356
LEFT JOIN ASSET A172_A356
ON        A172_A356.ID = A172_J356.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_27 A172_AD356
ON        A172_AD356.ID = A172_A356.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION A177_J10032
ON        A177_J10032.ASSET_ID = A177.ID
AND       A177_J10032.ASSET_FIELD_ID=10032
LEFT JOIN ASSET A177_A10032
ON        A177_A10032.ID = A177_J10032.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_19 A177_AD10032
ON        A177_AD10032.ID = A177_A10032.ASSET_DATA_ID
LEFT JOIN ASSET_ASSOCIATION A177_J231
ON        A177_J231.ASSET_ID = A177.ID
AND       A177_J231.ASSET_FIELD_ID=231
LEFT JOIN ASSET A177_A231
ON        A177_A231.ID = A177_J231.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_28 A177_AD231
ON        A177_AD231.ID = A177_A231.ASSET_DATA_ID
LEFT JOIN
          (
                 SELECT @limitct :=0) T
ON        1=1
ORDER BY  FIELD_161,
          FIELD_159

select at.NAME, concat('ASSET_DATA_', at.ID, '.FIELD_', afd.ID) as 'Table/Field',
       afd.FIELD_NAME as 'Field Name', afd.FIELD_TYPE 'as Field Type', afd.FIELD_VALUES as 'Field Values', afd.DEFAULT_VALUE as 'Default Value'
  from ASSET_FIELD_DEFINITION as afd, ASSET_TYPE as at
 where afd.ASSET_TYPE_ID = at.ID
order by at.NAME, afd.ORDINAL