Percona Monitoring and Management Query Analytics reporting

Percona Monitoring and Management Query Analytics reporting

I couldn't find a tool which could generate weekly PMM "Query Analytics" reports for our developers, so I wrote pmm_rep to solve this problem.

This script will fetch data from the PMM qan-api and create a multipart email report containing both a html and a 79 width column fixed text version.

NOTE: The script is hardcoded to set begin date to the preceding monday. This should be easy to adjust yourself.

Prerequisites

I recommend that this script runs in a local::lib environment and there is a postfix/ssmtp/sendmail relay service running on the platform (Linux) where you choose deploy it.

Installation

  1. Create a dedicated user.
    sudo useradd -d /opt/pmm_reporter -m -s /bin/bash -c"pmm reporter" pmm_reporter
    sudo su - pmm_reporter
  2. Run the following as the dedicated user to set up a local::lib environment:
    wget -O- https://cpanmin.us | perl - -l $HOME/perl5 App::cpanminus local::lib && echo 'eval `perl -I $HOME/perl5/lib/perl5 -Mlocal::lib`' >> $HOME/.bash_profile && echo 'export MANPATH=$HOME/perl5/man:$MANPATH' >> $HOME/.bash_profile
    . .bash_profile
  3. Install the following dependencies with cpanm again as the dedicated user:
    cpanm ExtUtils::MakeMaker Mojolicious Text::Wrap Email::Stuffer Sub::Identify namespace::autoclean DateTime HTML::Strip Config::Auto
  4. Clone the repository:
    git clone https://github.com/RasmusEdgar/pmm_rep.git
  5. Copy and adjust config (it should be self explanatory):
    cp ./pmm_rep/config/config.example ./pmm_rep/config/config && vim ./pmm_rep/config/config
  6. Test if script is working by running it by hand.
    cd ./pmm_rep/script && ./pmm_rep.pl
  7. Schedule a cronjob:
    PATH=/opt/pmm_reporter/perl5/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/pmm_reporter/.local/bin:/opt/pmm_reporter/bin
    PERL5LIB=/opt/pmm_reporter/perl5/lib/perl5
    PERL_MM_OPT=INSTALL_BASE=/opt/pmm_reporter/perl5
    30 10 * * 1 cd /opt/pmm_reporter/pmm_rep/script && ./pmm_rep.pl > /dev/null 2>&1