Custom Monitoring MySQL and SNMP with BixData

With BixData you can monitor your servers as well as VMware and Xen. BixData includes pre-built plugins for things like CPU, Memory, Disk, etc. but any good monitoring tool needs to be customizable. BixData includes the basic ability to run scripts and record their exit values, similar to Nagios Plugins. BixData 2.7 adds support for importing data in more complex formats. This allows you to monitor almost anything.

I'll go through the steps showing you how to monitor MySQL locally where an agent is installed and then the steps to monitor a device remotely through SNMP. The advantage of BixData is that any data available through a BixAgent works with the standard tools such as the situation room, scoreboards, notifications and the reporting system. All data are stored in standard SQL tables and are easily accessible.

This diagram shows three common uses.

Custom monitoring with BixData

Custom Monitoring with BixAgent

Version 2.7 adds a plug-in called dp-import which allows you to import any data. This plug-in scrapes output from command line applications and imports it into BixData.

dp-import expects output in a specific format of [key] [value].

There are two example scripts in 2.7, both use python, but you can use any scripting language or program, as long as it outputs text to the screen. If a program or file outputs data in the expected format, the pipeline for data is:

program / file - - > dp-import - - > BixAgent - - > BixServer - - > SQL Database

If you want to collect data from an application that doesn't output data in the format that dp-import expects you can make a simple script to translate. This would mean that your script reads the output of the application that you are interested in and then outputs it in the expected format:

program / file - - > script - - > dp-import - - > BixAgent - - > BixServer - - > SQL Database

Example 1: User information

This example is a simple python script that parses the output of the Unix 'who' command and produces the output that dp-import expects. You can open the bixagent/data/scripts/users.py file in your favorite editor and see how it works. Let's first start with the setup. Each script that can be run by dp-import requires a <Script> entry in the configuration file: data/xml/dp-import.xml

<?xml version="1.0" encoding="utf-8" ?>
  <Scripts>
    <Script>
      <namespace>UsersList</namespace>
      <path>python data/scripts/users.py</path>
      <version>1</version>
      <key>unique</key>
      <Field>
        <name>name</name>
        <type>schema_string</type>
      </Field>
      <Field>
        <name>tty</name>
        <type>schema_string</type>
      </Field>
      <Field>
        <name>logontime</name>
        <type>schema_string</type>
      </Field>
      <Field>
        <name>idle</name>
        <type>schema_string</type>
      </Field>
      <Field>
        <name>pid</name>
        <type>schema_uint64</type>
      </Field>
      <Field>
        <name>location</name>
        <type>schema_string</type>
      </Field>
    </Script>
  </Scripts>

Explanation of <Script> tag:

<namespace> Each set of data belongs to a namespace, which allows you to organize and find your data.

<path> Specifies the full command line to the program / script

<version> (Optional) You can have multiple versions of the same script. Usually this is 1

<key> (Optional) Allows you to designate a field that serves as a unique identifier when your program/script outputs more than one record. In the case of users.py, the script outputs all logged on user details, so we need a unique field.

<Field> Each Field contains the name and type of the data that dp-import can expect. You should list all possible fields. The program/script only has to output the fields it knows.

The possible types are:

• schema_string: a string of any length < 32KB
• schema_int32: a large signed number that can include negative numbers
• schema_uint32: a large number, only positive
• schema_uint64: even larger number
• schema_real4, schema_real8: large and even larger floating point values

In this case the script lives in a subdirectory of BixAgent, but could be located anywhere in the system.

When you run this script with python users.py, it parses the output of the Unix 'who' command and produces the following output:

unique : johnsmith.pts/0
name : johnsmith
tty : pts/0
logontime : Dec 14 09:46
idle : .
pid : 9466
location : (netblock1.jakesmith.org)

Each line represents a key value pair that will end up in the database in BixServer.

That's it. BixAgent and BixServer will take care of collecting the data, creating required database tables and providing the data to the entire system. You can do anything with this data you can with the standard data that BixData collects. For example, create a notification that sends you an email whenever anyone logs in after 11pm.

As a test, you can connect to an agent running on any Unix-like system and on the agent homepage, there is a list of logged on users, which is retrieved using this sample script. Here's a screenshot showing the information.

User info shown in Agent Home Page

Example 2: MySQL statistics

This next example parses the output of the MySQL command "mysql --execute='show status;'" and outputs the values in the format that dp-import expects. To use the mysqlstats script in dp-import we add a configuration entry for it. The configuration entry in bixagent/data/xml/dp-import.xml for mysql.py is slightly longer, but only because there are more fields. It's actually much simpler.

<Script>
  <namespace>MySQLStats</namespace>
  <path>python data/scripts/mysqlstats.py</path>
  <version>1</version>
  <Field>
    <name>Bytes_received_rate</name>
    <type>schema_real4</type>
  </Field>
  <Field>
    <name>Bytes_sent_rate</name>
    <type>schema_real4</type>
  </Field>
  <!-cut for simplicity -->
</Script>

All the MySQL stats will go under the namespace MySQLStats. When you execute the python mysqlstats.py command, it outputs the MySQL statistics in the expected format. With MySQL stats we would like to generate periodic graphs in .png format, like the graph below:

MySQL Statistics Graph

BixData has a template editor which allows you to create your own graphs. To create a template for MySQL stats, simple click the + button in the template editor. Add one or more streams from MySQL stats, and save the template. An example of a template with custom MySQL streams is shown below.

Adding graph template

Note on Multiple MySQL Instances

Since the MySQL script only outputs information for the local MySQL server, there is no need for a <key> field in the <Script> setup section of dp-import.xml. But if you want to publish stats for different MySQL instances, you can easily add a key field such as <key>mysqlinstance</key> and all the script has to do is output the unique name or identifier of the MySQL server.