I recently started using Pi-Hole and upgraded to 5.0 a few days ago.
The group management feature is great but I have a question regarding that. Maybe I am missing something.
Say I have two groups:
Unclassified (Default Group, I guess)
My Group (User created)
Now, every time I add an ad list or whitelist/blacklist a domain, I have to go and apply that to both these groups since by default it's only applied to the default group. 99% of the time, when I make any of these changes (add an ad list or whitelist/blacklist a domain), I want it to be for all groups. Maybe that's just my use-case and I'm not seeing the bigger picture. Am I missing a setting that would apply the changes to all groups by default and then in the rare case I want to include/exclude a certain group, I can go make that change specifically?
You could add a trigger into your database, automatically adding more/other groups on creation of new clients. Note that, however, this change may be in conflict with future Pi-hole updates so you'll have to make sure to reset your local changes before updating (it will just not work, otherwise, there is no other harm involved here).
The relevant triggers you'd want to look at are:
(located on your system in /etc/.pihole/advanced/Templates/gravity.db.sql)
They automatically add the group 0 (the default group) on every newly added item. You can modify the triggers to add more group links, such as:
CREATE TRIGGER tr_domainlist_add AFTER INSERT ON domainlist
BEGIN
INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 0);
INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 1);
INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 2);
END;
The changes should be absorbed by a pihole -g run (I have not tested this, but I'm fairly confident).
@DL6ER Thank you. That works. Although, it didn't quite work by updating the gravity.db.sql and if I updated the trigger directly in the database a pihole -g run was overwriting the changes. Apparently the changes were required in gravity_copy.sql. Then it worked like a charm. Thought I'd let you know.
Will there be such a setting in the GUI in a net release perhaps? I think that would benefit a lot of users (those less technical). And or add an option to add changes in domains/addlists to all all groups at once perhaps?
(at least for now?) thank you for this workaround of course, with kind regards.
You should open a feature request so this can be discussed and voted on by the community. This typically avoids adding features one thinks is of ultimate need for everyone, however, in the end it turns out only the one user asking for it is also using it.
I'm not saying that this is the case here, but you get the gist...
Is this also possible wit a trigger or something?(the above does work on adding a new domain/client/adlist indeed, great workaround)
BUT when I add a new group, it would be really nice if it had a direct option to directly add all domains to it and/or all clients and/or all adlists.
(or all/everything at once is also exceptable)
For instance, when adding a new group and allow 3 extra enable/disable switches to:
Add new group to all clients
Add new group to all domains
Add new group to all adlist
(then do some sql injection so that the new group gets the actions as chosen on add new)
Now when you add a new group you have to go through all de clients/domains/adlist to enable each one seperately to the new group.
The same could be done on adding a new domain or adlist, allow to choose on 'add new' to directly pick the groups it should apply to (same concept as you gave that sql DB trigger for, but more user friendly, imo)
I did the following with a trigger. Although, only to domainlist and adlist. Not to client list. Same logic cannot be applied to clients list. I also don't want to add all clients to the the new group created by default.
The trigger applies all domains/adlists that are enabled and applied to the existing default group to the new group. So if there exists a domain/adlist that is not applied to the default group, it's NOT applied to the new group that created.
CREATE TRIGGER tr_group_ad AFTER INSERT ON "group"
BEGIN
INSERT INTO domainlist_by_group SELECT domainlist.id AS domainlist_id, new_group.group_id
FROM (
SELECT domainlist.id
FROM domainlist
JOIN domainlist_by_group
ON domainlist_by_group.domainlist_id = domainlist.id
WHERE group_id = 0 and domainlist.enabled = 1
) AS domainlist
LEFT JOIN
(
SELECT "group".id AS group_id
FROM "group"
WHERE "group".id NOT IN
(
SELECT group_id
FROM domainlist_by_group
)
) AS new_group;
INSERT INTO adlist_by_group SELECT adlist.id AS adlist_id, new_group.group_id
FROM (
SELECT adlist.id
FROM adlist
JOIN adlist_by_group
ON adlist_by_group.adlist_id = adlist.id
WHERE group_id = 0 and adlist.enabled = 1
) AS adlist
LEFT JOIN
(
SELECT "group".id AS group_id
FROM "group"
WHERE "group".id NOT IN
(
SELECT group_id
FROM adlist_by_group
)
) AS new_group;
END;
CREATE TRIGGER tr_group_ad AFTER INSERT ON "group"
BEGIN
INSERT INTO domainlist_by_group
SELECT domainlist.id AS domainlist_id, new_group.group_id
FROM (SELECT domainlist.id FROM domainlist JOIN domainlist_by_group ON domainlist_by_group.domainlist_id = domainlist.id WHERE group_id = 0 and domainlist.enabled = 1) AS domainlist
LEFT JOIN (SELECT "group".id AS group_id FROM "group" WHERE "group".id NOT IN (SELECT group_id FROM domainlist_by_group)) AS new_group;
INSERT INTO adlist_by_group
SELECT adlist.id AS adlist_id, new_group.group_id
FROM (SELECT adlist.id FROM adlist JOIN adlist_by_group ON adlist_by_group.adlist_id = adlist.id WHERE group_id = 0 and adlist.enabled = 1) AS adlist
LEFT JOIN (SELECT "group".id AS group_id FROM "group" WHERE "group".id NOT IN (SELECT group_id FROM adlist_by_group)) AS new_group;
END;
@EPiC Do you get some error? It's working for me for about a day, granted I have only tried it a few times.
EDIT: I used your code and checked and it worked so there are no typos
Maybe something in the data that creates a different use-case that I'm not seeing.
