Empty query history in Pi-hole admin after migration

cascadian · September 2, 2022, 9:35am

Hi. I recently updated to Raspbian Bullseye on my RPI3, and started over with a fresh Raspbian image. I reinstalled Pi-hole just fine, and Teleporter migrated my settings. But I want my query history retained too. I imaged my SD card before reimaging to Bullseye, and extracted pihole-FTL.db from that backup into the reincarnated Raspberry Pi system. I copied it to /etc/pihole, replacing the empty db that was there, and changed the file ownership to be pihole:pihole. However, in the pi-hole web admin, I can't see my query history. It just isn't there. Is there a step I am missing?

Expected Behavior:

I should be able to see my query history, which is years-long, by going to /admin/queries.php in the Pi-hole admin.

Actual Behavior:

I only see the queries generated by the debugging exercise. My previous query history is not there.

Debug Token:

https://tricorder.pi-hole.net/75cnmtDE/

jfb · September 2, 2022, 2:56pm

Use these steps to replace your existing query database:

sudo service pihole-FTL stop

replace the existing database with your old database

sudo service pihole-FTL start

An oddity noted in your debug log - your DHCP server doesn't appear to be providing a complete response:

*** [ DIAGNOSING ]: Discovering active DHCP servers (takes 10 seconds)
   Scanning all your interfaces for DHCP servers
   Timeout: 10 seconds
   
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   * Received 300 bytes from eth0:192.168.88.1
     Offered IP address: 192.168.88.229
     Server IP address: 192.168.88.1
     Relay-agent IP address: N/A
     BOOTP server: (empty)
     BOOTP file: (empty)
     DHCP options:
      Message type: DHCPOFFER (2)
      server-identifier: 192.168.88.1
      lease-time: 600 ( 10m )
      --- end of options ---
    
   DHCP packets received on interface eth0: 23
   DHCP packets received on interface wlan0: 0
   DHCP packets received on interface lo: 0

cascadian · September 2, 2022, 6:17pm

I actually did run those commands before (start) and after (stop) dropping pihole-FTL.db into the /etc/pihole folder, I should have mentioned that. I just ran them again. I still don't see anything other than the queries generated by the debugging exercise in the web admin.

I am still trying to get a handle on my new Mikrotik router, and am aware of the DHCP weirdness. Could have Pi-hole take over DHCP duties once I get this issue sorted.

Edited to correct the path I used and the order of the commands I ran.

cascadian · September 4, 2022, 5:47am

If there's another file or another set of files I need to migrate, I can do that since I retained all of the contents of my SD card before I reflashed it. Just need to know what else to do.

Bucking_Horn · September 4, 2022, 8:40am

That doesn't sound quite right:
First, note that the path should read /etc/pihole/ (no hyphen!).
And then you'd need to run the command to stop pihole-FTL before replacing the database.
Only once that is done, should you start pihole-FTL again.

Please also share the output of the following commands:

