MySQL IP address camparing to ISP

Question

I have 2 tables in my mySQL database.

First table is isp:

isp_ip_address | isp_name
-------------------------
157.55.33.0    | isp_3

157.55.0.0     | isp_1

193.219.9.0    | isp_2

65.55.213.0    | isp_4

195.182.85.226 | isp_5

78.58.5.117    | isp_6

78.58.5.0      |  isp_7

Second table is user_stats:

uid | lastlogin_ip | lastlogin
------------------------------
111 | 78.58.83.111 | 2013-03-12

132 | 78.58.5.117  | 2013-03-12

258 | 195.182.85.226 | 2013-03-14

165 | 157.55.33.78  | 2013-03-12

822 | 65.55.213.216 | 2013-03-15

523 | 157.55.35.38 | 2013-03-12
.....

And so on.

I need to compare users lastlogin_ip to isp_ip_address and in a new table insert uid, lastlogin_ip and isp_name. I need to compare ip_address like this: if full lastlogin_ip equals isp_ip_address then stop searching and write isp_name, if they don't match, I need to compare firdt 3 parts of IP addresses and do the same, and if 3 parts don't match, I need to compare 2 parts of IP addresses and do the same.

I tried the following SQL code:

SELECT 
  user_stats.`uid`,
  user_stats.`lastlogin_ip`,
  isp.`isp`,
FROM
  stats.`user_stats` 
  LEFT JOIN stats.`isp` 
