When I first wrote about Heimdallr, I saw it as a project which was going to be written in Python using Django. I have been using both PHP and Python since their initial releases, but something made that pairing stand out. First, outside of it using colons with indention to indicate code blocks, I have liked the ecosystem with stronger typing like C/C++, whereas PHP was weaker in its typing. Then there was a nice set of types, particularly for Django's ORM, where a simple addon could add actual network types like those supported by PostgreSQL, which has mostly been my database of choice for over a decade (maybe partly because my first SQL RDBMS exposure was to Ingres, which was available on the tapes with BSD 4.3), and libraries for doing WhoIs lookups. But I have recently been banging into a problem, repeatedly.
As I wrote not long ago, I have centralized logging for many of my logfiles, and part of the Heimdallr "vision" was supposed to be giving me a insight into the logfiles, particularly those dealing with the firewall, which is the ultimate bridge into my network. (Did you see what I did there?? Sorry, but not sorry, I could not quite avoid that!). And as of this commit, I had started to achieve this. Indeed, here is the screen I just captured (the "Edit" button is just a development placeholder, as this is read-only data), which directly shows where my problem comes up...
I ultimately want to be able to add a click action to each record to see details about, well say, the IP addresses involved in making the 12718 requests toward my network in just 13 hours, or to see the history over the past several days (or even hours). The problem is, I cannot use raw queries necessary to produce the output needed for the DataTable using Django, or at least remotely easily. You see, I first hit this problem when I had to produce the data for this table. Django does not provide a means other than raw queries to work with network addresses and to turn them into CIDR blocks, or really even to do joins necessary to produce this data, and that means that the query has to build in its own filtering, ordering, etc. rather than rely on the methods used for most DataTables AJAX backends. Now with this table, I resorted to making a view, created by this script, which gives me the data, and I can just tell Django not to manage it, but to use the pre-existing view. But I cannot do that with the next set of queries, and I cannot easily convert the output from the raw query into a form to do the filtering and sorting... RawQuerySets are crippled compared to QuerySets in this regard. So what to do???
One option is to take on the task of building the raw query myself, with the necessary network operators like '<<', and then include the filtering/ordering. But another option is to convert to using PHP and Laravel, which would face its own set of issues, such as not really supporting native network address and CIDR types, but relying on strings when working with databases. But, I should be able to do a query like this:
$query->select('*')
->from('filterlog') ->where('NETWORK(SET_MASKLEN(source_ip,24))', '<<', $cidrBlock);})->get();and take the steps to turn it into an object.
Stay tuned... and Oh for the days when I had Zend Framework and was up to speed on this, as I used to do queries like this to produce ones like I showed at the start of this article. (Sadly, the code relied on features which were dropped from ZF1, which has been unsupported for roughly 9 years now.)