Database query - Calendar keywords expried

[John O'Mullane]

Hi,
Anyone with Static Calendar keyword will know that there is always a risk of reaching the end date.
I want to write a little SQL job that will alert me when a static keyword is reaching it's last set date.

Anyone out there implemented something like this?
If I knew the table that contained the keywords that would be a good start.
I did looked at the Database Schema under docs.automic.com but not so clear there which table I need to query.

John.

[laura_albrecht_automic]

Is there a reason you aren't using the keywords in UC_CLIENT_SETTINGS?  The CALE_WARN_NOTIFICATION and CALE_WARN_LEVEL would notify you of any keywords that are expiring.  Just curious.

[Legacy User]

Hello John,

Calendar keywords are stored in the OKB table.

Here is an example query that will return the CALE object, the keyword's name and its validity's end date if it's inferior to the end of this year :

select OH_Name as 'Calendar', OKB_Name as 'keyword', OKB_ValidTo as 'Valid until'
FROM OH, OKB
WHERE OH_Idnr=OKB_OH_idnr
AND OKB_ValidTo < '2016-12-31 23:59:59:999';

Best regards,
Antoine

[John O'Mullane]

Thanks LauraAlbrecht608310  and Antoine_Sauteron

LauraAlbrecht608310
I didn't know that existed so I'll look into using that, thanks.

[John O'Mullane]

LauraAlbrecht608310
How can I be sure that this check is done. I have some Calendar keywords in DEV that I know have expired. I restarted the engine but my NOTIFICATION did not generate.

This is the way I set it up...

https://us.v-cdn.net/5019921/uploads/editor/mh/euyaq25x8rdb.png" width="406"> 

[laura_albrecht_automic]

I don't believe it will go back in time.  If the keywords you have in DEV are ALREADY expired then I don't think it catches that.  Once you implement / populate the values for those 2 keywords - going forward any keywords that are going to expire in 60 days will then alert.

So this "fix" is more of a planning for the future kind of thing rather than a catch up kind of thing.  If that makes sense.

Easiest way to test would be to create a static keyword that expires in a few days and set the CALE_WARN_LEVEL to a lower number.  I'm pretty sure the alert comes out at the period turnaround time, which by default is midnight.

[John O'Mullane]

Great, thanks, will try that.

[Shashank Singh]

LauraAlbrecht608310 Are these keywords CALE_WARN_NOTIFICATION and CALE_WARN_LEVEL available only in UC client (when we login to client 0 with user id UC) since I am unable to see these keywords in UC_CLIENT_SETTINGS variable when I logged in through my ID in client 0. 

P.S. - We have these calendar notifications set and we receive alerts. I am just not able to figure out where these keywords are set.

[laura_albrecht_automic]

CALE_WARN_NOTIFICATION and CALE_WARN_LEVEL are keywords in the UC_CLIENT_SETTINGS variable.  This variable exists in each client and can be set differently for each client.

So I'd look for UC_CLIENT_SETTINGS in other clients - not client 0.