Best Practices : KACE Reporting - How to Use Dates for Filtering Assets

KACE Reporting - How to Use Dates for Filtering Assets

Hopefully someone can help me with a dumb question...

I am trying to create daily reports that will see if an asset (in this case a maintenance contract) is within 90 days of expiring.

I have already created the asset (and set the asset field for expiring for DATE type) and was creating my reports with the wizard.

When i get to filters and try to create a filter that is >91 days and <89 days, i can't seem to find that option under operator that would allow that. If i pick the < or >, it gives me a static date selector, which does no good.

If i use something like "During Following Days", it will just keep shooting out that report until the 90 days is over, which also isn't good.

Any ideas???

1 Comment [ + ] Show comment

Always remember if the specific area of KACE that you are building your SQL statement does not have the correct operand, there may be another area that will allow you to build the criteria from a wizard i.e. reporting does not give the option but building a ticket rule does. So just rip and replace the relevant SQL code ;o) - Hobbsy 6 years ago

Answers (2)

Posted by: chucksteel 6 years ago

The reporting wizard doesn't do a good job of handling dates. Once you create the report there should be an option to edit the SQL. Post the query here and we can show you how to correct it.

Comments:

Thanks Chuck!

SELECT ASSET.NAME AS ASSET_NAME, ASSET_DATA_10201.FIELD_10062 AS FIELD_10062, ASSET_DATA_10201.FIELD_10063 AS FIELD_10063, ASSET_DATA_10201.FIELD_10064 AS FIELD_10064 FROM ASSET_DATA_10201 LEFT JOIN ASSET ON ASSET_DATA_10201.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=10201 LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID WHERE (( date(ASSET_DATA_10201.FIELD_10064) <= date_add(curdate(), interval 10 day) and date(ASSET_DATA_10201.FIELD_10064) > curdate())) ORDER BY ASSET_NAME - PatrickFeller 6 years ago
- This portion is what we need to change:
  WHERE (( date(ASSET_DATA_10201.FIELD_10064) <= date_add(curdate(), interval 10 day) and date(ASSET_DATA_10201.FIELD_10064) > curdate()))
  
  If you want to return assets where the date is exactly 90 days from now, then you just need
  WHERE DATE(ASSET_DATA_10201.FIELD_10064) = DATE_ADD(DATE(NOW(), INTERVAL 90 DAY))
  
  If you want all those expiring within 90 days:
  WHERE DATE(ASSET_DATA_10201.FIELD_10064) < DATE_ADD(DATE(NOW(), INTERVAL 90 DAY))
  
  There isn't really a need to specify the between condition, unless you want to exclude assets that are past their expiration date and only report on those expiring in the next 90 days. In that case, I would use the between operator:
  WHERE DATE(ASSET_DATA_10201.FIELD_10064) BETWEEN DATE(NOW()) and DATE_ADD(DATE(NOW(), INTERVAL 90 DAY)) - chucksteel 6 years ago

Posted by: Drosses 6 years ago

I use this, hope it is useful for you

AND LAST_INVENTORY >= DATE '2018-10-05'

Answer this question

Posted by:

View more:

Best Practices Questions

question