Run a query to pull the number of Phabricator Tasks reported by community, resolved by WMF
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	ARamirez_WMF
	Fri, Apr 12, 1:33 PM

Description

For the Infrastructure quarterly Learning Session, Product & Tech senior leadership need to speak as to the number of Phabricator Tasks reported by community that were resolved by WMF .

Amanda has been requesting this information for the past FY. See previous reports:

I need the same report for the tasks resolved by WMF for the Q3 (Jan 2024 - March 2024) by end of working day April 17 2024 in order to include the narrative into this quarter's QLS.

If it helps, the query Andre Klapper used to pull this data in the past is included below:

SELECT DISTINCT CONCAT("https://phabricator.wikimedia.org/T", t.id) AS url, t.title, u1.userName AS author, u2.userName AS assignee
    -> FROM phabricator_maniphest.maniphest_task t
    -> INNER JOIN phabricator_user.user u1 ON u1.phid = t.authorPHID
    -> INNER JOIN phabricator_user.user u2 ON u2.phid = t.ownerPHID
    -> WHERE t.status = "resolved"
    -> AND FROM_UNIXTIME(t.closedEpoch,'%Y%m')>=202307 AND FROM_UNIXTIME(t.closedEpoch,'%Y%m')<=202309
    -> AND u1.phid NOT IN (SELECT ua.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_externalaccount ua ON ua.userPHID = u.phid WHERE ua.accountType = "mediawiki" AND ((ua.username LIKE '%(WMF)' OR ua.username LIKE '%-WMF') OR (ua.username LIKE '%(WMDE)' OR ua.username LIKE '%-WMDE')))
    -> AND u1.phid NOT IN (SELECT ue.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_email ue ON ue.userPHID = u.phid WHERE (ue.address LIKE '%@wikimedia.org' OR ue.address LIKE '%@wikimedia.de' OR ue.address LIKE '%@speedandfunction.com'))
    -> AND (u1.userName != "dbarratt" AND u1.userName != "Samwilson" AND u1.userName != "Amire80" AND u1.userName != "Dreamy_Jazz" AND u1.userName != "Ladsgroup" AND u1.userName != "Tchanders" AND u1.userName != "Daimona" AND u1.userName != "Nikerabbit" AND u1.userName != "Mooeypoo" AND u1.userName != "demon")
    -> AND (u2.phid IN (SELECT ua.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_externalaccount ua ON ua.userPHID = u.phid WHERE ua.accountType = "mediawiki" AND ((ua.username LIKE '%(WMF)' OR ua.username LIKE '%-WMF') OR (ua.username LIKE '%(WMDE)' OR ua.username LIKE '%-WMDE')))
    -> OR u2.phid IN (SELECT ue.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_email ue ON ue.userPHID = u.phid WHERE (ue.address LIKE '%@wikimedia.org' OR ue.address LIKE '%@wikimedia.de' OR ue.address LIKE '%@speedandfunction.com'))
    -> OR (u2.userName = "dbarratt" OR u2.userName = "Samwilson" OR u2.userName = "Amire80" OR u2.userName = "Dreamy_Jazz" OR u2.userName = "Ladsgroup" OR u2.userName = "Tchanders" OR u2.userName = "Daimona" OR u2.userName = "Nikerabbit" OR u2.userName = "Mooeypoo" OR u2.userName = "demon"));

Related Objects

Mentioned In: T362804: Automate quarterly WMF QLS request for Phabricator data (tasks reported by community, resolved by WMF)
Mentioned Here: P60752 T362406

Event Timeline

ARamirez_WMF created this task.Fri, Apr 12, 1:33 PM

Aklapper updated the task description. (Show Details)Fri, Apr 12, 2:52 PM

@ARamirez_WMF Please note that the database query result is incorrect as usual as WMF has no policy to make its staff use/link their staff accounts and/or use their staff email addresses (which would make staff members identifiable as staff members).
Also note that the database query also considers WMDE staff and WMF contractors as staff so the database query attempts to cover "reported by community" (with an unknown number of false positives, see previous sentence) but not exclusively "resolved by WMF" but instead ~"resolved by paid Wikimedia folks").
The database query above, adjusted for >=202401 AND <=202403, lists 464 tasks as its result.

Aklapper closed this task as Resolved.Tue, Apr 16, 4:16 PM

@Aklapper Can I get the google sheet report like the ones you sent amanda the previous times please? I need to be craft a story for the QLS around the types of tasks etc.

I just don't remember what we/I did months ago. :D I'll check my mail and try to remember

The list of hardcoded user names in there seems kind of arbitrary? Is this only supposed to be for tickets with a certain tag?

In T362406#9720340, @Dzahn wrote:

The list of hardcoded user names in there seems kind of arbitrary?

No, it's accounts of folks who I know are staff and currently use neither a linked WMF SUL account or wikimedia.org email address for their Phab accounts.

Is this only supposed to be for tickets with a certain tag?

No, see the task description.

Oh, I understand now, it's the staff who is known to not use wikimedia.org mail, ack, apologies.

I posted the DB query output (464 results: Phab task URL, title, assignee, author) in public P60752 (which offers a "View Raw File" option so data could be imported into a spreadsheet application by setting | as a delimiter character).
The very same list of tasks is https://phabricator.wikimedia.org/maniphest/query/Pha3X9Ry6g11/#R and "Use Results > Export Data" in the upper right corner allows e.g. exporting in CSV format, if that's more feasible.

That data of 464 results is incorrect as it has entries which should not be in there (random example: tasks filed by jbond).
I'd like to point out again that as long as staff is allowed to use non-staff accounts and non-staff email addresses for staff activity, this data will always be incorrect. @ARamirez_WMF: Could you please confirm that this caveat is clearly communicated when using this data in QLS? Thanks in advance!

For the records, SQL query that was used:

mysql:phstats@m3-slave.eqiad.wmnet [phabricator_maniphest]> SELECT DISTINCT CONCAT("https://phabricator.wikimedia.org/T", t.id) AS url, t.title, u1.userName AS author, u2.userName AS assignee FROM phabricator_maniphest.maniphest_task t INNER JOIN phabricator_user.user u1 ON u1.phid = t.authorPHID INNER JOIN phabricator_user.user u2 ON u2.phid = t.ownerPHID WHERE t.status = "resolved" AND FROM_UNIXTIME(t.closedEpoch,'%Y%m')>=202401 AND FROM_UNIXTIME(t.closedEpoch,'%Y%m')<=202403 AND u1.phid NOT IN (SELECT ua.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_externalaccount ua ON ua.userPHID = u.phid WHERE ua.accountType = "mediawiki" AND ((ua.username LIKE '%(WMF)' OR ua.username LIKE '%-WMF') OR (ua.username LIKE '%(WMDE)' OR ua.username LIKE '%-WMDE'))) AND u1.phid NOT IN (SELECT ue.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_email ue ON ue.userPHID = u.phid WHERE (ue.address LIKE '%@wikimedia.org' OR ue.address LIKE '%@wikimedia.de' OR ue.address LIKE '%@speedandfunction.com')) AND (u1.userName != "dbarratt" AND u1.userName != "Samwilson" AND u1.userName != "Amire80" AND u1.userName != "Dreamy_Jazz" AND u1.userName != "Ladsgroup" AND u1.userName != "Tchanders" AND u1.userName != "Daimona" AND u1.userName != "Nikerabbit" AND u1.userName != "Mooeypoo" AND u1.userName != "demon") AND (u2.phid IN (SELECT ua.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_externalaccount ua ON ua.userPHID = u.phid WHERE ua.accountType = "mediawiki" AND ((ua.username LIKE '%(WMF)' OR ua.username LIKE '%-WMF') OR (ua.username LIKE '%(WMDE)' OR ua.username LIKE '%-WMDE'))) OR u2.phid IN (SELECT ue.userPHID FROM phabricator_user.user u INNER JOIN phabricator_user.user_email ue ON ue.userPHID = u.phid WHERE (ue.address LIKE '%@wikimedia.org' OR ue.address LIKE '%@wikimedia.de' OR ue.address LIKE '%@speedandfunction.com')) OR (u2.userName = "dbarratt" OR u2.userName = "Samwilson" OR u2.userName = "Amire80" OR u2.userName = "Dreamy_Jazz" OR u2.userName = "Ladsgroup" OR u2.userName = "Tchanders" OR u2.userName = "Daimona" OR u2.userName = "Nikerabbit" OR u2.userName = "Mooeypoo" OR u2.userName = "demon"));

thank you @Aklapper! yes, i will make sure to add this disclaimer to the slide for amanda to present.

Aklapper mentioned this in T362804: Automate quarterly WMF QLS request for Phabricator data (tasks reported by community, resolved by WMF).Wed, Apr 17, 4:33 PM

Run a query to pull the number of Phabricator Tasks reported by community, resolved by WMF Closed, ResolvedPublicActions

Description

Related Objects

Event Timeline

Run a query to pull the number of Phabricator Tasks reported by community, resolved by WMF
Closed, ResolvedPublic
Actions