We use Ubiquiti's CRM for billing in our company.

We needed to access the database to extract a bit of information not included in the API.

I figured out how to do this.

HERE IS A PDF Access UCRM Database.pdf (87.15 kb)

Let me start with:

THIS IS A VERY DANGEROUS THING TO DO.  DO NOT DO THIS UNLESS YOU ARE COMPLETELY COMFORTABLE WITH DATABASE ACCESS AND POSSIBLY LOSING YOUR DATA!

Now that the warning is out of the way, here we go.

1. Install Apache2: sudo apt install apache2 <-- this will throw an error.  We will fix this in the next 2 steps
2. Change Apache2 port number in: sudo nano /etc/apache2/ports.conf
3. Start Apache2: sudo service apache2 start
4. Install the public key for the pgAdmin4 repository (if not done previously): sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
5. Create the repository configuration file: sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
6. Install pgAdmin4: sudo apt install pgadmin4-web 
7. Configure the webserver: sudo /usr/pgadmin4/bin/setup-web.sh
8. Add IP address restriction to pgAdmin4 directory for an extra layer of security
1. sudo nano /etc/apache2/conf-enabled/pgadmin4.conf
2. Change "Require all granted" to "Require ip your.office.ip.here"
9. Restart Apache2: sudo service apache2 restart
10. Get UCRM postgres DB IP and password:
1. container=`sudo docker ps | grep postgres | awk '{print $1;}'`; sudo docker inspect "$container" | grep '"IPAddress": "172\|UCRM_POSTGRES_PASSWORD'
2. sudo docker ps
3. sudo docker inspect ID xxxxx  <---- WHERE xxxxx is the ID of the docker referencing postgres
4. Look towards top for UCRM DB password
5. Look towards bottom for docker IP
11. Open web browser to: http://ucrm.ip.address.here:customPort/pgadmin4
12. Create new "Server"
1. Give it a name: "UCRM"
2. On the Connection tab:
1. Hostname: The IP from Step 10.3
2. Username: ucrm
3. Password: The password from Step 10.2
4. Save Password: yes.
13. If all goes correctly, you should have direct DB access to the database

14. DO NOT SCREW UP YOUR DATABASE!  I WARNED YOU!

15. Have fun.

I needed to add a VLAN to each interface on a large number of Mikrotik switches.  This is the script I worked up:

/interface bridge add name=Management disabled=no; 
:foreach i in=[/interface ethernet find] do={ 
  :local ifname [/interface get $i default-name]; 
  /interface vlan add interface=$i vlan-id=100 name=($ifname.".0100") disabled=no; 
  /interface bridge port add bridge=Management interface=($ifname.".0100") disabled=no; 
}

We have been switching to the UBNT EP-S16 on our towers instead of running cables down to the router at the bottom.  To segment things, you need to use VLANs.

This code snippet will create 16 VLANs on Port 3 of a Mikrotik with VLAN IDs of 1001-1016.

:for x from=1001 to=1016 do={/interface vlan add interface=ether3 vlan-id="$x" name="ether3.$x"}

I recently had a customer of my WISP ask if we could give her a way to log when her employees are working remotely.  She doesn't have a huge infrastructure with sophisticated logging abilities.  She felt that her employees were claiming to work when they really weren't even logged in.

This is a two part script.  One part runs on the Mikrotik, the other as a PHP script running on their server in the office.

Part 1(a) "create a script called 'vpn-log' on the Mikrotik":

:local urlRoot "http://192.168.99.22:82/?users=";

:foreach i in=[/interface find where type="pptp-in"] do={ 
  :local pptpName [/interface get $i name];
  :local userName [:pick $pptpName ([:find $pptpName "-"]+1) [:find $pptpName ">"]];
  set urlRoot ($urlRoot.$userName.",");
}
/tool fetch url="$urlRoot" keep-result=no

Part 1(b) "create the schedule":

/system scheduler add interval=1m name="Run vpn-log" on-event="/system script run vpn-log" \
    policy=\
    ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api \
    start-time=startup

<?php
date_default_timezone_set('America/Chicago');

$string_data = file_get_contents("current_users_DONT_DELETE.txt");
$currentUsers = unserialize($string_data);

$userArr = explode(",", $_GET['users']);
file_put_contents("current_users_DONT_DELETE.txt", serialize($userArr));

foreach ($currentUsers as $currentUser) {
	if ($currentUser !== "") {
		if (!file_exists($currentUser)) {
			mkdir($currentUser, 0777, true);
		}
		if (!in_array($currentUser, $userArr)) {
			file_put_contents($currentUser."/".date("Y-m-d").".txt", $currentUser . " - Log Out ".date('h:i A')."\r\n", FILE_APPEND);
			echo $currentUser . " - Log Out ".date('h:i A')."<BR>";
		}
	}
}

foreach ($userArr as $user) {
	if ($user !== "") {
		if (!file_exists($user)) {
			mkdir($user, 0777, true);
		}
		if (!in_array($user, $currentUsers)) {
			file_put_contents($user."/".date("Y-m-d").".txt", $user . " - Log In ".date('h:i A')."\r\n", FILE_APPEND);
			echo $user . " - Log In ".date('h:i A')."\n";
		}
	}
}
?>

