Monitoring mod_security and MySQL with Splunk

    Good time of day, habrozhiteli.
    I want to share the monitoring implementation of the virtual server that I support.
    Operating system: CentOS 6.3 x64, however, the described situation is suitable for other platforms, such as * Win and * nix.
    Formulation of the problem.
    The following services are installed on the server: apache2, MySQL, postfix and apache protection based on mod_security is implemented.
    The host of the server is not satisfied with connecting via the ssh console and reading logs in a text editor, in addition, there were no visibility of the load on the server, the number of requests, the complexity of requests to the server. I suggested the Zabbix server, but the client said a definitive no. Well, then he and the client are always right.
    It is necessary to quickly find the current situation related to server security, and proceeding from the event, take measures to eliminate the problem.

    I’ll immediately give a screenshot of what happened:




    So, by googling and asking a question and getting an answer, I tried to increase the complexity of all the interfaces listed on the official mod_security website . It turned out that they all ceased to be supported somewhere in 2008, which did not suit me.
    I left Splunk in the end, because firstly it is paid, and secondly I have a distrust of universal products for such tasks.
    Briefly about the solution:
    1). There are paid and free versions;
    2). The trial period is 60 days, and if the logs passing through the system are less than 500 MB, then you can safely conclude a free license agreement;
    3) I was pleased with a bunch of modules, of which I immediately became interested in the module for MySQL.
    4) the ability to install both on the server itself and on any other host on the network, with connection to various nodes to collect information. That is, the consolidation of the results of several nodes in a single center (for example, on a home server).

    Concerns that arose:
    1). Too versatile product;
    2). The complexity of installing on a live server;
    3). The security of Splunk itself.
    4). Eternally satisfied persons on the main page of the site and a minimum of technical information.

    So, it was decided to set. I had to register an account on the site, after that I downloaded the corresponding package for ubuntu (first I tested http + MySQL + miniDLNA + file + qBitTorrent on my home machine). I will not describe for a long time, it all worked. It was decided to put in production.

    Step by step download:
    1. Splunk * .rpm itself.
    Put rpm -i *.rpm,
    Start service splunk start
    Read the conclusion. The default port is 8000.
    2. The module for mod_security by reference from of. site. It is written that a completely free module.
    3. In addition, the module showed package dependencies: Visit amMap , Download Maxmind
    , Download SideView Utils , Google Maps. We do not mind downloading.
    4. Installing mod_security itself is also quite simple: App-> Manage apps -> Install app from a file.
    Installation of add. modules are worse, you need to unpack them into the Splunk directory, into the modules folder. Everything appeared for me and it worked right after the splunk
    5 restart . MySQL Monitor , a module that is also installed through Manage Apps, shook a bonus . However, his launch is through another place, separate from Splunk.

    Add Splunk startup at boot:
    echo "$SPLUNK/bin/splunk start" >> /etc/rc.local
    


    The most interesting thing started when setting up, as nothing is clearly said anywhere, the minimum instructions for installing modules are given on the pages.
    The necessary retreat. Splunk is positioned as a search engine on a certain database (which is stored on the hard disk of the server where it is installed). It is possible to connect information from files (Data Inputs), via TCP, UDP, Scripts (apparently transferring data from scripts). In turn, there is the concept of Indexes, which contains ... indexes that reference Data Inputs.
    Let's go back to setting up the mod_security information collection module. It turned out that it should start working; in the Manager -> Data Inputs section, enter the collection of logs from the file (I have /var/log/httpd/modsec_audit.log), I specify this:



    So, Data Input with the namemod_secur
    Go to Manager -> Indexes and create the mod_secur index, which requires you to specify the location of the Splunk database in the file system. Put:
    Home path: $SPLUNK_DB/mod_sec_db/db
    Cold path: $SPLUNK_DB/mod_sec_db/dbcold
    Thawed path: $SPLUNK_DB/mod_sec_db/dbthawe
    


    When the data input and index are created, we go to the module settings, which were not in the module settings of the Modules section. and in Manager -> Advanced Search -> Search Macros.

    In the upper left part of the window, make sure that the data refers to the Mod_security module (otherwise I poked around in a bunch of variables of another module there, looking for my variables).
    We change modsec_index from "main" to our index "mod_secur", we do not touch modsec_src.
    After these manipulations, the system begins to read the logs, and somewhere within a minute for a three-day file collects information. In the top processes, I did not see Splunk at all, the minimum eats up resources, which pleased me.
    Below I give a couple of screenshots, and then I will talk about MySQL monitor.
    One screenshot

    Two screenshot


    I’ll add on my own that the creators of the monitor for mod_security completely did not take care of the important information - the output of the enabled and disabled rules by which it works.

    The installation of the MySQL monitor made my brains more, if only because the design tears my eyes to the very depths of my soul.


    When you open the monitor, we always get to readme, where I did not find reconfiguring it.
    The module consists of a statistics collection daemon MySQl, which also needs to be started.
    But first, edit the module configuration file "$ SPLUNK_HOME / etc / apps / mysqlmonitor / bin / daemon / config.ini):
    > cp config.ini.sample config.ini
    > cat config.ini
    [mysql]
    host=localhost
    port=3306
    username=xxx
    password=xxx
    [splunk]
    host=localhost
    port=9936
    [statusvars]
    interval=10
    [slavestatus]
    interval=10
    [tablestats]
    interval=3600
    [processlist]
    interval=10
    

    and the connected server file $ SPLUNK_HOME / etc / apps / mysqlmonitor / hosts.yaml
    > cp hosts.yaml.sample hosts.yaml
    > cat hosts.yaml
    Databases:
      db01:
        host: localhost
        username: xxx
        password: xxx
    


    And you can start the demon.
    $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon start 
    echo "$SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon start " >> /etc/rc.local
    

    Depends on installed in the system
    • Python 2.6 or 2.7
    • Python MySQL drivers

    It collects information via TCP socket 9936, which will be created in Splunk itself: Manager -> Data Inputs -> TCP. We create socket 9936.


    Manager -> Indexes -> we create new mysql, the complete analogy with mod_security.
    In Manager -> Advanced search -> Search macros we don’t go - there is nothing to change there either. Everything took off, I bring screenshots (by the way, it won’t work automatically, you need to click the search button).

    UPD 04/29/2013: I gave birth to such a script, because the daemon sometimes crashes and stuffed it into cron *.
    cat mysql_mon.sh
    #!/bin/bash
    if ! [ -f / $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon/splunkmysql.pid ]; then
        echo "MySQL monitor PID-file is Dead. Restarting"
        $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon/splunkmysqlmonitor.py stop
        $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon/splunkmysqlmonitor.py start
    else
        read pid < $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon/splunkmysql.pid
        if ! `/bin/kill -0 "$pid"`; then
            echo "MySQL monitor process is Dead. Restarting"
            $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon/splunkmysqlmonitor.py stop
            $SPLUNK_HOME/etc/apps/mysqlmonitor/bin/daemon/splunkmysqlmonitor.py start
        fi
    fi
    echo "Mysql monitor now Working"
    

    restart does not work if the problem is with the PID file, therefore stop + start.
    $ SPLUNK_HOME change to the full path to the program.

    Time.


    Two. It turned out to be a pleasant opportunity on the third chart “slow queries” to click on any part of the chart and see the request itself.


    Three.


    Thank you all for your attention!

    Also popular now: