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 |
+-----------------+-----------------+---------+---------+
Best Answer
To see everything with aggregation, you could do this
To see subtotals
Using your sample data
Here is the query's output