Skip to content

Instantly share code, notes, and snippets.

@bennadel

bennadel/group-by-ip.sql

Created March 21, 2016 23:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bennadel/346c38bab92df06cdcf8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bennadel/346c38bab92df06cdcf8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. bennadel created this gist Mar 21, 2016.
    40 changes: 40 additions & 0 deletions group-by-ip.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,40 @@
    SELECT
    tmp.ipAddress,

    -- Calculate how many connections are being held by this IP address.
    COUNT( * ) AS ipAddressCount,

    -- For each connection, the TIME column represent how many SECONDS it has been in
    -- its current state. Running some aggregates will give us a fuzzy picture of what
    -- the connections from this IP address is doing.
    FLOOR( AVG( tmp.time ) ) AS timeAVG,
    MAX( tmp.time ) AS timeMAX
    FROM
    -- Let's create an intermediary table that includes an additional column representing
    -- the client IP address without the port.
    (

    SELECT
    -- We don't actually need all of these columns for the demo. But, I'm
    -- including them here to demonstrate what fields COULD be used in the
    -- processlist system.
    pl.id,
    pl.user,
    pl.host,
    pl.db,
    pl.command,
    pl.time,
    pl.state,
    pl.info,

    -- The host column is in the format of "IP:PORT". We want to strip off
    -- the port number so that we can group the results by the IP alone.
    LEFT( pl.host, ( LOCATE( ':', pl.host ) - 1 ) ) AS ipAddress
    FROM
    INFORMATION_SCHEMA.PROCESSLIST pl

    ) AS tmp
    GROUP BY
    tmp.ipAddress
    ORDER BY
    ipAddressCount DESC