How does Pihole generate the stats at the dashboard from its data sources (detailed code desired)

neil62 · June 8, 2020, 11:46am

Please someone, give me a url, or any alternative, where I can find a copy of the queries, which are used by the pihole dashboard. AND no I am not interested in doco links unless they provide all the queries details (query), used by pihole for each individual query for the reporting area within the dashboard. I just would like to see each query by its individual details. Thanks.

Bucking_Horn · June 8, 2020, 12:02pm

http://pi.hole/admin/queries.php

neil62 · June 8, 2020, 12:32pm

Thanks for that, but Sorry I am asking for each query code detail and not the end display via the web gui. Where is each query detail not the result of each query

jfb · June 8, 2020, 12:51pm

In addition to the long term database at /etc/pihole/pihole-FTL.db, query details are in /var/log/pihole.log (for today) and /var/log/pihole.log.1 (for yesterday). Each reply, response, etc. In plain text.

jason_a69 · June 8, 2020, 1:35pm

Let me explain a bit further...

In the query to construct "Top Permitted Domains" you are selecting "queries" records with statuses of 2 or 3.

How about the other "Top" selections on the dashboard, do they involve filtering on the "status" column in the "queries" table?

neil62 · June 10, 2020, 1:08pm

Anyone able to answer the above question?

yubiuser · June 10, 2020, 1:21pm

It's all in the source code. The sql queries against the database are put together piece by piece and use a lot of variables to be flexible and reusable.

github.com/pi-hole/web

api_db.php

b86e4a317

<?php
/*   Pi-hole: A black hole for Internet advertisements
*    (c) 2017 Pi-hole, LLC (https://pi-hole.net)
*    Network-wide ad blocking via your own hardware.
*
*    This file is copyright under the latest version of the EUPL.
*    Please see LICENSE file for your rights under this license */

$api = true;
header('Content-type: application/json');
require("scripts/pi-hole/php/database.php");
require("scripts/pi-hole/php/password.php");
require("scripts/pi-hole/php/auth.php");
check_cors();

// Set maximum execution time to 10 minutes
ini_set("max_execution_time","600");

$data = array();
$clients = array();

This file has been truncated. show original

deHakkelaar · June 10, 2020, 6:01pm

If your looking for the raw data (DNS is binary) thats communicated on the line when doing a DNS query (not including the anwser):

C:\>nslookup -type=a pi-hole.net.
Server:  ph5.dehakkelaar.nl
Address:  10.0.0.4

Non-authoritative answer:
Name:    pi-hole.net
Address:  192.124.249.118

pi@ph5:~ $ sudo tcpdump -lnqtX dst $(hostname -I) and udp port 53
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes
IP 10.0.0.11.59099 > 10.0.0.4.53: UDP, length 39
        0x0000:  4500 0043 0ce7 0000 8011 19b5 0a00 000b  E..C............
        0x0010:  0a00 0004 e6db 0035 002f 8196 0001 0100  .......5./......
        0x0020:  0001 0000 0000 0000 0134 0130 0130 0231  .........4.0.0.1
        0x0030:  3007 696e 2d61 6464 7204 6172 7061 0000  0.in-addr.arpa..
        0x0040:  0c00 01                                  ...
IP 10.0.0.11.59100 > 10.0.0.4.53: UDP, length 29
        0x0000:  4500 0039 0ce9 0000 8011 19bd 0a00 000b  E..9............
        0x0010:  0a00 0004 e6dc 0035 0025 532c 0002 0100  .......5.%S,....
        0x0020:  0001 0000 0000 0000 0770 692d 686f 6c65  .........pi-hole
        0x0030:  036e 6574 0000 0100 01                   .net.....

If thats not it, tell us in more details what specifically your looking for ?

yubiuser · June 10, 2020, 6:16pm

I guess it's something like...

