Create self defined matrix to monitor IBM i with Nagios

Nagios plug-in for IBM i provides the ability to allow users to define their own matrix for monitoring. The user could write SQL statements to define and create new matrix. It is recommended that you take advantage of IBM i SQL services (https://www.ibm.com/support/pages/node/1119123)  to add your own matrix. 

The follow steps show you how to configure your own matrix:

1. Get the latest Nagios for i plug-in from here: https://github.com/IBM/nagios-for-i/tree/master/service%20pack

2. Follow the README from above link to install or update your Nagios for i plug-in

3. After the installation has been completed, you could go to the directory /usr/local/nagios/etc/objects

4. Open the file named CustomSQL.xml

The file looks like below:

<nagios>

    <func id="TotalJobsNum">
        <common-name>Total Jobs Num</common-name>
        <type>single-value</type>
        <sql-command>SELECT TOTAL_JOBS_IN_SYSTEM FROM QSYS2.SYSTEM_STATUS_INFO</sql-command>
    </func>
    <func id="ReplyMsg">
        <common-name>Reply Message Num</common-name>
        <type>list</type>
        <sql-command>SELECT MESSAGE_ID, SEVERITY, MESSAGE_TEXT from qsys2.message_queue_info where Message_type = 'INQUIRY' </sql-command>
    </func>

</nagios>

Here are the types of matrix that you could define with SQL statements:

<type>single-value</type>: If you use this type, you need to make sure that you only return one column with SQL data type INTEGER in your SQL statement. Just one row is expected to be returned from your SQL. The plug-in will take the returned value from your SQL as the comparing value for warning and critical criteria. 

<type>list</type>: For this type, you could return a list of rows with multiple columns. The plug-in will take the count of returned rows as the comparing value for warning and critical criteria. 

Please note that the character "<" and "&" is illegal in xml file. You could surround the SQL command by <![CDATA[ ... ... ]]> to escape the invalid characters.

After finishing the CustomSQL.xml, you should create a command in /usr/local/nagios/etc/objects/commands.cfg if you are using Nagios Core as follows:
define command{
    command_name    custom-sql
    command_line    /bin/bash /usr/local/nagios/libexec/check_ibmi_status.sh -m CustomSQL -$HOSTADDRESS$ -f $ARG1$ -w $ARG2$ -c $ARG3$
}

And then you need to create a service for your function in /usr/local/nagios/etc/objects/services.cfg:
define service{
    use generic-service,srv-pnp
    host_name HOST_NAME
    service_description reply message number
    check_command   custom-sql！ReplyMsg！10！20
}

The parameter -f in the commands.cfg indicates which function you want to retrieve in CustomSQL.xml. The function name after -f option should map to the id of <func> tag in the CustomSQL.xml. In this case, the plugin will retrieve the node <func id="ReplyMsg"> and parse the SQL command by -f ReplyMsg.

After restarting the Nagios service, you are ready to use the newly created matrix ReplyMsg.