ON(
    IF(
      isp.isp_ip_address LIKE user_stats.`lastlogin_ip`,
      isp.isp_ip_address LIKE user_stats.`lastlogin_ip`,
      IF(
        SUBSTRING_INDEX(isp.isp_ip_address,'.',-1) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',-1),
        IF(
          SUBSTRING_INDEX(SUBSTRING_INDEX(isp.isp_ip_address,'.',3),'.',-1) LIKE SUBSTRING_INDEX(SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',3),'.',-1),
          IF(
            SUBSTRING_INDEX(SUBSTRING_INDEX(isp.isp_ip_address,'.',2),'.',-1) LIKE SUBSTRING_INDEX(SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',2),'.',-1),
            SUBSTRING_INDEX(isp.isp_ip_address,'.',1) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',1),
            NULL),
          SUBSTRING_INDEX(isp.isp_ip_address,'.',2) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',2)),
        SUBSTRING_INDEX(isp.isp_ip_address,'.',3) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',3))));

This code compares parts of IP addresses but it duplicates rows, which means that if it finds 3 equal parts and 2 equal parts, it writes the same row two times with different isp_name values.

Could you give me a hint how should I edit this code to work as it should? Any help would be appreciated!

score 0 · Answer 1 · edited Mar 25 '13 at 10:40

0

Your join criteria does not correspond to your description. When used with negative count, substring_index returns the right side of the ip, not the left.

Anyway, try the following join criteria (untested):

ON(
   isp.isp_ip_address = user_stats.lastlogin_ip
OR SUBSTRING_INDEX(isp.isp_ip_address,'.', 3) = SUBSTRING_INDEX(user_stats.lastlogin_ip,'.', 3)
OR SUBSTRING_INDEX(isp.isp_ip_address,'.', 2) = SUBSTRING_INDEX(user_stats.lastlogin_ip,'.', 2)
)

edited Mar 25 '13 at 10:40

huzeyfe

3,554
6
39
49

answered Mar 25 '13 at 10:17

koriander

3,110
2
15
23

This join criteria still duplicates rows. For example if i have IP address 78.58.5.117 it will be written once with isp_name isp_7 and once with isp_name isp_6 because there is isp_name -> isp_7 which has isp_ip_address -> 78.58.5.0 (3 parts are equal) and isp_name -> isp_6 which has isp_ip_address -> 78.58.5.117 (all 4 parts are equal). I need to insert only once if 78.58.5.117 = 78.58.5.117 or if it is not equal try comparing first 3 parts of IP address 78.58.5.% = 78.58.5.% and if that is not equal, try comparing first 2 parts 78.58.%.% = 78.58.%.% – aurimokas Mar 25 '13 at 11:08
I see. This can't be done with a single join because the join only compares one record from each table at a time. In your case multiple records are involved because of sub/super isps. Sub queries are needed for each of the three combinations and then partial joins to pick the relevant records. I'll try to provide a more complete answer later on if you need. – koriander Mar 25 '13 at 13:02
It would be great if you could provide more complete answer for me, because I am already empty with ideas how to qrite this query – aurimokas Mar 25 '13 at 13:12

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

isn't it better to store IP addresses and CIDR as integers and then use something like this: Is there way to match IP with IP+CIDR straight from SELECT query?

It's easier to read these queries
probably faster than what you have at the moment
the comparison is more accurate (in terms of networking) than substring comparisons.

edited May 23 '17 at 10:25

Community

1
1

answered Mar 25 '13 at 10:37

Alexey

3,414
7
26
44

koriander · Accepted Answer · 2013-03-26T14:30:47.703

0

very untested but hopefully should put you in the right track:

edit: qry fixed for syntax and tested.

SELECT match3.lastlogin_ip, ifnull(match3.isp_name, isp.isp_name) as isp_name FROM
(SELECT match4.lastlogin_ip, ifnull(match4.isp_name, isp.isp_name) as isp_name, isp.isp_ip_address FROM
  (SELECT user_stats.lastlogin_ip, isp.isp_name, isp.isp_ip_address FROM user_stats
  LEFT JOIN isp ON (user_stats.lastlogin_ip = isp.isp_ip_address)) as match4
  LEFT JOIN isp ON (match4.isp_name is null and SUBSTRING_INDEX(match4.lastlogin_ip,'.', 3) = SUBSTRING_INDEX(isp.isp_ip_address,'.', 3))) as match3
LEFT JOIN isp ON (match3.isp_name is null and SUBSTRING_INDEX(match3.lastlogin_ip,'.', 2) = SUBSTRING_INDEX(isp.isp_ip_address,'.', 2));

edited Mar 26 '13 at 14:30

answered Mar 25 '13 at 14:03

koriander

3,110
2
15
23

I edited your query (there were some syntax errors) but this query only gives isp_name to rows which ip_addresses are equal by all 4 parts. 3 parts, 2 parts and other rows are NULL – aurimokas Mar 26 '13 at 08:21
This looks to be what you asked for: "equal by all 4 parts. 3 parts, 2 parts". What is missing? – koriander Mar 26 '13 at 09:48
I can't find what's the problem. but when I run this query, it only shows isp_names where all 4 parts of ip_adress and lastlogin are equal. When 3 or 2 parts are equal, query doesn't write isp_name and write NULL value instead – aurimokas Mar 26 '13 at 11:58
ok, it was not clear from your first comment that the isp_name was null for 2&3 matches. Rows not matching an isp should indeed return isp_name as NULL. Please edit my answer with your syntax-correct SQL and I'll have a look at it. – koriander Mar 26 '13 at 12:12
I have fixed the syntax errors and changed a bit the criteria merely for readability purposes. The query is working according to what you explained. Note that uid=111 and uid=523 from your examples will get two isps each, as expected, since probably some isp records are missing there. – koriander Mar 26 '13 at 14:35
and that is the problem. It should write only isp with 3 equal parts. 2 equal parts should be ignored. 157.55.35.38 should have only isp_3 name near it. There shouldn't be swcond record with isp_1 name near it. It's a bit hard to explain this, but I hope you get that the priority of qriting isp_name is highest when comparing equal parts number is highest. And if query finds smaller number of parts equal after finding hiehger number of equal parts it should ignore all other smaller equal parts. There cannot be any duplicates. Only 1 isp_name for one lastlogin_ip. – aurimokas Mar 27 '13 at 07:18
Thank you! I have managed to ignore duplicate values by using GROUP BY. You are my saviour! :) – aurimokas Mar 27 '13 at 08:12

MySQL IP address camparing to ISP

3 Answers3