Custom alert if table has NOT been updated?

How would you decide on new input? Rows inserted? Rows updated?  Rows deleted? What happens if they delete a row then add a row?

How would you compare the difference?

You could set up a custom alert to return table name and a row count or a boolean (true/false) or something similar, but I'm not sure that would really address your requirement.

gcp,

We're just looking for rows inserted. This is just an alert to let us know that something is wrong. If this table hasn't had any input in over 15 minutes, something has gone wrong.

Best,

mh

Here is a reference that you might check out. If this were me, I'd limit it specifically to the table in question and do a diff between getdate (current system time) and last_user_update in minutes.

If that diff is greater than say between 15 and 30, send me a warning (as a threshold within DPA) and if greater than 30, send me a critical. Adjust for your own use case.

http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

Here is a reference that you might check out. If this were me, I'd limit it specifically to the table in question and do a diff between getdate (current system time) and last_user_update in minutes.

If that diff is greater than say between 15 and 30, send me a warning (as a threshold within DPA) and if greater than 30, send me a critical. Adjust for your own use case.

http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

ok, so I'm trying this query but it doesn't work. Says that "An expression of non-boolean type ... near "AND"

I picked the Custom SQL Alert-Single Boolean Return as the type and left the default of "Use Repository Default" as the notification policy.

I really have no idea why this isn't working. Got any ideas?

SELECT CASE WHEN EXISTS(

SELECT field FROM table WHERE getdate()-0.02083 AND taker IS NOT NULL)

THEN CAST(1 AS BIT)

ELSE CAST(0 AS BIT)

END

Do you have a field that gets updated with a current timestamp upon inserts? Might be an easier way to tackle this.

Looks like I do. I just started working on this database, but there are three columns in this table. We have date_created, date_last_modified and one called date_order_completed. I may have to play with it a bit but I'm sure one of those will work.

Let's use date_created for whatever you have in mind.

Thanks,

mh

So that got me thinking. I changed it up a bit and used this. And it worked!

SELECT CASE WHEN EXISTS(

SELECT field FROM table WHERE date_last_updated > getdate()-0.02083 AND field IS NULL)

THEN CAST(1 AS BIT)

ELSE CAST(0 AS BIT)

END

I was thinking something like this then:

select datediff(mi,(select max(date_created) from table)),getdate())

and use that value to compare with thresholds since it will be a numeric.

So set warning between 15 and 30 minutes and critical over 30 for example. Set the alert to run at 15 minute intervals to keep overhead low.