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

How can I monitor a SQL table or log file for error messages?

Hoping someone can point me in the right direction.

We have an application that runs scanning batch jobs and writes to a SQL database table with the status of each batch job.

COMPLETED

ERROR

PROCESSING

The table 'Batchinfo' has a column called Status and I want to monitor that Status column for any 'ERROR ' status and generate an alert email.

Alternatively if we can get it to write to a log file can I monitor that for the ERROR messages?

I did find an AppInsight query but that only looks at the first column for a numerical value.

I have NPM 11.5.3 and SAM 6.2.2 installed.

Any suggestions?

Regards

LPOR

Find more posts tagged with

Accepted answers

lpor

Answer from support:

It isn't possible to monitor a table within an
SQL DB with SAM at the moment. You could use a tool like Powershell to run SQL
queries against the database and then use a Powershell Monitor with SAM to
record the result of these queries.

Now the above is only a suggestion as we do not provide PS scripts or support
customized scripts but if you have someone in your organisation who is good
with Powershell this is a possible workaround.

I had a look online across forums and can see there are many users who have
provided their own scripts online which perform such queries.

https://cmatskas.com/execute-sql-query-with-powershell/

http://stackoverflow.com/questions/30543430/using-powershell-get-values-from-sql-table

https://community.spiceworks.com/topic/388926-sql-query-with-powershell

If I get it working I will post here.

All comments

jpr7311

Is there anyway you could get it to write to a Windows Event Log? If so, you could easily configure a Windows Event Log monitor in SAM that will trigger as down when that Event is found and then just use a component monitor Alert for it.

or you may be able to accomplish it with a SAM powershell script monitor that checks the log file for the ERROR string.

Sorry that I'm not more help, hopefully a scripting guru comes across this. I thought this was a good question and just throwing my thoughts out there.

lpor

Hi Thanks for the reply but now looks like I need to do this from the SQL table as the log file can have hundreds of entries for a single error.

The SQL table entry ties the errors to a transaction ID so I need to pull that with each error.

I need some sort of script I think to do this, I will update if I get a solution. I have a ticket raised for advise from Solarwinds.

lpor

Answer from support:

zackm

I don't think you would need powershell at all (unless I am missing something)...

SAM has a built-in component monitor for SQL Server: SQL Server User Experience monitor

Basically, just setup a read-only account for the DB and write a query to get the response you want as a statistic then set your threshold on it to apply for your component status.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

lpor

"The SQL statement you use for this query must return a numerical value, not a varchar."

I need to return an error with the associated batch job information.

The SQL User Experience monitor will only allow a numerical value to be returned.

geits

Do a Select Count on the SQL query. Something like SELECT COUNT(status) FROM batchinfo WHERE status like '%error%'

If you the status doesn't get reset by a successful run you would need to find a method to filter out previous issues, something like DATEDIFF within SQL will help if there is a date/time column.

bobmarley

You can do it in your SQL query

COMPLETED = 1

ERROR = 2

PROCESSING = 3

etc...