KACE Reporting - How to Create a Report for Assets When They Change Status
I am trying to figure out how to create a encompassing report via the Report Wizard that would let me generate a daily report (if any changes) when an Asset's status changes from Active to Repair/Disposed as well as (if Repair) when it returns to Active. Is that doable in the same report and if so what are some suggestions as far as setup.
Answers (1)
Sure, but you may have to build it with a SQL report rather than the Wizard. When reports require a complex query I like to connect to the KACE database using FlySpeed ( https://www.activedbsoft.com/download-querytool.html) because it creates the SQL in the native DB language for the DB you are connecting to.
Here is a quick query that will give you the changes for the past day. You can alter "Interval 1 Day" to however many days you want.
Select
ASSET_HISTORY.TIME,
ASSET_HISTORY.NAME,
ASSET_HISTORY.USER_ID,
ASSET_HISTORY.USER_TEXT,
ASSET_HISTORY.CHANGE_TYPE,
ASSET_HISTORY.FIELD_NAME,
ASSET_HISTORY.VALUE1,
ASSET_HISTORY.VALUE2,
ASSET_HISTORY.FRIENDLY_FIELD_NAME
From
ASSET_HISTORY
Where
ASSET_HISTORY.TIME > Date_Sub(Now(), Interval 1 Day) And
((ASSET_HISTORY.VALUE2 = 'Repair') Or
(ASSET_HISTORY.VALUE2 = 'Disposed') Or
(ASSET_HISTORY.VALUE2 = 'Active'))
Order By
TIME
