Can somebody help me to create a Kace1000 SQL Report ?
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
Answers (1)
Please log in to answer
Posted by:
grayematter
9 years ago
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.
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 - gskielvig 9 years ago