Scrape OTRS from files in Files_with_PermissionOTRS_template_but_without_P6305_SDC_statement

Fork of Scrape bad OTRS from "OTRS accreditation" templates by Jarekt

This query is marked as a draft This query has been published by Jarekt.

Scrape OTRS without relying on "Category:Files_with_PermissionOTRS_template_but_without_P6305_SDC_statement"

SQL

use commonswiki_p;
SELECT concat("M",page_id) AS MID, 
	concat('"',REPLACE(el_to,'https://ticket.wikimedia.org/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=',''),'"') AS OTRS
FROM page
JOIN externallinks ON page_id=el_from AND el_to LIKE 'https://ticket.wikimedia.org/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%'
WHERE page_namespace = 6 
  AND NOT page_id IN (
    SELECT c2.cl_from 
    FROM categorylinks c2
    WHERE c2.cl_to = "P6305_SDC"
  )

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...