I have created an Asset called "Domains" that, you guessed it, holds all the information for our currently active Domains.

I want to setup a custom notification that will e-mail me when "todays date" is within 30 days of the Domains Expiry date.

I have spoken with the trainer and he said its possible but we tried many different MYSQL commands like "$today" "$date"

but none of them work.

Does anyone have any ideas?

Cheers

Jason
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
You're going to have to create a custom SQL query. The function you'll need to use is DATEDIFF. I can't give you precise code, because the asset data table numbers are totally dependent upon the way you've setup assets in your KBOX.

The following assumes ASSET_DATA_1 is the proper table for your Domains (which it won't be, because the first 5 or so are builtin to each KBOX - yours may be ASSET_DATA_5, ASSET_DATA_9, etc... depending on how many asset types you have) and the field number for the domain expiration date is FIELD_1 (which also won't be true, you'll have to figure this out). Your domain expiration date field also needs to be in the format 'YYYY-MM-DD HH:MM:SS'.


SELECT * FROM ASSET A
JOIN ASSET_DATA_1 AD ON (A.ASSET_DATA_ID = AD.ID)
WHERE DATEDIFF(AD.FIELD_1,NOW()) < 30


To find table and field numbers, use the MySQL Query Browser tool.
Answered 03/22/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity