alert if the monitor is off for one instance

The instance availability alert should alert you when a monitored instance goes down. I've tested in our environment, but would suggest you test in yours.

Might you have a dev instance you can use for this?

I am looking for an alert if the Monitor is offline for an instance - we had a reboot of the client, but the monitor did not come back up, while the client itself was up and running just fine.   The alert above will only fire if the client itself is down.

If I understand correctly, the monitor was stopped (likely due to instance being down and unresponsive). When the instance came back up, the monitor remained in the stopped state. Are you wanting a monitor the monitor? One thing I've done is schedule out a job that periodically runs a query against the DPA repository to set the command column to START. Something like:

update COND set command='START' where...

Or if you want ALL of them started, just don't include the where clause.

If you don't want things to be started automagically, you could have the job run a select of command and status and send you the results periodically so you know the current state.

Nothing exists to do this out of the box currently though.

hi, thanks for your post, we are looking to build this same alert as one of our database monitors stopped recently, and the instance was and happy and we were unaware of it for awhile before someone brought it to our attention.

would you be willing to share the command  and tables where to find this information?

thanks so much in advance!

skenow

So not sure how you are going to schedule the job to run (SSMS or cronjob or ??), but here's what you will want to do:

take a look at COND in the DPA repository. That table has the info you need and updates directly to it (be careful of course) can cause action to be taken - like starting monitoring programmatically.

select NAME, COMMAND, STATUS from COND;

Should give you something like this:

If the command is 'START' and the status is 'STOPPED', you know you the monitor is supposed to be up, but it's down.

Hope that gets you going in the right direction!

hi Mandevil,

Yes thank you very much I just figured it out finally when I got your email,

but your answer raises another question, if the monitor has really stopped, shouldn't the status be in a stopped state?

because the query reported it was in a start state and the monitor really had stopped would that indicate the reporting was off and something needed to be reset in the database to report accurately?

also, can this be set up in a custom alert or just setup in a sql job?

thanks much! skenow
  

Yes, this can be set up using any kind of logic or schedule (I won't get into those specifics as there are MANY ways one could implement this).

What I meant is that the command and status *should* be consistent. If the command is set to 'START' and status is 'STOPPED', likely something has gone wrong and I'd look in the error logs.

ok thank you for the clarification

ok so I created a custom alert in dpa to monitor if monitoring has stopped on a server;

(Executes a user-defined SQL statement that will return one or more name/numeric value pairs) using the SQL statement, select NAME, COMMAND, STATUS from [ignite].[COND] WHERE [STATUS] = 'STOPPED',

Also set Notification Policy to  "Notify when level is not normal". and added 2 servers for testing one with monitor on and one with off,

the first few tests of the alert gave correct results back, One status Normal, one Status Broken.  Now no matter how I test it (servers on or off) both servers come back as a broken state, even though the monitors are on.

running the statement directly in SSMS brings back the correct results.

there is no SQL Alert type currently set up that accurately describes my sql statement I'm running and there isn't a way to create one3 that I am aware of.

can you provide some help on what the issue may be, I'm lost on this and we need this setup to monitor if monitoring has stopped on our servers?

thanks in advance for any help you may provide me.

I see a couple of issues. Try this SQL using a custom multi-numeric return.

select NAME+' Monitor is stopped', 1 from [ignite].[COND] WHERE [STATUS] = 'STOPPED' and [COMMAND] = 'START'

This will prevent you getting alerted when the monitoring was turned off explicitly (command set to STOP) - which assuming would be a false positive for you.

Also, the format of the multi-numeric alert can really only handle two outputs (one alphanumeric and one numeric value).