statistiques.py 9.65 KB
Newer Older
pessoles's avatar
pessoles committed
1
#! /usr/bin/env python
2
# -*- mode: python; coding: utf-8 -*-
pessoles's avatar
pessoles committed
3

chove's avatar
chove committed
4
"""
5 6
Script d'envoi des statistiques des déconnections
et du trafic de la journée à disconnect@
7

8
Copyright (C) Xavier Pessoles - Étienne Chové - Michel Blockelet
chove's avatar
chove committed
9
Licence : GPLv2
chove's avatar
chove committed
10 11
"""

pessoles's avatar
pessoles committed
12 13 14 15 16
###########################
# Import des commmandes : #
###########################

import commands
17
import sys, os
18
import psycopg2
pessoles's avatar
pessoles committed
19
sys.path.append('/usr/scripts/gestion')
20
sys.path.append('/usr/scripts/surveillance')
21
import config.virus
pessoles's avatar
pessoles committed
22
import smtplib
23
import socket
24
import time, random, hashlib
25
import netaddr
26
from analyse import stats
27
from affich_tools import tableau
28
from iptools import AddrInNet
29 30 31
from ldap_crans import AssociationCrans, crans_ldap

CL = crans_ldap()
32

33 34 35 36 37
def ipv4or6(addr):
    if ':' in addr:
        return "IPv6"
    else:
        return "IPv4"
38

39
# Liste des IP des serveurs
40 41 42
ips_serveurs = []
ips_proxys = []
for m in AssociationCrans().machines():
43
    if m.nom() in [u'charybde.crans.org', u'sable.crans.org']:
44 45 46
        ips_proxys.extend([m.ip(), str(m.ipv6())])
    else:
        ips_serveurs.extend([m.ip(), str(m.ipv6())])
47

48 49 50
##############################
# Ouverture des connexions : #
##############################
pessoles's avatar
pessoles committed
51

52
pgsql = psycopg2.connect(database="filtrage", user="crans")
pessoles's avatar
pessoles committed
53 54 55 56 57
curseur = pgsql.cursor()

###########################
# Statistiques d'upload : #
###########################
58
# Liste des uploads :
pessoles's avatar
pessoles committed
59
#####################
60
requete = """(SELECT ip_crans, sum(upload) AS somme, sum(download)
61 62 63 64 65 66 67 68 69 70
FROM upload
WHERE
  date > timestamp 'now' - interval '1 day'
  AND NOT EXISTS (
    SELECT 1
    FROM exemptes
    WHERE
      upload.ip_crans <<= exemptes.ip_crans
      AND upload.ip_ext <<= exemptes.ip_dest
  )
71 72 73 74 75
  GROUP BY ip_crans
)

UNION

76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
(SELECT 
    ip_crans, sum(upload) AS somme, sum(download) 
FROM
    (
        SELECT DISTINCT * FROM 
        (
            SELECT 
                upload6.date, mac_ip.mac AS ip_crans, upload6.ip_ext, upload6.id, upload6.port_crans, upload6.port_ext, upload6.download, upload6.upload
            FROM mac_ip,upload6 
            WHERE 
                upload6.ip_crans = mac_ip.ip 
                AND upload6.date > mac_ip.date 
                AND upload6.date - interval '1 day' < mac_ip.date 
                AND upload6.date > timestamp 'now' - interval '1 day'
                AND upload6.date < 'now'
                AND NOT EXISTS
                (
                    SELECT 1
                    FROM exemptes
                    WHERE upload6.ip_crans <<= exemptes.ip_crans
                    AND upload6.ip_ext <<= exemptes.ip_dest
                )
        ) AS upload
    ) AS upload
GROUP BY
    ip_crans
102
)
103
ORDER BY somme DESC"""
pessoles's avatar
pessoles committed
104
curseur.execute(requete)
105 106
data_upload = curseur.fetchall()

107
liste_upload = tableau(data = [ (l[1], l[2], ipv4or6(str(l[0])), socket.getfqdn(str(l[0])))
108
                                for l in data_upload
109 110
                                if int(l[1]) > 100*1024*1024
                                and l[0] not in (ips_serveurs + ips_proxys)],
111 112 113
                       titre = ['upload', 'download', 'proto', 'machine'],
                       largeur = [10, 10, 10, 40],
                       format = ['o', 'o', 's', 's'],
114
                       alignement = ['d', 'd', 'c', 'c']).encode('utf-8')
115

