/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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