Solved

mysql indexed question

Posted on 2013-12-18
15
560 Views
Last Modified: 2013-12-19
i have three tables one of them have my ip addresses with inetaton format

and second has columns
1. ip address
2. inputtraffic
3. outputtraffic
4. date

i want to insert / all the sum of octets of yesterday of ip adresses which is in ip_addresses table.


for example if i have 185.9.156.244 in ip_addresses table it will collect all values from the ip_traffic table but
if it is in ip src it will collect the octets to the outbound column
if it is in ip dst it will collect the octets to the in bound column

one last criteria is that :
it will collect until the end of the day . when the new day start it will not look back to the yesterday and insert or update to the new day


ip_traffic table :
date ip_src  ip_dst , protocol , src_port , dst_port ,octet,packets
'2013-12-19 03:17:46', '178.20.231.211', '176.41.159.200', 17, 9904, 55323, 22528, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 22528, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 22528, 256
'2013-12-19 03:17:46', '178.20.230.190', '66.249.75.214', 6, 80, 58894, 22528, 256
'2013-12-19 03:17:46', '69.171.246.113', '178.20.231.102', 6, 58640, 80, 22528, 256
'2013-12-19 03:17:46', '69.171.246.113', '178.20.231.102', 6, 58640, 80, 22528, 256
'2013-12-19 03:17:46', '185.9.159.8', '46.1.46.239', 6, 13000, 60196, 22528, 256
'2013-12-19 03:17:46', '185.9.159.121', '75.55.252.97', 17, 11326, 57854, 70656, 256
'2013-12-19 03:17:46', '37.123.100.42', '88.233.13.209', 6, 15001, 50338, 70656, 256
'2013-12-19 03:17:46', '115.79.90.205', '178.20.231.95', 6, 26687, 80, 70656, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 70656, 256
'2013-12-19 03:17:46', '178.20.231.82', '175.42.40.20', 6, 80, 59073, 70656, 256
'2013-12-19 03:17:46', '176.227.203.26', '178.20.228.56', 6, 34553, 58068, 70656, 256
'2013-12-19 03:17:46', '173.194.113.5', '185.9.159.240', 6, 443, 1318, 132608, 256
'2013-12-19 03:17:46', '88.248.53.217', '178.20.231.82', 6, 62708, 80, 11776, 256
'2013-12-19 03:17:46', '37.123.96.67', '155.133.141.208', 6, 63017, 445, 37376, 256
'2013-12-19 03:17:46', '185.9.156.136', '94.122.16.18', 6, 7582, 60619, 70144, 256
'2013-12-19 03:17:46', '178.20.226.200', '63.34.133.113', 6, 1553, 445, 12288, 256
'2013-12-19 03:17:46', '88.248.53.217', '178.20.231.82', 6, 62706, 80, 17408, 256
'2013-12-19 03:17:46', '173.194.70.139', '185.9.159.240', 6, 443, 1319, 376320, 256
'2013-12-19 03:17:46', '185.9.159.82', '110.75.10.13', 6, 4724, 80, 11776, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 384000, 256
'2013-12-19 03:17:46', '178.20.228.56', '37.220.23.58', 6, 58045, 58714, 11776, 256
'2013-12-19 03:17:46', '123.125.115.99', '185.9.159.82', 6, 80, 4303, 59648, 256
'2013-12-19 03:17:46', '185.31.16.184', '185.9.159.169', 6, 80, 60012, 384000, 256
'2013-12-19 03:17:46', '178.20.227.86', '83.66.211.2', 6, 17489, 58169, 368640, 256
'2013-12-19 03:17:46', '178.20.225.195', '141.101.93.43', 6, 80, 60313, 13312, 256
'2013-12-19 03:17:46', '212.83.158.20', '37.123.100.116', 6, 443, 52887, 11776, 256
'2013-12-19 03:17:46', '178.20.228.37', '173.194.112.67', 6, 62774, 443, 25344, 256
'2013-12-19 03:17:46', '178.20.227.86', '216.88.144.81', 6, 1592, 445, 12288, 256
'2013-12-19 03:17:46', '185.9.157.2', '65.55.215.79', 6, 80, 25261, 11776, 256
'2013-12-19 03:17:46', '178.20.228.56', '37.220.23.58', 6, 58045, 58714, 11776, 256
'2013-12-19 03:17:46', '185.9.159.124', '66.249.75.173', 6, 80, 64157, 376320, 256
'2013-12-19 03:17:46', '176.227.203.26', '178.20.228.56', 6, 34553, 58068, 384000, 256
'2013-12-19 03:17:46', '5.255.253.7', '178.20.231.51', 6, 15231, 80, 13312, 256
'2013-12-19 03:17:46', '66.249.78.211', '178.20.231.102', 6, 34240, 80, 13312, 256
'2013-12-19 03:17:46', '178.20.231.82', '173.193.103.156', 6, 80, 3027, 384000, 256
'2013-12-19 03:17:46', '173.194.112.91', '178.20.228.37', 6, 443, 62775, 11776, 256
'2013-12-19 03:17:46', '178.20.231.82', '88.248.53.217', 6, 80, 62706, 381952, 256
'2013-12-19 03:17:46', '46.245.160.238', '185.9.156.136', 6, 56396, 7102, 13312, 256
'2013-12-19 03:17:46', '185.9.156.136', '80.134.174.97', 6, 7890, 49603, 381952, 256
'2013-12-19 03:17:46', '88.240.166.80', '178.20.231.158', 6, 50343, 13000, 12288, 256
'2013-12-19 03:17:46', '173.194.70.118', '185.9.156.37', 6, 80, 59983, 148480, 256
'2013-12-19 03:17:46', '178.20.231.95', '99.228.154.75', 6, 80, 52920, 384000, 256
'2013-12-19 03:17:46', '178.20.227.86', '32.21.198.66', 6, 1831, 445, 12288, 256
'2013-12-19 03:17:46', '79.236.192.198', '185.9.156.136', 6, 42517, 7854, 13312, 256
'2013-12-19 03:17:46', '178.20.227.86', '202.74.247.2', 17, 17489, 64522, 27136, 256
'2013-12-19 03:17:46', '178.20.231.211', '95.14.37.104', 17, 9904, 51622, 22528, 256
'2013-12-19 03:17:46', '173.194.70.116', '178.20.228.38', 6, 80, 57258, 22528, 256
'2013-12-19 03:17:46', '185.9.159.82', '133.8.129.62', 6, 4552, 445, 12288, 256
'2013-12-19 03:17:46', '185.9.159.233', '31.13.64.81', 6, 37486, 443, 11776, 256
'2013-12-19 03:17:46', '178.20.227.75', '190.12.100.200', 17, 3578, 49902, 24064, 256
'2013-12-19 03:17:46', '178.20.227.86', '49.119.140.63', 6, 1841, 445, 24064, 256
'2013-12-19 03:17:46', '173.194.70.190', '185.9.159.240', 6, 443, 1313, 24064, 256
'2013-12-19 03:17:46', '178.20.228.56', '176.227.203.26', 6, 58068, 34553, 24064, 256
'2013-12-19 03:17:46', '185.9.159.189', '128.116.160.79', 6, 1178, 445, 24064, 256
'2013-12-19 03:17:46', '178.20.231.76', '133.78.223.85', 6, 1854, 445, 24064, 256
'2013-12-19 03:17:46', '82.145.241.23', '178.20.224.224', 6, 80, 53526, 15360, 256
'2013-12-19 03:17:46', '185.9.159.86', '220.122.150.37', 6, 1629, 445, 12288, 256
'2013-12-19 03:17:46', '178.20.231.95', '99.228.154.75', 6, 80, 52920, 384000, 256
'2013-12-19 03:17:46', '178.20.231.82', '88.248.53.217', 6, 80, 62709, 381952, 256
'2013-12-19 03:17:46', '178.20.228.56', '176.227.203.26', 6, 58068, 34553, 11776, 256
'2013-12-19 03:17:46', '37.123.100.42', '85.104.36.29', 6, 15001, 60798, 49664, 256
'2013-12-19 03:17:46', '185.9.159.160', '86.77.111.61', 6, 1431, 445, 12288, 256
'2013-12-19 03:17:46', '178.20.227.71', '198.228.210.97', 17, 19, 1050, 287744, 256
'2013-12-19 03:17:46', '178.20.227.86', '83.66.211.2', 6, 17489, 58169, 287744, 256
'2013-12-19 03:17:46', '178.20.231.211', '176.41.159.200', 17, 9904, 55323, 22528, 256
'2013-12-19 03:17:46', '78.168.215.254', '178.20.227.76', 17, 57712, 47801, 7168, 256
'2013-12-19 03:17:46', '78.188.3.71', '37.123.97.70', 6, 2388, 32228, 7168, 256
'2013-12-19 03:17:46', '178.20.231.84', '173.252.113.127', 17, 53, 57176, 12544, 256
'2013-12-19 03:17:46', '124.95.156.16', '185.9.159.70', 17, 20706, 12255, 27904, 256
'2013-12-19 03:17:46', '78.168.215.254', '178.20.227.76', 17, 57712, 47801, 7168, 256
'2013-12-19 03:17:46', '178.20.231.82', '157.55.33.39', 6, 80, 33714, 7168, 256
'2013-12-19 03:17:46', '178.20.228.38', '173.194.70.116', 6, 57258, 80, 7168, 256
'2013-12-19 03:17:46', '24.159.97.159', '185.9.157.162', 6, 56348, 3389, 23808, 256
'2013-12-19 03:17:46', '178.20.231.82', '88.248.53.217', 6, 80, 62708, 381952, 256
'2013-12-19 03:17:46', '178.20.227.43', '81.213.46.191', 6, 31691, 45441, 11776, 256
'2013-12-19 03:17:46', '37.123.101.163', '85.110.176.205', 6, 80, 55450, 371712, 256
'2013-12-19 03:17:46', '185.9.156.136', '85.98.117.95', 6, 7854, 62445, 337408, 256
'2013-12-19 03:17:46', '99.228.154.75', '178.20.231.95', 6, 52920, 80, 11776, 256
'2013-12-19 03:17:46', '180.76.5.118', '178.20.231.82', 6, 38342, 80, 11776, 256
'2013-12-19 03:17:46', '178.20.226.150', '78.168.196.164', 17, 41322, 38802, 31232, 256
'2013-12-19 03:17:46', '173.194.70.116', '178.20.228.38', 6, 80, 57258, 31232, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 31232, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 31232, 256
'2013-12-19 03:17:46', '37.123.100.111', '78.176.188.255', 6, 80, 50833, 31232, 256
'2013-12-19 03:17:46', '108.162.246.41', '37.123.96.116', 6, 17557, 80, 31232, 256
'2013-12-19 03:17:46', '78.165.254.67', '37.123.103.252', 6, 54704, 80, 31232, 256
'2013-12-19 03:17:46', '178.20.227.76', '78.168.215.254', 17, 47801, 57712, 370176, 256
'2013-12-19 03:17:46', '178.20.231.82', '173.255.201.130', 6, 80, 34514, 370176, 256
'2013-12-19 03:17:46', '178.20.231.155', '74.125.163.247', 6, 65091, 80, 370176, 256
'2013-12-19 03:17:46', '178.20.227.76', '78.168.215.254', 17, 47801, 57712, 370176, 256
'2013-12-19 03:17:46', '185.9.159.160', '21.113.178.108', 6, 1454, 445, 370176, 256
'2013-12-19 03:17:46', '37.123.96.67', '108.174.197.209', 6, 63035, 445, 370176, 256
'2013-12-19 03:17:46', '85.96.246.9', '185.9.156.136', 6, 4522, 8050, 370176, 256
'2013-12-19 03:17:46', '178.20.231.211', '176.41.159.200', 17, 9904, 55323, 22528, 256
'2013-12-19 03:17:46', '178.20.226.200', '46.163.108.121', 6, 3389, 49498, 22528, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 22528, 256
'2013-12-19 03:17:46', '185.9.159.189', '77.87.16.110', 6, 1429, 445, 22528, 256
'2013-12-19 03:17:46', '185.9.159.91', '83.7.42.63', 6, 3598, 445, 22528, 256