116
# Trafic exempté :
117
##################
118
requete = """(SELECT ip_crans, sum(upload) AS somme, sum(download)
glondu's avatar
glondu committed
119 120 121 122 123 124 125 126
FROM upload
WHERE
  date > timestamp 'now' - interval '1 day'
  AND EXISTS (
    SELECT 1
    FROM exemptes
    WHERE
      upload.ip_crans = exemptes.ip_crans
127
      -- AND upload.ip_ext <<= exemptes.ip_dest
glondu's avatar
glondu committed
128 129
  )
GROUP BY ip_crans
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144
)
UNION
(SELECT ip_crans, sum(upload) AS somme, sum(download)
FROM upload6
WHERE
  date > timestamp 'now' - interval '1 day'
  AND EXISTS (
    SELECT 1
    FROM exemptes
    WHERE
      upload6.ip_crans = exemptes.ip_crans
      -- AND upload6.ip_ext <<= exemptes.ip_dest
  )
GROUP BY ip_crans
)
glondu's avatar
glondu committed
145
ORDER BY somme DESC"""
pessoles's avatar
pessoles committed
146
curseur.execute(requete)
147
liste_exemptes = tableau(data = [[l[1], l[2], ipv4or6(str(l[0])), socket.getfqdn(str(l[0]))]
148
                            for l in curseur.fetchall()],
149 150 151
                         titre = ['upload', 'download', 'proto', 'machine'],
                         largeur = [10, 10, 10, 30],
                         format = ['o', 'o', 's', 's'],
152
                         alignement = ['d', 'd', 'c', 'c']).encode('utf-8')
chove's avatar
chove committed
153

154
# Upload des serveurs :
pessoles's avatar
pessoles committed
155
#######################
156
liste_serveurs = []
157 158 159 160 161 162 163 164 165 166
for l in data_upload:
    if l[0] in (ips_serveurs + ips_proxys) and l[1] + l[2] > 10*1024*1024:
        liste_serveurs.append([l[1], l[2], ipv4or6(str(l[0])), socket.getfqdn(l[0])])
#    requete = """SELECT sum(upload), sum(download)
#    FROM upload
#    WHERE
#      ip_crans='%s'
#      AND date > timestamp 'now' - interval '1 day' """ % IP
#    curseur.execute(requete)
#    traffic = curseur.fetchone()
167
    # On ne compte pas le serveur si le trafic est petit
168

169
liste_serveurs = tableau(data = liste_serveurs,
170 171 172
                         titre = ['upload', 'download', 'proto', 'serveur'],
                         largeur = [10, 10, 10, 30],
                         format = ['o', 'o', 's', 's'],
173
                         alignement = ['d', 'd', 'c', 'c']).encode('utf-8')
174 175


chove's avatar
chove committed
176 177
# statistiques des gros uploads depuis les serveurs
###################################################
178 179
# Liste des IP des serveurs
gros_uploads_des_serveurs = stats(ip_crans=ips_serveurs,
180
    show=['ip_crans', 'ip_ext'], upload_mini=50,
181
    show_limit=100).encode('utf-8')
chove's avatar
chove committed
182 183 184 185 186

############################
# Statistiques virus/p2p : #
############################

187 188
# IPs envoyant des paquets de protocole Ethernet inconnu :
#########################################################
189
requete = """(SELECT COUNT(*), ip_ext
190 191 192
FROM upload
WHERE
  id=-1
193 194 195 196 197 198 199 200 201 202
GROUP BY ip_ext)

UNION

(SELECT COUNT(*), ip_ext
FROM upload6
WHERE
  id=-1
GROUP BY ip_ext
)"""
203 204
curseur.execute(requete)

205
liste_etherunk = tableau(data = [[l[0], socket.getfqdn(str(l[1]))]
206 207 208
                            for l in curseur.fetchall()],
                         titre = ['nombre','ip'],
                         largeur = [10, 30],
209
                         alignement = ['d','c']).encode('utf-8')
210 211


212
# Machines actuellement déconnectées :
chove's avatar
chove committed
213
######################################
chove's avatar
chove committed
214
requete = "SELECT DISTINCT ip_crans FROM avertis_virus"
pessoles's avatar
pessoles committed
215
curseur.execute(requete)
chove's avatar
chove committed
216
infections = [ x[0] for x in curseur.fetchall() ]
glondu's avatar
glondu committed
217
liste_virus = []
chove's avatar
chove committed
218
for IP in infections:
219
    hostname = socket.getfqdn(IP)
glondu's avatar
glondu committed
220
    liste_virus.append(["%s" % (str(hostname))])
221

222
liste_virus = tableau(liste_virus,
223
    titre=['machine'], largeur=[30]).encode('utf-8')
224

225
# Machines ayant fait des attaques virus dans la journée :
chove's avatar
chove committed
226
##########################################################
227 228 229 230 231
requete = """SELECT * FROM (SELECT ip_src,count(ip_src) as compteur FROM virus
WHERE date > timestamp 'now' - interval '1 day'
GROUP BY ip_src ORDER BY compteur DESC)
AS tous
WHERE tous.compteur>'%s' LIMIT 30""" % config.virus.virus
pessoles's avatar
pessoles committed
232 233 234
curseur.execute(requete)
liste_virus2 = []
for IP, compteur in curseur.fetchall():
235
    hostname = socket.getfqdn(IP)
