/build/static/layout/Breadcrumb_cap_w.png
10/01/2018 185 views
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

Comments

  • 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)

All Answers

0
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.
Answered 10/02/2018 by: chucksteel
Red Belt

  • 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
    • 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))
0
I use this, hope it is useful for you

AND LAST_INVENTORY >= DATE '2018-10-05'
Answered 10/05/2018 by: Drosses
White Belt