a Monitor service for SQL table

Hi All

I'm looking for the a simple-quick-easy solution to monitor a SQL table thought Ensemble.

I have a process that update a DataBase ,a scheduled task that runs every night  (Not Ensemble)

In the end it updates a table (replica_status) with a new recored with two fileds:  Id, DateTime

I looked around the community but didn't find an answerd case.

I'm thinking on a Task that will run a sql outboud adapter BO that checks that table and send a alert if no new record was created yesterday

is this the right approach or is there's a better solution?

Thanks Gadi

Answers

If your Table is on a Caché instance you may use the DSTIME feature that is also used to update DeepSee cubes.
You track changes, deletes, inserts and can act appropriately. 

I'd run a BS with SQL inbound adapter called every 86400 seconds. Query is some SELECT Count(*) ...

To get optimal WHERE condition you'll probably need to answer these questions:

  • Are records immutable?
  • Can they be updated?
  • Can records be deleted?
  • Can records be inserted for a past/future dates?

If it's a local table you can run BS without adapter and just use embedded SQL.

I use a similar approach to Edwards answer (using a BS that runs every x seconds), but for alerting when freespace on my drives fall below a specified value.

I'm not sure if my approach is the most efficient to your requirement, but I would do something like this in the Service:

  • Query the table and sort it so you get the most recent result at the top (I'm assuiming the dateTime field is suitable for this)
  • Take a Snapshot of the results, and and get the first datetime result.
  • Compare it to the system datetime
  • Send an alert to Ens.Alert if the difference is more than 24 hours

Hi Julian, Eduard and Robert

thanks very much for your answers.

I took all answers and constructed a solution for them that consist a BS,BO and a task.

I have created a sql inbuond adapter that retrives the last record (select top 1 desc...)  and datediff by hours on getdate.

In the BS I created property in settings (Overraide SETTINGS) for the threshold logic condition that if true calls an Email adapter (BO) that send the alert to whom it concern.

I read in some cases that for process that should fire once a day it is preffrealbe to use a task, so I used a %SYS.Task.Definition that activates that BS.

Thanks for all the help

Gadi

Hi Gadi.

Glad to hear you have something in place now.

I guess a task is better for specifying the exact time for the event to run rather than every x number of seconds, as the timing could go out of sync if the service was restarted at any point.

For the setup I have, because I want to check the status of the drive free space (and I also check a specific folders files to alert if any of them have existed for more than 5 minutes) it makes sense to just let the Call interval run every x seconds.