Wikipédia:Projetos/AntiVandalismo/Hackathon/Queries

Para avaliar os filtros

Páginas que tiveram o filtro 113 disparado sem disparar outro filtro que avise ou desautorize

select afl_filter, afl_title from (select afl_var_dump, afl_filter, afl_title from abuse_filter_log where afl_timestamp>20131205000000 and afl_filter = 113 group by afl_var_dump, afl_filter, afl_title ) a where a.afl_var_dump not in (select afl_var_dump from abuse_filter_log where afl_actions in ('warn','disallow') and afl_timestamp>20131205000000);

Filtros que estão sem avaliação de falso positivo há um mês

select REPLACE(page_title,'Filtro_de_edições/Análise/Filtro_',) AS FILTRO from page inner join revision on page_latest = rev_id inner join abuse_filter ON REPLACE(page_title,'Filtro_de_edições/Análise/Filtro_',) = af_id where page_namespace = 4 and page_title like 'Filtro_de_edições/Análise/Filtro%' and af_enabled = 1 and rev_timestamp < (utc_timestamp() - 100000000) order by 1 asc;

Filtros despoletados por admins

select afl_filter, afl_user, afl_user_text, sum(temp) as disparos from (select afl_filter, afl_user, afl_user_text, count(*) as temp from abuse_filter_log where afl_user != 0 and afl_filter not in (75,56,86,119,104,82) and afl_timestamp > 20130700000000 group by afl_filter, afl_user ) a where afl_user in (select ug_user from user_groups where ug_group in ('sysop')) group by afl_filter, afl_user, afl_user_text order by disparos, afl_user_text;

Usuários admins despoletando filtros (exceto 75, 56, 86, 119, 104, 82)

select afl_user_text, afl_user, count(*) as tentativas from (select afl_var_dump, afl_user_text, afl_user, afl_timestamp from abuse_filter_log where afl_user != 0 and afl_filter not in (75,56,86,119,104,82) and afl_timestamp > 20130700000000 group by afl_var_dump, afl_user_text, afl_user, afl_timestamp ) a where afl_user in (select ug_user from user_groups where ug_group in ('sysop')) group by afl_user_text,afl_user order by tentativas;

Usuários admins, reversores e autorevisores despoletando filtros

select afl_user_text, afl_user, count(*) as tentativas from (select afl_var_dump, afl_user_text, afl_user, afl_timestamp from abuse_filter_log where afl_user != 0 and afl_timestamp > 20130900000000 group by afl_var_dump, afl_user_text, afl_user, afl_timestamp ) a where afl_user in (select ug_user from user_groups where ug_group in ('autoreviewer', 'rollbacker','sysop')) group by afl_user_text,afl_user order by tentativas;

Bots despoletando filtros

select afl_user_text, afl_user, count(*) as tentativas from (select afl_var_dump, afl_user_text, afl_user, afl_timestamp from abuse_filter_log where afl_user != 0 and afl_timestamp > 20130700000000 group by afl_var_dump, afl_user_text, afl_user, afl_timestamp ) a where afl_user in (select ug_user from user_groups where ug_group = 'bot') group by afl_user_text,afl_user order by tentativas;

Para avaliar potenciais bloqueios

Exibe usuários com potencial para serem bloqueados (mais de 2 tentativas de edição com aviso ou desautorização em 24 horas)

select * from (select afl_user_text , count(*) as tentativas from (select afl_var_dump, afl_user_text, afl_timestamp from abuse_filter_log where (afl_actions in ('warn','disallow') OR afl_filter = 112 ) group by afl_var_dump, afl_user_text, afl_timestamp) a where afl_timestamp > (utc_timestamp() - 1000000) group by afl_user_text) b where tentativas > 2 order by tentativas asc;

Exibe usuários com potencial para serem bloqueados (mais de 2 tentativas de edição com aviso ou desautorização em 24 horas sem ter sido bloqueado nas últimas 24 horas)

select * from (select afl_user_text , count(*) as tentativas from (select afl_var_dump, afl_user_text, afl_timestamp from abuse_filter_log where (afl_actions in ('warn','disallow') OR afl_filter = 112 ) and afl_user_text not in (select log_title from logging where log_action = 'block' and log_timestamp > (utc_timestamp() - 1000000)) group by afl_var_dump, afl_user_text, afl_timestamp) a where afl_timestamp > (utc_timestamp() - 1000000) group by afl_user_text) b where tentativas > 2 order by tentativas asc;