scripts/surveillance/statistiques2.py

#!/bin/bash /usr/scripts/python.sh
# -*- encoding: utf-8 -*-
#
# Script qui calcule les statistiques d'upload des diverses machines
# Crans.
# Auteur : Pierre-Elliott Bécue
# Licence : GPLv3

"""
Script gérant les diverses statistiques liées au comptage
d'upload. Exécuté quotidiennement sur le serveur qui compte
l'upload.
"""

import sys
import argparse
import socket
import time
import gestion.affichage as affichage
import lc_ldap.shortcuts
import psycopg2
import psycopg2.extras
import gestion.config.upload as upload_config
import gestion.config as config
from gestion import mail as mail_module

ldap = lc_ldap.shortcuts.lc_ldap_readonly()
encoding = "UTF-8"

pgsql = psycopg2.connect(database='filtrage', user='crans')
pgsql.set_session(autocommit=True)
curseur = pgsql.cursor(cursor_factory=psycopg2.extras.DictCursor)

def ipv4or6(addr):
    """Méthode affichang IPv4/6 en fonction du
    type d'IP. C'est juste un bulk pour éviter d'utiliser
    des bazookas style netaddr.

    """
    if ':' in addr:
        return "IPv6"
    else:
        return "IPv4"

def upload_srv_stats(args):
    """Méthode générant les stats d'upload des serveurs à partir
    des données de la base upload.

    """
    upload_srv_req = """
WITH
    machines_sans_doublon
AS
    (
        SELECT DISTINCT ON(mac_addr)
            *
        FROM
            machines
    )
SELECT
    SUM(upload) as tot_upload,
    SUM(download) as tot_download,
    mac,
    ip_crans
FROM (
        (
        SELECT
            sum(bytes) as upload,
            '0' as download,
            mac_src as mac,
            ip_src as ip_crans
        FROM
            upload
        LEFT JOIN
            machines_sans_doublon
        ON
            machines_sans_doublon.mac_addr = upload.mac_src
        WHERE
            machines_sans_doublon.type = 'crans'
        AND NOT
            ip_src <<= inet%(ipv6_local)s
        AND NOT
            ip_dst <<= inet%(ipv6_local)s
        AND NOT
            ip_dst <<= inet%(plage_ens)s
        AND NOT
            ip_dst <<= inet%(appt)s
        AND NOT
            ip_dst <<= inet%(plage_adm)s
        AND NOT
            ip_dst <<= inet%(plage_ipv6)s
        AND
            stamp_inserted >= %(begin)s
        AND
            stamp_inserted <= %(end)s
        GROUP BY
            mac,
            ip_crans
        )
    UNION
        (
        SELECT
            '0' as upload,
            sum(bytes) as download,
            mac_dst as mac,
            ip_dst as ip_crans
        FROM
            upload
        LEFT JOIN
            machines_sans_doublon
        ON
            machines_sans_doublon.mac_addr = upload.mac_dst
        WHERE
            machines_sans_doublon.type = 'crans'
        AND NOT
            ip_src <<= inet%(ipv6_local)s
        AND NOT
            ip_dst <<= inet%(ipv6_local)s
        AND NOT
            ip_src <<= inet%(plage_ens)s
        AND NOT
            ip_src <<= inet%(appt)s
        AND NOT
            ip_src <<= inet%(plage_adm)s
        AND NOT
            ip_src <<= inet%(plage_ipv6)s
        AND
            stamp_inserted >= %(begin)s
        AND
            stamp_inserted <= %(end)s
        GROUP BY
            mac,
            ip_crans
        )
    )
AS
    famille
GROUP BY
    mac,
    ip_crans
ORDER BY
    tot_upload
DESC
"""
    fill_in_dict = {
        "begin" : args.begin,
        "end" : args.end,
        "plage_ens" : config.plage_ens,
        "ipv6_local" : 'fe80::/8',
        "plage_ipv6" : config.prefix['subnet'][0],
        "appt" : config.NETs['personnel-ens'][0],
        "plage_adm" : config.NETs['adm'][0],
        }
    curseur.execute(upload_srv_req, fill_in_dict)
    upload_result = curseur.fetchall()
    upload_srv = [[socket.getfqdn(ligne["ip_crans"]), ipv4or6(ligne['ip_crans']), ligne['tot_upload'], ligne['tot_download']] for ligne in upload_result if ligne["tot_upload"] >= upload_config.stats_upload_seuil]

    return upload_srv

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Script d'analyse d'échange de données entre un truc et un autre.", add_help=False)
    parser.add_argument("-b", "--begin", help="Date de début, dans un format \"AAAA/MM/JJ HH:MM:SS\"", type=str, action="store")
    parser.add_argument("-e", "--end", help="Date de fin, dans un format \"AAAA/MM/JJTHH:MM:SS\"", type=str, action="store")
    parser.add_argument("-m", "--mail", help="Envoyer un mail avec les résultats.", action="store_true")
    parser.add_argument("-h", "--help", help="Affiche cette aide et quitte.", action="store_true")

    args = parser.parse_args()
    now = time.time()

    if args.help:
        parser.print_help()
        sys.exit(0)
    if not args.begin:
        args.begin = time.strftime("%Y/%m/%d %H:%M:%S", time.localtime(now - 86400))
    if not args.end:
        args.end = time.strftime("%Y/%m/%d %H:%M:%S", time.localtime(now))

    stats_upload = upload_srv_stats(args)
    entete = [u"Nom", u"Type", u"Upload", u"Download"]
    longueur = ['*', 4, 13, 13]
    formats = ['s', 's', 'o', 'o']
    if args.mail:
        styles = [None, None, None, None]
    else:
        styles = [None, None, "vert", "rouge"]

    fetched_upload_srv = affichage.tableau(stats_upload, titre=entete, largeur=longueur, styles=styles, format=formats, width=80)

    if args.mail:
        expediteur = "disconnect@crans.org"
        destinataire = "disconnect@crans.org"
        # Testing purpose:
        # destinataire = "your.mail@crans.org"
        corps = mail_module.generate('upload_stats',
            {
                'from': expediteur,
                'to': destinataire,
                'upload_srv': fetched_upload_srv,
                'seuil_upload': upload_config.pretty_seuil,
            }).as_string()

        with mail_module.ServerConnection() as conn:
            conn.sendmail(expediteur, destinataire, corps)
    else:
        print fetched_upload_srv