Commons:Scrape OTRS

SQL

use commonswiki_p;
SELECT concat("M",page_id) AS MID, "P6305",
	concat('"',REPLACE(el_to_path,'/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%',''),'"') AS OTRS,
    count(el_to_path) as num
FROM page
JOIN categorylinks c1 ON page_id=c1.cl_from AND c1.cl_to='Files_with_PermissionTicket_template_but_without_P6305_SDC_statement'
#join templatelinks on page_id=tl_from and tl_namespace = 10  AND tl_title="PermissionTicket"
JOIN templatelinks ON page_id=tl_from AND tl_target_id = 242
JOIN externallinks ON page_id=el_from AND el_to_path LIKE '/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%'

#JOIN revision ON page_id=rev_page and rev_actor in (40,695,36593) AND rev_timestamp>2020000000000 # edited this year by JarektBot
#JOIN categorylinks on page_id=cl_from and cl_to = "P6305_SDC"
#WHERE page_namespace = 6 
#  AND NOT page_id IN (
#    SELECT rev_page
#    FROM revision
#    WHERE  rev_actor in (40,695,36593) AND rev_timestamp>2020000000000
#  )
group by MID
having num=1
SQL

Checking query status...