glondu's avatar
glondu committed
236
    liste_virus2.append([hostname, compteur])
237 238 239
liste_virus2 = tableau(data = liste_virus2,
                       titre = ['machine', 'nombre'],
                       largeur = [30, 12],
240
                       alignement = ['c', 'd']).encode('utf-8')
pessoles's avatar
pessoles committed
241

chove's avatar
chove committed
242

243
# Machines ayant fait de attaques flood dans la journée :
chove's avatar
chove committed
244
#########################################################
245
requete = """SELECT * FROM (SELECT ip_src,count(ip_src) as compteur FROM flood
246
WHERE date > timestamp 'now' - interval '1 day'
247 248 249
GROUP BY ip_src ORDER BY compteur DESC)
AS tous
WHERE tous.compteur>'%s' LIMIT 30""" % config.virus.flood
pessoles's avatar
pessoles committed
250 251 252
curseur.execute(requete)
liste_virus3 = []
for IP, compteur in curseur.fetchall():
253
    hostname = socket.getfqdn(IP)
glondu's avatar
glondu committed
254
    liste_virus3.append([hostname, compteur])
255 256 257
liste_virus3 = tableau(data = liste_virus3,
                       titre = ['machine', 'nombre'],
                       largeur = [30, 12],
258
                       alignement = ['c', 'd']).encode('utf-8')
259 260


pessoles's avatar
pessoles committed
261 262 263
#############
# Message : #
#############
chove's avatar
chove committed
264

pessoles's avatar
pessoles committed
265
expediteur = "disconnect@crans.org"
pessoles's avatar
pessoles committed
266
destinataire = "disconnect@crans.org"
glondu's avatar
glondu committed
267
message = """From: %(From)s
pessoles's avatar
pessoles committed
268
To: %(To)s
269
Subject: Statistiques des =?utf-8?q?derni=C3=A8res?= 24h
270
X-Mailer: /usr/scripts/surveillance/statistiques.py
bernat's avatar
bernat committed
271
Message-Id: <%(uuid)s1@crans.org>
272
Content-Type: text/plain; charset="utf-8"
pessoles's avatar
pessoles committed
273

274
*Gros uploads des serveurs* (charybde et sable sont exemptés totalement)
pessoles's avatar
pessoles committed
275

276
%(gros_uploads_des_serveurs)s
pessoles's avatar
pessoles committed
277

278
*Statistiques de trafic des serveurs*
pessoles's avatar
pessoles committed
279 280 281

%(liste_serveurs)s

282 283 284 285
*IPs envoyant des paquets de protocole Ethernet inconnu*

%(liste_etherunk)s

286
*Machines actuellement déconnectées pour virus*
pessoles's avatar
pessoles committed
287 288 289

%(liste_virus)s

290
*Machines ayant commis des attaques virales dans la journée*
pessoles's avatar
pessoles committed
291 292 293

%(liste_virus2)s

294
*Machines ayant commis des attaques virales de type flood dans la journée*
pessoles's avatar
pessoles committed
295 296 297

%(liste_virus3)s

298
*Statistiques de trafic des adhérents* (tout le trafic)
pessoles's avatar
pessoles committed
299 300 301

%(liste_upload)s

302
*Statistiques de trafic des adhérents exemptés* (juste le trafic exempté)
pessoles's avatar
pessoles committed
303 304 305

%(liste_exemptes)s

306
-- 
pessoles's avatar
pessoles committed
307 308
statistiques.py
"""
309

310
uuid = hashlib.md5(str(long(time.time() * 1000)) +
bernat's avatar
bernat committed
311
               str(long(random.random()*100000000000000000L))).hexdigest()
glondu's avatar
glondu committed
312 313 314
corps = message % { 'From': expediteur,
                    'To': destinataire,
                    'uuid': uuid,
315
                    'gros_uploads_des_serveurs': gros_uploads_des_serveurs,
316
                    'liste_etherunk': liste_etherunk,
317
                    'liste_serveurs': liste_serveurs,
glondu's avatar
glondu committed
318 319 320 321
                    'liste_upload': liste_upload,
                    'liste_virus': liste_virus,
                    'liste_virus2': liste_virus2,
                    'liste_virus3': liste_virus3,
322 323
                    'liste_exemptes': liste_exemptes
                     }
pessoles's avatar
pessoles committed
324

Mathieu Segaud's avatar
Mathieu Segaud committed
325
mail = smtplib.SMTP('localhost')
326 327
mailaddr = os.getenv('CRANS_EMAIL', 'disconnect@crans.org')
mail.sendmail(mailaddr, mailaddr, corps)