Fixing Netrack vs Sonicwall Discrepancies

Occasionally Netrack and Sonicwall will get out of sync. Netrack will still be monitoring and notifying students of exceeded bandwidth caps, but the caps won't be enforced by the sonicwall.

To fix this:

• Determine the state of Netrack and Sonicwall. In a browser, open the websites for each service:
  • https://netrack.smus.ca/index.php?next_page=admin/report.php
  • https://sonicwall.smus.ca/main.html
• to find the constrained users in the sonicwall, navigate down this path

Network > Address Objects > input into search box: 'Penalty'

• make a note of which IPs are constrained on Netrack but not Sonicwall, and vice versa.
• SSH to Netrack
• log into the sonicwall analyzer database (the password can be pulled from vars.php, or found in keepass,)

head /var/www/v2/overrides/vars.php
psql -h 172.16.0.150 -U analyzer -W -p 5029 -d syslogs

• plug the list of IPs (surrounded by quotes and separated by commas) that are in Netrack, but not in sonicwall into this query:

SELECT * FROM (SELECT DISTINCT ON (ip) constrained_id, ip FROM constrained WHERE constrained=1 AND ip IN (PUT_IP_LIST_HERE) ORDER BY ip, constrained_id DESC) AS sonicwall ORDER BY constrained_id DESC;

It will return something like this:

syslogs=# SELECT * FROM (SELECT DISTINCT ON (ip) constrained_id, ip FROM constrained WHERE constrained=1 AND ip IN ('172.17.124.80', '172.17.124.53', '172.17.120.121', '172.17.120.116', '172.17.116.230', '172.17.116.191', '172.17.112.168', '172.17.112.150', '2620:00ed:4000:177c:e53e:7fbd:9b8e:bf28', '2620:00ed:4000:1778:71bd:5cf0:68af:6c59', '2620:00ed:4000:1770:8921:1e45:25f0:6017', '2620:00ed:4000:1770:732e:16f4:2b04:46ae', '2620:00ed:4000:1770:5a0c:41cc:b7b7:b779') ORDER BY ip, constrained_id DESC) AS sonicwall ORDER BY constrained_id DESC;
 constrained_id |                   ip
----------------+----------------------------------------
            774 | 172.17.124.53
            773 | 172.17.112.168
            772 | 2620:00ed:4000:1770:5a0c:41cc:b7b7:b779
            771 | 172.17.112.150
            770 | 172.17.116.230
            768 | 172.17.120.121
            767 | 172.17.124.80
            766 | 2620:00ed:4000:1778:71bd:5cf0:68af:6c59
            765 | 2620:00ed:4000:1770:732e:16f4:2b04:46ae
            764 | 172.17.116.191
            763 | 2620:00ed:4000:177c:e53e:7fbd:9b8e:bf28
            762 | 2620:00ed:4000:1770:8921:1e45:25f0:6017
            761 | 172.17.120.116
(13 rows)

• make a list of the constrained_ids, and plug it into this query:

UPDATE constrained SET constrained=0 WHERE constrained_id IN (PUT_ID_LIST_HERE);