Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[bug] PiHole (Device sync) not working due to incorrect PIHOLE_CMD SQL query #521

Closed
jsmith432 opened this issue Jan 1, 2024 · 9 comments
Closed

[bug] PiHole (Device sync) not working due to incorrect PIHOLE_CMD SQL query #521

jsmith432 opened this issue Jan 1, 2024 · 9 comments
Labels
bug 🐛 Something isn't working next release/in dev image🚀 This is coming in the next release or was already released if the issue is Closed.

Comments

@jsmith432
Copy link

First time install, I have four scanners: Internet-Check, PiHole (Device sync), DHCP Leases (Device import), and Arp-Scan (Network scan). They are all on the same schedule [*/10 * * * *]. DHCP leases and ARP took a while to produce anything, but they are working. I don't see anything coming from PiHole (Device sync). I enabled debug but cannot see anything abnormal in the log. I also had debug on because I couldn't make SMTP publisher work but I quit that and configured apprise to talk to my SMTP. During that time, no errors from SMTP were in the log, so I wonder where all plugin errors go.

The files are properly mounted, I logged inside the container just to make sure it was there (/etc/pihole/pihole-FTL.db)

Happy to provide more information.
@jokob-sk
Copy link
Owner

jokob-sk commented Jan 1, 2024

Hi there!

Can you please provide the log for the PiHole plugin load from the pialert.log file? The relevant section should contain a log similar to the one below (notice the first line):

17:31:05 [Scheduler] - Scheduler run for PIHOLE: YES
17:31:05 [Plugin utils] ---------------------------------------------
17:31:05 [Plugin utils] display_name: PiHole (Device sync)
17:31:05 [Plugins] CMD: SELECT n.hwaddr AS Object_PrimaryID, {s-quote}null{s-quote} AS Object_SecondaryID, datetime() AS DateTime, na.ip  AS Watched_Value1, n.lastQuery AS Watched_Value2, na.name AS Watched_Value3, n.macVendor AS Watched_Value4, {s-quote}null{s-quote} AS Extra, n.hwaddr AS ForeignKey FROM EXTERNAL_PIHOLE.Network AS n LEFT JOIN EXTERNAL_PIHOLE.Network_Addresses AS na ON na.network_id = n.id WHERE n.hwaddr NOT LIKE {s-quote}ip-%{s-quote} AND n.hwaddr is not {s-quote}00:00:00:00:00:00{s-quote}  AND na.ip is not null
17:31:05 [Plugins] setTyp: subnets
17:31:05 [Plugin utils] Flattening the below array
17:31:05 ['192.168.1.0/24 --interface=eth1']
17:31:05 [Plugin utils] isinstance(arr, list) : False | isinstance(arr, str) : True
17:31:05 [Plugins] Resolved value: 192.168.1.0/24 --interface=eth1
17:31:05 [Plugins] Convert to Base64: True
17:31:05 [Plugins] base64 value: b'MTkyLjE2OC4xLjAvMjQgLS1pbnRlcmZhY2U9ZXRoMQ=='
17:31:05 [Plugins] Timeout: 10
17:31:05 [Plugins] Executing: SELECT n.hwaddr AS Object_PrimaryID, 'null' AS Object_SecondaryID, datetime() AS DateTime, na.ip  AS Watched_Value1, n.lastQuery AS Watched_Value2, na.name AS Watched_Value3, n.macVendor AS Watched_Value4, 'null' AS Extra, n.hwaddr AS ForeignKey FROM EXTERNAL_PIHOLE.Network AS n LEFT JOIN EXTERNAL_PIHOLE.Network_Addresses AS na ON na.network_id = n.id WHERE n.hwaddr NOT LIKE 'ip-%' AND n.hwaddr is not '00:00:00:00:00:00'  AND na.ip is not null
17:31:05 [Plugins] SUCCESS, received 2 entries
17:31:05 [Plugins] sqlParam entries: [(0, 'PIHOLE', '01:01:01:01:01:01', 'null', 'null', '2023-12-25 06:31:05', '172.30.0.1', 0, 'aaaa', 'vvvvvvvvv', 'not-processed', 'null', 'null', '01:01:01:01:01:01'), (0, 'PIHOLE', '02:42:ac:1e:00:02', 'null', 'null', '2023-12-25 06:31:05', '172.30.0.2', 0, 'dddd', 'vvvvv2222', 'not-processed', 'null', 'null', '02:42:ac:1e:00:02')]
17:31:05 [Plugins] Processing        : PIHOLE
17:31:05 [Plugins] Existing objects from Plugins_Objects: 4
17:31:05 [Plugins] Logged events from the plugin run    : 2
17:31:05 [Plugins] pluginEvents      count: 2
17:31:05 [Plugins] pluginObjects     count: 4
17:31:05 [Plugins] events_to_insert  count: 0
17:31:05 [Plugins] history_to_insert count: 4
17:31:05 [Plugins] objects_to_insert count: 0
17:31:05 [Plugins] objects_to_update count: 4
17:31:05 [Plugin utils] In pluginEvents there are 2 events with the status "watched-not-changed" 
17:31:05 [Plugin utils] In pluginObjects there are 2 events with the status "missing-in-last-scan" 
17:31:05 [Plugin utils] In pluginObjects there are 2 events with the status "watched-not-changed" 
17:31:05 [Plugins] Mapping objects to database table: CurrentScan
17:31:05 [Plugins] SQL query for mapping: INSERT into CurrentScan ( "cur_MAC", "cur_IP", "cur_LastQuery", "cur_Name", "cur_Vendor", "cur_ScanMethod") VALUES ( ?, ?, ?, ?, ?, ?)
17:31:05 [Plugins] SQL sqlParams for mapping: [('01:01:01:01:01:01', '172.30.0.1', 0, 'aaaa', 'vvvvvvvvv', 'PIHOLE'), ('02:42:ac:1e:00:02', '172.30.0.2', 0, 'dddd', 'vvvvv2222', 'PIHOLE')]
17:31:05 [API] Update API starting
17:31:06 [API] Updating table_plugins_history.json file in /front/api

