Pfsense 2.2.2 + Snort + barnyard2 + Snorby



  • Hola comunidad, tengo un firewall en mi red usando pfsense en su última versión, tengo snort corriendo en el mismo y en un servidor virtual (openvz) sobre proxmox tengo montardo en un debian 7 en el cual tengo corriendo una aplicación llamada snorby la cual me da información desde si interfaz web sobre toda la actividad que registra el snort en el pfsense, ya que pfsense usando el barnyard2 debe conectarte a un servidor mysql que esta corriendo en el propio openvz donde está snorby, la cuestión es que no se conecta a pesar de que esta creada la base de datos, esta creado el usuario con el que el pfsense debe conectarse, tiene bien especificado la ip del pfsense para que ese user solo pueda conectar con esa base de datos desde la ip del pfsense que es el gateway de la subred donde estan los servidores virtuales, la clave la he rectificado una pila de veces, desde la consola del pfsense puedo hacer un telnet al puerto 3306 del servidor mysql en el openvz y conecta sin problemas, sin embargo cuando voy a la interfaz del pfsense al menu Services/Snort y en la pestaña:
    Snort Interfaces me aparece el snort en verde que significa que está corriendo y la del Barnyard2 en rojo, le doy click ahi mismo y arranca y pasa a verde, mando a reiniciar el snort y se quedan los 2 en verde, pero pasados unos seguntos o el minuto el Barnyard2 se vuelve a poner en rojo y en los log del pfsense lo único que veo es esto:

    Jun 15 12:10:17 barnyard2[28043]:
    Jun 15 12:10:17 barnyard2[28043]: –== Initializing Barnyard2 ==--
    Jun 15 12:10:17 barnyard2[28043]: Initializing Input Plugins!
    Jun 15 12:10:17 barnyard2[28043]: Initializing Output Plugins!
    Jun 15 12:10:17 barnyard2[28043]: Parsing config file "/usr/pbi/snort-amd64/etc/snort/snort_13223_re0/barnyard2.conf"
    Jun 15 12:10:17 barnyard2[28043]: Found pid path directive (/var/run)
    Jun 15 12:10:17 barnyard2[28043]: +[ Signature Suppress list ]+ –--------------------------
    Jun 15 12:10:17 barnyard2[28043]: +[No entry in Signature Suppress List]+
    Jun 15 12:10:17 barnyard2[28043]: –-------------------------- +[ Signature Suppress list ]+
    Jun 15 12:10:19 barnyard2[28043]: Barnyard2 spooler: Event cache size set to [8192]
    Jun 15 12:10:19 barnyard2[28043]: Log directory = /var/log/snort/snort_re013223
    Jun 15 12:10:19 barnyard2[28043]: INFO database: Defaulting Reconnect/Transaction Error limit to 10
    Jun 15 12:10:19 barnyard2[28043]: INFO database: Defaulting Reconnect sleep time to 5 second
    Jun 15 12:10:19 barnyard2[28043]: Initializing daemon mode
    Jun 15 12:10:19 barnyard2[28043]: Daemon parent exiting
    Jun 15 12:10:19 barnyard2[31207]: Daemon initialized, signaled parent pid: 28043
    Jun 15 12:10:19 barnyard2[31207]: PID path stat checked out ok, PID path set to /var/run
    Jun 15 12:10:19 barnyard2[31207]: Writing PID "31207" to file "/var/run/barnyard2_re013223.pid"

    Alguien me pueda dar una mano con esto???

    Gracias…



  • Hola,

    Que muestran los logs del mysql ?

    Si llegan las peticiones al server de Mysql?



  • Hola comlega, gracias por contestar a mi mensaje, sabes que eso no lo había comprobado! Pero bueno en el servidor donde tengo el MySQL Server y el Snorby en /var/log tengo un directorio llamado mysql pero está vacio y en la raiz de /var/log hay varios ficheros llamados:

    /var/log/mysql.log
    /var/log/mysql.log.1.gz
    /var/log/mysql.log.2.gz
    .
    .
    .
    y así sucesivamente, también hay otros llamados

    /var/log/mysql.err

    de este solo existe ese, no hay ninguno .err.gz y todos están en blanco, sin información! el servidor mysql funciona perfectamente, acceso a la base de datos sin problema tanto desde la consola como desde phpmyadmin y el servicio inicia sin dar palo alguno.

    Saludos…



  • Yo sugiero que cheques que las peticiones esten llegando al servidor de MySQL via un tcpdump o wireshark directamente en el servidor de mysql para determinar si esto puede ser o no el problema.

    La otra cuestion es que puedes verficiar en el mysql.log si hay algun error de autenticacion o algo similar



  • Bueno, corriendo el comando:  tcpdump port 3306 -vv

    Desde la consola del servidor Snorby obtengo esto:

    13:44:25.581048 IP (tos 0x8, ttl 64, id 31227, offset 0, flags [DF], proto TCP (6), length 63)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x6ea8 (correct), seq 209591:209602, ack 8266601, win 520, options [nop,nop,TS val 331579447 ecr 186656487], length 11
    13:44:25.581128 IP (tos 0x8, ttl 64, id 45116, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x392b (correct), seq 8266601:8266612, ack 209602, win 1104, options [nop,nop,TS val 186656487 ecr 331579447], length 11
    13:44:25.581223 IP (tos 0x8, ttl 64, id 52400, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x427d (correct), seq 209602, ack 8266612, win 520, options [nop,nop,TS val 331579447 ecr 186656487], length 0
    13:44:25.581268 IP (tos 0x8, ttl 64, id 33948, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x3370 (correct), seq 209602:209607, ack 8266612, win 520, options [nop,nop,TS val 331579447 ecr 186656487], length 5
    13:44:25.581325 IP (tos 0x8, ttl 64, id 45117, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x391b (correct), seq 8266612:8266623, ack 209607, win 1104, options [nop,nop,TS val 186656487 ecr 331579447], length 11
    13:44:25.581421 IP (tos 0x8, ttl 64, id 42375, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x426d (correct), seq 209607, ack 8266623, win 520, options [nop,nop,TS val 331579447 ecr 186656487], length 0
    13:44:25.581477 IP (tos 0x8, ttl 64, id 54583, offset 0, flags [DF], proto TCP (6), length 134)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x9778 (correct), seq 209607:209689, ack 8266623, win 520, options [nop,nop,TS val 331579447 ecr 186656487], length 82
    13:44:25.581664 IP (tos 0x8, ttl 64, id 45118, offset 0, flags [DF], proto TCP (6), length 107)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0xdf7f (correct), seq 8266623:8266678, ack 209689, win 1104, options [nop,nop,TS val 186656488 ecr 331579447], length 55
    13:44:25.581777 IP (tos 0x8, ttl 64, id 45986, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x41e4 (correct), seq 209689, ack 8266678, win 519, options [nop,nop,TS val 331579447 ecr 186656488], length 0
    13:44:25.581941 IP (tos 0x8, ttl 64, id 17808, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x32d6 (correct), seq 209689:209694, ack 8266678, win 520, options [nop,nop,TS val 331579447 ecr 186656488], length 5
    13:44:25.581969 IP (tos 0x8, ttl 64, id 45119, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x3880 (correct), seq 8266678:8266689, ack 209694, win 1104, options [nop,nop,TS val 186656488 ecr 331579447], length 11
    13:44:25.582093 IP (tos 0x8, ttl 64, id 2485, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x41d2 (correct), seq 209694, ack 8266689, win 520, options [nop,nop,TS val 331579448 ecr 186656488], length 0
    13:44:25.582121 IP (tos 0x8, ttl 64, id 14784, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x32c5 (correct), seq 209694:209699, ack 8266689, win 520, options [nop,nop,TS val 331579448 ecr 186656488], length 5
    13:44:25.582198 IP (tos 0x8, ttl 64, id 45120, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x386f (correct), seq 8266689:8266700, ack 209699, win 1104, options [nop,nop,TS val 186656488 ecr 331579448], length 11
    13:44:25.582294 IP (tos 0x8, ttl 64, id 8923, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x41c2 (correct), seq 209699, ack 8266700, win 520, options [nop,nop,TS val 331579448 ecr 186656488], length 0
    13:44:25.582339 IP (tos 0x8, ttl 64, id 18125, offset 0, flags [DF], proto TCP (6), length 66)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x0c4d (correct), seq 209699:209713, ack 8266700, win 520, options [nop,nop,TS val 331579448 ecr 186656488], length 14
    13:44:25.582413 IP (tos 0x8, ttl 64, id 45121, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x3858 (correct), seq 8266700:8266711, ack 209713, win 1104, options [nop,nop,TS val 186656488 ecr 331579448], length 11
    13:44:25.582512 IP (tos 0x8, ttl 64, id 5642, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x41a9 (correct), seq 209713, ack 8266711, win 520, options [nop,nop,TS val 331579448 ecr 186656488], length 0
    13:44:25.607999 IP (tos 0x8, ttl 64, id 31367, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x3282 (correct), seq 209713:209718, ack 8266711, win 520, options [nop,nop,TS val 331579474 ecr 186656488], length 5
    13:44:25.608032 IP (tos 0x8, ttl 64, id 45122, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x3814 (correct), seq 8266711:8266722, ack 209718, win 1104, options [nop,nop,TS val 186656514 ecr 331579474], length 11
    13:44:25.608155 IP (tos 0x8, ttl 64, id 40683, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x4165 (correct), seq 209718, ack 8266722, win 520, options [nop,nop,TS val 331579474 ecr 186656514], length 0
    13:44:25.608233 IP (tos 0x8, ttl 64, id 7215, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x3258 (correct), seq 209718:209723, ack 8266722, win 520, options [nop,nop,TS val 331579474 ecr 186656514], length 5
    13:44:25.608260 IP (tos 0x8, ttl 64, id 45123, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x3804 (correct), seq 8266722:8266733, ack 209723, win 1104, options [nop,nop,TS val 186656514 ecr 331579474], length 11
    13:44:25.608354 IP (tos 0x8, ttl 64, id 22422, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x4155 (correct), seq 209723, ack 8266733, win 520, options [nop,nop,TS val 331579474 ecr 186656514], length 0
    13:44:25.608385 IP (tos 0x8, ttl 64, id 27665, offset 0, flags [DF], proto TCP (6), length 63)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x6d6a (correct), seq 209723:209734, ack 8266733, win 520, options [nop,nop,TS val 331579474 ecr 186656514], length 11
    13:44:25.608463 IP (tos 0x8, ttl 64, id 45124, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x37ed (correct), seq 8266733:8266744, ack 209734, win 1104, options [nop,nop,TS val 186656514 ecr 331579474], length 11
    13:44:25.608560 IP (tos 0x8, ttl 64, id 24436, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x413f (correct), seq 209734, ack 8266744, win 520, options [nop,nop,TS val 331579474 ecr 186656514], length 0
    13:44:25.608614 IP (tos 0x8, ttl 64, id 28572, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x3232 (correct), seq 209734:209739, ack 8266744, win 520, options [nop,nop,TS val 331579474 ecr 186656514], length 5
    13:44:25.608635 IP (tos 0x8, ttl 64, id 45125, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x37dc (correct), seq 8266744:8266755, ack 209739, win 1104, options [nop,nop,TS val 186656515 ecr 331579474], length 11
    13:44:25.608732 IP (tos 0x8, ttl 64, id 47580, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x412e (correct), seq 209739, ack 8266755, win 520, options [nop,nop,TS val 331579474 ecr 186656515], length 0
    13:44:25.608754 IP (tos 0x8, ttl 64, id 36905, offset 0, flags [DF], proto TCP (6), length 102)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x294a (correct), seq 209739:209789, ack 8266755, win 520, options [nop,nop,TS val 331579474 ecr 186656515], length 50
    13:44:25.609032 IP (tos 0x8, ttl 64, id 45126, offset 0, flags [DF], proto TCP (6), length 104)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0xd4c5 (correct), seq 8266755:8266807, ack 209789, win 1104, options [nop,nop,TS val 186656515 ecr 331579474], length 52
    13:44:25.609154 IP (tos 0x8, ttl 64, id 63391, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x40c8 (correct), seq 209789, ack 8266807, win 519, options [nop,nop,TS val 331579475 ecr 186656515], length 0
    13:44:25.609183 IP (tos 0x8, ttl 64, id 49893, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x31ba (correct), seq 209789:209794, ack 8266807, win 520, options [nop,nop,TS val 331579475 ecr 186656515], length 5
    13:44:25.609258 IP (tos 0x8, ttl 64, id 45127, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x3765 (correct), seq 8266807:8266818, ack 209794, win 1104, options [nop,nop,TS val 186656515 ecr 331579475], length 11
    13:44:25.609355 IP (tos 0x8, ttl 64, id 24888, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x40b7 (correct), seq 209794, ack 8266818, win 520, options [nop,nop,TS val 331579475 ecr 186656515], length 0
    13:44:25.609399 IP (tos 0x8, ttl 64, id 59639, offset 0, flags [DF], proto TCP (6), length 64)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x448e (correct), seq 209794:209806, ack 8266818, win 520, options [nop,nop,TS val 331579475 ecr 186656515], length 12
    13:44:25.633090 IP (tos 0x8, ttl 64, id 45128, offset 0, flags [DF], proto TCP (6), length 63)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [P.], cksum 0x3737 (correct), seq 8266818:8266829, ack 209806, win 1104, options [nop,nop,TS val 186656539 ecr 331579475], length 11
    13:44:25.633212 IP (tos 0x8, ttl 64, id 16075, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [.], cksum 0x4070 (correct), seq 209806, ack 8266829, win 520, options [nop,nop,TS val 331579499 ecr 186656539], length 0
    13:44:25.633385 IP (tos 0x8, ttl 64, id 61270, offset 0, flags [DF], proto TCP (6), length 57)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [P.], cksum 0x3e63 (correct), seq 209806:209811, ack 8266829, win 520, options [nop,nop,TS val 331579499 ecr 186656539], length 5
    13:44:25.633416 IP (tos 0x8, ttl 64, id 45129, offset 0, flags [DF], proto TCP (6), length 52)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [F.], cksum 0x3e22 (correct), seq 8266829, ack 209811, win 1104, options [nop,nop,TS val 186656539 ecr 331579499], length 0
    13:44:25.633427 IP (tos 0x8, ttl 64, id 65171, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [F.], cksum 0x406a (correct), seq 209811, ack 8266829, win 520, options [nop,nop,TS val 331579499 ecr 186656539], length 0
    13:44:25.633437 IP (tos 0x8, ttl 64, id 45130, offset 0, flags [DF], proto TCP (6), length 52)
        snorby.pri.co.cu.mysql > firewall.pri.co.cu.39864: Flags [.], cksum 0x3e21 (correct), seq 8266830, ack 209812, win 1104, options [nop,nop,TS val 186656539 ecr 331579499], length 0
    13:44:25.633528 IP (tos 0x8, ttl 64, id 54831, offset 0, flags [DF], proto TCP (6), length 52)
        firewall.pri.co.cu.39864 > snorby.pri.co.cu.mysql: Flags [F.], cksum 0x4069 (correct), seq 209811, ack 8266830, win 520, options [nop,nop,TS val 331579499 ecr 186656539], length 0

    Con este otro comando obtuve esto desde el fichero .txt al cual redireccione la salida

    12:43:03.900620 IP snorby.pri.co.cu.mysql > firewall.pri.co.cu.64088: Flags [.], seq 6762194:6763642, ack 1098, win 122, options [nop,nop,TS val 182974807 ecr 327897766], length 1448
    E….H@.@..u...........XE.62.Q.....z.......

    ..W..R.der: WARNING: Bad MPLS FrameV..W.83022.165.116.1.19.3@snort_decoder: WARNING: GRE Trans header length > payload lengthI..X.83023.164.116.1.19.33snort_decoder: WARNING: Invalid GRE v.1 PPTP headerD..Y.83024.163.116.1.19.3.snort_decoder: WARNING: Invalid GRE v.0 headerA..Z.83025.162.116.1.19.3+snort_decoder: WARNING: Invalid GRE versionO..[.83026.161.116.1.19.39snort_decoder: WARNING: Multiple encapsulations in packetP...83027.160.116.1.19.3:snort_decoder: WARNING: GRE header length > payload lengthH..].83028.151.116.1.5.23snort_decoder: WARNING: Bad Traffic Same Src/Dst IPD..^.83029.150.116.1.5.2/snort_decoder: WARNING: Bad Traffic Loopback IPE.._.83030.143.116.1.5.20snort_decoder: WARNING: Bad Token Ring MR HeaderH..`.83031.142.116.1.5.23snort_decoder: WARNING: Bad Token Ring MRLEN HeaderI..a.83032.141.116.1.5.24snort_decoder: WARNING: Bad Token Ring ETHLLC HeaderB..b.83033.140.116.1.5.2-snort_decoder: WARNING: Bad Token Ring HeaderF..c.83034.134.116.1.5.21snort_decoder: WARNING: Bad 802.11 Extra LLC InfoB..d.83035.133.116.1.5.2-snort_decoder: WARNING: Bad 802.11 LLC header?..e.83036.132.116.1.5.2*snort_decoder: WARNING: Bad Extra LLC Info;..f.83037.131.116.1.5.2&snort_decoder: WARNING: Bad LLC header;..g.83038.130.116.1.5.2&snort_decoder: WARNING: Bad VLAN FrameE..h.83039.120.116.1.5.20snort_decoder: WARNING: Bad PPPOE frame detectedA..i.83040.112.116.1.5.2,snort_decoder: WARNING: EAP Header Truncated>..j.8
    12:43:03.900726 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6739026, win 509, options [nop,nop,TS val 327897766 ecr 182974804], length 0
    E..4.E@.@.. …......X...Q..E.......^T.....
    ..R.
    ..T
    12:43:03.900787 IP snorby.pri.co.cu.mysql > firewall.pri.co.cu.64088: Flags [.], seq 6763642:6765090, ack 1098, win 122, options [nop,nop,TS val 182974807 ecr 327897766], length 1448
    E….I@.@..t...........XE.;..Q.....z.&.....

    ..W..R..2)snort_decoder: WARNING: EAP Key TruncatedA..k.83042.110.116.1.5.2,snort_decoder: WARNING: Truncated EAP HeaderA..l.83043.109.116.1.5.2,snort_decoder: WARNING: Truncated ARP PacketO..m.83044.108.116.1.19.39snort_decoder: WARNING: Unknown Datagram decoding problemK..n.83045.107.116.1.19.35snort_decoder: WARNING: ICMP Address Header TruncatedM..o.83046.106.116.1.19.37snort_decoder: WARNING: ICMP Timestamp Header TruncatedC..p.83047.105.116.1.19.3-snort_decoder: WARNING: ICMP Header Truncated[..q.83048.98.116.1.19.3Fsnort_decoder: WARNING: Long UDP packet, length field < payload length..r.83049.97.116.1.19.3Gsnort_decoder: WARNING: Short UDP packet, length field > payload lengthQ..s.83050.96.116.1.19.3 <snort_decoder: warning:="" invalid="" udp="" header,="" length="" field="" <="" 8a..t.83051.95.116.1.19.3,snort_decoder:="" truncated="" headery..u.83052.59.116.1.19.3dsnort_decoder:="" tcp="" window="" scale="" option="" (="">14)E..v.83053.58.116.1.19.30snort_decoder: WARNING: Experimental TCP optionsA..w.83054.57.116.1.19.3,snort_decoder: WARNING: Obsolete TCP options;..x.83055.56.116.1.19.3&snort_decoder: WARNING: T/TCP DetectedB..y.83056.55.116.1.19.3-snort_decoder: WARNING: Truncated Tcp OptionsO..z.83057.54.116.1.19.3:snort_decoder: WARNING: Tcp Options found with bad lengthsR..{.83058.47.116.1.5.2>snort_decoder: WARNING: TCP Data Offset is longer than payloadJ..|.83059.46.116.1.5.26snort_decoder: WARNING: TCP Da
    12:43:03.900848 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6740474, win 509, options [nop,nop,TS val 327897766 ecr 182974805], length 0
    E..4..@.@….........X...Q..E..Z....X......
    ..R.
    ..U
    12:43:03.900888 IP snorby.pri.co.cu.mysql > firewall.pri.co.cu.64088: Flags [.], seq 6765090:6766538, ack 1098, win 122, options [nop,nop,TS val 182974807 ecr 327897766], length 1448
    E….J@.@..s...........XE.A..Q.....z.Y.....

    ..W..R.s than 5T..}.83060.45.116.1.19.3?snort_decoder: WARNING: TCP packet len is smaller than 20 bytesE..~.83061.6.116.1.19.31snort_decoder: WARNING: IP dgm len > captured lenB....83062.5.116.1.19.3.snort_decoder: WARNING: Truncated IPv4 Options<....83063.4.116.1.19.3(snort_decoder: WARNING: Bad IPv4 OptionsC....83064.3.116.1.19.3/snort_decoder: WARNING: IP dgm len < IP Hdr len@....83065.2.116.1.19.3,snort_decoder: WARNING: hlen < IP_HEADER_LEN=....83066.1.116.1.19.3)snort_decoder: WARNING: Not IPv4 datagram;....83067.4.112.1.5.2(spp_arpspoof: ARP Cache Overwrite Attack<....83068.3.112.1.5.2)spp_arpspoof: Etherframe ARP Mismatch DST<....83069.2.112.1.5.2)spp_arpspoof: Etherframe ARP Mismatch SRC6....83070.1.112.1.19.3"spp_arpspoof: Directed ARP Request;....83071.5.106.1.5.2(spp_rpc_decode: Zero-length RPC Fragment9....83072.4.106.1.5.2&spp_rpc_decode: Incomplete RPC segment<....83073.3.106.1.5.2)spp_rpc_decode: Large RPC Record FragmentB....83074.2.106.1.19.3.spp_rpc_decode: Multiple Records in one packet:....83075.1.106.1.19.3&spp_rpc_decode: Fragmented RPC Records<....83076.4.105.1.33.1(spp_bo: Back Orifice Snort Buffer Attack@....83077.3.105.1.33.1,spp_bo: Back Orifice Server Traffic Detected@....83078.2.105.1.33.1,spp_bo: Back Orifice Client Traffic Detected9....83079.1.105.1.33.1%spp_bo: Back Orifice Traffic Detected$....83080.1.2.1.17.3.tag: Tagged Packetm....83081.2016858.1.7.33.1UET TROJAN Generic - POST To
    12:43:03.900974 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6741922, win 509, options [nop,nop,TS val 327897766 ecr 182974805], length 0
    E..4*.@.@….........X...Q..E.......S......
    ..R.
    ..U
    12:43:03.900990 IP snorby.pri.co.cu.mysql > firewall.pri.co.cu.64088: Flags [P.], seq 6766538:6766704, ack 1098, win 122, options [nop,nop,TS val 182974807 ecr 327897766], length 166
    E….K@.@..u...........XE.G*.Q.....z.......

    ..W..R. ASCII Characters (Likely Zeus Derivative)o....83082.2016173.1.7.5.2XET TROJAN Generic -POST To gate.php w/Extended ASCII Characters (Likely Zeus Derivative).......!.
    12:43:03.901086 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6743370, win 509, options [nop,nop,TS val 327897767 ecr 182974805], length 0
    E..4..@.@….........X...Q..E.......MZ.....
    ..R.
    ..U
    12:43:03.901239 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6744818, win 509, options [nop,nop,TS val 327897767 ecr 182974805], length 0
    E..4l5@.@.M1…......X...Q..E..R....G......
    ..R.
    ..U
    12:43:03.901343 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6746266, win 509, options [nop,nop,TS val 327897767 ecr 182974805], length 0
    E..4..@.@….........X...Q..E.......B
    .....
    ..R.
    ..U
    12:43:03.901466 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6747714, win 520, options [nop,nop,TS val 327897767 ecr 182974805], length 0
    E..4Hj@.@.p….......X...Q..E.......<w.....<br>..R.
    ..U
    12:43:03.901589 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6749162, win 509, options [nop,nop,TS val 327897767 ecr 182974805], length 0
    E..4..@.@….........X...Q..E..J....6......
    ..R.
    ..U
    12:43:03.901711 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6750610, win 509, options [nop,nop,TS val 327897767 ecr 182974805], length 0
    E..4.>@.@..(…......X...Q..E.......1......
    ..R.
    ..U
    12:43:03.901853 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6752058, win 509, options [nop,nop,TS val 327897767 ecr 182974806], length 0
    E..4..@.@….........X...Q..E.......+i.....
    ..R.
    ..V
    12:43:03.901969 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6753506, win 509, options [nop,nop,TS val 327897767 ecr 182974806], length 0
    E..4.5@.@..1…......X...Q..E..B....%......
    ..R.
    ..V
    12:43:03.902094 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6754954, win 509, options [nop,nop,TS val 327897768 ecr 182974806], length 0
    E..4..@.@….........X...Q..E....... ......
    ..R.
    ..V
    12:43:03.902205 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6756402, win 509, options [nop,nop,TS val 327897768 ecr 182974806], length 0
    E..4Z,@.@._:…......X...Q..E........p.....
    ..R.
    ..V
    12:43:03.902325 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6757850, win 509, options [nop,nop,TS val 327897768 ecr 182974806], length 0
    E..4.
    @.@..Y…......X...Q..E.%:...........
    ..R.
    ..V
    12:43:03.902468 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6759298, win 509, options [nop,nop,TS val 327897768 ecr 182974806], length 0
    E..4..@.@….........X...Q..E....... .....
    ..R.
    ..V
    12:43:03.902590 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6760746, win 509, options [nop,nop,TS val 327897768 ecr 182974806], length 0
    E..4..@.@….........X...Q..E.0..... x.....
    ..R.
    ..V
    12:43:03.902709 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6762194, win 509, options [nop,nop,TS val 327897768 ecr 182974807], length 0
    E..4$r@.@….........X...Q..E.62...........
    ..R.
    ..W
    12:43:03.902817 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6763642, win 509, options [nop,nop,TS val 327897768 ecr 182974807], length 0
    E..4x.@.@.@….......X...Q..E.;......&.....
    ..R.
    ..W
    12:43:03.902947 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6765090, win 509, options [nop,nop,TS val 327897768 ecr 182974807], length 0
    E..4g.@.@.Q….......X...Q..E.A......~.....
    ..R.
    ..W
    12:43:03.903071 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6766538, win 509, options [nop,nop,TS val 327897769 ecr 182974807], length 0
    E..4..@.@….........X...Q..E.G
    ...........
    ..R.
    ..W
    12:43:03.903133 IP firewall.pri.co.cu.64088 > snorby.pri.co.cu.mysql: Flags [.], ack 6766704, win 520, options [nop,nop,TS val 327897769 ecr 182974807], length 0
    E..4.Y@.@….........X...Q..E.G......$.....
    ..R.
    ..W

    Alguna idea???

    Saludos...</w.....<br></snort_decoder:>



  • La idea es verificar si las peticiones del pfsense llegan al myslq por lo que yo filtraría la salida del tcpdump. La verdad es que me da flojera ver todo ese log



  • jajaja si la verdad toda esa información en ese log no es facilmente digerible!! :) Bueno he tenido a buscar información para hacer eso que me piedes, pues la verdad no había trabajado esta parte de capturar paquetes y esas cosas, bueno para filtrar el log use el comando de esta forma, si crees que hay una mejor forma te agradecería me dijeras como, no soy experto en esta parte pero por lo que veo en el log el servidor pfsense con IP 192.168.0.1 por la interfaz de la DMZ que es la subred donde están los demás servidores virtualizados sobre los servidores proxmox, todo parece indicar que si se está comunicando con la ip 192.168.0.4 por el puerto 3306 que es el del servidor MySQL en el servidor donde está Snorby, no puedo hacer otras conclusiones al respecto, quizás tu puedes ver algo que yo no veo o que no se interpretar, aqui esta la salida de tcpdump ya filtrada:

    Estos comandos son ejecutados en la consola del servidor MySQL

    tcpdump -nni eth0 'port 3306' -w /tmp/puerto.3306

    tcpdump -r /tmp/puerto.3306 -nn 'host snorby.pri.co.cu' > /home/traza.txt

    12:27:05.260625 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7271674, win 509, options [nop,nop,TS val 413339126 ecr 268416165], length 0
    12:27:05.260701 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7289050:7290498, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339126], length 1448
    12:27:05.260723 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7273122, win 509, options [nop,nop,TS val 413339126 ecr 268416165], length 0
    12:27:05.260761 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [P.], seq 7290498:7291946, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339126], length 1448
    12:27:05.260834 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7274570, win 509, options [nop,nop,TS val 413339126 ecr 268416165], length 0
    12:27:05.260856 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7291946:7293394, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339126], length 1448
    12:27:05.260955 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7276018, win 509, options [nop,nop,TS val 413339126 ecr 268416165], length 0
    12:27:05.260970 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7293394:7294842, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339126], length 1448
    12:27:05.261075 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7277466, win 509, options [nop,nop,TS val 413339126 ecr 268416166], length 0
    12:27:05.261097 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7294842:7296290, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339126], length 1448
    12:27:05.261202 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7278914, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261223 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7296290:7297738, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339127], length 1448
    12:27:05.261324 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7280362, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261362 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [P.], seq 7297738:7299186, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339127], length 1448
    12:27:05.261449 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7281810, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261470 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7299186:7300634, ack 1098, win 122, options [nop,nop,TS val 268416167 ecr 413339127], length 1448
    12:27:05.261571 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7283258, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261593 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7300634:7302082, ack 1098, win 122, options [nop,nop,TS val 268416168 ecr 413339127], length 1448
    12:27:05.261694 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7284706, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261715 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7302082:7303530, ack 1098, win 122, options [nop,nop,TS val 268416168 ecr 413339127], length 1448
    12:27:05.261817 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7286154, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261837 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7303530:7304978, ack 1098, win 122, options [nop,nop,TS val 268416168 ecr 413339127], length 1448
    12:27:05.261941 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7287602, win 509, options [nop,nop,TS val 413339127 ecr 268416166], length 0
    12:27:05.261961 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7304978:7306426, ack 1098, win 122, options [nop,nop,TS val 268416168 ecr 413339127], length 1448
    12:27:05.262063 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7289050, win 509, options [nop,nop,TS val 413339127 ecr 268416167], length 0
    12:27:05.262085 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [.], seq 7306426:7307874, ack 1098, win 122, options [nop,nop,TS val 268416168 ecr 413339127], length 1448
    12:27:05.262218 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7290498, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.262239 IP 192.168.0.4.3306 > 192.168.0.1.6419: Flags [P.], seq 7307874:7308252, ack 1098, win 122, options [nop,nop,TS val 268416168 ecr 413339128], length 378
    12:27:05.262337 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7291946, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.262461 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7293394, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.262585 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7294842, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.262706 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7296290, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.262836 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7297738, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.262953 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7299186, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.263076 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7300634, win 509, options [nop,nop,TS val 413339128 ecr 268416167], length 0
    12:27:05.263200 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7302082, win 509, options [nop,nop,TS val 413339129 ecr 268416168], length 0
    12:27:05.263330 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7303530, win 509, options [nop,nop,TS val 413339129 ecr 268416168], length 0
    12:27:05.263454 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7304978, win 509, options [nop,nop,TS val 413339129 ecr 268416168], length 0
    12:27:05.263576 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7306426, win 509, options [nop,nop,TS val 413339129 ecr 268416168], length 0
    12:27:05.263701 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7307874, win 509, options [nop,nop,TS val 413339129 ecr 268416168], length 0
    12:27:05.263726 IP 192.168.0.1.6419 > 192.168.0.4.3306: Flags [.], ack 7308252, win 520, options [nop,nop,TS val 413339129 ecr 268416168], length 0



  • 0.1 y 0.4 son los hosts involucrados ?

    Parece que si hay comunicacion entre ellos a nivel de la aplicacion, en este caso MySQL.

    Habria que revisar el log de MySQL y el de la aplicacion para rastrear si ha algun error en como estan llegando los datos, contraseñas erroneas o algo parecido.



  • Efectivamente, 192.168.0.1 es la ip del pfsense y 192.168.0.4 es la del servidor mysql. En los log del pfsense no veo otra cosa que haga referencia al Barnyard2 distinto de lo que ya puse en el 1er mensaje en este Post, ya sea desde la interfaz web del pfsense en la pestaña System o directamente accediendo vía SSH al pfsense para ir al Shell usando la opción 8 en su menu de opciones de consola, el archivo /var/log/system.log dice exactamente lo mismo, tienes idea de otro lugar donde buscar? En el caso del Servidor MySQL, tengo esto:

    cat /var/log/mysql/mysql.log

    c_ips,dst_ips,created_at,updated_atFROMcachesWHERE (ran_at>= '2015-06-19 00:00:00' ANDran_at<= '2015-06-19 23:59:59') ORDER BYupdated_atDESC LIMIT 1   50 Query SELECTsignatureFROMeventGROUP BYsignatureORDER BYtimestampDESC LIMIT 5   50 Query SELECTid,sig_id,events_count,name,text_color,bg_colorFROMseveritiesORDER BYid50 Query SELECTname,valueFROMsettingsWHEREname= 'company' ORDER BYnameLIMIT 1   50 Query SELECTname,valueFROMsettingsWHEREname= 'company' ORDER BYnameLIMIT 1 150620 13:20:56   50 Query SELECTname,valueFROMsettingsWHEREname= 'autodrop' ORDER BYnameLIMIT 1   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHEREhandlerLIKE '%!ruby/struct:Snorby::Jobs::SensorCacheJob%' ORDER BYidLIMIT 1   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHEREhandlerLIKE '%!ruby/struct:Snorby::Jobs::SensorCacheJob%' ORDER BYidLIMIT 1   50 Query DELETE FROMdelayed_jobsWHEREid= 33499   50 Query INSERT INTOdelayed_jobs(priority,attempts,handler,run_at) VALUES (1, 0, '–- !ruby/struct:Snorby::Jobs::SensorCacheJob verbose: false ', '2015-06-20 13:30:56')   50 Query DELETE FROMdelayed_jobsWHEREid= 33499   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHERE ((locked_atIS NULL ORlocked_at< '2015-06-20 09:20:56' ORlocked_by= 'delayed_job host:snorby pid:17264') ANDrun_at<= '2015-06-20 13:20:56' ANDfailed_atIS NULL) ORDER BYpriority,run_atLIMIT 5   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHERE ((locked_atIS NULL ORlocked_at< '2015-06-20 09:20:56' ORlocked_by= 'delayed_job host:snorby pid:17264') ANDrun_at<= '2015-06-20 13:20:56' ANDfailed_atIS NULL) ORDER BYpriority,run_atLIMIT 5 150620 13:21:01   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHERE ((locked_atIS NULL ORlocked_at< '2015-06-20 09:21:01' ORlocked_by= 'delayed_job host:snorby pid:17264') ANDrun_at<= '2015-06-20 13:21:01' ANDfailed_atIS NULL) ORDER BYpriority,run_atLIMIT 5 150620 13:21:06   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHERE ((locked_atIS NULL ORlocked_at< '2015-06-20 09:21:06' ORlocked_by= 'delayed_job host:snorby pid:17264') ANDrun_at<= '2015-06-20 13:21:06' ANDfailed_atIS NULL) ORDER BYpriority,run_atLIMIT 5 150620 13:21:11   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHERE ((locked_atIS NULL ORlocked_at< '2015-06-20 09:21:11' ORlocked_by= 'delayed_job host:snorby pid:17264') ANDrun_at<= '2015-06-20 13:21:11' ANDfailed_atIS NULL) ORDER BYpriority,run_atLIMIT 5 150620 13:21:16   50 Query SELECTid,priority,attempts,handler,run_at,locked_at,failed_atFROMdelayed_jobsWHERE ((locked_atIS NULL ORlocked_at< '2015-06-20 09:21:16' ORlocked_by= 'delayed_job host:snorby pid:17264') ANDrun_at<= '2015-06-20 13:21:16' ANDfailed_atIS NULL) ORDER BYpriority,run_at` LIMIT 5

    cat /var/log/mysql/mysql-slow.log

    SET timestamp=1434821458;
    update classifications set events_count = (select count(*)
              from event where event.classification_id = classifications.id);

    Time: 150620 13:30:59

    User@Host: root[root] @ localhost []

    Query_time: 0.000271  Lock_time: 0.000095 Rows_sent: 1  Rows_examined: 2

    SET timestamp=1434821459;
    SELECT id, priority, attempts, handler, run_at, locked_at, failed_at FROM delayed_jobs WHERE handler LIKE '%!ruby/struct:Snorby::Jobs::SensorCacheJob%' ORDER BY id LIMIT 1;

    User@Host: root[root] @ localhost []

    Query_time: 0.000298  Lock_time: 0.000137 Rows_sent: 0  Rows_examined: 2

    SET timestamp=1434821459;
    SELECT id, priority, attempts, handler, run_at, locked_at, failed_at FROM delayed_jobs WHERE ((locked_at IS NULL OR locked_at < '2015-06-20 09:30:59' OR locked_by = 'delayed_job host:snorby pid:17264') AND run_at <= '2015-06-20 13:30:59' AND failed_at IS NULL) ORDER BY priority, run_at LIMIT 5;

    Time: 150620 13:31:04

    User@Host: root[root] @ localhost []

    Query_time: 0.000316  Lock_time: 0.000129 Rows_sent: 0  Rows_examined: 2

    SET timestamp=1434821464;
    SELECT id, priority, attempts, handler, run_at, locked_at, failed_at FROM delayed_jobs WHERE ((locked_at IS NULL OR locked_at < '2015-06-20 09:31:04' OR locked_by = 'delayed_job host:snorby pid:17264') AND run_at <= '2015-06-20 13:31:04' AND failed_at IS NULL) ORDER BY priority, run_at LIMIT 5;

    Time: 150620 13:31:09

    User@Host: root[root] @ localhost []

    Query_time: 0.000353  Lock_time: 0.000137 Rows_sent: 0  Rows_examined: 2

    SET timestamp=1434821469;
    SELECT id, priority, attempts, handler, run_at, locked_at, failed_at FROM delayed_jobs WHERE ((locked_at IS NULL OR locked_at < '2015-06-20 09:31:09' OR locked_by = 'delayed_job host:snorby pid:17264') AND run_at <= '2015-06-20 13:31:09' AND failed_at IS NULL) ORDER BY priority, run_at LIMIT 5;

    Time: 150620 13:31:14

    User@Host: root[root] @ localhost []

    Query_time: 0.000325  Lock_time: 0.000131 Rows_sent: 0  Rows_examined: 2

    SET timestamp=1434821474;
    SELECT id, priority, attempts, handler, run_at, locked_at, failed_at FROM delayed_jobs WHERE ((locked_at IS NULL OR locked_at < '2015-06-20 09:31:14' OR locked_by = 'delayed_job host:snorby pid:17264') AND run_at <= '2015-06-20 13:31:14' AND failed_at IS NULL) ORDER BY priority, run_at LIMIT 5;

    Time: 150620 13:31:19

    Alguna idea? Se me ocurrio limpiar las tablas de la base de datos ya que vi tablas que contenían información, pero continúa sin trabajar.



  • intenta acceder con un cliente de MySQL para windows o el SO que utilices e intenta acceder a las tablas y BD .

    Es probable que no tengas permisos en MySQL para acceder a la tabla desde otro host que no sea el localhost.  Si esto pasa deberas de crear los permisos con el comando PERMIT

    saludos



  • El cliente MySQL que utilizo es PHPMyAdmin, mi estación de trabajo es un LinuxMint 17.1 Rebecca. Hace años que trabajo son sitios web y bases de datos, Sitios en PHP a pulso, Joomla, Wordpress, Symfony, etc… por lo que trabajar con phpmyadmin para crear usuarios y dar acceso a todas las bases de datos del Servidor MySQL oh! a una en específico es algo que he hecho muchas veces, nunca te tenido a ir a dar permisos en las tablas de una base de datos, pero bueno verifique si a la hora de editar los permisos que habia otorgado al usuario llamado pfsense al cual le dije que solo se podría conectar desde la IP del Firewall, había algo que me permitiera darle permisos a las tablas y si! Le di todos los permisos tabla por tabla, algo tedioso ya que la base de datos de Snorby tiene bastantes tablas y aún el problema sigue identico. Ya sabemos que el servidor MySQL está escuchando en eth0 y no solamente en el localhost como viene por defecto y que hay comunicación entre el firewall y el MySQL, si el usuario que cree desde phpmyadmin no tuviera acceso a la base de datos del snorby la realizar un telnet desde la consola del pfsense hacia el puerto 3306 del MySQL no conectaría, ya que como dije a ese usuario que llame: pfsense solo le di permiso para acceder desde la IP del firewall y al hacer un telnet desde el firewall al MySQL obtengo esto:

    root: telnet 192.168.0.4 3306
    Trying 192.168.0.4...
    Connected to snorby.gobpr.co.cu.
    Escape character is '^]'.
    W
    5.5.43-0+deb7u1-log�7*me7}6�.6exN\c](M/>mysql_native_password

    en cambio si lo hago desde otro equipo en la red desde el cual no se le haya permitido acceso a ninguna base de datos del servidor MySQL como por ejemplo mi PC, obtengo esto otro:

    telnet 192.168.0.4 3306
    Trying 192.168.0.4...
    Connected to 192.168.0.4.
    Escape character is '^]'.
    FHost '192.168.0.100' is not allowed to connect to this MySQL serverConnection closed by foreign host.

    Nada, que estoy ya me tiene molesto, tendré que intentar hacer una instalación nueva de snorby desde cero cosa que me da dolor de cabeza lidear con ruby y sus dependencias... :)

    Saludos...



  • Habiendo descartado eso,  hay que buscar en otra parte .  Tal vez sea un error interno del sistema.



  • Bueno colega, gracias por todas tus respuestas, creo que intentaré montar un nuevo openvz e intentaré pasar de nuevo por la odisea de poner a funcionar de nuevo un snorby desde cero en debian wheezy.

    Saludos…


Log in to reply