My problem is I do not know SQL and Kace 1000 v 6.0 reporting does not connect Asset types.  I have a group of assets in Asset type Cost Center 7000.  These were assets purchased after 2/1/2014.  Each month I have to run a report what assets changed to different cost centers along with the dollars.  Right now it I have been creating 4 separate reports for different Asset Types.  Computer, Misc Hardware and Peripherals,Network devices (routers,switches,telephones) and printers.  All 4 asset types have the same fields to pull from - Serial or Service tag, Make Model, total purchase price,cost center, and date purchased.

If I cannot create a SQL report my last resort will be is to combine all the above types into 1 type to make it work.  Any help will be greatly appeciated
2 Comments   [ + ] Show Comments

Comments

  • I'm a little confused because you said you have an asset type of "Cost Center 7000" but they you also have four other asset types: Computer, Misc Hardware and Peripherals, Network Devices, Printers. Do these four asset types all link to the "Cost Center 7000" asset type which contains different cost centers and you want to create a report showing the change in the cost center?
  • yes - you are right - Asset Type is Cost Center
    • Can you post the SQL queries for the four reports that you have?
      • asset type Misc

        SELECT A61.NAME AS FIELD_61, ASSET_DATA_12.FIELD_57 AS FIELD_57, ASSET_DATA_12.FIELD_58 AS FIELD_58, ASSET_DATA_12.FIELD_62 AS FIELD_62, ASSET_DATA_12.FIELD_67 AS FIELD_67, ASSET_DATA_12.FIELD_258 AS FIELD_258, ASSET_DATA_12.FIELD_240 AS FIELD_240, ASSET_DATA_12.FIELD_10009 AS FIELD_10009, ASSET_DATA_12.FIELD_242 AS FIELD_242, ASSET_DATA_12.FIELD_60 AS FIELD_60, ASSET_DATA_12.FIELD_10005 AS FIELD_10005 FROM ASSET_DATA_12 LEFT JOIN ASSET ON ASSET_DATA_12.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=12 LEFT JOIN ASSET_ASSOCIATION J61 ON J61.ASSET_ID = ASSET.ID AND J61.ASSET_FIELD_ID=61
        LEFT JOIN ASSET A61 ON A61.ID = J61.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_1 AD61 ON AD61.ID = A61.ASSET_DATA_ID
        WHERE ((ASSET_DATA_12.FIELD_62 > '2014-02-01')) ORDER BY FIELD_10009, FIELD_10005

        Asset Type Printers

        SELECT A49.NAME AS FIELD_49, ASSET_DATA_8.FIELD_46 AS FIELD_46, ASSET_DATA_8.FIELD_47 AS FIELD_47, ASSET_DATA_8.FIELD_74 AS FIELD_74, ASSET_DATA_8.FIELD_50 AS FIELD_50, ASSET_DATA_8.FIELD_66 AS FIELD_66, A226.NAME AS FIELD_226, ASSET_DATA_8.FIELD_302 AS FIELD_302, ASSET_DATA_8.FIELD_255 AS FIELD_255, ASSET_DATA_8.FIELD_257 AS FIELD_257, A10006.NAME AS FIELD_10006 FROM ASSET_DATA_8 LEFT JOIN ASSET ON ASSET_DATA_8.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=8 LEFT JOIN ASSET_ASSOCIATION J49 ON J49.ASSET_ID = ASSET.ID AND J49.ASSET_FIELD_ID=49
        LEFT JOIN ASSET A49 ON A49.ID = J49.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_1 AD49 ON AD49.ID = A49.ASSET_DATA_ID
        LEFT JOIN ASSET_ASSOCIATION J226 ON J226.ASSET_ID = ASSET.ID AND J226.ASSET_FIELD_ID=226
        LEFT JOIN ASSET A226 ON A226.ID = J226.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_22 AD226 ON AD226.ID = A226.ASSET_DATA_ID
        LEFT JOIN ASSET_ASSOCIATION J10006 ON J10006.ASSET_ID = ASSET.ID AND J10006.ASSET_FIELD_ID=10006
        LEFT JOIN ASSET A10006 ON A10006.ID = J10006.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_4 AD10006 ON AD10006.ID = A10006.ASSET_DATA_ID
        WHERE ((ASSET_DATA_8.FIELD_50 > '2014-02-01') AND (ASSET_DATA_8.FIELD_46 like '%HP%')) ORDER BY FIELD_302, FIELD_10006

        Asset Type Network Devices (Routers,Switches,Telephones)

        SELECT A49.NAME AS FIELD_49, ASSET_DATA_8.FIELD_46 AS FIELD_46, ASSET_DATA_8.FIELD_47 AS FIELD_47, ASSET_DATA_8.FIELD_74 AS FIELD_74, ASSET_DATA_8.FIELD_50 AS FIELD_50, ASSET_DATA_8.FIELD_66 AS FIELD_66, A226.NAME AS FIELD_226, ASSET_DATA_8.FIELD_302 AS FIELD_302, ASSET_DATA_8.FIELD_255 AS FIELD_255, ASSET_DATA_8.FIELD_257 AS FIELD_257, A10006.NAME AS FIELD_10006 FROM ASSET_DATA_8 LEFT JOIN ASSET ON ASSET_DATA_8.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=8 LEFT JOIN ASSET_ASSOCIATION J49 ON J49.ASSET_ID = ASSET.ID AND J49.ASSET_FIELD_ID=49
        LEFT JOIN ASSET A49 ON A49.ID = J49.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_1 AD49 ON AD49.ID = A49.ASSET_DATA_ID
        LEFT JOIN ASSET_ASSOCIATION J226 ON J226.ASSET_ID = ASSET.ID AND J226.ASSET_FIELD_ID=226
        LEFT JOIN ASSET A226 ON A226.ID = J226.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_22 AD226 ON AD226.ID = A226.ASSET_DATA_ID
        LEFT JOIN ASSET_ASSOCIATION J10006 ON J10006.ASSET_ID = ASSET.ID AND J10006.ASSET_FIELD_ID=10006
        LEFT JOIN ASSET A10006 ON A10006.ID = J10006.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_4 AD10006 ON AD10006.ID = A10006.ASSET_DATA_ID
        WHERE ((ASSET_DATA_8.FIELD_50 > '2014-02-01') AND (ASSET_DATA_8.FIELD_46 like '%HP%')) ORDER BY FIELD_302, FIELD_10006

        Asset Computer

        SELECT A299.NAME AS FIELD_299, ASSET_DATA_5.FIELD_159 AS FIELD_159, ASSET_DATA_5.FIELD_224 AS FIELD_224, A25.NAME AS FIELD_25, ASSET_DATA_5.FIELD_22 AS FIELD_22, ASSET_DATA_5.FIELD_27 AS FIELD_27, ASSET_DATA_5.FIELD_189 AS FIELD_189, A228.NAME AS FIELD_228, ASSET_DATA_5.FIELD_24 AS FIELD_24, ASSET_DATA_5.FIELD_229 AS FIELD_229, ASSET_DATA_5.FIELD_10008 AS FIELD_10008 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 J299 ON J299.ASSET_ID = ASSET.ID AND J299.ASSET_FIELD_ID=299
        LEFT JOIN ASSET A299 ON A299.ID = J299.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_3 AD299 ON AD299.ID = A299.ASSET_DATA_ID
        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_ASSOCIATION J228 ON J228.ASSET_ID = ASSET.ID AND J228.ASSET_FIELD_ID=228
        LEFT JOIN ASSET A228 ON A228.ID = J228.ASSOCIATED_ASSET_ID
        LEFT JOIN ASSET_DATA_22 AD228 ON AD228.ID = A228.ASSET_DATA_ID
        WHERE ((ASSET_DATA_5.FIELD_189 > '2014-02-01')) ORDER BY FIELD_299, FIELD_10008
Please log in to comment

Answers

0
If you want the 4 reports merged into one report and they have the same fields (number and type), you can union the separate queries into one report.  For the syntax of union, check the documentation at http://dev.mysql.com/doc/refman/5.6/en/union.html.

Answered 07/24/2014 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity