Custom alert if table has NOT been updated?

Is there a way to setup a custom alert if a table has not received any new input? I'm using MSSQL and the latest version of DPA.

I'm having a difficult time even understanding the different types of alerts. From what i can figure it looks like we can only get a custom alert if it can be ranked by high, low, etc.

Thanks for any help/guidance

Message was edited by: m henrychanged reports to alerts. If I missed any, I'm asking about alerts only.

Find more posts tagged with

custom alerts

sql

SQL Server

dpa

mssql

Alerts

Accepted answers

mandevil

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

All comments

mandevil

You could set something up as a boolean type of query. Check out using like a CASE statement in your select to determine changes and then alert based on that value.

gcp

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.

mhenry

I'm not even sure where to start with that. I guess I would try a Custom Alert - Single Boolean Return? I'm also unclear on the notification policy field. How does that work in a Boolean case?

Thanks for taking the time to help on this.

mhenry

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,

mandevil

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/

mhenry

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