SQL : top blocked domain

Help
1

I don't have much experience with SQL but I'm try to output the top 100 blocked domains.

Based on this document I try to do the opposite so I though I just use != 2 and != 3 (with are the allowed domain right?) but yet it's seems that I have allowed domain on the output

sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries WHERE (STATUS != 2 OR STATUS != 3) GROUP BY domain ORDER BY count(domain) DESC LIMIT 100"

2

otherwise I can limit to gravity maybe with my be good enough:

sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries WHERE (STATUS == 1) GROUP BY domain ORDER BY count(domain) DESC LIMIT 100"

3

Maybe like this

sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries WHERE NOT (STATUS == 2 OR STATUS == 3) GROUP BY domain ORDER BY count(domain) DESC LIMIT 100"

1 Like
4

Your WHERE (STATUS != 2 OR STATUS != 3) clause returns TRUE for status value 2 and 3.

Use AND instead of OR, or consider the following statement:

sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries \
WHERE status NOT IN(0,2,3) \
GROUP BY domain ORDER BY count(domain) DESC LIMIT 100;"
2 Likes
5

thank a lot, yes that make sense!

6

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