a Monitor service for SQL table

Question

Question

Paster-Bachar Gadi · May 23, 2018

#Ensemble #SQL #Monitoring

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

Discussion (5)0

Log in or sign up to continue

Robert Cemper · May 23, 2018

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.

0 0

Eduard Lebedyuk · May 23, 2018

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.

0 0

Julian Matthews · May 24, 2018

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

0 0

score 0 · Answer 1 · 2018-05-28T04:04:39-04:00

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

score 0 · Answer 2 · 2018-05-29T07:07:53-04:00

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.