{"id":1195,"date":"2023-09-18T23:08:39","date_gmt":"2023-09-18T23:08:39","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1195"},"modified":"2023-09-18T23:31:12","modified_gmt":"2023-09-18T23:31:12","slug":"pi-hole-metrics-from-a-postgres-database","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1195","title":{"rendered":"Pi-Hole: Metrics from a Postgres database"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

I love Pi-Hole<\/a>.<\/p>

It is one of my favourite services that I have running on my home network.<\/p>

I definitely take Pi-Hole for granted at home and only really appreciate how effective it is when I travel and connect to other networks.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

While the function of Pi-Hole is brilliant – my Data warehouse Analyst and reporting background craves for more power from the metrics behind Pi-Hole.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

By default Pi-Hole offers a simple dashboard with a few catchy graphs and metrics.\u00a0 This can be hypnotizing to watch when first installing Pi-Hole; watching shock of how many DNS queries are blocked visiting different\u00a0 websites.<\/p>

Behind the scenes is a sqlite database located on the server for more power users to query.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

We need more power!<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

For me – I have a couple of problems with the Pi-Hole dashboard and crave for more functionality and power from what is on offer.<\/p>

Some issues I have:<\/p>

  • On my home network – I have two Pi-Hole servers working in a redundancy.\u00a0 The main Pi-Hole server is sitting on a hypervisor while the backup one is on a Raspberry Pi.\u00a0 The main rational of this is that if we have a power outage that knocks the hypervisor offline while I am not at home; the backup Pi-Hole server will take over and save an angry phone call from the wife on why she can’t access the internet.

    One of my wishes is to see the combined Pi-Hole metrics across both servers in one dashboard.

    <\/li>
  • A couple of items on the network creates a large amount of noise that skews the metrics.\u00a0 For instance the security cameras use to query a NTP server every second until I routed them through a NTP server at home.\u00a0 My work computer when it drops off the VPN will flood particular addresses with traffic.\u00a0

    I would like to exclude these addresses from the metrics; but not exclude them from the P-Hole functionality.

    <\/li>
  • Access to better analytics from the Pi-Hole data.\u00a0
    • How about a heat map of blocked queries on devices?\u00a0<\/li>
    • Or weekly trends with a moving average?\u00a0<\/li>
    • Or ability to drill into a time where there is a high amount of blocked traffic to determine the\u00a0 device and application is behind it? (Answer: My kid playing mobile app games)<\/li><\/ul><\/li><\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t

      Napkin Solution<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t

      This is what I am working on at the moment to extract and load the data from my two Pi-Hole servers into a Postgres database to query from.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t\t\t
      1. A NFS share is set up to share out the pihole-FLT.db to a linux server on both Pi-Hole servers<\/li>
      2. A cron job runs every x<\/em> minutes to run a python script.\u00a0 The python scrip will query the pihole-FTL.db to get the records from the last timestamp it ran to the current timestamp in the Pi-Hole database and land the data in to a “landing” schema on the postgres database<\/li>
      3. A stored procedure will run to transfer and normalise the data into an ods schema<\/li>
      4. A reporting tool will then query off the ods schema.\u00a0 In this case I am using Apache Superset; just because it was freely available and the web nature means I can share dashboards to multiple devices without installing client software.<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
        \n\t\t\t\t\t\t
        \n\t\t\t\t\t
        \n\t\t\t
        \n\t\t\t\t\t\t
        \n\t\t\t\t
        \n\t\t\t\t\t

        Performance of querying sqlite through a NFS share <\/h4>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
        \n\t\t\t\t
        \n\t\t\t\t\t\t\t\t\t

        Initially I was worried about querying the pihole-FTL.db over the NFS share; thinking it will be too slow to be practical.<\/p>

        I was thinking of a few clunky solutions:<\/p>

        1. Copy the entire DB over to the postgres host and load it from there<\/li>
        2. Write a script that runs on the Pi-Hole server to extract the incremental data from the pihole-FLT.db into files and load the files<\/li><\/ol>

          Thankfully from the volume of traffic we produce; it is not a concern.<\/p>

          The initial load of copying all the data out of the Pi-Hole database does take a long time if there is a large amount of history to transfer across.<\/p>

          But once the initial load is done; small incremental bites of data only take a few seconds<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t

          Current state<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t

          I have a pilot running on my home network as a proof of concept.<\/p>

          Fundamentally it works fine and I can gleam some interesting metrics from my Pi-Hole servers.<\/p>

          At the moment the security is not great<\/del> atrocious as I am just using a super username and password throughout the pipeline.<\/p>

          Once I have cleaned up the postgres sql, python code I can share the complete solution.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

          I love Pi-Hole. It is one of my favourite services that I have running on my home network. I definitely take Pi-Hole for granted at home...<\/p>\n","protected":false},"author":1,"featured_media":1201,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[54],"tags":[55,56,57],"class_list":["post-1195","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pihole","tag-pihole","tag-postgres","tag-superset"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1195","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1195"}],"version-history":[{"count":7,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1195\/revisions"}],"predecessor-version":[{"id":1204,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1195\/revisions\/1204"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1201"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}