Community discussions

MikroTik App
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Generate User Manager users from an external data base

Thu Feb 19, 2015 6:45 pm

Due to the request from one of our clients, a hotel, we have developed a system service that takes usernames and passwords from a data base an creates and mantains hotspot users on User Manager.

Download from http://www.infinitel.es/productos/infin ... to_en.html

As an example, the hotel has a SQL Server from the hotel management software that, logically, has a list of rooms and their respective guests. The service executes periodically a sql query over that database and takes the room number as the username for User Manager and the surname of the guest as the password. As soon as a guest checks in, the service updates the User Manager's user for that room number. The hotel's staff does not need to mantain the User Manager's users, neither has to generate vouchers for their guests.

The service can connect to any database that has a JDBC driver. Or use a CSV file as the user/password source with the open source CSVJDBC driver.

It connects to Mikrotik User Manager through the API service.

A configuration example looks like this:
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver  #The JDBC Driver (MS SQL Server in this case)
spring.datasource.url=jdbc:sqlserver://server;databaseName=HOTEL #The DB connection URL
spring.datasource.username=sa  #The DB username
spring.datasource.password=password  #The DB password

data.query=select room as username, surname as password from guests  #The executed query

update.rate=* 0/5 * * * ? #The update rate in CRON format (every 5 minutes here)

hotspot.address=192.168.0.1  # The Mikrotik device IP  
hotspot.user=admin #The mikrotik API user
hotspot.password=password  #The mikrotik API user's password
hotspot.port=8728 # The mikrotik API service port
hotspot.customer=admin # The User Manager's customer
hotspot.profile=default # The profile assigned to the newly created users

