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




Answer Summary:
aragorn.2003's query gets the correct column names for the generic ones (below): 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 Thanks!
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
In Addition to chucksteel, maybe this SQL could help you

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
Answered 04/21/2015 by: aragorn.2003
Red Belt

  • Thanks, that was exactly what I was looking for!
Please log in to comment

Answers

0
The names of the fields are stored in ASSET_FIELD_DEFINITION but I'm not sure if that is much help. You can certainly use that table as a reference for modifying the query but I'm not sure if there is any way to have the query pull in the names itself. It is certainly something to think about, though.

Answered 04/21/2015 by: chucksteel
Red Belt

  • Thanks for the response. I was going through that table as well as well as the Reports tables trying to map things to something reasonable, but it was too overwhelming. aragorn.2003 had a query that displays the fields above. Thanks again!
Please log in to comment
Answer this question or Comment on this question for clarity