Hi everyone, this time I want to share one of my preferred side activities : playing with my Ubiquiti home setup!
As you already know I have my controller running on Azure , but I wanted to understand more on which kind of data is stored inside the controller, in other words where the data that we see in the controller dashboard is stored.
Inspecting the binaries and looking in 2-3 posts on the forums I figured out that this data is sitting in a mongodb database, but I wanted of course to look a bit inside of this database.
What I did is the following, I made a backup of the data of the controller using the web interface of the controller and I downloaded it locally:
At this stage I downloaded the controller software for an installation on my laptop (Macbook) and at controller startup i requested a restore of the backup i just downloaded from the cloud controller.
Once the restore is done, mantain the controller running and you can use a mongodb client like robo 3T and connect to localhost and port 27117 (we connect to the mongod process started by the controller locally).
This is great! But I would like to produce some nice dashboards , with some visualization tool like Tableau or PowerBi or simply Excel but the data in a “Document” format while I need it to be in Table/Records format.
The solution is the Mongo Bi Connector that is a kind of “wrapper” or “translator” between the “Document” world and the tables/records world.
But things are never simple ;-), this connector works only from MongoDB v. 3.0 or higher while the one inside the controller software is 2.6. So first we have to download a separate mongodb server that works with it but more importantly upgrade the database itself to the 3.X format.
First let’s copy the database from the controller folder (check a folder called db) and copy it to another location, write down this location.
I tested and failed various times before understanding how to do it but this is the sequence (using brew to install mongodb on my mac):
install mongodb 3.0 –> open the controller database in the location we copied.
uninstall 3.0 /install 3.2 –> open the controller database in the location we copied.
uninstall 3.2 /install 3.4 –> open the controller database in the location we copied.
This will bring the database to a format that is working with the Bi Connector.
Now in the Bi Connector you can extract the schema of a document collection you like (for example the stat_daily collection of ace_stat database) and after that spawn the wrapper process that can be used by a visualization tool:
In my case I used tableau to create some test dashboards:
Here I see that the CPU of my gateway was a bit high during the first part of the month and then decreased significantly.
I can add other metrics like downloaded data, etc.. to understand better:
In reality in this specific case there is already a super nice visualization already offered by the controller dashboards:
So the real interesting thing here is that you can actually create your own report and also discover new insights looking at the your own network data
So what are you waiting for ? Happy custom reports on your Unifi network and device data!