Community discussions

 
User avatar
NathanA
Forum Veteran
Forum Veteran
Topic Author
Posts: 793
Joined: Tue Aug 03, 2004 9:01 am

Pulling data direct from Dude database -- proof-of-concept

Fri Mar 01, 2019 5:08 am

We have slowly begun to rely more and more on The Dude (which some might argue is maybe a mistake?), and one of the things that is still clearly lacking (in a "big E on the eyechart" sense) is programmatic access to the data. The CLI support for Dude access on RouterOS is basically useless, and the RouterOS API basically mirrors the CLI, so without CLI support, you can't really do anything. This omission is all the more baffling since you can almost get or change any information about objects in your Dude instance (aside from mapping) from within Winbox itself, and it is almost unprecedented that Winbox is able to do or access a RouterOS feature that the CLI cannot (even though the reverse is often true when new features are rolled out: CLI is usually addressed before Winbox is). That Winbox can access information about Dude objects suggests that all of the hard work has already been done to integrate Dude into RouterOS at a fundamental level, but nobody has bothered to take the time to put the CLI hooks in place.

This wouldn't be a problem so much if the Dude data was stored in an accessible and well-understood format, but even though it is all being written to a SQLite database file, the actual data is just being stored in that database as a proprietary binary blob! So it's not even taking advantage of the fact that the underlying file format is an open-format database (nor can table indexes really help to speed up queries). Why you would bother to use a SQLite database back-end only to house closed-format blobs makes no sense to me...

Anyway, we had an immediate need to be able to collect lists of devices in our Dude instance based on device type. I took a peek at the object blobs to see what they looked like, did some research, and came to the conclusion that the blobs are basically in the same format that RouterOS stores all of its config data in (and which is also reflected in the Winbox protocol). There isn't a whole lot of information out there about this format, and of course MikroTik isn't going to publish information about it, but after studying this clever Python script that can parse RouterOS .dat files and this Wireshark Winbox protocol dissector, I was pretty confident that I could parse out the information that we needed directly from the SQLite database ourselves.

I've written a very simple PHP script that demonstrates the basic technique. It is able to generate a CSV of the name and IP address of every device in the database that is of a particular device type. You just have to pass it a valid Dude device type ID. I have attached this script as "getdevicelist.txt" (rename to .php). I also threw together a super-basic page that fetches all of the device types defined in your database and gives you a list for you to pick from. This is attached as "index-dude.txt"; you can rename it to "index.php" if you want it to be the index page for whatever directory on your PHP-enabled web server you throw these scripts into.

These scripts are very rudimentary, and I don't necessarily intend for them to be used as-is (though if you find them useful as-is, then great!), just to demonstrate the basic concept. It should be possible to modify & extend the scripts to collect more information than just the object name and IP address...you will merely have to dissect the object record to figure out what field the information you want to get at is stored in.

The scripts should be placed in a directory on a web server that they also share with a backup copy of your dude.db file; they can't read the information out of the database of a live and running Dude instance. This means running "/dude export-db" first on your Dude server, copying the backup file (which is just a plain gzipped tarball) off, and extracting dude.db from it. You will also need the SQLite PDO extension for PHP installed, and you will need SQLite 3.7 or greater; either that or you will need to first twiddle the WAL (write-ahead logging) bits off in the SQLite database file before it can be read by SQLite 3.x older than 3.7.

This, of course, doesn't help with programmatically *adding* or *changing* records in the database; that's a taller order than just reading what is there, so that will likely have to wait until MikroTik finally finishes CLI (& API) access for Dude. And if they ever do finish CLI support, we will be one of the first in line to adopt it. Until that time, though, this should be able to get us by.

-- Nathan
You do not have the required permissions to view the files attached to this post.
 
cdemers
Member Candidate
Member Candidate
Posts: 184
Joined: Sun Feb 26, 2006 3:32 pm
Location: Canada
Contact:

Re: Pulling data direct from Dude database -- proof-of-concept

Sat Mar 02, 2019 3:02 pm

This looks interesting, i will have to check it out. :)


Sent from my SM-A520W using Tapatalk

 
Ulypka
Frequent Visitor
Frequent Visitor
Posts: 52
Joined: Wed Jan 09, 2013 8:26 am

Re: Pulling data direct from Dude database -- proof-of-concept

Fri Apr 12, 2019 3:28 pm

i write simple script for extract data from dude
exportDude.txt
You do not have the required permissions to view the files attached to this post.
 
picom
just joined
Posts: 7
Joined: Mon Nov 15, 2010 11:47 am

Re: Pulling data direct from Dude database -- proof-of-concept

Tue Apr 30, 2019 3:42 pm

Did you manage to connect the device name, IP address and map name? I'm trying to get the name of the map, but no result...

Who is online

Users browsing this forum: No registered users and 5 guests