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.

Fixing Netrack vs Sonicwall discrepancies

• 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.53', '172.17.120.121', '172.17.116.230', '172.17.116.175', '172.17.113.23', '172.17.112.168', '172.17.112.150', '2620:00ed:4000:1770:d655:a802:501d:3cee', '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
----------------+-----------------------------------------
            777 | 172.17.113.23
            776 | 2620:00ed:4000:1770:d655:a802:501d:3cee
            775 | 172.17.116.175
            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
            765 | 2620:00ed:4000:1770:732e:16f4:2b04:46ae
(10 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);

• It will return something like this:

syslogs=# UPDATE constrained SET constrained=0 WHERE constrained_id IN (777, 776, 775, 774, 773, 772, 771, 770, 768, 765);
UPDATE 10

• The flags have now been changed in the database, so we need to tell the sonicwall to update it's list of constrained users
• Still on Netrack, switch to root, and then run the daemon manually (this usually runs off a cron job on netrack)

sudo su -
cd /var/www/v2 && php -f daemons/sonicwall.php

• Once it's done, check the sonicwall and see if the penalty box contains the expected IPs, if it doesn't then there is a communication problem that needs to be resolved.

Fixing communication problems between Netrack and Sonicwall

• Attempt to manually run the shell script which communicates to the sonicwall (as root on netrack)

/bin/bash /tmp/sonicwall.sh >> /tmp/sonicwall.log

• If there is a problem, the output will look like this:

root@netrack:~# /bin/bash /tmp/sonicwall.sh >> /tmp/sonicwall.log
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
SHA256:5KnKKgW6gSJ0zKiuA8WoqyQ9ONe2CZSxYM5UjUzajUQ.
Please contact your system administrator.
Add correct host key in /root/.ssh/known_hosts to get rid of this message.
Offending RSA key in /root/.ssh/known_hosts:7
  remove with:
  ssh-keygen -f "/root/.ssh/known_hosts" -R sonicwall.smus.ca
Password authentication is disabled to avoid man-in-the-middle attacks.
Keyboard-interactive authentication is disabled to avoid man-in-the-middle attacks.
Permission denied (password).

• This message says that the key has changed, and provides a command for removing the old key. Run that command (and an additional command for the naked IP)

ssh-keygen -f "/root/.ssh/known_hosts" -R sonicwall.smus.ca
ssh-keygen -f "/root/.ssh/known_hosts" -R 172.16.0.253

• Now re-run the sonicwall daemon, and check the sonicwall penaltybox afterwards

IPs in Sonicwall but not Netrack

You will do the same thing as fixing discrepancies where the IP is in Netrack but not Sonicwall, but using different queries for the SELECT and UPDATE

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

UPDATE constrained SET constrained=1 WHERE constrained_id IN ();

After doing this, you'll need to check the Netrack admin page to see that the changes took place

Files of note

Paths to the files used to execute/log the communication between Netrack and Sonicwall

/var/www/v2/overrides/vars.php
/var/www/v2/pages/admin/report.php
/var/www/v2/daemons/constrainer.php
/var/www/v2/daemons/sonicwall.php
/tmp/sonicwall.log
/tmp/sonicwall.sh
/tmp/sds_email.log
/tmp/sds_email.sh