Custom Notification Query!

Custom Notification Query!

Click here to associate this thread with a Package KB article.

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> [Dell KACE Appliances] >> K1000 Management Appliance >> Custom Notification Query!

Page: [1]

Message

<< Older Topic Newer Topic >>

Custom Notification Query! - 3/22/2010 4:09:30 AM

Jason_H

Posts: 5
Joined: 3/1/2010
Status: offline

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

Post #: 1

RE: Custom Notification Query! - 3/22/2010 9:05:39 AM

airwolf

Posts: 563
Joined: 7/13/2009
From: teh interwebs
Status: offline

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.

_____________________________

Andy Flesner, MCSA - Flesner.com
author.programmer.serveradmin.gamer

Follow Me on Twitter

(in reply to Jason_H)

Post #: 2