Open in new window



is there any way to do this ?
0
Comment
Question by:3XLcom
  • 8
  • 6
15 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39728344
Provide your expected result for the sample data provided.
0
 

Author Comment

by:3XLcom
ID: 39728914
ip_trafik table
date                                  ip Address                Total inut       Total output
'2013-12-19 00:00:00', '178.20.231.211',      990324            55322323  


Total input is the sum of dst_ip 's (which is 178.20.231.211) octet
Total output is the sum of src_ip's  (which is 178.20.231.211) octet


and also i have a table which include my ip addresses in inetaton format if the ip does not in the ip list it will not write to the ip_trafik table so it will check each src and dst ip on the table which is given below if it is ours or not



this function will work on every hour and it will calculate the same day then when the day finished it will



This is the traffic logging table :


date ip_src  ip_dst , protocol , src_port , dst_port ,octet,packets
'2013-12-19 03:17:46', '178.20.231.211', '176.41.159.200', 17, 9904, 55323, 22528, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 22528, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 22528, 256
'2013-12-19 03:17:46', '178.20.230.190', '66.249.75.214', 6, 80, 58894, 22528, 256
'2013-12-19 03:17:46', '69.171.246.113', '178.20.231.102', 6, 58640, 80, 22528, 256
'2013-12-19 03:17:46', '69.171.246.113', '178.20.231.102', 6, 58640, 80, 22528, 256
'2013-12-19 03:17:46', '185.9.159.8', '46.1.46.239', 6, 13000, 60196, 22528, 256
'2013-12-19 03:17:46', '185.9.159.121', '75.55.252.97', 17, 11326, 57854, 70656, 256
'2013-12-19 03:17:46', '37.123.100.42', '88.233.13.209', 6, 15001, 50338, 70656, 256
'2013-12-19 03:17:46', '115.79.90.205', '178.20.231.95', 6, 26687, 80, 70656, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 70656, 256
'2013-12-19 03:17:46', '178.20.231.82', '175.42.40.20', 6, 80, 59073, 70656, 256
'2013-12-19 03:17:46', '176.227.203.26', '178.20.228.56', 6, 34553, 58068, 70656, 256
'2013-12-19 03:17:46', '173.194.113.5', '185.9.159.240', 6, 443, 1318, 132608, 256
'2013-12-19 03:17:46', '88.248.53.217', '178.20.231.82', 6, 62708, 80, 11776, 256
'2013-12-19 03:17:46', '37.123.96.67', '155.133.141.208', 6, 63017, 445, 37376, 256
'2013-12-19 03:17:46', '185.9.156.136', '94.122.16.18', 6, 7582, 60619, 70144, 256
'2013-12-19 03:17:46', '178.20.226.200', '63.34.133.113', 6, 1553, 445, 12288, 256
'2013-12-19 03:17:46', '88.248.53.217', '178.20.231.82', 6, 62706, 80, 17408, 256
'2013-12-19 03:17:46', '173.194.70.139', '185.9.159.240', 6, 443, 1319, 376320, 256
'2013-12-19 03:17:46', '185.9.159.82', '110.75.10.13', 6, 4724, 80, 11776, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 384000, 256
'2013-12-19 03:17:46', '178.20.228.56', '37.220.23.58', 6, 58045, 58714, 11776, 256
'2013-12-19 03:17:46', '123.125.115.99', '185.9.159.82', 6, 80, 4303, 59648, 256
'2013-12-19 03:17:46', '185.31.16.184', '185.9.159.169', 6, 80, 60012, 384000, 256
'2013-12-19 03:17:46', '178.20.227.86', '83.66.211.2', 6, 17489, 58169, 368640, 256
'2013-12-19 03:17:46', '178.20.225.195', '141.101.93.43', 6, 80, 60313, 13312, 256
'2013-12-19 03:17:46', '212.83.158.20', '37.123.100.116', 6, 443, 52887, 11776, 256
'2013-12-19 03:17:46', '178.20.228.37', '173.194.112.67', 6, 62774, 443, 25344, 256
'2013-12-19 03:17:46', '178.20.227.86', '216.88.144.81', 6, 1592, 445, 12288, 256
'2013-12-19 03:17:46', '185.9.157.2', '65.55.215.79', 6, 80, 25261, 11776, 256
'2013-12-19 03:17:46', '178.20.228.56', '37.220.23.58', 6, 58045, 58714, 11776, 256
'2013-12-19 03:17:46', '185.9.159.124', '66.249.75.173', 6, 80, 64157, 376320, 256
'2013-12-19 03:17:46', '176.227.203.26', '178.20.228.56', 6, 34553, 58068, 384000, 256
'2013-12-19 03:17:46', '5.255.253.7', '178.20.231.51', 6, 15231, 80, 13312, 256
'2013-12-19 03:17:46', '66.249.78.211', '178.20.231.102', 6, 34240, 80, 13312, 256
'2013-12-19 03:17:46', '178.20.231.82', '173.193.103.156', 6, 80, 3027, 384000, 256
'2013-12-19 03:17:46', '173.194.112.91', '178.20.228.37', 6, 443, 62775, 11776, 256
'2013-12-19 03:17:46', '178.20.231.82', '88.248.53.217', 6, 80, 62706, 381952, 256
'2013-12-19 03:17:46', '46.245.160.238', '185.9.156.136', 6, 56396, 7102, 13312, 256
'2013-12-19 03:17:46', '185.9.156.136', '80.134.174.97', 6, 7890, 49603, 381952, 256
'2013-12-19 03:17:46', '88.240.166.80', '178.20.231.158', 6, 50343, 13000, 12288, 256
'2013-12-19 03:17:46', '173.194.70.118', '185.9.156.37', 6, 80, 59983, 148480, 256
'2013-12-19 03:17:46', '178.20.231.95', '99.228.154.75', 6, 80, 52920, 384000, 256
'2013-12-19 03:17:46', '178.20.227.86', '32.21.198.66', 6, 1831, 445, 12288, 256
'2013-12-19 03:17:46', '79.236.192.198', '185.9.156.136', 6, 42517, 7854, 13312, 256
'2013-12-19 03:17:46', '178.20.227.86', '202.74.247.2', 17, 17489, 64522, 27136, 256
'2013-12-19 03:17:46', '178.20.231.211', '95.14.37.104', 17, 9904, 51622, 22528, 256
'2013-12-19 03:17:46', '173.194.70.116', '178.20.228.38', 6, 80, 57258, 22528, 256
'2013-12-19 03:17:46', '185.9.159.82', '133.8.129.62', 6, 4552, 445, 12288, 256
'2013-12-19 03:17:46', '185.9.159.233', '31.13.64.81', 6, 37486, 443, 11776, 256
'2013-12-19 03:17:46', '178.20.227.75', '190.12.100.200', 17, 3578, 49902, 24064, 256
'2013-12-19 03:17:46', '178.20.227.86', '49.119.140.63', 6, 1841, 445, 24064, 256
'2013-12-19 03:17:46', '173.194.70.190', '185.9.159.240', 6, 443, 1313, 24064, 256
'2013-12-19 03:17:46', '178.20.228.56', '176.227.203.26', 6, 58068, 34553, 24064, 256
'2013-12-19 03:17:46', '185.9.159.189', '128.116.160.79', 6, 1178, 445, 24064, 256
'2013-12-19 03:17:46', '178.20.231.76', '133.78.223.85', 6, 1854, 445, 24064, 256
'2013-12-19 03:17:46', '82.145.241.23', '178.20.224.224', 6, 80, 53526, 15360, 256
'2013-12-19 03:17:46', '185.9.159.86', '220.122.150.37', 6, 1629, 445, 12288, 256
'2013-12-19 03:17:46', '178.20.231.95', '99.228.154.75', 6, 80, 52920, 384000, 256
'2013-12-19 03:17:46', '178.20.231.82', '88.248.53.217', 6, 80, 62709, 381952, 256
'2013-12-19 03:17:46', '178.20.228.56', '176.227.203.26', 6, 58068, 34553, 11776, 256
'2013-12-19 03:17:46', '37.123.100.42', '85.104.36.29', 6, 15001, 60798, 49664, 256
'2013-12-19 03:17:46', '185.9.159.160', '86.77.111.61', 6, 1431, 445, 12288, 256
'2013-12-19 03:17:46', '178.20.227.71', '198.228.210.97', 17, 19, 1050, 287744, 256
'2013-12-19 03:17:46', '178.20.227.86', '83.66.211.2', 6, 17489, 58169, 287744, 256
'2013-12-19 03:17:46', '178.20.231.211', '176.41.159.200', 17, 9904, 55323, 22528, 256
'2013-12-19 03:17:46', '78.168.215.254', '178.20.227.76', 17, 57712, 47801, 7168, 256
'2013-12-19 03:17:46', '78.188.3.71', '37.123.97.70', 6, 2388, 32228, 7168, 256
'2013-12-19 03:17:46', '178.20.231.84', '173.252.113.127', 17, 53, 57176, 12544, 256
'2013-12-19 03:17:46', '124.95.156.16', '185.9.159.70', 17, 20706, 12255, 27904, 256
'2013-12-19 03:17:46', '78.168.215.254', '178.20.227.76', 17, 57712, 47801, 7168, 256
'2013-12-19 03:17:46', '178.20.231.82', '157.55.33.39', 6, 80, 33714, 7168, 256
'2013-12-19 03:17:46', '178.20.228.38', '173.194.70.116', 6, 57258, 80, 7168, 256
'2013-12-19 03:17:46', '24.159.97.159', '185.9.157.162', 6, 56348, 3389, 23808, 256
'2013-12-19 03:17:46', '178.20.231.82', '88.248.53.217', 6, 80, 62708, 381952, 256
'2013-12-19 03:17:46', '178.20.227.43', '81.213.46.191', 6, 31691, 45441, 11776, 256
'2013-12-19 03:17:46', '37.123.101.163', '85.110.176.205', 6, 80, 55450, 371712, 256
'2013-12-19 03:17:46', '185.9.156.136', '85.98.117.95', 6, 7854, 62445, 337408, 256
'2013-12-19 03:17:46', '99.228.154.75', '178.20.231.95', 6, 52920, 80, 11776, 256
'2013-12-19 03:17:46', '180.76.5.118', '178.20.231.82', 6, 38342, 80, 11776, 256
'2013-12-19 03:17:46', '178.20.226.150', '78.168.196.164', 17, 41322, 38802, 31232, 256
'2013-12-19 03:17:46', '173.194.70.116', '178.20.228.38', 6, 80, 57258, 31232, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 31232, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 31232, 256
'2013-12-19 03:17:46', '37.123.100.111', '78.176.188.255', 6, 80, 50833, 31232, 256
'2013-12-19 03:17:46', '108.162.246.41', '37.123.96.116', 6, 17557, 80, 31232, 256
'2013-12-19 03:17:46', '78.165.254.67', '37.123.103.252', 6, 54704, 80, 31232, 256
'2013-12-19 03:17:46', '178.20.227.76', '78.168.215.254', 17, 47801, 57712, 370176, 256
'2013-12-19 03:17:46', '178.20.231.82', '173.255.201.130', 6, 80, 34514, 370176, 256
'2013-12-19 03:17:46', '178.20.231.155', '74.125.163.247', 6, 65091, 80, 370176, 256
'2013-12-19 03:17:46', '178.20.227.76', '78.168.215.254', 17, 47801, 57712, 370176, 256
'2013-12-19 03:17:46', '185.9.159.160', '21.113.178.108', 6, 1454, 445, 370176, 256
'2013-12-19 03:17:46', '37.123.96.67', '108.174.197.209', 6, 63035, 445, 370176, 256
'2013-12-19 03:17:46', '85.96.246.9', '185.9.156.136', 6, 4522, 8050, 370176, 256
'2013-12-19 03:17:46', '178.20.231.211', '176.41.159.200', 17, 9904, 55323, 22528, 256
'2013-12-19 03:17:46', '178.20.226.200', '46.163.108.121', 6, 3389, 49498, 22528, 256
'2013-12-19 03:17:46', '37.220.23.58', '178.20.228.56', 6, 58714, 58045, 22528, 256
'2013-12-19 03:17:46', '185.9.159.189', '77.87.16.110', 6, 1429, 445, 22528, 256
'2013-12-19 03:17:46', '185.9.159.91', '83.7.42.63', 6, 3598, 445, 22528, 256

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39728981
One approach is to use a derived table (query) that performs a UNION to normalize the data.  For example, you can do something like:

SELECT `date`, `ip_src` AS ip_addr, `octet`, 'out' AS `direction`
FROM ip_traffic
UNION ALL
SELECT `date`, `ip_dst` AS ip_addr, `octet`, 'in' AS `direction`
FROM ip_traffic

You can save this as a view if you do these calculations often.  Otherwise, you can just wrap this in the FROM like this with conditional aggregates in the SELECT:

SELECT `date`, `ip_addr`
     , SUM(IF(`direction`='in', `octet`, 0)) AS total_in
     , SUM(IF(`direction`='out', `octet`, 0)) AS total_out

FROM (
   -- insert query from above here
) derived

GROUP BY `date`, `ip_addr`

EDIT: If you saved the query as a view, you can just use FROM view_name.

I hope that helps!
0
 

Author Comment

by:3XLcom
ID: 39728998
table has 50million rows nearly this takes too long time
and also that does not check if it is in our ip list or not and it does not record into daily table for todays limitation
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39729004
Yes, the code I showed did not provide the entire solution.  I was addressing the how you get the sum part.  You will need to filter by date and add INSERT statement to update the second table.  If you have a good index on the date column, the query should perform fine.
0
 

Author Comment

by:3XLcom
ID: 39729014
i have tryed sth. llike this to run this job every hour instead of every day to create smaller peaces. but nothing returned :)



