Page MenuHomePhabricator
Create Task
Maniphest T85183

"Project creation/edits log" cronjob email for Phab admins
Closed, ResolvedPublic
Actions

Description

Bugzilla admins received a custom daily email (cronjob) that listed recent changes (new accounts created, projects created/renamed/changed).
Once it existed (after Bugzilla 4.2 only), it was helpful to identify and avoid duplication of certain efforts or inconcistency.

In Phabricator, getting a better overview which projects get created and guideline application can be helpful, as we have many people in the ProjectCreators group who can perform such actions.

In the phabricator_project DB:

SELECT name, editPolicy, viewPolicy, editPolicy, joinPolicy, icon, color, status FROM project WHERE FROM_UNIXTIME(dateModified,'%Y%m')=date_format(NOW() - INTERVAL 1 MONTH,'%Y%m');

Status: 0 = Active; 100 = Archived.
The rest of the values is self-explaining.

Details

SubjectRepoBranchLines +/-
phabricator: fix output formatting in metrics scriptoperations/puppetproduction+3 -8
Phabricator project creation/changes log email for Phab adminsoperations/puppetproduction+97 -0
Customize query in gerrit

Related Objects
Search...

Event Timeline

Aklapper created this task.Dec 22 2014, 11:36 PM
Aklapper claimed this task.
Aklapper raised the priority of this task from to Low.
Aklapper updated the task description. (Show Details)
Aklapper added a project: Phabricator.
Aklapper subscribed.
Aklapper mentioned this in T85096: Set up dumping Phabricator's project taxonomy to a wiki.Dec 23 2014, 2:47 PM
Qgil subscribed.Dec 29 2014, 10:27 AM

I guess the advantage of a cronjob email is that we can create it for this instance without bothering upstream?

It looks like Phabricator should include a feature to receive a notification via subscription or Herald whenever a new project is created. Even the possibility to sort projects by creation date would help, but only alphabetical sorting is available.

Qgil moved this task from To Triage to Need discussion on the Phabricator board.Dec 29 2014, 10:27 AM
scfc subscribed.Jan 10 2015, 5:37 AM
Aklapper added a comment.Jan 29 2015, 7:27 PM

Not to myself: Might want to extend to also cover "newly created workboards" to get a list of column names.

Aklapper renamed this task from "Project creation log" cronjob email for Phab admins to "Project creation/edits log" cronjob email for Phab admins.Apr 13 2015, 12:00 PM
Aklapper set Security to None.
Aklapper added a comment.Jun 1 2015, 11:45 AM

Seems like the last Phabricator upgrade brought email notification emails about Project creations and changes. Nice.

Still need to check a bit more which stuff they don't cover, why I get them, and if I maybe don't get some change notifications.

Aklapper mentioned this in T97273: Simpler process for Phabricator project creation.Jun 22 2015, 12:22 PM
gerritbot subscribed.Aug 23 2015, 5:27 PM

Change 233219 had a related patch set uploaded (by Aklapper):
[WIP] Phabricator project creation/changes log email for Phab admins

https://gerrit.wikimedia.org/r/233219

gerritbot added a project: Patch-For-Review.Aug 23 2015, 5:27 PM
Aklapper added a comment.Aug 23 2015, 5:32 PM
In T85183#946843, @Qgil wrote:

I guess the advantage of a cronjob email is that we can create it for this instance without bothering upstream?

Yes. Quick and dirty, and I've recently been quite unhappy when discovering newly created projects violating our project creation guidelines.

Aklapper added a comment.Aug 23 2015, 5:33 PM

As I'm after recent changes, the query I posted above above isn't too helpful. Instead here are the SQL queries which I used in the linked patchset:

Project creations and project name changes in the last 7 days:
[phabricator_project] SELECT oldValue, newValue FROM project_transaction WHERE transactionType = "project:name" AND dateModified > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));

Project color changes in the last 7 days:
[phabricator_project] SELECT project_transaction.oldValue, project_transaction.newValue, project.name FROM project_transaction JOIN project WHERE project_transaction.transactionType = "project:color" AND project_transaction.objectPHID = project.phid AND project_transaction.dateModified > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));

Project policy/locking/archiving changes in the last 7 days:
[phabricator_project] SELECT project_transaction.oldValue, project_transaction.newValue, project_transaction.transactionType, project.name FROM project_transaction JOIN project WHERE (project_transaction.transactionType = "core:join-policy" OR project_transaction.transactionType = "core:edit-policy" OR project_transaction.transactionType = "core:view-policy" OR project_transaction.transactionType = "project:locked" OR project_transaction.transactionType = "project:status") AND project_transaction.objectPHID = project.phid AND project_transaction.dateModified > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));

