# Long-term statistics without individual query data

**URL:** <https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044>\
**Category:** Help\
**Created:** [February 7, 2018, 8:02pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044 "2018-02-07T20:02:44Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![levinus](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/levinus/32/2761_2.png) [@levinus](https://discourse.pi-hole.net/u/levinus)\
**Post date:** [February 7, 2018, 8:02pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/1 "2018-02-07T20:02:44Z")

</div>

Hey,  
DL6ER posted in the original thread about long-term statistics this (in Feb 2017):

> [@Long-Term statistics for Pi-hole](https://discourse.pi-hole.net/t/long-term-statistics-for-pi-hole/1914/1):
>
> What we will not keep: Individual Query data

Looking at the current implementation this has changed (or was never implemented in the mentioned way). I can query the DB for a specific time and date and it will show me complete individual queries with all information on query type, domain, device, status....  
Because I don't want each and every DNS query in my network to be logged and saved (in cleartext) on my Pi I decided to deactivate long term logging with [this parameter](https://discourse.pi-hole.net/t/how-to-disable-logging/422/19).  
But nevertheless I would like to have long term statistics, just not that specific. Is there a option for this? If not would it be possible to implement this feature?

---

<div class="post-metadata">

**Author:** ![DL6ER](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dl6er/32/281_2.png) [@DL6ER](https://discourse.pi-hole.net/u/DL6ER)\
**Post date:** [February 7, 2018, 9:55pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/2 "2018-02-07T21:55:00Z")

</div>

> [@levinus](#):
>
> (or was never implemented in the mentioned way)

Yes, you are right. The reason why I said that was that I was afraid that this would use way too much space if we do it. However, after actually implementing and testing it, we saw that a standard at home scenario with a few fairly active clients will result in databases with less than 500 MB in file size and we decided that this should always be possible. The benefits of this are clear as you can compute any statistics for any time interval you want and do that probably eben for statistics we might only implement in the future. You can, for example, now also (fairly) easily query what domain is most often requested by a single client or if a suspicious domain you have seen just today has also already been queried in the past and if this query was then blocked or not. You see, there are basically endless opportunities for inspecting your data retrospectively if you keep all logs and since the size of the database seems manageable (systems with many clients should also have larger disks, on average), we decided early on to follow this path.

> [@levinus](#):
>
> But nevertheless I would like to have long term statistics, just not that specific. Is there a option for this? If not would it be possible to implement this feature?

Yes, we could add an additional table that just contains ever growing counters for total and blocked queries. Albeit being a bit boring (they will not be able to filter specific time intervals), it would be a straightforward endeavor. I move this to the feature requests section so people can vote for it.

If I find some time over carnival, I might add this right away as it should not be much to be done.

P.S.: You should re-active your long term database (and even if it is only for the most recent two days) as future versions of `FTL` will load historic data (the most recent 24 hours) from the database in the future. This increases the startup speed of `FTL` significantly as we do not have to analyze the log in so much detail each time but can use the already "digested" and properly analyzed data from the database. With this, the file `pihole.log.1` also becomes obsolete.

---

<div class="post-metadata">

**Author:** ![DL6ER](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dl6er/32/281_2.png) [@DL6ER](https://discourse.pi-hole.net/u/DL6ER)\
**Post date:** [February 8, 2018, 6:26pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/3 "2018-02-08T18:26:16Z")

</div>

> [@DL6ER](#):
>
> Yes, we could add an additional table that just contains ever growing counters for total and blocked queries. Albeit being a bit boring (they will not be able to filter specific time intervals), it would be a straightforward endeavor. I move this to the feature requests section so people can vote for it.
> 
> If I find some time over carnival, I might add this right away as it should not be much to be done.

Done, but it won't make it into the (already internally reviewed and tested) `v3.0` release.

> <https://github.com/pi-hole/FTL/pull/224>
>
> \*\*By submitting this pull request, I confirm the following (please check boxes, …eg \[X\]) \_Failure to fill the template will close your PR\_:\*\*
> 
> \*\*\*Please submit all pull requests against the \`development\` branch. Failure to do so will delay or deny your request\*\*\*
> 
> \- \[X\] I have read and understood the \[contributors guide\](https://github.com/pi-hole/pi-hole/blob/master/CONTRIBUTING.md).
> \- \[X\] I have checked that \[another pull request\](https://github.com/pi-hole/FTL/pulls) for this purpose does not exist.
> \- \[X\] I have considered, and confirmed that this submission will be valuable to others.
> \- \[X\] I accept that this submission may not be used, and the pull request closed at the will of the maintainer.
> \- \[X\] I give this submission freely, and claim no ownership to its content.
> 
> \*\*How familiar are you with the codebase?:\*\* 
> 
> \## 10
> 
> \---
> 
> See title. We create a new table in which we store the number of total and blocked queries since the beginning of this integration. On new systems, the tables are created from the beginning and set the DB version to 2. On existing installations (FTL DB version 1), we add the table and initialize it and set the DB version to 2 to signal that the counters table has been created.
> 
> !\[screenshot at 2018-02-08 19-10-52\](https://user-images.githubusercontent.com/16748619/35990285-d0a11ab0-0d03-11e8-8615-339978a6132f.png)
> 
> We also store the time stamp of when this new table has been created so that we can later respond with something like: \`123,456 queries (56,789 blocked) since Deb 8, 2018, 19:09.\`
> 
> As this is a more fundamental change (although a safe one) merging this is scheduled for after the release of \`FTL v3.0\` and hence does not go towards the release branch.
> 
> \_This template was created based on the work of \[\`udemy-dl\`\](https://github.com/nishad/udemy-dl/blob/master/LICENSE).\_

---

<div class="post-metadata">

**Author:** ![levinus](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/levinus/32/2761_2.png) [@levinus](https://discourse.pi-hole.net/u/levinus)\
**Post date:** [February 9, 2018, 10:25pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/4 "2018-02-09T22:25:56Z")

</div>

Thank you for your work and your help!  
The general all time statistics are good.  
Wouldn't it be possible to compute a lot more detailed statistics (at least the graphs, top sites could be a bit more complicated) by just "compressing" the information in the logs to the things really needed? With: "client A made a A/AAAA request that was blocked/forwarded" basic statistics on 1) query type, 2) clients and 3) percentage of blocked queries would be possible. In a even more minimized version, this could mean: "from 16:50 to 17:00 PiHole was asked for 786 DNS records". In this case, you could filter specific time intervals and have at least some kind of statistics without any sensitive information on the clients.  
I know this is of course a lot more complicated compared to the counter you made. Maybe this would be more kind of a long term milestone (if someone else also wants it...)?

---

<div class="post-metadata">

**Author:** ![DanSchaper](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/danschaper/32/91_2.png) [@DanSchaper](https://discourse.pi-hole.net/u/DanSchaper)\
**Post date:** [February 9, 2018, 10:53pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/5 "2018-02-09T22:53:52Z")

</div>

You can open a new topic in the Feature Requests area, people would then be able to vote for your request and help us judge which features should have a higher priority.

---

<div class="post-metadata">

**Author:** ![DL6ER](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dl6er/32/281_2.png) [@DL6ER](https://discourse.pi-hole.net/u/DL6ER)\
**Post date:** [February 10, 2018, 5:44pm UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/6 "2018-02-10T17:44:07Z")

</div>

> [@levinus](#):
>
> Wouldn’t it be possible to compute a lot more detailed statistics (at least the graphs, top sites could be a bit more complicated) by just “compressing” the information in the logs to the things really needed?

> [@levinus](#):
>
> I know this is of course a lot more complicated compared to the counter you made. Maybe this would be more kind of a long term milestone (if someone else also wants it…)?

We have an (inofficial) long-term road map which already includes the idea of having different "privacy levels". Much of the code is already written but not yet tested, but will appear at some point for public testing.

The basic idea is to give users more fine grained settings for setting with how much detail they are comfortable. The currently defined levels are:

1. Permit everything (no privacy filtering)
2. Obfuscate domains names (replace domains by `hidden`, Top Domains and Top Ads not available)
3. Obfuscate domains _and_ clients (replace domains + clients by `hidden` + `0.0.0.0`, all Top Lists unavailable)
4. Maximum privacy (Top Lists, Query Log, and database aren't updated with any data)

---

<div class="post-metadata">

**Author:** ![levinus](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/levinus/32/2761_2.png) [@levinus](https://discourse.pi-hole.net/u/levinus)\
**Post date:** [February 12, 2018, 9:45am UTC](https://discourse.pi-hole.net/t/long-term-statistics-without-individual-query-data/7044/9 "2018-02-12T09:45:41Z")

</div>

Thank you for the clarification. That sounds good to me.

> [@DanSchaper](#):
>
> You can open a new topic in the Feature Requests area, people would then be able to vote for your request and help us judge which features should have a higher priority.

I will open a new FR about the privacy levels. Thank you.