if (isset($_GET['topDomains']) && $auth)
{
	$limit = "";

	if(isset($_GET["from"]) && isset($_GET["until"]))
	{
		$limit = " AND timestamp >= :from AND timestamp <= :until";
	}
	elseif(isset($_GET["from"]) && !isset($_GET["until"]))
	{
		$limit = " AND timestamp >= :from";
	}
	elseif(!isset($_GET["from"]) && isset($_GET["until"]))
	{
		$limit = " AND timestamp <= :until";
	}
	$stmt = $db->prepare('SELECT domain,count(domain) FROM queries WHERE (STATUS == 2 OR STATUS == 3)'.$limit.' GROUP by domain order by count(domain) desc limit 20');
	$stmt->bindValue(":from", intval($_GET['from']), SQLITE3_INTEGER);
	$stmt->bindValue(":until", intval($_GET['until']), SQLITE3_INTEGER);
	$results = $stmt->execute()

... to generate a sql query for the top clients table on the dashboard. If you want to repeat it manually you have to replace the variable with the values the dashboard inserts (eg for 24h)

sqlite3 /etc/pihole/pihole-FTL.db 'SELECT domain,count(domain) FROM queries WHERE (STATUS == 2 OR STATUS == 3) AND timestamp >=strftime("%s", "now")-86400 GROUP by domain order by count(domain) desc limit 20'

jason_a69 · June 11, 2020, 7:11pm

I am still having problems with the "Top Clients (total)" table. The number of records in the sqlite database does not match what is being displayed on the dashboard. It looks like the queries table is read over a period of a day (without any status filtering), does anyone know how often the data is refreshed on the admin.php page?

Thanks for all your help so far.

deHakkelaar · June 11, 2020, 7:30pm

I believe most on the dashboard (only showing last 24 hours) is derived from the logs.
And the long term data is pulled from the database ... correct me if am wrong ?
About update/refresh period:

jfb · June 11, 2020, 8:11pm

The long term database shows all queries (to the limit of your configuration parameter MAXDBDAYS, default = 365). On startup, FTL reads the most recent 24 hours (the default setting) of history from the database and uses that to populate the dashboard.

The dashboard always shows the most recent 24 hours. I haven't checked the code, but I recall it's updated in 10 minute intervals.

yubiuser · June 12, 2020, 8:33am

Ok, it's more complicated than I thought...

The api_db.php I linked above (and also the code example I showed later) is just for querying "long-term database" and getting the "Top Lists" from there.

For the Dashboard it's different.

I walked through the script as far as I could but didn't succeed.

index.php contains an element with id="client-frequency

github.com/pi-hole/web

index.php

b86e4a317


      
                    <i class="fa fa-sync fa-spin"></i>
                  </div>
                  <!-- /.box-body -->
                </div>
                <!-- /.box -->
              </div>
          </div>
          <div class="row">
              <!-- /.col -->
              <div class="<?php echo $tablelayout; ?>">
                <div class="box" id="client-frequency">
                  <div class="box-header with-border">
                    <h3 class="box-title">Top Clients (total)</h3>
                  </div>
                  <!-- /.box-header -->
                  <div class="box-body">
                      <div class="table-responsive">
                          <table class="table table-bordered">
                            <tbody>
                              <tr>
                              <th>Client</th>

This triggers in index.js the function ''updateTopClientsChart''

github.com/pi-hole/web

scripts/pi-hole/js/index.js

b86e4a317


      
            // Pull in data via AJAX
            updateClientsOverTime();
          }
          
          // Create / load "Top Domains" and "Top Advertisers" only if authorized
          if (document.getElementById("domain-frequency") && document.getElementById("ad-frequency")) {
            updateTopLists();
          }
          
          // Create / load "Top Clients" only if authorized
          if (document.getElementById("client-frequency")) {
            updateTopClientsChart();
          }
          
          $("#queryOverTimeChart").click(function(evt) {
            var activePoints = timeLineChart.getElementAtEvent(evt);
            if (activePoints.length > 0) {
              //get the internal index of slice in pie chart
              var clickedElementindex = activePoints[0]._index;
          
              //get specific label by index

which sends a request to the API with get api.php?summaryRaw&getQuerySources&topClientsBlocked. Interesting part is the getQuerySource

github.com/pi-hole/web

scripts/pi-hole/js/index.js

b86e4a317


      
              '"': "&quot;",
              "'": "&#039;"
            };
          
            return text.replace(/[&<>"']/g, function(m) {
              return map[m];
            });
          }
          
          function updateTopClientsChart() {
            $.getJSON("api.php?summaryRaw&getQuerySources&topClientsBlocked", function(data) {
              if ("FTLnotrunning" in data) {
                return;
              }
          
              // Clear tables before filling them with data
              $("#client-frequency td")
                .parent()
                .remove();
              var clienttable = $("#client-frequency").find("tbody:last");
              var client, percentage, clientname, clientip, idx, url;

api.php doesn't know getQuerySource but references api_FTL.php

github.com/pi-hole/web

api.php

b86e4a317


      
          		require("scripts/pi-hole/php/sub.php");
          	}
          	else
          	{
          		require("scripts/pi-hole/php/get.php");
          	}
          
          	return;
          }
          
          // Other API functions
          require("api_FTL.php");
          
          if(isset($_GET["jsonForceObject"]))
          {
          	echo json_encode($data, JSON_FORCE_OBJECT);
          }
          else
          {
          	echo json_encode($data);
          }

In api_FTL.php PHP sends a "top-clients" request to FTL

github.com/pi-hole/web

api_FTL.php

b86e4a317


      
          		{
          			$number = $_GET['getQuerySources'];
          		}
          
          		if(is_numeric($number))
          		{
          			sendRequestFTL("top-clients (".$number.")");
          		}
          		else
          		{
          			sendRequestFTL("top-clients");
          		}
          
          		$return = getResponseFTL();
          		$top_clients = array();
          		foreach($return as $line)
          		{
          			$tmp = explode(" ",$line);
          			$clientip = utf8_encode($tmp[2]);
          			if(count($tmp) > 3 && strlen($tmp[3]) > 0)
          			{

sendRequestFTL itself is defined in FTL.php

https://github.com/pi-hole/AdminLTE/blob/b86e4a3178e46cef9fc738c6a0fb8d0ef0ff2fbc/scripts/pi-hole/php/FTL.php#L24

FTL src/api/request.c calls a function getTopClients trigged by top-clients request

https://github.com/pi-hole/FTL/blob/3d7c095cb10a1b692c80dd28f6051dea91d45baa/src/api/request.c#L54

getTopClients is defined in /src/api/api.c

https://github.com/pi-hole/FTL/blob/3d7c095cb10a1b692c80dd28f6051dea91d45baa/src/api/api.c#L349

And here it got complicated and I stopped (for now). Feel free to continue and let us know

It's 10 minutes for some graphts
https://github.com/pi-hole/AdminLTE/blob/b86e4a3178e46cef9fc738c6a0fb8d0ef0ff2fbc/scripts/pi-hole/js/index.js#L262

but the tables are updated every 10 sec

https://github.com/pi-hole/AdminLTE/blob/b86e4a3178e46cef9fc738c6a0fb8d0ef0ff2fbc/scripts/pi-hole/js/index.js#L659

system · July 3, 2020, 8:33am

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.