0

I am using two MYSQL tables on has big log strings for example: "this is a sample log entry with 123.456.789 IP address". Also, there is second table that contains list of Ip addresses in each row. I want to check for all the matching Ip addresses in the log entries and get the result as all the entries in log tables with matching IPs.

I have installed Mysql community version 5.7.22 on RHEL server.

Table 1 : log table 

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| log_id      | int(11)       | NO   | PRI | NULL    | auto_increment |
| Id          | varchar(30)   | NO   |     | NULL    |                |
| host        | varchar(50)   | YES  |     | NULL    |                |
| external_id | varchar(40)   | NO   |     | NULL    |                |
| message     | varchar(8000) | YES  |     | NULL    |                |
| timestamp   | varchar(30)   | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

Table 2 : IP table

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| ip        | varchar(30) | NO   |     | NULL    |                |
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
+-----------+-------------+------+-----+---------+----------------+

I am using below query :

select * from logs where message like '%'ip_table.ip'%';

which is giving a syntax error.

Any other ideas to work on this?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Deep
  • 35
  • 1
  • 3
  • 8

1 Answers1

0

You can fix the syntax error by using concat():

select *
from logs l join
     ip_table it
     on l.message like concat('%', it.ip, '%');

However, this would match, say, '1.1.1.1' and '1.1.1.10'.

To fix this, you need to take delimiters into account. Assuming this is always a space:

select *
from logs l join
     ip_table it
     on concat(' ', l.message, ' ') like concat('% ', it.ip, ' %');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I am receiving "Unknown column 'ip_table.ip_value' in 'where clause' ". as the ip_table. I believe we need to use some Join here as these a 2nd table. please do correct me if i am wrong – Deep Aug 29 '19 at 02:01
  • anyone any suggestions? – Deep Sep 03 '19 at 02:45
  • @DEEPANSHUMARWAH . . . This answer has no `where` clause, so it cannot be generating that error. – Gordon Linoff Sep 03 '19 at 10:33