FYI in /tmp/system.cfg (configuration file):

radio.1.txchainmask=[bit mask]
radio.1.rxchainmask=[bit mask]

Where bit mask means which chain/chains to enable:

1(0bit) - chain0

2(1bit) - chain1

4(2bit) - chain2

So you can make combinations like 1 and 4 = 5, means chain0 and chain2 enabled. And so on.

NOTE: Chains are HW specif, not all have all chains or the same chains.

I guess my sample will help to make live easier and avoid custom scripts to be installed.

Lifted from: http://community.ubnt.com/t5/Installation-Troubleshooting/iwpriv-to-make-2x2-devices-work-like-1x1/m-p/657119/highlight/true#M57052

Ok... Since it looks like UBNT isn't excited about pulling data from AC1 into AC2, I whipped up a python script that copies the GPS coordinates from AC1 to the device so that it can be used in AC2.

WARNING!!!: THIS MAY BREAK YOUR NETWORK.

Don't say I didn't warn you.

That said, I have run it against my network and it just caused breif outages while devices rebooted.

# 
# AC1 -> AC2 Coordinate Pusher 1.0
# Jim Bouse
# 8/13/2013
# jim@mobileitpro.com
#
# This script runs on Python 3.3.2 on Windows.
# I have not tested against any other OS or version
# This script may destroy your network.
# This script may destroy your network.
# This script may destroy your network.
# I really mean it.  If this script blows your network up, 
# I'm sorry but I did warn you.
# 
# The purpose of this script is to query AirControl v1 and populate
# the coordinates from AC1 into the config file in the devices.
# When the coordinates are in the config file, AirControl v2 properly
# places the device after discovery.
# 
# NOTE: <------ READ THIS !!!
# This script reboots the devices after the file change.
# This means you will lose connection and it will be a problem if you # do it during a busy time.
# GOOD NEWS is that it only does it on the devices that need it.
# BAD NEWS is that it will have to be run a few times to get all the
# devices in your org,
# because if it reboots an AP, the clients will not be able to be
# connected to until the next pass.
#
#
# Instructions: Place this script and plink into the same directory.
# Open a command prompt and CD to the directory.
# Run this file.
#

AC1-to-AC2-Coordinate-Pusher.py.txt (3.64 kb)

From time to time, I install UniFi access points for customers.  These customers normally do not need a controller and just want a nice looking AP.  NVX from the UBNT forums put together the firmware needed to convert the UniFi to a standard UBNT radio.

Here is the original post: http://forum.ubnt.com/showpost.php?p=389380

I have mirrored the files here: AirOS 5.5.2 for UniFi.zip (10.65 mb)

I have a WISP that has more customers that I would like to keep up with.  Most of the time is spent chasing down late payments.

I wrote the following combination of files to handle automating handling of Non-Payment customers.

Requirements:

• Web server on a public IP address.
• Domain Name.
• FreshBooks account.
• Simple Queue with a name EXACTLY matching the Last Name, First Name of the customer from FreshBooks.
• Mikrotik Router with API access enabled.

Instructions:

• In the Readme.txt

Problems?

• E-mail Me: jim@mobileitpro.com

Zip File (v1.0)

From the Readme.txt:

Mikrotik Freshbooks Intergration Script v1.0

Jim Bouse
jim@mobileitpro.com
http://jimbouse.com/post/2012/10/09/FreshBooks-and-Mikrotik-Automatic-Non-Payment-Script.aspx
October 9, 2012

License: None...  Feel free to use, modify, butcher, whatever, this code.  I ask that if you add features, you send me a copy.

Plug: I own and operate an WISP that uses Ubiquiti and Mikrotik equipment.  I also do consulting work and onsite assistance.

======================================================================================

Description:
I needed to automatically block customers with overdue invoices.  This is what I came up with.

======================================================================================

Files:
config.php		<-- all PHP variables set here
create_callback.php	<-- creates callbacks on FreshBooks.com
email.php		<-- queries FreshBooks for customers matching names in Simple Queues
index.php		<-- responds to callback requests from FreshBooks.com 
			    AND is the page the customer is redirected to on non-payment.
Readme.txt		<-- This file.
routeros_api.class.php	<-- API class downloaded from http://wiki.mikrotik.com/wiki/API_PHP_class

======================================================================================

