OPNsense Forum

English Forums => Web Proxy Filtering and Caching => Topic started by: anomaly0617 on March 05, 2019, 11:14:53 pm

Title: Logging to a MySQL database on the back-end?
Post by: anomaly0617 on March 05, 2019, 11:14:53 pm

Hi all,

I haven't seen this here yet, but perhaps I missed it. If so, let me know and I'll see myself out. ;-)

I've recently been exploring the idea that access log data from squid could be piped into MySQL (see here (http://manpages.ubuntu.com/manpages/trusty/man8/log_db_daemon.8.html)). My thought is NOT that the OpnSense server would host this MySQL data, but rather that I could push that MySQL data to a MySQL server internally for further analysis. All of this comes from the desire of management-types that do not want to read through data, but rather would like to see the data from a 1000 meter view and then tunnel down into the data they want.

For instance, if last week squid allowed 98.5% of traffic and blocked 1.5% of traffic for employees, that's one "1000 meter" view. Then if management wants to drill down into the 1.5% of traffic to see what is being blocked and who is attempting to access that information, they can.

Ultimately, this comes around to being able to ask squid on opnsense to send the log file to MySQL. And the link above seems to indicate that squid is possibly capable of doing so. The question is, has anyone done it, is it possible with OpnSense on the BSD platform (as the link above is for a debian platform), and if the answer to that question is yes, what would it take to get the functionality incorporated into Squid for OpnSense?

An ancillary question that I could see coming about would be a way to point multiple OpnSense servers to a single MySQL database (again, internally, over the VPN tunnel for instance) and the ability to see the multi-site view. Why is the internet in [location] always so slow? Lets see what their browsing patterns are like....

So, how far-fetched is this idea? In the short term I'm considering deploying proxy servers out to each location,  but in the longer term, I'm looking for a way to manage the data in a way that isn't cumbersome.

Title: Re: Logging to a MySQL database on the back-end?
Post by: fabian on March 06, 2019, 06:56:24 am

that seems to be an extra service which is doing that. but it is far better to use logstash or filebeat to handle that.

Title: Re: Logging to a MySQL database on the back-end?
Post by: anomaly0617 on March 06, 2019, 01:42:16 pm

Thanks for the response, Fabian. I’ll pursue logstash and report back on my findings!