jeudi 29 janvier 2015

Mysql join not working


I have database with two tables: logs & src_info



mysql> describe logs;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sessionid | int(8) | NO | | NULL | |
| date | date | NO | | NULL | |
| time | time(6) | NO | | NULL | |
| src | varchar(15) | NO | | NULL | |
| dst | varchar(15) | NO | | NULL | |
| dstport | int(6) | NO | | NULL | |
| proto | varchar(6) | NO | | NULL | |
| rcvdbyte | int(24) | NO | | NULL | |
| sentbyte | int(24) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

AND:



mysql> describe src_ipinfo;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| src | varchar(15) | NO | | NULL | |
| hostname | varchar(50) | NO | | NULL | |
| city | varchar(50) | NO | | NULL | |
| region | varchar(50) | NO | | NULL | |
| country | varchar(2) | NO | | NULL | |
| org | varchar(150) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

I am trying to run queries extracting from both tables but when I do myslq just hangs and I get no error and no results: This are the queries that are causing problems:



mysql>SELECT logs.src, logs.dst, logs.dstport, src_ipinfo.country, COUNT(1) hits FROM logs, src_ipinfo WHERE logs.src = src_ipinfo.src GROUP BY logs.src, logs.dst, logs.dstport ORDER BY hits DESC;

mysql>SELECT * FROM logs a , src_ipinfo b WHERE a.src = b.src AND a.dstport= 60595 ORDER BY a.src, a.dst;

The ultimate goal is to search from logs where dstport = 'xxxxx' and the country != 'US' keeping count of how many times that source reached the same dst, dstport combination. Example output:



mysql> describe src_ipinfo;
+----------+--------------+---------+-------+---------+-------+
| src | dst | dstport | proto | country | hits |
+----------+--------------+---------+-------+---------+-------+
| 2.3.45.3 | 10.10.1.23 | 60531 | TCP | CN | 3452 |
| 1.2.45.3 | 10.10.1.23 | 80801 | TCP | NL | 37 |
| 4.5.45.3 | 10.10.1.23 | 443 | TCP | IN | 2 |
+----------+--------------+---------+-------+---------+-------+

I already have individual queries for counting src, dst combo:



mysql>SELECT src, dst, dstport, proto, COUNT(src) hits FROM `logs` WHERE dstport = '60595' GROUP BY src,dst ORDER BY hits DESC;

Now I need to add to that query the country information from src_ipinfo table and further manipulate the result so that I only get non US sources.





Aucun commentaire:

Enregistrer un commentaire