1

I have a table for logs with over 6 million records. I want a query that shows me all the occurrences where the src goes to the same destination/port.

I tried this:

SELECT src, dst, dstport, COUNT(src) AS Hits 
FROM logs  
GROUP BY src 
ORDER BY Hits DESC;

Not sure that this query is giving me exactly what I want. Additionally, what is the best way to exclude specific port? Say dstport = 53?

I would like the to show each time each src reached the same dst & dstport while keeping count of the occurances. Say src 10.110.0.10 reached dst 10.2.9.124:53 2,345,568 times & also reached 192.168.9.18:80 174 times ; then the result would look like: Example:

+-----------------+-----------------+---------+---------+ | src | dst | dstport | Hist | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 10.2.9.124 | 53 | 2345568 | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 192.168.9.18 | 80 | 174 | +-----------------+-----------------+---------+---------+

However with the query above I am getting this results:

+-----------------+-----------------+---------+---------+ | src | dst | dstport | Hist | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 10.2.9.124 | 53 | 1443780 | | 10.110.0.10 | 192.168.9.124 | 53 | 1402210 | | 10.192.31.23 | 10.192.1.120 | 8082 | 319507 | | 10.192.31.19 | 10.192.1.186 | 8081 | 319203 | | 192.168.31.131 | 192.168.31.130 | 80 | 290818 | +-----------------+-----------------+---------+---------+

  • 1
    Doesn't seem entirely clear what the result is supposed to be. Could you add a data sample and the expected result for it to illustrate the problem? – Andriy M Jan 14 '15 at 18:29
  • Your result seems fine to me - there's no dups of either src or dst - which is what I would expect. Perhaps you could show us some DDL (SHOW CREATE TABLE...) and DML (INSERT INTO log VALUES(...)) - or an SQLFiddle. – Vérace Jan 14 '15 at 19:42
0

To see everything with aggregation, you could do this

SELECT src,CONCAT(dst,':',dstport) dsp_port,COUNT(1) hits
FROM logs
GROUP BY src,dsp_port;

To see subtotals

SELECT
    IF
    (
        ISNULL(src)=1,
        'Total',
        IF
        (
            ISNULL(dst_port)=1,
            CONCAT(src,' Total'),
            CONCAT(src,' ',dst_port)
        )
    ) Statistic,hits
FROM
(
    SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    FROM logs
    GROUP BY src,dst_port
    WITH ROLLUP
) A;

Using your sample data

mysql> DROP DATABASE IF EXISTS rob_d;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE rob_d;
Query OK, 1 row affected (0.00 sec)

mysql> USE rob_d
Database changed
mysql> CREATE TABLE logs
    -> (
    ->     src CHAR(15) NOT NULL,
    ->     dst CHAR(15) NOT NULL,
    ->     dstport  INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logs (src,dst,dstport) VALUES
    -> ('10.110.0.10',   '10.2.9.124'    ,53),
    -> ('10.110.0.10',   '192.168.9.124' ,53),
    -> ('10.192.31.23',  '10.192.1.120'  ,8082),
    -> ('10.192.31.19',  '10.192.1.186'  ,8081),
    -> ('192.168.31.131','192.168.31.130',80);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----------------+----------------+---------+
| src            | dst            | dstport |
+----------------+----------------+---------+
| 10.110.0.10    | 10.2.9.124     |      53 |
| 10.110.0.10    | 192.168.9.124  |      53 |
| 10.192.31.23   | 10.192.1.120   |    8082 |
| 10.192.31.19   | 10.192.1.186   |    8081 |
| 192.168.31.131 | 192.168.31.130 |      80 |
+----------------+----------------+---------+
5 rows in set (0.00 sec)

Here is the query's output

mysql> SELECT
    ->     IF
    ->     (
    ->         ISNULL(src)=1,
    ->         'Total',
    ->         IF
    ->         (
    ->             ISNULL(dst_port)=1,
    ->             CONCAT(src,' Total'),
    ->             CONCAT(src,' ',dst_port)
    ->         )
    ->     ) Statistic,hits
    -> FROM
    -> (
    ->     SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    ->     FROM logs
    ->     GROUP BY src,dst_port
    ->     WITH ROLLUP
    -> ) A;
+----------------------------------+------+
| Statistic                        | hits |
+----------------------------------+------+
| 10.110.0.10 10.2.9.124:53        |    1 |
| 10.110.0.10 192.168.9.124:53     |    1 |
| 10.110.0.10 Total                |    2 |
| 10.192.31.19 10.192.1.186:8081   |    1 |
| 10.192.31.19 Total               |    1 |
| 10.192.31.23 10.192.1.120:8082   |    1 |
| 10.192.31.23 Total               |    1 |
| 192.168.31.131 192.168.31.130:80 |    1 |
| 192.168.31.131 Total             |    1 |
| Total                            |    5 |
+----------------------------------+------+
10 rows in set (0.00 sec)

mysql>
  • Thanks Ronaldo. How can I get it to display the src, dst, dstport and the hits? Right now I have only the src and hits. – Rob D Jan 14 '15 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.