I know, it sounds a bit odd but for simple monitoring requirements which require the execution of SQL queries, the PATROL for Log Management KM can be used. Technically speaking it is not the PATROL for Log Management KM which executes the query. On Windows systems the query execution must be placed into a CMD script. I experimented with PowerShell as well but I could not get the desired result. The reason for that is, that you first have to call the PowerShell executable an then the script. I think with some effort it will definitely work as well with PowerShell.
A little excurse to the Scripting KM
When talking about PowerShell the Scripting KM could come into mind. The KM offers PowerShell execution but in my humble opinion it is focuses on remote execution of PowerShell commands/scripts. Executing local PowerShell is not as easy as you might think. I don't know why there is no simple Type of Command for PowerShell is implemented into the KM to allow local PowerShell execution. The KM is another example for a good approach but it is not completely thought through. It is a pity, because this KM could have a great potential. Maybe too great?
Let's get started
However, let's get started. As a first step I have created a wrapper script, which allows a SQL script to be passed as argument. In early testing I executed the SQL query directly from the script but I thought it would be more clear and flexible to store the SQL queries in separate SQL script files. There could be further improvements for example to pass the database host, database name and database user & password as arguments to the wrapper script. But there is always room for improvement, right?
The use case where I used this solution was on Windows and the database was MS SQL. You have to adapt for other OS and database types.
In order to execute SQL commands/queries against Microsoft SQL Server from command line, the simplest solution is the use of the sqlcmd utility. To use the utility you have to install the following components on the server from where the command is executed:
- Microsoft ODBC Driver for SQL Server
- Microsoft SQL Server Command Line Utilities
The wrapper script
So here is an example how the wrapper script could look like:
:: Name: SQLCmdWrapper.cmd :: Purpose: Execute a SQL :: Author: :: Revision: April 2020 - initial version @ECHO OFF :: variables set sqlHost=SQLHOST set dbName=DBNAME set dbUser=DBUSER set dbSecret=DBPASS set sqlScript=%~1 ::execute sqlcmd sqlcmd -S %sqlHost% -d %dbName% -U %dbUser% -P %dbSecret% -i %sqlScript%
Of course the place holders SQLHOST, DBNAME, DBUSER and DBPASS have to be replaced. I know, there are some security issues with this, as the DBPASS is stored in clear text in the script. I think this area could be improved but for the simplicity I recommend to use a low privileged user for the SQL execution.
The SQL script is passed as the first and only argument to the wrapper script. We can see this in the assignment set sqlScript=%~1.
I would like to mention, that the example does not have any exception handling. This should be taken into consideration when using this script in a production environment.
The SQL scripts
The SQL scripts can be almost anything but I suggest you keep it as simple as possible. This approach is not designed to parse a lot of output. At best the output of your SQL is a single line with either a text string you can parse or a numeric value.
Configuration of the PATROL for Log Management KM
In a next step, the PATROL for Log Management KM has to be configured. Firs we have to add a Log Management Script Files Monitor:
With "Add" we create our first Instance for a SQL-Query execution with our wrapper script. First we enter some basic things into the form:
Monitoring environment label
Here we enter a name for our monitor environment.
Monitoring file logical name
As we don't want our instance to have the name of the wrapper script, we enter a name we can recognize easily and bring into conjunction with our monitor.
Script file (full path)
Full path to the wrapper script.
As argument we enter the full path to our SQL script
In a next step we can either configure a string search or a numeric search. Here you can follow the documentation of the KM. But I would like to mention that the numeric part needs to get used to.
You have to specify a range in which the numbers you search for are in. In this example, we take all numbers greater than 5 as first number.
The number must be greater than the value entered in the First Number field.
The numbers smaller or equal to 10.
The number must be smaller or equal to the value entered in the Second Number field.
The begin token where to search for the number. This is a bid odd. You can find a description here.
The end token where to search for the number.
Now we can also define our event creation. E.g.:
That's it folks! If you have questions, don't hesitate and put them here as comment.