SELECT `timestampfromfile`, `ip_addr`
     , SUM(IF(`direction`='in', `octets`, 0)) AS total_in
     , SUM(IF(`direction`='out', `octets`, 0)) AS total_out
FROM (
SELECT `timestampfromfile`, `srcip` AS ip_addr, `octets`, 'out' AS `direction`
FROM mytable where timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
UNION ALL
SELECT `timestampfromfile`, `dstip` AS ip_addr, `octets`, 'in' AS `direction`
FROM mytable where timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
) derived
GROUP BY `timestampfromfile`, `ip_addr`

Open in new window

0
 

Author Comment

by:3XLcom
ID: 39729023
i  am sorry it worked :

http://prntscr.com/2cbthu


but how should i check ip addresses what if it exist in my  ipaddresses table or not ? and insert into the daily ip traffic table ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:3XLcom
ID: 39729031
SELECT `timestampfromfile`, `ip_addr`
     , SUM(IF(`direction`='in', `octets`, 0)) AS total_in
     , SUM(IF(`direction`='out', `octets`, 0)) AS total_out
FROM (
SELECT `timestampfromfile`, `srcip` AS ip_addr, `octets`, 'out' AS `direction`
FROM mytable where timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
UNION ALL
SELECT `timestampfromfile`, `dstip` AS ip_addr, `octets`, 'in' AS `direction`
FROM mytable where timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
) a inner join ipAdresleri i on i.ip=a.ip_addr
GROUP BY `timestampfromfile`, `ip_addr`

