I am writing a phone app that makes a big noise when one of my routers has problem like packet loss, or some CPE’s go offline.
The idea is for the event to be picked up by a scheduled taks then written to a MySql database, then a cloud service reads new record and pushes notification to the phone app.
I have written a basic apk that reads an email that gets sent by the ROS email tool. If certain keywords in the email then the cloud service will sound the alarm. But that is clunky, complicated and slow. Because I am relying on mail servers to sync all the time.
I would prefer to have a script running in ROS to be able to make a connection directly to an external MySql database which is quicker and simpler.
Is this possible? Or do I have to keep sending emails? Or is there another indirect way or writing events to an external database like json, http or rest?
External monitoring tools using things like ICMP and SNMP (plus API access to log into the routers and do any extended testing) would be the best way.
For real-time alerting I suggest nagios or icinga - trending something like cacti or librenms. If you really want events on router to go to an external MySQL DB - create a very basic API endpoint and then you can have RouterOS scripts push the data to the https endpoint.
You’re kinda reinventing the wheel. Lot of existing packages/software already does roughly this.
Kinda. /tool/fetch url=… on RouterOS can work, generally. But not directly to MySQL AKAIK – they don’t have REST API AFAIK, only connectors/clients. You could setup an endpoint on your web server that does the SQL INSERT to do it indirectly as suggested above.
On a Mikrotik, I think /tool/email using a shared mailbox is totally reasonable approach – esp. if already working. The state is managed by the mailbox & it’s already secure since you have to auth to mail server. And even with email, you can still use the body to send structured data. And JSON is possible in v7.13+… so could parse the JSON for needed details when reading email via mbox/POP3/IMAP on your server. There is a new :serialize to=json operation to help with the RouterOS variables/array to JSON. Anyway, you can use the mailbox as the DB if you’re clever. e.g. like using read mark to indicate if “mail” has been added DB/send to device.
But most REST API (either you’re or DB or something else) can use same :serialize to generate JSON typically needed. There is no XML support, so kinda limited on the type of API without a lot of manual coding of strings to build XML.
I suppose another option is you could perhaps use /system/ssh to send command on MySQL server to insert a row. But all the security model gets complex using ssh.