-1
test1.com dstnat: in:ether1 out:(unknown 0), src-mac 5c:5e:ab:7b:21:a3, proto TCP (SYN), 10.20.23.32:41630->12.3.3.45, len 52
 test2.com dstnat: in:ether1 out:(unknown 0), src-mac 5c:5e:ab:7b:21:a3, proto TCP (SYN), 10.20.123.2:41630->12.3.3.45, len 52
 test1.com dstnat: in:ether1 out:(unknown 0), src-mac 5c:5e:ab:7b:21:a3, proto TCP (SYN), 30.20.223.32:41630->12.3.3.45, len 52
 test1.com dstnat: in:ether1 out:(unknown 0), src-mac 5c:5e:ab:7b:21:a3, proto TCP (SYN), 12.20.23.32:41630->12.3.3.45, len 52

i want to get only first IP for example 10.20.23.32 in first line,second line 10.20.123.2 and etc... and also count and get top 10 repeated IP. what is best way?

4
  • Check out these suggestions. – mustaccio Jun 4 at 18:27
  • 1
    Hi, and welcome to dba.se. What sort of table is this data in? Could you provide the output of SHOW CREATE TABLE xyz\G - we need this, otherwise, we're flying blind. – Vérace Jun 4 at 19:31
  • Which exact version of MySQL are you using? Are you not able to upgrade to a more current version? – Colin 't Hart Jun 5 at 8:44
  • i am using last version – Top Music Jun 5 at 16:02
0

You are using an old version of MySQL, so certain things are not available, such as REGEXP_REPLACE().

Instead, let's work on using SUBSTRING_INDEX(). It looks like the IP is after the 3rd space space from the end, then before the first colon of that?

SELECT ...
        SUBSTRING_INDEX(
        SUBSTRING_INDEX(col, ' ', -3)
                           , ':',  1) AS ip
    FROM ...

Then

SELECT ... (as above) AS ip
     , COUNT(*) AS ct
    FROM tbl
    GROUP BY ip
    ORDER BY ct DESC
    LIMIT 10
2
  • But sometimes length of IP not always 9 or 8 – Top Music Jun 5 at 5:36
  • @TopMusic - IPv4, as a string, needs up to 15 characters. Where does the "9 or 8" come into play? – Rick James Jun 5 at 17:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.