LibreQoE / LibreQoS

A Quality of Experience and Smart Queue Management system for ISPs. Leverage CAKE to improve network responsiveness, enforce bandwidth plans, and reduce bufferbloat.
https://libreqos.io/
GNU General Public License v2.0
445 stars 48 forks source link

Splynx Integration works but Parent Node empty #535

Closed owaismai closed 22 hours ago

owaismai commented 3 months ago

How does libreqos get the Parent Node information from Splynx? What field in Splynx determines the Parent Node. Is this field supposed to be empty even after successfull integration with Splynx? (I'm referring to the file shapeddevices.csv)

rchac commented 3 months ago

Hello. To our knowledge, the Splynx API does not offer a way to find out what AP an Internet Service is connected to. I believe they are working on adding that functionality. If this is incorrect and there is a way to pull that info from their API, please let us know and we'll be happy to use that.

owaismai commented 3 months ago

Its quite possible. I have spent the day with Splynx support and for now, we can do this by pulling the "port" value under Statistics. Meaning the AP name is NAS PORT ID. Here is a screenshot from splynx :

NASportid

So running the following sql query gives me the IP and port in a nice table, however this can be retrieved via the API:


CREATE TEMPORARY TABLE temp_stat AS
SELECT s1.customer_id,
       CONCAT_WS('.',
                 CONV(HEX(SUBSTRING(s1.ipv4, 1, 1)), 16, 10), 
                 CONV(HEX(SUBSTRING(s1.ipv4, 2, 1)), 16, 10), 
                 CONV(HEX(SUBSTRING(s1.ipv4, 3, 1)), 16, 10), 
                 CONV(HEX(SUBSTRING(s1.ipv4, 4, 1)), 16, 10)) AS ipv4, 
       s1.nas_id,
       s1.port
FROM statistics s1
JOIN (
    SELECT customer_id, 
           COALESCE(
               (SELECT MAX(id) 
                FROM statistics 
                WHERE port IS NOT NULL AND port != '' 
                AND start_date > '2024-07-01' AND customer_id = s.customer_id),
               (SELECT MAX(id) 
                FROM statistics 
                WHERE start_date > '2024-07-01' AND customer_id = s.customer_id)
           ) AS max_id
    FROM statistics s
    WHERE start_date > '2024-07-01'
    GROUP BY customer_id
) s2 ON s1.customer_id = s2.customer_id AND s1.id = s2.max_id;

select customers.name as customer_name, temp_stat.ipv4, routers.title as access_server, temp_stat.port from temp_stat 
left join customers on temp_stat.customer_id = customers.id
left join routers on temp_stat.nas_id = routers.id
where customers.status = 'active'
rchac commented 3 months ago

Ok. We could create Parent Nodes by joining together some of those parameters. We could have the Parent Node name = router_id _ sector_id _ port_id. Would that work? I wouldn't want to use port_id by itself since not all Splynx users will be using port_id in that way. But this way you'd get consistent, predictable Parent Node (AP) names.

owaismai commented 3 months ago

I think that's a great idea. As a side note, is this the reason why I'm not able to see any heatmaps as shown in the demo?

On Sun, 28 Jul 2024, 19:39 Robert Chacón, @.***> wrote:

Ok. We could create Parent Nodes by joining together some of those parameters. We could have the Parent Node name = router_id _ sector_id _ port_id. Would that work? I wouldn't want to use port_id by itself since not all Splynx users will be using port_id in that way. But this way you'd get consistent, predictable Parent Node (AP) names.

— Reply to this email directly, view it on GitHub https://github.com/LibreQoE/LibreQoS/issues/535#issuecomment-2254591173, or unsubscribe https://github.com/notifications/unsubscribe-auth/BIO2XSIRYHGUBR52COTPBQ3ZOUUEVAVCNFSM6AAAAABLSQBL3KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENJUGU4TCMJXGM . You are receiving this because you authored the thread.Message ID: @.***>

rchac commented 3 months ago

Ok. Working on it now. And the heatmaps are a feature of LibreQoS LTS. To learn more, click Statistic Free Trial in the top right corner of the Web UI.

rchac commented 3 months ago

Please test this PR to confirm if it helps. https://github.com/LibreQoE/LibreQoS/pull/536

owaismai commented 3 months ago

Thanks. I tried it, but getting this error:


Running Python Version 3.10.12 (main, Mar 22 2024, 16:50:05) [GCC 11.4.0]
Traceback (most recent call last):
  File "/opt/libreqos/src/newsplyxintegration.py", line 7, in <module>
    from liblqos_python import exclude_sites, find_ipv6_using_mikrotik, bandwidth_overhead_factor, splynx_api_key, \
ImportError: cannot import name 'exclude_sites' from 'liblqos_python' (/opt/libreqos/src/liblqos_python.so)
owaismai commented 3 months ago

I installed libreqos from apt on ubuntu 22.04. Does this mean I have v1.4? Would that be the issue here?

rchac commented 3 months ago

@owaismai Gotcha. Here's a v1.4 compatible version you can try. https://github.com/LibreQoE/LibreQoS/pull/537 Downloadable file: https://github.com/LibreQoE/LibreQoS/blob/a82cc112455fcc35f453fc2dbcae9e249b2fe280/src/integrationSplynx.py

owaismai commented 3 months ago

Thanks However I think there was some confusion. I meant that we take the Parent Node name from the NAS Port ID, from Statistics I have requested help regarding this from Splynx support and they have given the following:

Use the Online customer collection: https://splynx.docs.apiary.io/#reference/customers/online-customers-collection/list-all-online-customers

The 'call to' field is the pppoe server name and the 'port' field is the interface (AP) that those customers are connected to on that router.

So I tested it, if I make a call to https://myserver.com/api/2.0/admin/customers/customers-online I get the following:


{
    "id": "12345678",
    "customer_id": "1234",
    "service_id": "5678",
    "tariff_id": "61",
    "partner_id": "22",
    "nas_id": "4",
    "login": "user@example.com",
    "username_real": "user@example.com",
    "in_bytes": "495999852891",
    "out_bytes": "24806186253",
    "start_session": "2024-06-05 12:58:07",
    "ipv4": "10.0.0.1",
    "ipv6": "",
    "ipv6_prefix": "0",
    "mac": "00:00:00:00:00:00",
    "call_to": "service17",
    "port": "vlan104",
    "price": "0.0000",
    "time_on": "4662013",
    "last_change": "2024-07-29 11:58:19",
    "type": "radius",
    "login_is": "user",
    "session_id": "87654321",
    "blocked": "0",
    "kill": null,
    "fup": null,
    "signal": null,
    "services_internet_router_id": null,
    "services_internet_sector_id": null,
    "services_internet_login": null,
    "services_internet_ipv4": null,
    "services_internet_ipv4_route": null,
    "services_internet_mac": null,
    "services_internet_unit_price": null,
    "services_internet_start_date": null,
    "services_internet_end_date": null,
    "services_internet_description": null
}

From the above, the relevent ones are:

"nas_id": "4",
"ipv4": "10.0.0.1",
"call_to": "service17",
"port": "vlan104",

From the above: ipv4 is the client ip address and we can make the Parent node named like this nas_id + call_to + port. So AP name will be 4service17vlan104.

The pppoe server names in the Mikrotik router cane be changed to make the AP names look better

rchac commented 3 months ago

Ok. Here is a v1.4 compatible version that will create the nodes as nasid + + callto + + port. https://github.com/LibreQoE/LibreQoS/blob/splynx-patch-jul28-1.4/src/integrationSplynx.py

owaismai commented 3 months ago

Thanks. I tested and I'm getting this:


:/opt/libreqos/src$ sudo python3 test3.py
Running Python Version 3.10.12 (main, Mar 22 2024, 16:50:05) [GCC 11.4.0]
Fetching data from Spylnx
Traceback (most recent call last):
  File "/opt/libreqos/src/test3.py", line 259, in <module>
    importFromSplynx()
  File "/opt/libreqos/src/test3.py", line 256, in importFromSplynx
    createShaper()
  File "/opt/libreqos/src/test3.py", line 134, in createShaper
    headers = buildHeaders()
  File "/opt/libreqos/src/test3.py", line 16, in buildHeaders
    credentials = splynx_api_key() + ':' + splynx_api_secret()
TypeError: 'str' object is not callable
owaismai commented 3 months ago

Thanks. I tested and I'm getting this:


:/opt/libreqos/src$ sudo python3 test3.py
Running Python Version 3.10.12 (main, Mar 22 2024, 16:50:05) [GCC 11.4.0]
Fetching data from Spylnx
Traceback (most recent call last):
  File "/opt/libreqos/src/test3.py", line 259, in <module>
    importFromSplynx()
  File "/opt/libreqos/src/test3.py", line 256, in importFromSplynx
    createShaper()
  File "/opt/libreqos/src/test3.py", line 134, in createShaper
    headers = buildHeaders()
  File "/opt/libreqos/src/test3.py", line 16, in buildHeaders
    credentials = splynx_api_key() + ':' + splynx_api_secret()
TypeError: 'str' object is not callable
owaismai commented 3 months ago

OK, I seemed to have been able to make some changed to the code and its working, here is an updated version that works for me:


from pythonCheck import checkPythonVersion
checkPythonVersion()

import requests
import warnings
from ispConfig import excludeSites, findIPv6usingMikrotik, bandwidthOverheadFactor, exceptionCPEs, splynx_api_key, splynx_api_secret, splynx_api_url
from integrationCommon import isIpv4Permitted
import base64
from requests.auth import HTTPBasicAuth

if findIPv6usingMikrotik:
    from mikrotikFindIPv6 import pullMikrotikIPv6

from integrationCommon import NetworkGraph, NetworkNode, NodeType
import os
import csv

def buildHeaders():
    """
    Build authorization headers for Splynx API requests using API key and secret.
    """
    credentials = splynx_api_key + ':' + splynx_api_secret
    credentials = base64.b64encode(credentials.encode()).decode()
    return {'Authorization': "Basic %s" % credentials}

def spylnxRequest(target, headers):
    """
    Send a GET request to the Splynx API and return the JSON response.
    """
    url = splynx_api_url + "/api/2.0/" + target
    r = requests.get(url, headers=headers, timeout=120)
    return r.json()

def getTariffs(headers):
    """
    Retrieve tariff data from Splynx API and calculate download/upload speeds for each tariff.
    """
    data = spylnxRequest("admin/tariffs/internet", headers)
    downloadForTariffID = {}
    uploadForTariffID = {}
    for tariff in data:
        tariffID = tariff['id']
        speed_download = round((int(tariff['speed_download']) / 1000))
        speed_upload = round((int(tariff['speed_upload']) / 1000))
        downloadForTariffID[tariffID] = speed_download
        uploadForTariffID[tariffID] = speed_upload
    return (data, downloadForTariffID, uploadForTariffID)

def buildSiteBandwidths():
    """
    Build a dictionary of site bandwidths by reading data from a CSV file.
    """
    siteBandwidth = {}
    if os.path.isfile("integrationSplynxBandwidths.csv"):
        with open('integrationSplynxBandwidths.csv') as csv_file:
            csv_reader = csv.reader(csv_file, delimiter=',')
            next(csv_reader)
            for row in csv_reader:
                name, download, upload = row
                download = int(float(download))
                upload = int(float(upload))
                siteBandwidth[name] = {"download": download, "upload": upload}
    return siteBandwidth

def getCustomers(headers):
    """
    Retrieve all customer data from Splynx API.
    """
    return spylnxRequest("admin/customers/customer", headers)

def getCustomersOnline(headers):
    """
    Retrieve data of currently online customers from Splynx API.
    """
    return spylnxRequest("admin/customers/customers-online", headers)

def getRouters(headers):
    """
    Retrieve router data from Splynx API and build dictionaries for router IPs and names.
    """
    data = spylnxRequest("admin/networking/routers", headers)
    routerIdList = []
    ipForRouter = {}
    nameForRouterID = {}
    for router in data:
        routerID = router['id']
        if router['id'] not in routerIdList:
            routerIdList.append(router['id'])
        ipForRouter[routerID] = router['ip']
        nameForRouterID[routerID] = router['title']
    print("Router IPs found: " + str(len(ipForRouter)))
    return (ipForRouter, nameForRouterID, routerIdList)

def getSectors(headers):
    """
    Retrieve sector data from Splynx API and build a dictionary mapping routers to their sectors.
    """
    data = spylnxRequest("admin/networking/routers-sectors", headers)
    sectorForRouter = {}
    for sector in data:
        routerID = sector['router_id']
        if routerID not in sectorForRouter:
            newList = []
            newList.append(sector)
            sectorForRouter[routerID] = newList
        else:
            newList = sectorForRouter[routerID]
            newList.append(sector)
            sectorForRouter[routerID] = newList

    print("Router Sectors found: " + str(len(sectorForRouter)))
    return sectorForRouter

def combineAddress(json):
    """
    Combine address fields into a single string. If address fields are empty, use ID and name.
    """
    if json["street_1"] == "" and json["city"] == "" and json["zip_code"] == "":
        return str(json["id"]) + "/" + json["name"]
    else:
        return json["street_1"] + " " + json["city"] + " " + json["zip_code"]

def getAllServices(headers):
    """
    Retrieve all active internet services from Splynx API.
    """
    return spylnxRequest("admin/customers/customer/0/internet-services?main_attributes%5Bstatus%5D=active", headers)

def getAllIPs(headers):
    """
    Retrieve all used IPv4 and IPv6 addresses from Splynx API and map them to customer IDs.
    """
    ipv4ByCustomerID = {}
    ipv6ByCustomerID = {}
    allIPv4 = spylnxRequest("admin/networking/ipv4-ip?main_attributes%5Bis_used%5D=1", headers)
    allIPv6 = spylnxRequest("admin/networking/ipv6-ip", headers)
    for ipv4 in allIPv4:
        if ipv4['customer_id'] not in ipv4ByCustomerID:
            ipv4ByCustomerID[ipv4['customer_id']] = []
        temp = ipv4ByCustomerID[ipv4['customer_id']]
        temp.append(ipv4['ip'])
        ipv4ByCustomerID[ipv4['customer_id']] = temp
    for ipv6 in allIPv6:
        if ipv6['is_used'] == 1:
            if ipv6['customer_id'] not in ipv6ByCustomerID:
                ipv6ByCustomerID[ipv6['customer_id']] = []
            temp = ipv6ByCustomerID[ipv6['customer_id']]
            temp.append(ipv6['ip'])
            ipv6ByCustomerID[ipv6['customer_id']] = temp
    return (ipv4ByCustomerID, ipv6ByCustomerID)

def createShaper():
    """
    Main function to fetch data from Splynx, build the network graph, and shape devices.
    """
    net = NetworkGraph()

    print("Fetching data from Spylnx")
    headers = buildHeaders()
    tariff, downloadForTariffID, uploadForTariffID = getTariffs(headers)
    customers = getCustomers(headers)
    customersOnline = getCustomersOnline(headers)
    ipForRouter, nameForRouterID, routerIdList = getRouters(headers)
    sectorForRouter = getSectors(headers)
    allServices = getAllServices(headers)
    ipv4ByCustomerID, ipv6ByCustomerID = getAllIPs(headers)
    siteBandwidth = buildSiteBandwidths()

    allParentNodes = []
    custIDtoParentNode = {}
    parentNodeIDCounter = 30000

    # Create nodes for sites and assign bandwidth
    for customer in customersOnline:
        download = 1000
        upload = 1000
        nodeName = customer['nas_id'] + "_" + customer['call_to'] + "_" + customer['port']

        if nodeName not in allParentNodes:
            if nodeName in siteBandwidth:
                download = siteBandwidth[nodeName]["download"]
                upload = siteBandwidth[nodeName]["upload"]

            node = NetworkNode(id=parentNodeIDCounter, displayName=nodeName, type=NodeType.site,
                               parentId=None, download=download, upload=upload, address=None)
            net.addRawNode(node)

            pnEntry = {}
            pnEntry['name'] = nodeName
            pnEntry['id'] = parentNodeIDCounter
            custIDtoParentNode[customer['customer_id']] = pnEntry

            parentNodeIDCounter += 1

    allServicesDict = {}
    for serviceItem in allServices:
        if serviceItem['status'] == 'active':
            if serviceItem["customer_id"] not in allServicesDict:
                allServicesDict[serviceItem["customer_id"]] = []
            temp = allServicesDict[serviceItem["customer_id"]]
            temp.append(serviceItem)
            allServicesDict[serviceItem["customer_id"]] = temp

    # Create nodes for customers and their devices
    for customerJson in customers:
        if customerJson['status'] == 'active':
            if customerJson['id'] in allServicesDict:
                servicesForCustomer = allServicesDict[customerJson['id']]
                for service in servicesForCustomer:
                    combinedId = "c_" + str(customerJson["id"]) + "_s_" + str(service["id"])
                    tariff_id = service['tariff_id']

                    parentID = None
                    if customerJson['id'] in custIDtoParentNode:
                        parentID = custIDtoParentNode[customerJson['id']]['id']

                    customer = NetworkNode(
                        type=NodeType.client,
                        id=combinedId,
                        parentId=parentID,
                        displayName=customerJson["name"],
                        address=combineAddress(customerJson),
                        customerName=customerJson["name"],
                        download=downloadForTariffID[tariff_id],
                        upload=uploadForTariffID[tariff_id]
                    )
                    net.addRawNode(customer)

                    ipv4 = ipv4ByCustomerID.get(customerJson["id"], [])
                    ipv6 = ipv6ByCustomerID.get(customerJson["id"], [])

                    device = NetworkNode(
                        id=combinedId + "_d" + str(service["id"]),
                        displayName=service["id"],
                        type=NodeType.device,
                        parentId=combinedId,
                        mac=service["mac"],
                        ipv4=ipv4,
                        ipv6=ipv6
                    )
                    net.addRawNode(device)

    net.prepareTree()
    net.plotNetworkGraph(False)
    if net.doesNetworkJsonExist():
        print("network.json already exists. Leaving in-place.")
    else:
        net.createNetworkJson()
    net.createShapedDevices()

def importFromSplynx():
    """
    Entry point for the script to initiate the Splynx data import and shaper creation process.
    """
    createShaper()

if __name__ == '__main__':
    importFromSplynx()
rchac commented 3 months ago

@owaismai Thank you! Just to confirm - is it working correctly now for your use case?

By the way - this integration allows you to define parent node (AP) limits using the file integrationSplynxBandwidths.csv. A template example for that file is available here. That way you can over-ride the default AP ceiling of 1000M, specifying whatever is appropriate for each AP.

owaismai commented 3 months ago

@owaismai Thank you! Just to confirm - is it working correctly now for your use case?

By the way - this integration allows you to define parent node (AP) limits using the file integrationSplynxBandwidths.csv. A template example for that file is available here. That way you can over-ride the default AP ceiling of 1000M, specifying whatever is appropriate for each AP.

Yes, its working 100% now. I am testing now in production on a small number of clients. This will really be useful going forward for anyone using Splynx. Thank you for the great work! Thats great, I will be using this bandwidth template and will provide feedback if required.

tylerardissono commented 3 months ago

@owaismai I get an error while trying your script. Any changes in Splynx I need to perform?

Running Python Version 3.10.12 (main, Jul 29 2024, 16:56:48) [GCC 11.4.0] Fetching data from Spylnx Router IPs found: 2 Router Sectors found: 0 Traceback (most recent call last): File "/opt/libreqos/src/integrationSplynx.py", line 259, in importFromSplynx() File "/opt/libreqos/src/integrationSplynx.py", line 256, in importFromSplynx createShaper() File "/opt/libreqos/src/integrationSplynx.py", line 178, in createShaper nodeName = customer['nasid'] + "" + customer['callto'] + "" + customer['port'] TypeError: unsupported operand type(s) for +: 'int' and 'str'

rchac commented 3 months ago

@tylerardissono

Could you try this version? I just made a change that should help. v1.4: https://github.com/LibreQoE/LibreQoS/blob/main/src/integrationSplynx.py v1.5: https://github.com/LibreQoE/LibreQoS/blob/develop/src/integrationSplynx.py

rchac commented 22 hours ago

Believed fixed now in v1.5-Beta3