ls -lah /etc/pihole/*.db

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM ftl"

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM counters"

cascadian · September 4, 2022, 9:07am

My apologies -- the path I used is /etc/pihole. The hyphen was only in my post

And I did run the stop command prior to copying in the .db file. Then I ran the start command.

user@PiHole:/etc/pihole $ pwd
/etc/pihole

user@PiHole:/etc/pihole $ ls -lsa
total 4865788
      4 drwxrwxr-x   3 pihole pihole       4096 Sep  4 02:02 .
     12 drwxr-xr-x 123 root   root        12288 Sep  3 23:57 ..
      4 -rw-r--r--   1 root   root           65 Sep  3 23:56 adlists.list
      4 -rw-r--r--   1 root   root           65 Sep  2 01:44 adlists.list.old
      0 -rw-r--r--   1 root   root            0 Sep  2 01:46 custom.list
      0 -rw-r--r--   1 pihole pihole          0 Sep  2 01:47 dhcp.leases
      4 -rw-r--r--   1 root   root          651 Sep  3 23:57 dns-servers.conf
  11184 -rw-rw-r--   1 pihole pihole   11452416 Sep  3 23:57 gravity.db
   9856 -rw-rw-r--   1 pihole pihole   10092544 Sep  3 23:57 gravity_old.db
      4 -rw-r--r--   1 root   root         1122 Sep  3 23:57 install.log
   3712 -rw-r--r--   1 root   root      3800247 Sep  3 23:57 list.1.raw.githubusercontent.com.domains
      4 -rw-r--r--   1 root   root           95 Sep  3 23:57 list.1.raw.githubusercontent.com.domains.sha1
    404 -rw-r--r--   1 root   root       412338 Sep  3 23:57 list.3.sysctl.org.domains
      4 -rw-r--r--   1 root   root           80 Sep  3 23:57 list.3.sysctl.org.domains.sha1
      4 -rw-r--r--   1 root   root          521 Sep  3 23:57 list.5.s3.amazonaws.com.domains
      4 -rw-r--r--   1 root   root           86 Sep  3 23:57 list.5.s3.amazonaws.com.domains.sha1
     44 -rw-r--r--   1 root   root        43529 Sep  3 23:57 list.6.s3.amazonaws.com.domains
      4 -rw-r--r--   1 root   root           86 Sep  3 23:57 list.6.s3.amazonaws.com.domains.sha1
      4 -rw-r--r--   1 root   root           65 Sep  3 23:57 local.list
      4 -rw-r--r--   1 root   root          241 Sep  2 01:46 logrotate
   2872 -rw-r--r--   1 pihole pihole    2940928 Sep  3 23:57 macvendor.db
      4 drwxr-xr-x   2 root   root         4096 Sep  2 01:47 migration_backup
      4 -rw-rw-r--   1 pihole root          127 Sep  3 23:57 pihole-FTL.conf
4837636 -rw-rw-r--   1 pihole pihole 4953735168 Sep  4 02:02 pihole-FTL.db
      4 -rw-r--r--   1 root   root          347 Sep  3 23:57 setupVars.conf
      4 -rw-r--r--   1 root   root          347 Sep  2 01:47 setupVars.conf.update.bak
      4 -rw-r--r--   1 root   root          212 Sep  4 02:00 versions

ls -lah /etc/pihole/*.db/ returns:

ls: cannot access '/etc/pihole/*.db/': No such file or directory

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM ftl" returns:

0|12
1|1662142396
2|1554012677

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT * FROM counters" returns:

0|101513206
1|19806791

Thank you for your help!

Bucking_Horn · September 4, 2022, 9:33am

Ah, my turn to apologise - there shouldn't have been a / at the end (corrected above).

It may not be necessary to rerun that, as the SQL results look promising:
They would suggest your database covers a period from Sun 31 Mar 2019 through Fri 02 Sep 2022.

Let's see if we can access some queried domains from last year's early September.
Please provide the output of:

pihole-FTL sqlite3 --header --column "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries \
WHERE timestamp BETWEEN strftime('%s','2021-09-01','utc') AND strftime('%s','2021-09-10','utc') \
GROUP BY domain ORDER BY count(domain) DESC LIMIT 10;"

cascadian · September 4, 2022, 10:14am

Sure thing, here you go!

user@PiHole:$ -FTL sqlite3 --header --column "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries \
WHERE timestamp BETWEEN strftime('%s','2021-09-01','utc') AND strftime('%s','2021-09-10','utc') \
GROUP BY domain ORDER BY count(domain) DESC LIMIT 10;"
domain                          count(domain)
------------------------------  -------------
device-metrics-us-2.amazon.com  20356        
eat-efz.ms-acdc.office.com      11738        
firewalla.encipher.io           8672           
nbcume.sc.omtrdc.net            7730         
star-mini.c10r.facebook.com     6802         
outlook.ha.office365.com        5898         
scribe.logs.roku.com            5593         
outlook.office.com              4392         
www.google-analytics.com        4361

Bucking_Horn · September 4, 2022, 10:32am

That confirms that your history data is available as well as accessible.

It makes me confident that using Long-term Data | Top lists from Pi-hole's UI with a custom range of September 1st 2021 to September 10th 2021 should produce similar results.

cascadian · September 4, 2022, 10:55am

And yet, there's nothing there. Puzzling, isn't it?

Bucking_Horn · September 4, 2022, 10:56am

You didn't select a date range yet.

cascadian · September 4, 2022, 11:08am

Oops, wrong screenshot. I took one before I opened the date selector and then another one after. Here's a fresh one. It's been like this for a while now...