Open in new window





i am sorry it worked now with that inner join :)

but i need one last thing how shold i add this to the my ipminutelytraffic table
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39729044
Can you provide the creation script for the ip_traffic table to make sure I use the same data types?  I suspect is there is no data matching to that range.  What data type (format) is timestampfromfile?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39729051
Please disregard my earlier message, I got distracted and did not see your last post.  To insert just add:

INSERT INTO target_table( /* columns */ )
/* query from above */

If you are doing this by day, you can add another filter to WHERE clause that checks that the `date` and `ip_addr` combination does not exist in the target table already.  If it exists and you want to update, you can look at MySQL's upsert ability, which allows you to change INSERT to an UPDATE on duplicate key (if key to target table is ip address and date for example).

EDIT: you may want to consider adjusting the date based on your grouping.  For example, if you want to GROUP BY day, use the date at midnight (i.e., drop time stamp).  If you want to GROUP BY minute, use the top of the minute you are including (i.e., drop the seconds part of the time stamp).  This way, you will lower the number of rows per ip_addr per segment as well as give you a method to tell if you already inserted the data as the new records should have a new date.
0
 

Author Comment

by:3XLcom
ID: 39729124
I've gone drink coffe and come back but still the query is working :)

insert into dakikatrafik (saat,ip,total_in,total_out) values ((

SELECT `timestampfromfile`, `ip_addr`
     , SUM(IF(`direction`='in', `octets`, 0)) AS total_in
     , SUM(IF(`direction`='out', `octets`, 0)) AS total_out
FROM (
SELECT `timestampfromfile`, `srcip` AS ip_addr, `octets`, 'out' AS `direction`
FROM mytable where timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
UNION ALL
SELECT `timestampfromfile`, `dstip` AS ip_addr, `octets`, 'in' AS `direction`
FROM mytable where timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
) a inner join ipAdresleri i on i.ip=a.ip_addr
GROUP BY `timestampfromfile`, `ip_addr` );

Open in new window


mytable
http://prntscr.com/2cc3pm

and 5000+ ipaddress
0
 

Author Comment

by:3XLcom
ID: 39729137
this system now every hour collecting minute by minute
at the end of the day it will collect daily total for the yesterday and new day will begin
hourly table and live table will be completely deleted at the 00:00 am  

but that query will never end i thing :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39729147
Note: with INSERT INTO ... SELECT you omit the VALUES.  Therefore, if you meant to say you drank coffee but it still does not work, that may be the issue.  

INSERT INTO dakikatrafik (saat, ip, total_in, total_out)
SELECT `timestampfromfile`, `ip_addr`
     , SUM(IF(`direction`='in', `octets`, 0)) AS total_in
     , SUM(IF(`direction`='out', `octets`, 0)) AS total_out
FROM (
    SELECT `timestampfromfile`, `srcip` AS ip_addr, `octets`, 'out' AS `direction`
    FROM mytable 
    WHERE timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
    UNION ALL
    SELECT `timestampfromfile`, `dstip` AS ip_addr, `octets`, 'in' AS `direction`
    FROM mytable 
    WHERE timestampfromfile >   DATE_SUB(NOW(), INTERVAL minute(now()) MINUTE)
) a
INNER JOIN ipAdresleri i ON i.ip=a.ip_addr
GROUP BY `timestampfromfile`, `ip_addr`
;

Open in new window


If you drank coffee and are pleased it still works, I am glad. *smile*

I need to grab a new cup myself.

--Kevin
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39729157
By the way, if you ever get into a situation where the query slows.  You can use some techniques provided by gr8gonzo's article 3 Ways to Speed Up MySQL.
0
 

Author Closing Comment

by:3XLcom
ID: 39729247
Thank you so much please check out this question too
http://www.experts-exchange.com/Database/MySQL/Q_28322177.html
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now