Selectively Prune IP Table?

[alex2k5]

Anyone have a query that could prune the IP table selectively?

Specifically - keep only IPs for those in Banned Usergroup (usergroup x) AND keep ALL IPs for the last 1 year. Truncate the rest.

Our table is very very large, going back 17 years, but it's not needed for the most part. What is needed is a record of banned folks so that future research can be done if needed (rejoins, alt ids, etc). And then recent year or so for anything that may pop up new.

 

[Mouth]

Not specifically checking for Banned Usergroup, but checking the user is_banned flag ....

delete xf_ip from xf_ip
inner join xf_user on xf_ip.user_id = xf_user.user_id 
where xf_user.is_banned = 0 and log_date < date_sub(now(),interval 1 year);

 

[alex2k5]

Thanks much. Will check to see if this can work. We had about 10 years of vbulletin ported in before this, not sure if the Is_banned got applied to those members in the Banned Usergroup.

 

[Mouth]

not sure if the Is_banned got applied to those members in the Banned Usergroup.

delete xf_ip from xf_ip
inner join xf_user on xf_ip.user_id = xf_user.user_id
where find_in_set('xx', xf_user.secondary_group_ids) = 0 and log_date < date_sub(now(),interval 1 year);

xx on the third line being your banned usergroup id.