Business Data Monitoring with Zabbix – Easily Done

By Snir Balgaly and Eitan Blumin

Cross-posted with Snir Balgaly – not the best but the sexiest DevOps ever lived

In this article, We will show you how we implemented a little system to create generic business data based alerts, and show them on Zabbix when needed.

In the first part, Snir will be more focused on the generic process that will deliver the data you want to Zabbix in case of a problem, and then Eitan will explain the steps that need to be taken on the database end of things.

In this particular example we will create a query that will provide us a number (e.g – Hourly revenue of our business), and we would like to see an alert when the number is less than 500.

Step 1: Creating the infrastructure

Item

First things first, let’s create the item.

The item is going to be a “Trapper” item.

Trapper item is an item that doesn’t do any check, but waits for a message from the “Zabbix Sender” tool.

You can read more about it over here.

This is the item, nothing much to do, just make sure that the “Type of information” field is correct, because it can cause the item to be unsupported if it’s misconfigured.

Alert

Now for the easy part, Creating an alert that will let us know when the latest value we got is bellow 500.

In case you didn’t know, the macro {ITEM.LASTVALUE1} will return that latest value of the item, which in this case will be the most current revenue amount.

Step 2: The Database Command

Now that we have the item (and the alert of course), we need to create the actual database command that will send the data to the Zabbix server.

In our example, we will be using Microsoft SQL Server. The same methodology may be possible in other database technologies as well, though with different syntax.

Query

Since we’re going to be sending a value from the database, we obviously first need to retrieve this value.

We would prepare a T-SQL script that looks like this:

DECLARE @Revenue decimal(19,2)
SELECT @Revenue = SUM(Profit - Loss)
FROM TBL_Transactions
WHERE TransDate > DATEADD(minute, -30, GETDATE())

In this example, we’re saving in variable @Revenue the result that we need to return, based on a query from the TBL_Transactions table. Obviously, this logic would change based on your specific business needs.

Command Line Executable

Next thing we need to do is send this value to Zabbix. We will do so by running the Zabbix Sender executable file using xp_cmdshell, and set some parameters for it (one of which will be the revenue value we found):

DECLARE @cmd nvarchar(4000)

set @cmd = '""C:\Zabbix\bin\win64\zabbix_sender.exe" -z 123.456.789.012 -c "C:\zabbix_agentd.conf" -s "DB Server" -k HourlyRevenue.Alert -o "' + CONVERT(VARCHAR,ISNULL(@Revenue,0)) + '""'

print @cmd

EXEC xp_cmdshell @cmd, 'no_output'

Let’s break this command down and examine the different parameters it receives:

• -z 123.456.789.012
  This parameter identifies the Zabbix server address that will be capturing the event. You will need to replace 123.456.789.012 with your correct Zabbix server address.
• -c “C:\zabbix_agentd.conf”
  This parameter lets you specify a configuration file for the sender, with additional settings as needed.
• -s “DB Server”
  This parameter identifies the “source” of the alert based on how the server is called within Zabbix (the host). Replace “DB Server” with however you called your server within your own Zabbix configuration.
• -k HourlyRevenue.Alert
  This identifies the Trapper item name as you created it within Zabbix. Replace HourlyRevenue.Alert with your own Trapper item name.
• -o “‘ + CONVERT(varchar, ISNULL(@Revenue,0)) + ‘“
  This is where we send the output value itself, which will be captured by the Zabbix Trapper item.

The command is saved in a variable called @cmd and then executed using xp_cmdshell (for more information about xp_cmdshell click here).

Now the only thing left is to run this script every few minutes, either using a SQL Agent Job, or a Windows Scheduled Task that runs the script with SQLCMD (if, for example, you have SQL Server Express edition without the SQL Agent service).

Conclusion

Once you setup the item, alert and the command all, is ready to start the job.

Initiating the job will push the item to the Zabbix server (use the “Latest Data” section to see it).

If the latest item doesn’t meet the condition in the Trigger section, an alert (or a Problem) will show up on your dashboard.

_______________________________________________________

Other Strategies

In this example we saw how we can handle a numeric data, but sometimes we would like to monitor something which is not a number, but more like “Happened or didn’t Happen”.

We can write a query that will look for something which is problematic. e.g – a user logged in from a blocked IP, or large amount of users are logged in from the same IP.

The command will send “OK” as long as everything is ok, and when it’s not, the command will send a custom made message, with a short explanation about the nature of the problem.

Example for this use case

For this example, we’ll assume that we’ve already written a T-SQL query to retrieve number of logins from blocked IPs, saved in a variable called @BlockedIPCount, and another variable that holds a list of the actual IPs themselves, called @BlockedIPsList.

The database command will look similar but different:

DECLARE @cmd nvarchar(4000)

IF @BlockedIPCount > 0
    set @cmd = '""C:\Zabbix\bin\win64\zabbix_sender.exe" -z 123.456.789.012 -c "C:\zabbix_agentd.conf" -s "Prod DB 1" -k AnotherExample.Alert -o "Found ' + CONVERT(VARCHAR,ISNULL(@BlockedIPCount,0)) + ' logins from blocked IPs: ‘ + @BlockedIPsList + ‘""'
ELSE
    set @cmd = '""C:\Zabbix\bin\win64\zabbix_sender.exe" -z 123.456.789.012 -c "C:\zabbix_agentd.conf" -s "Prod DB 1" -k AnotherExample.Alert -o "OK""'

print @cmd
EXEC xp_cmdshell @cmd, 'no_output'

We can see two different types of textual output that may be sent to Zabbix. One that looks like “Found 2 logins from blocked IPs: 123.123.123.123, 456.456.456.456”, and one that’s simply the text “OK”. The Alert configuration in Zabbix’s side will do the rest.

Here’e a little example of an alert that will show the custom message as long as it’s not “OK”:

Zabbix will check the latest item data if it’s “OK” or not. If it is, nothing happens, if it’s not, the last value (which is the message in the “-o”) will appear as an alert in Zabbix.

Of course, the contents sent to the -o parameter can be anything. So you can easily customize such alerts to fit whatever business requirement you may have, and change your T-SQL scripts accordingly.