Database monitoring HostTracker

Today we would like to write about the next feature of our service - monitoring databases .
There are many different services and metrics that help collect information about the number of visits to the site, the amount of sales and other business-relevant indicators. But if you need to somehow compare these figures with each other? Or will it be necessary to check more “technical” values, for example, a base increase?



HostTracker offers a function for solving such problems - monitoring the database.
')

Request setup

This function allows you to execute a specific query to the database with each check and process its result in a specified way. If the request is not specified, the ability to connect to the database will be tested. The most interesting thing is the ability to execute a specific query with a specified interval, save and compare the result. The request can be any - from simple samples to regular updates of tables, running procedures (instead of the scheduler), various comparisons and logical operations. The only restriction is that it should be executed in 30 seconds, otherwise the error (timeout) will be returned. How can this data be analyzed?



It is recommended to write such queries that return the desired value in the first column of the first row of the result. This value can be analyzed. For queries like UPDATE, INSERT, DELETE, the number of processed rows is fixed.

For example, a graph of the result of a DELETE on certain parameters:



Each result of the query is recorded, the latter is shown in the table of tasks on the interface. If the value is numeric, a graph is plotted using it and you can track its behavior. It is possible to choose the conditions for comparing this result - it can be equal to / not equal to a certain value, exceed it, be in a given range or outside it.
If the condition is not fulfilled, or the request failed due to a timeout, or there is no connection with the database, you will receive an alert (we remind you that HostTracker supports SMS, E-mail, Skype, Gtalk, voice call).

Usage example

Script to track the size of the database files and receive notifications when a certain critical value is reached.
1. We write a query to pull out the data we need about the free space in the tablespace:
SELECT
convert(DECIMAL(12,2),round(sysfile.size/128.000,2)) AS 'FileSize/mb'
, convert(DECIMAL(12,2),round(fileproperty(sysfile.name,'SpaceUsed')/128.000,2))
AS 'Used/mb'
, convert(DECIMAL(12,2),round((sysfile.size-fileproperty(sysfile.name,'SpaceUsed'))/128.000,2))
AS 'Free/mb'
, filegroup.groupname AS 'File-group'
, sysfile.[name],sysfile.[filename]
FROM dbo.sysfiles sysfile (NOLOCK)
inner join dbo.sysfilegroups filegroup (NOLOCK) ON filegroup.groupid =
sysfile.groupid
UNION ALL
SELECT
convert(DECIMAL(12,2),round(sysfile.size/128.000,2)) AS 'FileSize/mb'
, convert(DECIMAL(12,2),round(fileproperty(sysfile.name,'SpaceUsed')/128.000,2))
AS 'Used/mb'
, convert(DECIMAL(12,2),round((sysfile.size-fileproperty(sysfile.name,'SpaceUsed'))/128.000,2))
AS 'Free/mb'
, (CASE WHEN sysfile.groupid = 0 THEN 'Log' END) AS 'File-group'
, sysfile.[name],sysfile.[filename]
FROM dbo.sysfiles sysfile (NOLOCK) WHERE groupid = 0
ORDER BY [File-group],sysfile.[name]
2. We get this picture:

3. Select the desired parameters:

The result of the query is “the value in the first column of the first row”

The check of the result is “less than” and enter “1000” values ​​in the field.

The result - if the log file exceeds 1 GB, we will receive an alert. In addition, you can view the history - how quickly and when exactly the increase is observed.

Monitoring setup

When setting up, it is necessary to set the server address, port, database name, username and password of the user under which the request will be executed. It is strongly recommended to create a separate user with limited rights , just in case. And at the same time, do not forget to give him the rights to the actions that he is supposed to perform. It is also necessary to add the addresses of the HostTrecker servers, from where the checks will be carried out, to the white list of the firewall and the database server in order to provide access to the database. Addresses are listed immediately, they are permanent.