Hi,
I want to write a script that monitors block logs or the list of active blocks for a certain reason (open proxy), and warns me if a block expires soon. Warning may be written either to a noticeboard or a mailing list, the main thing is to find them. Rationale: we give 1 year block for proxies, but after expiration they are likely to be still open, and need revision.
Do we have a clever tool for this? How would you begin the task?
Nothing currently exists, but should be fairly easy using a log parser and a local database.
On Tue, Nov 1, 2022 at 5:39 PM Bináris wikiposta@gmail.com wrote:
Hi,
I want to write a script that monitors block logs or the list of active blocks for a certain reason (open proxy), and warns me if a block expires soon. Warning may be written either to a noticeboard or a mailing list, the main thing is to find them. Rationale: we give 1 year block for proxies, but after expiration they are likely to be still open, and need revision.
Do we have a clever tool for this? How would you begin the task?
-- Bináris _______________________________________________ pywikibot mailing list -- pywikibot@lists.wikimedia.org To unsubscribe send an email to pywikibot-leave@lists.wikimedia.org
You may be able to do this with a SQL query against the database.
https://quarry.wmcloud.org/query/68543 https://quarry.wmcloud.org/query/68543
I think this does what you want, with the proviso that once a block expires, it falls out of the ipblocks table, so you'll be able to see blocks that are about to expire, but once they do expire, they're gone from the table so you've missed them. At least I think that's how it works.
Parameterize this to have an appropriate limit for ipb_expiry, and add some logic to search comment_text for "open proxy" or whatever, and I think that's basically what you want.
On Nov 1, 2022, at 5:39 PM, Bináris wikiposta@gmail.com wrote:
Hi,
I want to write a script that monitors block logs or the list of active blocks for a certain reason (open proxy), and warns me if a block expires soon. Warning may be written either to a noticeboard or a mailing list, the main thing is to find them. Rationale: we give 1 year block for proxies, but after expiration they are likely to be still open, and need revision.
Do we have a clever tool for this? How would you begin the task?
-- Bináris _______________________________________________ pywikibot mailing list -- pywikibot@lists.wikimedia.org To unsubscribe send an email to pywikibot-leave@lists.wikimedia.org
Roy, thank you! This is great! My version is https://quarry.wmcloud.org/query/68559 Next step is to try to use it with Pywikibot as Xqt said.
There's a bunch of block reason templates such as {{blocked proxy}}, {{webhostblock}}, etc. They don't always get used, but they're good things to look for before you fall back to just comment_text like '%proxy%'
I don't think you need to check for ipb_expiry != 'infinity'. People who know SQL better than I do would know for sure, but I think the previous clause (ipb_expiry < now() + interval 1 month) would always be false in that case.
On Nov 2, 2022, at 12:58 PM, Bináris wikiposta@gmail.com wrote:
Roy, thank you! This is great! My version is https://quarry.wmcloud.org/query/68559 https://quarry.wmcloud.org/query/68559 Next step is to try to use it with Pywikibot as Xqt said.
pywikibot mailing list -- pywikibot@lists.wikimedia.org To unsubscribe send an email to pywikibot-leave@lists.wikimedia.org
Roy Smith roy@panix.com ezt írta (időpont: 2022. nov. 2., Sze, 18:19):
There's a bunch of block reason templates such as {{blocked proxy}}, {{webhostblock}}, etc. They don't always get used, but they're good things to look for before you fall back to just comment_text like '%proxy%'
This works for huwiki, and we have only one.
I don't think you need to check for ipb_expiry != 'infinity'. People who know SQL better than I do would know for sure, but I think the previous clause (ipb_expiry < now() + interval 1 month) would always be false in that case.
Unfortunately not, I tried without that, and a lot of infinities appeared. So this was a workaround. I think MySQL is very loose with types, and in your original version expiry date was a string constant. Numbers are before letters. I modified it to show always one month, thus my comparison uses a date type, while 'infinity' is still a string, and seems to precede dates.