Aklapper added a comment.Aug 23 2015, 5:33 PM
In T85183#1001391, @Aklapper wrote:

Note to myself: Might want to extend to also cover "newly created workboards" to get a list of column names.

Not this time as I consider this less of an issue, but just to document one related SQL query for anybody interested:

Project workboard columns created or renamed in the last 7 days (and who performed that):
SELECT cltr.oldValue, cltr.newValue, prj.name, usr.userName FROM phabricator_project.project_columntransaction cltr JOIN phabricator_project.project prj JOIN phabricator_project.project_column pcl JOIN phabricator_user.user usr WHERE cltr.transactionType = "project:col:name" AND cltr.objectPHID = pcl.phid AND pcl.projectPHID = prj.phid AND cltr.authorPHID = usr.phid AND cltr.dateModified > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));

Aklapper added a project: DevRel-September-2015.Aug 31 2015, 7:21 PM
Aklapper moved this task from Backlog to Doing on the DevRel-September-2015 board.Aug 31 2015, 7:24 PM
Aklapper mentioned this in T111200: Grant puppet script access to "phabricator_project" DB.Sep 2 2015, 3:31 PM
Aklapper added a subtask: T111200: Grant puppet script access to "phabricator_project" DB.
Aklapper closed subtask T111200: Grant puppet script access to "phabricator_project" DB as Resolved.Sep 3 2015, 11:07 AM
gerritbot added a comment.Sep 3 2015, 7:06 PM

Change 233219 merged by Dzahn:
Phabricator project creation/changes log email for Phab admins

https://gerrit.wikimedia.org/r/233219

Dzahn mentioned this in rOPUP05a796e0c211: Phabricator project creation/changes log email for Phab admins.Sep 3 2015, 7:06 PM
Dzahn subscribed.Sep 3 2015, 7:10 PM

I tested the puppet part in compiler http://puppet-compiler.wmflabs.org/871/

applied it on iridium, cron has been created

Dzahn added a comment.Sep 3 2015, 7:15 PM

I ran /usr/local/bin/project_changes.sh as root on iridium.

Then i checked /var/log/exim4/mainlog and i could see a mail has been sent out to aklapper@.

Dzahn added a comment.Sep 3 2015, 7:18 PM

@Aklapper I made the script send a mail to me as well. There is data in it, just the formatting could still be improved it seems.

Should i just paste the entire thing here on the ticket?

Dzahn closed this task as Resolved.Sep 3 2015, 7:22 PM
# Puppet Name: collect_exim_stats_via_gmetric
* * * * * /usr/local/bin/exim-to-gmetric
# Puppet Name: phabstatscron_communitymetrics
0 0 1 * * /usr/local/bin/community_metrics.sh
# Puppet Name: phab_dump
10 4 * * * rsync -zpt --bwlimit=40000 -4 /srv/dumps/phabricator_public.dump dataset1001.wikimedia.org::other_misc/ >/dev/null 2>&1
# Puppet Name: phabstatscron_projectchanges
0 0 1,8,15,22,29 * * /usr/local/bin/project_changes.sh
Aklapper added a comment.Sep 3 2015, 7:43 PM

Yay. Works. No linebreaks but who needs them anyway. Life is a stream.

dzahn, jcrespo: Thanks a lot for your help!

gerritbot added a comment.Sep 3 2015, 8:03 PM

Change 235848 had a related patch set uploaded (by Dzahn):
phabricator: fix output formatting in metrics script

https://gerrit.wikimedia.org/r/235848

gerritbot added a comment.Sep 3 2015, 8:05 PM

Change 235848 merged by Dzahn:
phabricator: fix output formatting in metrics script

https://gerrit.wikimedia.org/r/235848

Dzahn mentioned this in rOPUP8aef71eec365: phabricator: fix output formatting in metrics script.Sep 3 2015, 8:05 PM
Dzahn added a comment.Sep 3 2015, 8:08 PM
In T85183#1603782, @Aklapper wrote:

No linebreaks but who needs them anyway. Life is a stream.

fixed with https://gerrit.wikimedia.org/r/#/c/235848/1

Danny_B subscribed.May 31 2016, 10:35 AM
Restricted Application added a subscriber: TerraCodes. · View Herald TranscriptMay 31 2016, 10:35 AM
Danny_B mentioned this in T139950: Phabricator weekly report not generated (or at least sent).Jul 18 2016, 8:58 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL