Custom Notification Query!
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
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
Answers (1)
Please log in to answer
Posted by:
airwolf
14 years ago
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'.
To find table and field numbers, use the MySQL Query Browser tool.
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
