Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Custom SQL Query

Hi All,

Is there anyone who could offer some advice on how to get this working, i have created a SAM template and added the component monitor "SQL Server User Experience Monitor" where i have entered the below query

SELECT BatchIdentifier, BatchProcessType, BatchStart, BatchLastUpdatepdate, BatchCompleted

FROM [HATH_ExternalComms].[dbo].[BatchControl]

where BatchTotal = '-1'

and BatchCompleted = '0'

order by BatchStart desc

however when i test it i get the following "Test failed with "Down" status on Servername Query must return at least 1 row"

If working the query should not return a value, and if it does i need it to report as down

thanks for any help

Stuart

Find more posts tagged with

sql

Accepted answers

Petr.Vilem

"SQL Server User Experience Monitor" is designed to accept one mandatory numeric output and one optional string (message) output.

In order to get it working you will need to change your query to always return one row. In cases like this it can be done simply by COUNT aggregation function...
If I am not mistaken, then 0 BatchControl records matching given criteria is OK (green) while any existing that matches (COUNT>0) is NOT OK (red)...

So, changing query to something like this will cause that component monitor will start accepting its output:

SELECT COUNT(*) FROM [HATH_ExternalComms].[dbo].[BatchControl] where BatchTotal = '-1' and BatchCompleted = '0'

Now, to make component "Not OK" when count is greater than zero, you may use thresholds so component will go Warn/Critical (you cannot get down this way) when statistic value (i.e. count of matching records) is greater than 0.

If you want to get any additional info (like identification of records that are matching criteria) then you will need to elaborate little bit more with the SQL statement to concatenate all the details into single string that will be returned as message.

All comments

Petr.Vilem

"SQL Server User Experience Monitor" is designed to accept one mandatory numeric output and one optional string (message) output.

So, changing query to something like this will cause that component monitor will start accepting its output:

SELECT COUNT(*) FROM [HATH_ExternalComms].[dbo].[BatchControl] where BatchTotal = '-1' and BatchCompleted = '0'

Petr.Vilem

You may find some inspiration in MS SQL Agent - failed jobs template resp. SQL statement that it uses internally. It is about something different, but it deals with the same issues.

hansons

Petr.Vilem

Thanks for the edit on the query it worked a treat and for the extra info will be keeping that to hand for reference as i know this is going to come up again

Stuart