hotspot.remove=true  # Option to delete unused users
hotspot.remove.skip= # List of excluded user's profiles to skip delete
The SQL query must return a column called username (the name for each user) and a column called password (the user's password). Optionally it can return also a column named profile with the name of the User Manager profile to assign to the newly created user. In the example above, it queries a table called guests and returns the room number as username, the surname as password, and it does not return a profile for each user so it takes the one from the line "hotspot.profile=...". The username must be unique in the query's result set.

When the update process runs it checks that each username returned from the query does not exist on User Manager. If it doesn't exists it creates the user on the User Manager. If it exists, it checks if the password returned from the query equals the password from the user on the User Manager, if it's the same, it leaves the user untouched, if it's different, it removes the old user and creates a new one with the same username and the new password. If the hotspot.remove is enabled it will remove all the users on the User Manager that does not appear on the database query excluding the users with an assigned profile included on the hotspot.remove.skip parameter.

After all, as the hotel's guest checks in, the welcome card that usually is given with the room key has a note that says that, to access the internet the username is the room number and the password is the guest's surname.

That's how we are using it now, but the query and the source database could be whatever you need and I think it can be used in many different scenarios.

The service is developed in Java, so it needs a Java Runtime installed version 1.7 or higher. It includes a Windows service wrapper to install it as a service on Windows. It can also run on any other OS with Java support (Linux, Mac, whatever...) as it's a self contained executable jar file that can be executed by calling java -jar HotSpotAuto.jar.

README file included:

Usage:

Uncompress to a folder. Place the JDBC driver jar file for your database on the lib subfolder.

Modify application.properties to match your settings.

To test the application it comes with the CSVJDBC driver included, a data sample file placed on the data subfolder and an update rate of 2 minutes configured. Just set up hotspot.* values yo match your Mikrotik settings.

You can find the output log on log\hotspotauto.log, where you can see the created/removed users or any error produced during execution.

To install Windows service:

HotSpotAuto.exe install

To start Windows service:

HotSpotAuto.exe start

To stop Windows service:

HotSpotAuto.exe stop

To uninstall Windows service:

HotSpotAuto.exe uninstall

To run it from command line without installing the Windows service:

java -jar HotSpotAuto-1.0.jar
 
seriousreal
just joined
Posts: 1
Joined: Tue Jun 06, 2017 1:36 am

Re: Generate User Manager users from an external data base

Mon Jun 19, 2017 3:43 am

Hi,

I am interested in getting to know more about the Infinitel Hotspot Auto. How can I download for testing?... I am looking for this same solution for use at a hotel... Please help... Thank you.

Ernest.
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Re: Generate User Manager users from an external data base

Thu Jun 22, 2017 7:20 pm

You can download it from:

http://www.infinitel.es/inicio/hostpot/ ... otspotauto

You can download and test it before purchasing
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Mon Jan 28, 2019 12:43 pm

ok tried to download from the mentioned site but apparently there is no link on the file. Can help?
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Re: Generate User Manager users from an external data base

Mon Jan 28, 2019 12:53 pm

The download link is only available on the spanish version of that page. Sorry about that. Use this direct link instead: https://www.infinitel.es/software/HotSpotAuto.rar
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Mon Jan 28, 2019 7:51 pm

ok got the file, will do some test later today.

just a quick question, after you do the query from external DB and had the result, before you write those lines into the user manager DB, do you do a comparison whether the existing username already exist in the user manager? E.g. in the existing Userman, there is a username: Room1 and password:Jonathan, so when it happened that the result set from the ext DB had a record, say username: Room1, password: David.

So how you handle that, you overwrite it?
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Re: Generate User Manager users from an external data base

Mon Jan 28, 2019 8:23 pm

The service compares all the records obtained from the query and operates in different cases:

  • The user does not exist, so it creates it on the User Manager.
  • The user already exists and it has a different password, so it replaces it with a new entry in User Manager.
  • The user already exists and it has the same password, so it leaves it untouched in User Manager.
  • The user exists in User Manager but does not appear on the returned records from the query, so it deletes it from User Manager.

To resume it, the service tries to keep syncronized the content of the query with the users list in User Manager.

To skip this behaviour for some users you can manually create an user on User Manager and assign it to a "skipped" profile (something like user: receptionist pass:12345). The skipped profile name can be defined on the settings file. Usually we use the default profile for regular users and a profile named "skip" for users we need to keep during the syncronization process.
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 10:30 am

hotspotauto.log
ok, getting tons of error.....do I need a specific version of J2RE to run?
You do not have the required permissions to view the files attached to this post.
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 11:01 am

Have you installed it as a service? If not, which command are you using to launch it?

The problem reports that it can not load de csvjdbc driver.
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 11:26 am

I just double click on the jar file to run it
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 11:35 am

ok I had retry running the program as your instruction, the HotspotAuto.exe way doesnt seems to show anything at all, even the log is not generating at all. I run it with the command line java -jar HotSpotAuto-1.0.jar, still the same.
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 11:42 am

it seems the error started at "Injection of autowired dependencies failed". Alright basically there is nothing much I can do now. I would suggest you to get a freshly installed PC to try run that, if it works, let me know what I should do next.
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 12:10 pm

To run it directly without installing the service you must use:

hotspotauto.exe -run

The executable is a service wrapper so calling it without params will not do anything. If you want to run it directly from java.exe you'll have to declare all dependencies manually on the command line.

If you want to use a database connection you'll have to copy the jdbc driver jar for your dbms on the lib folder and modify the application.properties:

spring.datasource.driverClassName=[your driver]
spring.datasource.url=[your jdbc url]
spring.datasource.username=[your db user]
spring.datasource.password=[your db password]

There's a mysql sample config commented in application.properties:

# Use MySQL example
# spring.datasource.url=jdbc:mysql://localhost/mydb
# spring.datasource.username=user
# spring.datasource.password=password
# spring.datasource.driverClassName=com.mysql.jdbc.Driver
# End Use MySQL example

Also notice that, at the moment, the 6.43.xx version of RouterOS is not compatible. This issue will be fixed shortly but be sure to test the application on a device with 6.42.xx or older.
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 12:42 pm

ok it works now but I observe the following problem:
when it was importing from your file, the import process just kinda hang half way for some error. This happen when there are already some pre-generated users inside the user manager, the only way to resolve it is to remove all users then the import process will resume.

The other observation was there were some error when I terminate the program (with control+c), it would show some error which it seems got to do with the mikrotik API connection state.

I attached the log file here.
For my case I am dealing with a 2 huge block of hotel block that houses about 400 rooms in total, so I cannot afford to have the import process stuck halfway and had to remove all the users, seriously not sure what would happen if that is being done when the hotel guests are login to the hotspot
hotspotauto.log
.
You do not have the required permissions to view the files attached to this post.
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 12:46 pm

To run it directly without installing the service you must use:

hotspotauto.exe -run

The executable is a service wrapper so calling it without params will not do anything. If you want to run it directly from java.exe you'll have to declare all dependencies manually on the command line.

If you want to use a database connection you'll have to copy the jdbc driver jar for your dbms on the lib folder and modify the application.properties:

spring.datasource.driverClassName=[your driver]
spring.datasource.url=[your jdbc url]
spring.datasource.username=[your db user]
spring.datasource.password=[your db password]

There's a mysql sample config commented in application.properties:

# Use MySQL example
# spring.datasource.url=jdbc:mysql://localhost/mydb
# spring.datasource.username=user
# spring.datasource.password=password
# spring.datasource.driverClassName=com.mysql.jdbc.Driver
# End Use MySQL example

Also notice that, at the moment, the 6.43.xx version of RouterOS is not compatible. This issue will be fixed shortly but be sure to test the application on a device with 6.42.xx or older.
Dang it, seems those error because my unit is in 6.43.x firmware, will need to find the stable 6.42.x firmware. ok I am interested in your work, can you PM me for some pricing talk?
 
User avatar
pribasv
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 66
Joined: Thu Mar 10, 2011 12:09 pm
Contact:

Re: Generate User Manager users from an external data base

Thu Jan 31, 2019 1:08 pm

The log reports that you're version is 1.3, which is outdated. I've uploaded version 1.6. Use the same link to download.

Pricing can be found at https://wifi.tienda/15-hotspots. Listed prices are final user pricing, installers can apply for discounts.
 
learnyee
newbie
Posts: 34
Joined: Fri May 20, 2016 8:55 pm

Re: Generate User Manager users from an external data base

Sat Feb 02, 2019 5:54 pm

1 last question, because your website is non-english, I am wondering upon purchasing the program, is the license granted to be used in 1 terminal only or I can use the same license code in multiple terminal/customer?

Who is online

Users browsing this forum: No registered users and 12 guests