KACE reporting script
Hello,
We currently have a script that everyday at 7am emails a set of people. I took this script to see if I could get a list of assets that were updated within the last 365 days so I changed INTERVAL 24 HOUR to INTERVAL 365 DAY but got the same result when exporting both reports. What am I doing wrong?
SELECT ASSET_HISTORY.*
FROM ORG1.ASSET_HISTORY
JOIN ASSET on ASSET.ID = ASSET_HISTORY.ASSET_ID
WHERE ASSET.ASSET_TYPE_ID = 5
AND TIME > DATE_SUB(NOW(), INTERVAL 24 HOUR) AND USER_ID != 0
Answers (3)
Top Answer
I tried your script and changed it from 24 HOUR to 365 DAY and it seems to work for me, can you try it in your appliance please?
SELECT ASSET_HISTORY.*
FROM ASSET_HISTORY
JOIN ASSET on ASSET.ID = ASSET_HISTORY.ASSET_ID
WHERE ASSET.ASSET_TYPE_ID = 5
AND TIME > DATE_SUB(NOW(), INTERVAL 365 DAY) AND USER_ID != 0
When I changed it to 365 DAY, it only showed back until September 24 which Definitely isn't right, Does KACE only go back so far maybe?
Comments:
-
Maybe check your retention settings for the history. In default it is set to 3 month but would need to be increased if you would like to have the whole year displayed. You can find it in Settings/History/Subscriptions/Assets. - sven.hain 3 weeks ago
-
Oh but if i change it now, I most likely won't be able to see edits past that 3 month interval correct? - jrichard21 3 weeks ago
-
Yes that is right unfortunately. - sven.hain 3 weeks ago
-
Thank you for the info and assistance - jrichard21 3 weeks ago
So this is an issue with the SMA that Quest need to address if they want to be taken seriously for asset management. I am getting the same issue and that is with the retention set to 12 months, so I am going to suggest that something in the SMA is not working as it should.
I ran a report for any assets set to disposed the last 12 months and got identical results to the last 30 days.
So I have taken to recording key asset history into a field in each asset on a daily basis, so any change in location, status or other key asset data fields is written into a text field. This means I have history captured indefinitely that I can then report on.
Comments:
-
I have to test it in my lab since I have set it only to 3 months but as of now I can see the last 3 months of changes in my report back to 5th of September. Might be good to open a support ticket to check, why no data is displayed older than 30 days. - sven.hain 2 weeks ago