Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql indexed question

Posted on 2013-12-18
15
Medium Priority
?
657 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 41

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 60

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 60

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
 

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 60

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 60

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

688 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