Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

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 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.

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:

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.

Example 3: Collecting SNMP Data

Overview

Basically all SNMP devices have an interface table that gives you basic statistics on bytes sent and received link status (up/down), link speed, etc. You get snmp values from a device by issuing a command like

snmpget -v1 -c public hostname OID

Where hostname is the name of the snmp device and OID is the special SNMP string.

First let’s make a script that can query SNMP devices for Interface statistics (bytes in and out). I will be using snmpget from Net-SNMP and python. But you could use perl and other SNMP modules.

You can get the latest script from http://bixdata.com/addons. It is BSD license which means you are free to use and expand it.

At the top of the script there is a list of hosts and Interface IDs that you can replace with your own. To find out what interface IDs your host has, issue the following command:

snmpwalk -v 1 -c public hostname interfaces.ifTable.ifEntry.ifDescr

Here is the section in bixsnmp.py:

hostIntf = { “192.168.1.11” : 19, “192.168.1.12” : 18415 }

As you can see I am getting stats on interface 19 on 192.168.11 and interface 18415 on 192.168.1.12. You can add as many hosts and interfaces as you want. The rest of the script simply executes the snmpget command to get the data and outputs it in this format:

host : 192.168.1.12
interface : 18415
in : 1441849
out : 317351

host : 192.168.1.11
interface : 19
in : 10540
out : 1395103

SNMP Step 1

Download the bixsnmp.py script from http://www.bixdata.com/addons and copy it to bixagent/data/scripts.

SNMP Step 2

Do an snmpwalk on your SNMP devices to determine their interface IDs.

snmpwalk -v 1 -c public hostname interfaces.ifTable.ifEntry.ifDescr

The output will look like this:

IF-MIB::ifDescr.1 = STRING: Switch Port 1
IF-MIB::ifDescr.200 = STRING: Switch Port 200

Here the ‘.1’ and ‘.200’ are the IDs. So edit bixsnmp.py and add those ids:

hostIntf = { “hostname” : 1, “hostname” : 200 }

SNMP Step 3

Next run the script to make sure it works.

cd bixagent/data/scripts
python bixsnmp.py

SNMP Step 4

Add the script to bixagent/data/xml/dp-import.xml.

<Script>
<namespace>BixSNMP</namespace>
<path>python data/scripts/bixsnmp.py</path>
<instance>host</instance>
<key>interface</key>
<version>v1</version>
<Field>
<name>in</name>
<type>schema_uint64</type>
</Field>
<Field>
<name>out</name>
<type>schema_uint64</type>
</Field>
</Script>

This tells BixData what fields to expect.

SNMP Step 5

In BixDesktop connect to your BixAgent and under real-time graph choose ‘Custom’. Navigate to the BixSNMP namespace and add the counters you want to see.

Navigating SNMP data

In/out are cumulative while rate is the rate over the sample period. The following screenshot shows an example of realtime data:

custom_monitoring_snmp_realtime

Real-time graph of SNMP data

All other components in BixData such as Service Checks and Notifications will now work with SNMP.

SNMP Step 6

Next we want to create graphs. You can download the graph used in this example and copy it to bixserver/data/reporting/templates/, or you can follow these steps to create a graph.

Connect to your BixServer and then:

  • Go to Reporting, Setup Templates
  • Click the + button and select New Graph
  • Enter a reportName and description
  • Click on the big + button next to streams to browse for streams. You can add as many streams as you want.
  • Click ‘Save changes Yes’ and you can add the report to your Report Setup

Hopefully this gives you an idea of the flexibility of BixData. As you can see it is an easy tool to use and extend that should be able to adapt to your particular monitoring needs. Once you have a script that produces data, the chore of collecting, storing and visualizing data is taken care of by BixData. You can download some of the scripts from their site http://www.bixdata.com/addons, and since the scripts are BSD license you can make them your own.

Comments

comments