Instructions:
1. Setup a sub-domain for your files to reside on. (http://payments.example.com)
   Place all files from zip there.

2. (OPTIONAL) Enable port 800 on web server.
   This is needed if you don't have a dedicated IP for your payment server.
   Also edit the PaymentServerPort to match in the Mikrotik Script at the bottom.
   
3. Setup the web server to redirect all 404 errors to index.php

4. Verify that the webserver can write to the directory of these files.
   This is used for logging.

5. Modify config.php to match your environment.

6. Add the "Late Payment Fees" add-on in FreshBooks

7. Modify the default behavior of the late payment email to send a notice on day 20
   and another notice on day 30. (FreshBooks ->Settings -> Emails)

8. Use create_callback.php to create the following Invoice "Past Due 2" and "Payment Create"

9. Run The script at the bottom on each of your Mikrotiks


======================================================================================
Currently, the index.php script only responds to:

callback.verify
invoice.pastdue.2
payment.create

You can use the existing code as an example to create more.
Reminder: If you add new features, please send me the code so I can update the files for others.


======================================================================================

Mikrotik Script:

:global PaymentServerIP "XXX.XXX.XXX.XXX"
:global PaymentServerPort "80"

/ip firewall nat add action=dst-nat chain=dstnat comment="Redirect Non-Payment HTTP"\
	port=80 protocol=tcp src-address-list=NonPayment to-addresses=$PaymentServerIP\
	to-ports=$PaymentServerPort
/ip firewall filter add chain=forward comment="Allow Non-Payment HTTPS to FreshBooks"\
	dst-port=443 protocol=tcp src-address-list=NonPayment
/ip firewall filter add chain=forward comment="Allow Non-Payment HTTP to Payment Server"\
	dst-address=$PaymentServerIP port=800 protocol=tcp src-address-list=NonPayment
/ip firewall filter add chain=forward comment="Allow DNS" port=53 protocol=udp
/ip firewall filter add chain=forward comment="Allow DNS" port=53 protocol=tcp
/ip firewall filter add action=drop chain=forward comment="Drop Non-Payment tcp traffic"\
	protocol=tcp src-address-list=NonPayment
/ip firewall filter add action=drop chain=forward comment="Drop Non-Payment udp traffic"\
	protocol=udp src-address-list=NonPayment
:put "Done"

FreshBooks-And-Mikrotik-Automatic-Non-Payment-Scripts-v1.0.zip (26.49 kb)

I have a Mikrotik router as my bandwidth limiter for my ISP.  I use Simple Queues for each IP to assign them to a speed.  I recently decided to be nice and add bursting to the plans.  I decided that the customers would get 2X their purchased speed for 16 seconds (a standard time window).  Since I had about 100 IPs and Simple Queues, I defanately did not want to manually add bursting to my Simple Queues.  I wrote the following script.  It will loop through all your simple queues and add the 2X burst amount.

You can download the .txt here in case the formatting gets screwed up. - setSimpleQueueBurst.txt (1.46 kb)

:local rate

:local inboundrate

:local outboundrate

:local inboundburstrate

:local outboundburstrate

:local inboundburstthreshold

:local outboundburstthreshold

:local ip

/queue simple

:foreach i in=[find] do={

  :set ip [get $i target-addresses]

  :set rate [get $i max-limit]

  :set inboundrate [:pick $rate ([:find $rate "/"]+1) 999 ]

  :if ([:find [:tostr $inboundrate] "k" ] > 0) do={

    :set inboundrate ([:pick $inboundrate 0 [:find [:tostr $inboundrate] "k" ]]*1000)

  }

  :if ([:find [:tostr $inboundrate] "M" ] > 0) do={

    :set inboundrate ([:pick $inboundrate 0 [:find [:tostr $inboundrate] "M" ]]*1000000)

  }

  :set outboundrate [:pick $rate 0 ([:find $rate "/"]+1)]

  :if ([:find [:tostr $outboundrate] "k" ] > 0) do={

    :set outboundrate ([:pick $outboundrate 0 [:find [:tostr $outboundrate] "k" ]]*1000)

  }

  :if ([:find [:tostr $outboundrate] "M" ] > 0) do={

    :set outboundrate ([:pick $outboundrate 0 [:find [:tostr $outboundrate] "M" ]]*1000000)

  }

  :set inboundburstrate ($inboundrate*2)

  :set outboundburstrate ($outboundrate*2)

  :set outboundburstthreshold ($outboundrate*2)

  :set inboundburstthreshold ($inboundrate*2)

  :set inboundrate ($inboundrate/5)

  :set outboundrate ($outboundrate/5)

  set $i limit-at="$outboundrate/$inboundrate" burst-time="16s/16s" burst-threshold="$outboundburstthreshold/$inboundburstthreshold" burst-limit="$outboundburstrate/$inboundburstrate"

  :put "Set $ip simple queue burst."

}

I own a small WISP and need an UPS in a small enclosure at the bottom of my towers to provide temporary power for when the grid drops for a few minutes/hours.

• $40   - GE Breaker Box - Home Depot
• $25   - NES-75-48 - Meanwell 48V 75W DC power supply.
• $90   - TP-SCPOE-2424HP - Tycon Power Systems 24V charge controller and regulated power output.
• $10   - POE-PowerTap x2 - Tycon Power Systems POE Power Tap
• $110 - 12V 12AH Battery x2 - Interstate Batteries 12V 12AH Battery

This is often coupled with a multi-port POE injector from Wifi-Soft and a router for a WISP tower location.

• $23 - WS-POE-8-ENC - 8 Port passive POE injector.
• $79 - RB-2011L - Mikrotik RouterBoard 2011L.
• $10 - Sticky Standoffs

This comes to $387 for an outdoor UPS with a run time of about 8 hours without power for 2 - 8 watt radios and a RouterBoard.

Here is a similar version of what I have described.