#/bin/bash
. "/etc/CFG_FULL.cfg"
#nmap --excludefile $pwd/db_ip_exclude.txt -sn 192.168.212.0/22 | egrep -v "Host|nmap" | awk '{print $5}' > $pwd/db_ip_test.txt
cd $pwd/port_up_down
#ip=192.168.213.50
while read ip
do
cat /dev/null > $pwd/port_up_down/tmp/tmp_$ip.log
#/home/user/test/port_up_down/port_status.sh $ip
cat /var/log/remote/switches.log | grep -w $ip | grep Port | awk '{ print $7 }' | sort -n | uniq >> $pwd/port_up_down/tmp/tmp_$ip.log
cat /var/log/remote/switches.log.1 | grep -w $ip | grep Port | awk '{ print $7 }' | sort -n | uniq >> $pwd/port_up_down/tmp/tmp_$ip.log
zcat /var/log/remote/switches.log.2.gz | grep " $ip " | grep Port | awk '{ print $7 }' | sort -n | uniq >> $pwd/port_up_down/tmp/tmp_$ip.log
cat $pwd/port_up_down/tmp/tmp_$ip.log | sort -n | uniq > $pwd/port_up_down/log/$ip.log
done < $pwd/db_ip_test.txt

#!/usr/bin/env python
from string import *
import MySQLdb
import operator
import array
fIP = open("db_ip_test.txt")
xString = fIP.readlines()
fIP.close()
nambers = len(xString)
xString = [xString.replace("\n","") for xString in xString]
for cntr in range(0,nambers):
    fOut = open("log/" + xString[cntr] + ".log", "w")
    print (xString[cntr])
    select_sql = "SELECT `msg` FROM `logs` WHERE host = '{0}' and msg LIKE '{1}' ORDER BY `logs`.`msg` ASC;"
    db = MySQLdb.connect(host="127.0.0.1",user="root",passwd="121788",db="syslog")
    cursor = db.cursor()
    cursor.execute(select_sql.format(xString[cntr],'Port% link%'))
    for k in cursor.fetchall():
      k = str(k)
      k = k.split()
      k = k[1]
      fOut.write(k + '\n')
      print (k)
    fOut.close()

SELECT `msg` FROM `logs` WHERE host = '192.168.213.92' and msg LIKE 'Port%link%' and `datetime` between '2013-07-04 00:00:00' and '2013-08-04 23:59:59'

SELECT `msg` FROM `logs` WHERE host = '192.168.213.92' and msg LIKE 'Port%link%' 

SELECT `msg` FROM `logs` WHERE `datetime` between '2013-05-04 00:00:00' and '2013-08-04 23:59:59'

sorted(set(open("db_ip_test.txt").read().splitlines()))

11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
11
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
23
3
3

print sorted(set(open("db_ip_test.txt").read().splitlines()))
# ['11', '23', '3']

      k = str(k)
      k = k.split()
      k = k[1]

192.168.212.32
192.168.212.50
192.168.212.51
192.168.212.52

Aug 20 18:08:13 192.168.213.134 INFO: Port 2 link down
Aug 20 17:08:15 192.168.33.215 342236: Aug 20 18:08:14.617 XMAO: %PIM-6-INVALID_RP_JOIN: Received (*, 224.1.1.160) Join from 192.168.46.2 for invalid RP 150.158.231.8
Aug 20 18:08:16 192.168.213.134 INFO: Port 2 link up, 10Mbps HALF duplex
Aug 20 18:08:27 192.168.213.134 INFO: Port 2 link down
Aug 20 18:08:32 192.168.213.134 INFO: Port 2 link up, 10Mbps HALF duplex
Aug 20 17:08:37 192.168.215.250 INFO: Port 32 link up, 100Mbps  FULL duplex
Aug 20 10:20:25 192.168.212.72 INFO: Port 17 link down
Aug 20 10:20:28 192.168.212.72 INFO: Port 17 link up, 10Mbps  FULL duplex
Aug 20 10:20:36 192.168.212.72 INFO: Port 17 link down
Aug 20 17:08:58 192.168.215.250 INFO: Port 32 link down
Aug 20 17:09:00 192.168.215.250 INFO: Port 32 link up, 100Mbps  FULL duplex
Aug 20 18:09:02 192.168.213.134 INFO: Port 2 link down
Aug 20 18:09:07 192.168.213.134 INFO: Port 2 link up, 10Mbps HALF duplex

#!/usr/bin/env python
#-*- coding: utf8 -*-
import MySQLdb
def read_ip(fname):
    with open(fname) as fd:
        return set(fd.read().splitlines())
def main():
    query = 'SELECT msg FROM logs WHERE host = %s and msg LIKE %s ORDER BY msg ASC'
    db = MySQLdb.connect(host='127.0.0.1', user='root', passwd='121788', db='syslog')
    cursor = db.cursor()
    for ip in read_ip('db_ip_test.txt'):
        print ip
        cursor.execute(query, (ip, 'Port% link%'))
        result = set(map(lambda a: str(a).split()[1], cursor.fetchall()))
        with open('log/' + ip + '.log', 'w') as fd:
            fd.write('\n'.join(result)
main()

def read_ip(fname):
    """
    В данной функции заданно открытие файла (fname)
    и его построчное считывание
    :param fname:
    :return:
    """
    with open(fname) as fd:
        return set(fd.read().splitlines())

def main():
    """
    Здесь мы конектимся к базе и отправляем sql
    запрос, потом циклом собираем данные и убираем
    дубли и получившееся записываем в файл
    :rtype : object
    """
    query = 'SELECT msg FROM logs WHERE host = %s and msg LIKE %s ORDER BY msg ASC'
    db = MySQLdb.connect(host='127.0.0.1', user='root', passwd='121788', db='syslog')
    cursor = db.cursor()
    for ip in read_ip('../db_ip_test.txt'):
        print ip
        cursor.execute(query, (ip, 'Port% link%'))
        [b]result = set(map(lambda a: str(a).split()[1], cursor.fetchall()))[/b]
        with open('log/' + ip + '.log', 'w') as fd:
            fd.write('\n'.join(result))

result = set(map(lambda a: str(a).split()[1], cursor.fetchall()))

result = []
for row in cursor.fetchall():
    r = str(row)
    r = r.split()
    r = r[1]
    result.append(r)
result = set(result)