If nothing too different shows up, check also the Plugins objects imported by the plugin:

plugin_objects_pihole

@jokob-sk jokob-sk added the Waiting for reply⏳ Waiting for the original poster to respond, or discussion in progress. label Jan 1, 2024
@jokob-sk
Copy link
Owner

jokob-sk commented Jan 1, 2024

One more thing - can you try to use the dev branch to see if issues persist?

version: "3.9"
services:
  pialert_dev:
    ...
    image: "jokobsk/pi.alert_dev:latest"
    ...

@jsmith432
Copy link
Author

jsmith432 commented Jan 1, 2024 •
edited

I see more information on the logs now with the _dev:

pialert_pihole.txt

Screenshot 2023-12-31 202738

@jokob-sk
Copy link
Owner

jokob-sk commented Jan 1, 2024

Can you try to replace the PIHOLE_CMD setting with the following value?

SELECT n.hwaddr AS Object_PrimaryID, {s-quote}null{s-quote} AS Object_SecondaryID, datetime() AS DateTime, na.ip  AS Watched_Value1, n.lastQuery AS Watched_Value2, na.name AS Watched_Value3, n.macVendor AS Watched_Value4, {s-quote}null{s-quote} AS Extra, n.hwaddr AS ForeignKey FROM EXTERNAL_PIHOLE.Network AS n LEFT JOIN EXTERNAL_PIHOLE.Network_Addresses AS na ON na.network_id = n.id WHERE n.hwaddr NOT LIKE {s-quote}ip-%{s-quote} AND n.hwaddr is not {s-quote}00:00:00:00:00:00{s-quote}  AND na.ip is not null

@jsmith432
Copy link
Author

New log file:
pialert_pihole2.txt

@jokob-sk
Copy link
Owner

jokob-sk commented Jan 1, 2024

Thanks!

I can still see the old setting value in the log:

21:10:21 [Plugins] CMD: SELECT n.hwaddr AS Object_PrimaryID, {s-quote}null{s-quote} AS Object_SecondaryID, datetime() AS DateTime, na.ip  AS Watched_Value1, n.lastQuery AS Watched_Value2, na.name AS Watched_Value3, n.macVendor AS Watched_Value4, {s-quote}null{s-quote} AS Extra, n.hwaddr AS ForeignKey FROM EXTERNAL_PIHOLE.Network AS n LEFT JOIN EXTERNAL_PIHOLE.Network_Addresses AS na ON na.network_id = n.id WHERE n.hwaddr NOT LIKE {s-quote}ip-%{s-quote} AND n.hwaddr <> {s-quote}00:00:00:00:00:00{s-quote} AND na.ip <> null;

Can you make sure the setting is set to (notice the NOT null vs <> null usage:

SELECT n.hwaddr AS Object_PrimaryID, {s-quote}null{s-quote} AS Object_SecondaryID, datetime() AS DateTime, na.ip  AS Watched_Value1, n.lastQuery AS Watched_Value2, na.name AS Watched_Value3, n.macVendor AS Watched_Value4, {s-quote}null{s-quote} AS Extra, n.hwaddr AS ForeignKey FROM EXTERNAL_PIHOLE.Network AS n LEFT JOIN EXTERNAL_PIHOLE.Network_Addresses AS na ON na.network_id = n.id WHERE n.hwaddr NOT LIKE {s-quote}ip-%{s-quote} AND n.hwaddr is not {s-quote}00:00:00:00:00:00{s-quote}  AND na.ip is not null

@jsmith432
Copy link
Author

Oh... I thought I double-checked that. Anyway, now it is working! Thank you, and happy new year!

@jokob-sk jokob-sk added bug 🐛 Something isn't working next release/in dev image🚀 This is coming in the next release or was already released if the issue is Closed. labels Jan 1, 2024
@jokob-sk
Copy link
Owner

jokob-sk commented Jan 1, 2024

Thanks for the update, the default value will be fixed in the next release 👍
Happy New Year :)

@jokob-sk jokob-sk mentioned this issue Jan 1, 2024
Closed
@jokob-sk jokob-sk changed the title PiHole (Device sync) not working [bug] PiHole (Device sync) not working due to incorrect PIHOLE_CMD SQL query Jan 3, 2024
@jokob-sk jokob-sk removed the Waiting for reply⏳ Waiting for the original poster to respond, or discussion in progress. label Jan 3, 2024
@jokob-sk
Copy link
Owner

Released -> Closing

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug 🐛 Something isn't working next release/in dev image🚀 This is coming in the next release or was already released if the issue is Closed.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jsmith432 @jokob-sk