Helper to add networks to the IntranetSubNetwork Plugin for Piwik - Also updating previous entries in the database. See http://goo.gl/efzP1 .

helper.py

#!/usr/bin/env python3

from sys import exit
from platform import system

try:
    import ipaddress
    # see https://docs.python.org/3/library/ipaddress.html
    # and https://docs.python.org/3/howto/ipaddress.html
except ImportError:
    print(" This script needs to be run with Python3.3 or newer in order to have the new ipaddress module.")
    exit(1)

def help_with_name_and_nets(netName, text):
    nets = evaluate_text_containing_networks(text)
    if len(nets) == 0:
        print("Could not detect any networks in your input. Check it again. Exiting.")
        exit(2)
    print_PHP(netName, nets)
    print_SQL(netName, nets)

def evaluate_text_containing_networks(text):
    fragments = []
    for line in text.split("\n"):
        fragments += line.split(' ')
    nets = []
    for fragment in fragments:
        net = None
        try:
            net = ipaddress.IPv4Network(fragment, strict=False)
        except:
            pass
        try:
            net = ipaddress.IPv4Network(fragment, strict=False)
        except:
            pass
        try:
            net = ipaddress.IPv6Network(fragment, strict=False)
        except:
            pass
        if not net:
            print("Could not parse IP/network " + fragment)
            continue
        nets.append(net)
    return nets

def print_PHP(netName, nets):

    print("""\nHere are the lines that you have to add to the IntranetSubNetwork 
            plugin to match the network '%s' from now on:\n""".replace('    ','') % netName)
    
    for ip in nets:
        print("    if (Piwik_IP::isIpInRange($visitorInfo['location_ip'], array('%s'))) { $networkName = '%s'; }" % (ip, netName))

def print_SQL(netName, nets):

    print("""\nHere is the SQL statement you have to
    run in your Piwik database in order to update
    older entries to the network name '%s'.
    
    Also you have to remove the archived blob
    tables afterwards and recalculate the archive.\n""".replace('    ','') % netName)
    
    sql = create_single_SQL(netName, nets)
    #sql = create_multi_SQL(netName, nets)
    # print the indented SQL commands:
    print("\n".join(["    "+line for line in sql.split("\n")]) + "\n")

def create_multi_SQL(netName, nets):
    """
    Create SQL commands for the Piwik plugin IntranetSubNetwork.
    This function creates multiple SQL commands that updates the old entries.
    The commands have the same effect as running the single SQL created by create_single_SQL().
    """
    sql_v4 = """
    UPDATE `piwik_log_visit`
    SET `location_IntranetSubNetwork`='%s'
    WHERE
    conv(hex(location_ip), 16,10) >= INET_ATON('%s') AND conv(hex(location_ip), 16,10) <= INET_ATON('%s');
    """
    sql_v6 = """
    UPDATE `piwik_log_visit`
    SET `location_IntranetSubNetwork`='%s'
    WHERE
    location_ip >= x'%s' AND location_ip <= x'%s';
    """
    sql = ""
    for ip in nets:
        if ip.version == 4:
            sql += sql_v4 % (netName,  ip[0], ip[-1])
        else: # IPv6
            sql += sql_v6 % (netName, "{:032x}".format(int(ip[0])), "{:032x}".format(int(ip[-1])))
    return sql.replace('    ','')

def create_single_SQL(netName, nets):
    """
    Create an SQL command for the Piwik plugin IntranetSubNetwork.
    This function creates a single SQL command that updates the old entries.
    This might take longer to run but is simpler.
    """
    sql = """
    UPDATE `piwik_log_visit`
    SET `location_IntranetSubNetwork`='%s'
    WHERE
    """ % netName
    conditions = []
    for ip in nets:
        if ip.version == 4:
            conditions.append("( conv(hex(location_ip), 16,10) >= INET_ATON('%s') AND conv(hex(location_ip), 16,10) <= INET_ATON('%s') ) \n"
                    % (ip[0], ip[-1]) )
        else: # IPv6
            conditions.append("( location_ip >= x'%s' AND location_ip <= x'%s' )"
                    % ("{:032x}".format(int(ip[0])), "{:032x}".format(int(ip[-1]))) )
    sql += "\n OR ".join(conditions)
    sql += ";"
    return sql.replace('    ','')

if __name__ == "__main__":
    netName = input('Please enter a name for the networks: ')
    eof_key = "[CTRL]-[Z] and [ENTER] again" if system() == 'Windows' else "[CTRL]-[D]"
    print("Now you need to enter the networks that should match that network name.\n" \
            "An example would be '10.20.0.0/16  192.168.1.0/24  2610:130:10:10::/64'.\n" \
            "They can be separated by spaces and/or line breaks.\n" \
            "Text that cannot be interpreted as an IP or IP network will be dropped.\n" \
            "The networks you enter will be aggregated if possible.\n" \
            "When finished, press [Enter] and %s:" % eof_key )
    networks = []
    import sys
    for line in sys.stdin.readlines():
        networks.append(line)
    networks = "\n".join(networks)
    help_with_name_and_nets(netName, networks)
    if system() == 'Windows':
        input("Press [Enter] to leave the tool.")