[SOLVED] Complicated problem

[hvle]

Hope this question entertain you guys. 

I got a table called packet_log.  As the name say it, it logs packets data in and out the gateway.

 

1.  The table design

Field Name - Data Type - Description
id                int(10)        unique id of a packet, auto increment
src_ip          int(10)        Source IP address - The ip address translated into 32 bits int
dst_ip          int(10)        Destination IP address - same as above
src_port       short int      source port - 16 bits int
dst_port       short int      destination port -16 bits int
sent_byte     int(10)       number of bytes sent from src ip to dst ip
recv_byte     int(10)       number of bytes received from dst ip to src_ip
conn_time    int(10)        time the connection initiated - in Epoch

 

sent byte and recv bytes logged based on src ip and dst ip.

 

If a connection initiated by A to B, sent byte will be the traffic from A to B, recv byte will be opposite.

now, if B initiated by A, then sent byte will be from B to A, recv byte vice versa.

 

Total traffic of host A consist of total send and total recv.

Total send will be all the traffic send from A regardless of the connection make by A or by other host.

Total recv is the same basic.

 

Objective:  Find the statistic report of a host A which include the total send and total received between host A and host B, order by (total send + total received).

 

A sample report would be something like this

 

report for host A

IP        sendsize          recvsize       total 
x.x.x.a    12mb              21mb          33mb
x.x.x.y    13mb               18mb         31mb
x.x.x.z    11mb               19mb          30mb

 

Is there a way to get this report with a single query???

 

By using many query, I am able get what I wanted, but it just bug me not to find a way to do this with a single query.

 

 

Thank you all.

[luca200]

Try this

select ip, sum(send1) as sendsize, sum(recv1) as recvsize, sum(send1) + sum(recv1) as total
from
((select dst_ip as ip, sum(sent_byte) as send1, 0 as recv1
from packet_log 
where src_ip = yourIp group by ip)
union
(select src_ip as ip,  0, sum(recv_byte)
from packet_log 
where dst_ip = yourIp group by ip)) as t
group by ip

Just written without trying... 

[mushroom]

Hope this question entertain you guys. 

I got a table called packet_log.  As the name say it, it logs packets data in and out the gateway.

If you have any amount of traffic I would suggest a total rethink of your table structure, it will get way to big way to fast, I know. I wrote a suite for my local ISP to track usage on 9 subnets.

[hvle]

Try this

select ip, sum(send1) as sendsize, sum(recv1) as recvsize, sum(send1) + sum(recv1) as total
from
((select dst_ip as ip, sum(sent_byte) as send1, 0 as recv1
from packet_log 
where src_ip = yourIp group by ip)
union
(select src_ip as ip,  0, sum(recv_byte)
from packet_log 
where dst_ip = yourIp group by ip)) as t
group by ip

Just written without trying... 

 

Luca200, it works great.

How did you learned these stuff?  Do you have any recommended book on sql?

 

Thank you very much

 

[hvle]

If you have any amount of traffic I would suggest a total rethink of your table structure, it will get way to big way to fast, I know. I wrote a suite for my local ISP to track usage on 9 subnets.

 

yes, the table filled 600K of records already after 2 weeks.  It may look like a lot, but each record is only a few integers, with no text so it's pretty fast.  It will eventually overflow after about 4 billions records which is approx 20 years.  I intended to keep a 3 years history. 

 

[mushroom]

yes, the table filled 600K of records already after 2 weeks.  It may look like a lot, but each record is only a few integers, with no text so it's pretty fast.  It will eventually overflow after about 4 billions records which is approx 20 years.  I intended to keep a 3 years history. 

Fairly slow then....

on the group I am tracking one particular IP generated 173M records in a month. ( a mail server)

A thing to remember is that the larger a table gets the slower it works.

I may be wise to only keep a month worth of data in a single table and create new table(s) for each month. this will minimize the slow down.

[hvle]

You're definitely right.  Thanks

[luca200]

How did you learned these stuff?  Do you have any recommended book on sql?

Honestly I can't say how and when I learned this.. I work with sql since many years

 

For sure I can't recommend you any english written book on sql, as I never read one. Could be I read some in italian, but I'm not sure...

Anyway I don't think you could find this kind of solution on a book, unless it's a very advanced sql book.

 

First of all you must know the principles of relational db's, figure out how you can find what you need and finally you have to know sql to translate your ideas in something a db server can understand 

If you don't remember something about the latter one, you can find help on a book or on the net; but if you don't know how to start, it's far more difficult I guess...

 

I'm afraid this won't help you much, but it's what I can answer to your question 

[hvle]

Thanks Luca, your answer are as good as any others.  I am asking for a book to learn the quick and easy way, but the fact is there is no quick and easy way get to expert.  I gotta work on it everyday like you did.  Good thing is we have this forum to share our experience. 

 

Again, thank for a excellent solution and advice.

 

Huy