[Feauture request] Queryable stats and better reporting

PonchoPowers commented 5 years ago

One of the reasons I switched to using the Technitium DNS Server was the reporting, and I think it is much better than what you get with most other DNS Servers.

I followed your advice and set up two DNS servers, one for the internal network and one for the public network. :)

This works great as I have a 3 year old son, and I like to keep an eye on what websites he is visiting. I do so by looking at the DNS server set up to resolve local DNS queries to see what hostnames are being resolved.

The dashboard also works great in that for the public network, I can see if my DNS server is being flooded, or is under some other sort of attack.

However, I feel the reporting could be improved in that if it was backed by a database, so that no queries have to be logged to text or binary files.

I also think the database would lend itself to a reporting interface which allows you to query what domains were resolved on specific devices, the times the domains were resolved, the ability to search for specific domains or keywords, etc.

Having the data in a database also means as a developer, I can then implement some form of log monitoring with ease, by querying the last 10 minutes of logs and setting up email alerts etc. so the possibilities are endless if the DNS queries are logged to a database.

ShreyasZare commented 5 years ago

Thanks for the feedback. What we have now is logging and basic stats on dashboard. Like you said, for anything more, it will need a database which then can be queried for different kind of views/stats.

I will need to find out suitable cross-platform support for something like sqlite which can then have all stats and query logging covered.

Globulopolis commented 5 years ago

I will need to find out suitable cross-platform support for something like sqlite which can then have all stats and query logging covered.

Please, can you provide just a connector to available database, instead of built-in database like sqlite.

ShreyasZare commented 5 years ago

Please, can you provide just a connector to available database, instead of built-in database like sqlite.

That's a great suggestion. I just need to make sure that it can be done for all platforms without much issues.

DRSDavidSoft commented 4 years ago

+1 for ability to use database-backed logging, especially with the following field:

https://github.com/TechnitiumSoftware/DnsServer/issues/103#issuecomment-630107140

daffyducke commented 3 years ago

Logging DNS Queries to a database and custom reporting is very high on my list.

Do you accept outside development and pull requests? I would be glad to work on adding that feature.

PonchoPowers commented 3 years ago

Please, can you provide just a connector to available database, instead of built-in database like sqlite.

That's a great suggestion. I just need to make sure that it can be done for all platforms without much issues.

You could use EF to achieve this. It supports most databases.

ShreyasZare commented 3 years ago

Logging DNS Queries to a database and custom reporting is very high on my list.

Do you accept outside development and pull requests? I would be glad to work on adding that feature.

Yes sure if you can get a decent PR done then it would be great to have since it will take lot of time for me to start working on this feature.

What I would expect is :

To use only officially supported libraries for databases, no 3rd party libraries.
Keep it as simple as possible. No large frameworks should be used.
Must be cross platform.
It should work on low powered devices like Raspberry Pi.
The feature should be completely optional.
Should support basic sqlite database since this can work standalone for small installations.
A configurable DB connector to allow using databases of user's choice. Best option will be to have a plug-able system where you just create a separate DLL for supporting different databases and load the DLL required as per the settings. So, the DNS core will just load this DLL and use an Interface to insert records and query for reports. The DLLs would then do the actual insert in the database. So this becomes a generic design where you can have mysql.dll, mssql.dll or mongodb.dll where these DLLs will do the database interfacing for inserting and querying the data. So anyone should be able to add support for a database they have that is not yet supported.

I will be adding a similar DLL based support for a new type of zone called Application zone. This feature is to allow anyone to create a DLL that gets added to the Application zone such that the DNS server hands over the requests for the zone to the DLL and then the DLL can be programmed to return a response as per the user's custom requirements.

Do let me know what have you though about implementing this feature.

gitpkmx commented 3 years ago

To know which IP requested which DNS would be extremely helpful

ShreyasZare commented 3 years ago

To know which IP requested which DNS would be extremely helpful

Yes, this feature will be rolled in as a DNS App with support for multiple databases.

ShreyasZare commented 3 years ago

New version 7.0 is now available with a new Query Logs (Sqlite) DNS App that allows logging. A GUI on the DNS web panel is available to query the app for logs. Do update and let me know if there are any changes required. Closing this issue for now.

TechnitiumSoftware / DnsServer

[Feauture request] Queryable stats and better reporting #64