Reply
Highlighted
New Member
Posts: 10
Registered: ‎12-05-2016
Kudos: 5

Importing RADIUS users from an Excel spreadsheet

[ Edited ]

It is possible to import a properly formatted Excel spreadsheet of RADIUS users with vlan assignment and passwords into the Unifi controller. I couldn't find any instructions online so I thought I would share my method. 

 

**WARNING, THIS INVOLVES MONGODB EDITING AND COULD CAUSE YOU TO RESET AND RESTORE A CONTROLLER**

**ALWAYS START WITH A GOOD BACKUP OF YOUR RUNNING CONFIGURATION**

 

Preliminary Setups:

-Excel spread sheet with specific column headers and user information filled in

     _id      vlan        x_password       name        site_id        tunnel_type          tunnel_medium_type

 

-Download Robo 3T from https://robomongo.org/  HERE

 

-Navigate to Excel to JSON converter HERE

 

-Download Notepad++ HERE

 

First we need to install and configure Robo 3T to connect to your mongodb. Connecting to the DB is simple if using a local PC controller. Address localhost port 27117 nothing else need to be configured. If connecting to a remote Unifi controller host you will need to setup a SSH tunnel to the host device (SSH access required). There are instructions to do this on various systems online just search it. The mongoDB instance is only bound to the host machine on 127.0.0.1 this is why a SSH tunnel is required.

 

-Cloud Key - Enable SSH access. In Robo 3T under connection creation, tab to SSH, Check Use SSH tunnel. Address will be the address of the cloud key device, port should be 22. Username and password will be that same that you use for any SSH access. Private keys can also be used if you have them set up. You can test the connection in the Robo 3T interface. 

 

After successful connection to the MongoDB we need to convert and prepare the Excel spreadsheet into a usable JSON format. This requires using the JSON to Excel converter. Keep in mind the fields in the columns in Excel need to conform to FreeRADIUS format. Example no  '   (This one got me at first). Also using Robo 3T connect to you mongoDB and open >Ace >Collections double click "Account". You should have one configured (if not configure at least one) RADIUS user in Unifi so that it will populate here and you can see the site_id. Copy the site_id into the appropriate column in your Excel spreadsheet for all users.

 

vlanx_passwordnamesite_idtunnel_typetunnel_medium_type
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX136
YYYYYYYYYYYYYYYYYYYYYXXXXXXXXXXXXXXXX136

 

Save your spreadsheet and load it into the converter. It should convert it and display the output. Select all and paste into Notepad++ for further preparation. 

 

In notepad++ you should now have a JSON file. I have tabs set as 4 spaces in Notepad++ (I don’t know it this will matter). It is not ready to load into mongo you would just get one user will a lot of variables. First we need to delete the [ at the top and ] at the bottom. Then select all and shift+tab to move the entries back one indent. The first line should be {   . I used the built in find replace feature (ctrl+H) in Notepad++ to finish prepping the data. The tunnel type and medium type need to have the "" removed from "13" and "6". Also find }, and replace with }\r with " Extended (\n, \t, \t, \0, \x...) "selected in the find replace window. This entry should separate all the users into multiple JSON groups. This should be it. Now select all and copy.

 

In Robo 3T >Ace >Collections "Account" right click in account and Insert Document. The dialog that opens is a text editor. Remove whatever is already in there and past your formatted JSON entries. Save. Without any errors this will update your Unifi controller with all the users. If there is an error something isn’t formatted correctly. The JSON entries need to be exact. No extra spaces and returns where they shouldn’t be.

 

These are 2 completely formatted entry’s ready for Robo 3T. 

 

{

    "vlan": "X",

    "x_password": "XXXXXXX",

    "name": "XXXXXXXXXX",

    "site_id": "XXXXXXXXXXXXXXXX",

    "tunnel_type": 13,

    "tunnel_medium_type": 6

}

                                                                <------------------ Note the entry spacing

{

    "vlan": "Y",

    "x_password": "YYYYYYYY",

    "name": "YYYYYYYYYYYY",

    "site_id": "XXXXXXXXXXXXXXXX",

    "tunnel_type": 13,

    "tunnel_medium_type": 6

}

 

I hope this worked for you. It took me a few hours to figure out, but saves so much time for big sites that want RADIUS profiles. Not much online about it. If this helped you, consider donating.

 

Bitcoin (BTC): 16yynqenXyZeiQuRL7D11NTfShYpdjFna9

 

 

SuperUser
Posts: 8,233
Registered: ‎01-10-2012
Kudos: 4756
Solutions: 371

Re: Importing RADIUS users from an Excel spreadsheet

Excellent - I may have to test this out!

 

@UBNT-AlexCaldas - great example of something that would be great to turn into a community generated how-to or something similar. 

When you receive a solution to your question/issue, don't forget to mark your thread as solved and to give kudo's to the people who have helped you out!

Having wifi problems? Take a look here first: https://help.ubnt.com/hc/en-us/articles/221029967-UniFi-Debugging-Intermittent-Connectivity-Issues-on-your-UAP
Ubiquiti Employee
Posts: 753
Registered: ‎12-21-2015
Kudos: 255
Solutions: 13

Re: Importing RADIUS users from an Excel spreadsheet

Agh! I meant to get back to you about this yesterday @EricE. So the problem with putting this article up in the help center is that it's a bit risky, as @curvingedge's warning states. So this would be one of those advanced "do under your own risk" configurations that we don't want to officially support because of the very different levels of expertise our UniFi clients have. I do however see the value of keeping these all together in one place. And I think it would be best if it were in the Community. So what I could do is group them in a forum section dedicated to great finds like this one, we might also have those CLI configs we've talked about in there as well. Would that be useful do you think?

SuperUser
Posts: 8,233
Registered: ‎01-10-2012
Kudos: 4756
Solutions: 371

Re: Importing RADIUS users from an Excel spreadsheet


wrote:

So what I could do is group them in a forum section dedicated to great finds like this one, we might also have those CLI configs we've talked about in there as well. Would that be useful do you think?


Anything that plucks useful content out of threads and helps surface it would be beneficial in my opinion!

When you receive a solution to your question/issue, don't forget to mark your thread as solved and to give kudo's to the people who have helped you out!

Having wifi problems? Take a look here first: https://help.ubnt.com/hc/en-us/articles/221029967-UniFi-Debugging-Intermittent-Connectivity-Issues-on-your-UAP
Ubiquiti Employee
Posts: 753
Registered: ‎12-21-2015
Kudos: 255
Solutions: 13

Re: Importing RADIUS users from an Excel spreadsheet

I´ll PM you @EricE! I'd love to hear in the comments from other users who tested this and it worked! And of course give @curvingedge some kuddos if this made your life better Smiley Very Happy

Reply