· 9 years ago · May 20, 2017, 05:34 PM
1#!/usr/bin/perl
2# ââîäèì ïåðåìåÃÃûå
3# õîñò, êîòîðûé áóäåì îïðà øèâà òü
4
5$snmp_host = $ARGV[0];
6$MYDATE = $ARGV[1];
7$MYTIME = $ARGV[2];
8# äîñòà ¸ì âðåìÿ
9
10# MySQL õîñò
11#$host = '10.40.2.30';
12# MySQL þçåð
13#$user = 'snmp';
14# MySQL ïà ðîëü
15#$password = 'snmp';
16# MySQL áà çà äà ÃÃûõ
17#$database = 'SNMP';
18# ïîäðóáà åì ìîäóëü äëÿ ðà áîòû ñ MySQL
19use DBI;
20
21
22
23# ÊîÃÃåêòèìñÿ ê MySQL
24$dbh = DBI->connect("DBI:mysql:SNMP:10.40.2.30", "root", "yohnonyou");
25
26# Ñîçäà ¸ì â ÃÄ òà áëèöó äëÿ õðà ÃÃ¥Ãèÿ èòîãîâ
27$MySQL_query = "CREATE TABLE IF NOT EXISTS `ALL_SNMP`
28(`unic_id` INT(12) NOT NULL AUTO_INCREMENT ,
29`ALL_time` TIMESTAMP(12) NOT NULL ,
30`ALL_user` TEXT(12) NOT NULL ,
31`ALL_ip` TEXT(20) NOT NULL ,
32`ALL_mac` TEXT(30) NOT NULL ,
33`ALL_port` TEXT(12) NOT NULL ,
34
35PRIMARY KEY (`unic_id`)
36)";
37
38$dbh->do("$MySQL_query") or die $Mysql::db_errstr;
39
40# Ñáîð äà ÃÃûõ
41
42
43#$dbh->do();
44my $res = $dbh->prepare("SELECT DISTINCT CISCO_user, ip, mac, port
45FROM SNMP_MAC, CISCO_SES
46WHERE CISCO_IF_mac=mac
47AND CISCO_date=date
48AND CISCO_time=time
49AND port<25 ;");
50$res->execute();
51if ($res->err) {
52 # Åñëè âîçÃèêëà îøèáêÃ
53 print "Îøèáêà " . $res->errstr;
54}
55else {
56 while (my @row = $res->fetchrow_array()) {
57#
58 print $row[0], " - ", $row[0], " - ", $row[2], " - ", $row[3], " --MASS1", "\n";
59
60 $res2 = $dbh->prepare("SELECT DISTINCT ALL_user, ALL_ip, ALL_mac, ALL_port FROM ALL_SNMP
61 WHERE ALL_user = '.$row[0].'
62 AND ALL_ip != '.$row[1].'
63 AND ALL_mac != '.$row[2].'
64 AND ALL_port != '.$row[3].'");
65 $res2->execute();
66
67 my @row2 = $res->fetchrow_array();
68 print $row2[0], " = ", $row[1], " - ", $row2[1], " - ", $row2[2], " - ", $row2[3], " --MASS2", "\n";
69# $MySQL_query = "INSERT INTO `ALL_SNMP`
70# (`ALL_user`,`ALL_ip`,`ALL_mac`,`ALL_port`) VALUES
71# ('" . $row2[0] . "', '" . $row2[1] . "','" . $row2[2] ."', '" . $row2[3] . "')";
72# $dbh->do("$MySQL_query") or die $Mysql::db_errstr;
73
74 }
75
76
77#print @row2;
78# $MySQL_query = "INSERT INTO `ALL_SNMP`
79# (`ALL_user`,`ALL_ip`,`ALL_mac`,`ALL_port`) VALUES
80# ('" . $row[0] . "', '" . $row[1] . "','" . $row[2] ."', '" . $row[3] . "')";
81# $dbh->do("$MySQL_query") or die $Mysql::db_errstr;
82 }
83
84$res->finish();
85
86print "END", "\n";
87
88
89
90
91
92
93
94# çà ñîâûâà åì äà Ãûå â ïîñòîÿÃÃóþ òà áëèöó
95#$MySQL_query = "INSERT INTO `IP_SNMP`
96#(`SNMP_date`,`SNMP_time`,`SNMP_unix_time`,`SNMP_IF`,
97#`SNMP_IF_speed`,`SNMP_IF_status`,`SNMP_IF_traff_in`,
98#`SNMP_IF_traff_out`,`SNMP_MIB_MTU`,`SNMP_IF_descr`) VALUES
99#('" . $year . "-" . $mon . "-" . $mday . "',
100#'" . $hour . ":" . $min . ":00',
101#'" . $unix_time . "','" . $i ."',
102#'" . $IF_speed . "','" . $IF_status . "',
103#'" . $delta_traff_in . "','" . $delta_traff_out . "','" . $IF_MTU . "','" . $IF_descr . "')";
104#$dbh->do("$MySQL_query") or die $Mysql::db_errstr;
105#}
106
1071;