Diederik has uploaded a new change for review.
https://gerrit.wikimedia.org/r/111152
Change subject: [WIP] MySQL scripts to generate Kanban chart for RT
......................................................................
[WIP] MySQL scripts to generate Kanban chart for RT
Do not merge, see also RT 6761
Change-Id: Ie3a5f19c25d2828e64c8c6383525ab3e9dd4bfe7
---
M manifests/misc/statistics.pp
A templates/misc/rt-cumulative-flow-dataset.erb
2 files changed, 143 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/operations/puppet
refs/changes/52/111152/1
diff --git a/manifests/misc/statistics.pp b/manifests/misc/statistics.pp
index 956d136..eda49bc 100644
--- a/manifests/misc/statistics.pp
+++ b/manifests/misc/statistics.pp
@@ -798,6 +798,38 @@
}
}
+# Class: misc::statistics::cron_rt_cdf
+#
+# Sets up daily cron jobs to run a script which
+# generates a cumulative flow dataset used in
+# Kanban to measure work in progress and cycle time.
+# See also https://rt.wikimedia.org/Ticket/Display.html?id=6761
+class misc::statistics::cron_rt_cdf {
+ include passwords::mysql::rt_readonly
+
+ $script = '/usr/local/bin/rt-cdf.sh'
+
+ $db_host = ''
+ $db_user = $passwords::mysql::rt_readonly::user
+ $db_pass = $passwords::mysql::rt_readonly::pass
+ $db_database = 'rt4'
+
+ file { $script:
+ mode => '0755',
+ content => template('misc/rt-cumulative-flow-dataset.erb'),
+ }
+
+ # Create a daily cron job to run the blog script
+ # This requires that the $misc::statistics::user::username
+ # user is installed on the source host.
+ cron { 'blog_pageviews_email':
+ command => $script,
+ user => $misc::statistics::user::username,
+ hour => 1,
+ minute => 0,
+ }
+}
+
# Class: misc::statistics::limn::mobile_data_sync
#
diff --git a/templates/misc/rt-cumulative-flow-dataset.erb
b/templates/misc/rt-cumulative-flow-dataset.erb
new file mode 100644
index 0000000..9d312b7
--- /dev/null
+++ b/templates/misc/rt-cumulative-flow-dataset.erb
@@ -0,0 +1,111 @@
+#!/bin/bash
+
+# Written by Diederik van Liere ([email protected]) for WMF
+# These MySQL queries jointly create a cumulative flow dataset
+# that can be used to visualize a chart called the cumulative flow
+# diagram (cdf). A cdf is a key chart in Kanban like operations
+# and it visualizes two key metrics:
+# 1) Work-in-Progress (WIP) -- for any given day, how many tickets
+were worked on?
+# 2) Mean Cycle Time -- how much does it take between an RT ticket
+gets submitted and resolved.
+#
+# RT does not have metrics regarding the 'size' of the task, so each
+# ticket is weighted equally.
+
+DATE=`date --date=yesterday +%Y-%m-%d`;
+
+echo "Generating the cumulative flow dataset for $DATE UTC"
+
+#RT was taken into production around August 1st, 2010
+
+mysql log -h <%= db_host %> -p<%= db_pass %> -u<%= db_user %> <%= db_database
%=> -e "
+
+-- The drop statement is a bit stupid but MySQL does not have a
+-- CREATE IF NOT EXISTS for procedures
+DROP PROCEDURE IF EXISTS fill_calendar;
+
+CREATE TABLE IF NOT EXISTS calendar (
+ id MEDIUMINT NOT NULL AUTO_INCREMENT,
+ datefield DATE,
+ new MEDIUMINT,
+ open MEDIUMINT,
+ resolved MEDIUMINT,
+ PRIMARY KEY (id)
+);
+
+DELIMITER |
+
+CREATE PROCEDURE fill_calendar()
+ BEGIN
+ DECLARE start_date DATE;
+ DECLARE end_date DATE;
+ SELECT COALESCE((SELECT datefield FROM calendar WHERE id = 0), (SELECT
STR_TO_DATE('2010-01-08', '%Y-%m-%d'))) INTO start_date;
+ SELECT COALESCE((SELECT datefield FROM calendar ORDER BY id DESC LIMIT 1),
(CURDATE() - INTERVAL 1 DAY)) INTO end_date;
+ WHILE start_date < end_date DO
+ INSERT INTO calendar (datefield) VALUES(start_date);
+ SET start_date = ADDDATE(start_date, INTERVAL 1 DAY);
+ END WHILE;
+ END |
+DELIMITER ;
+
+CALL fill_calendar();
+
+
+-- Days that the ticket was marked new
+UPDATE
+ calendar
+JOIN (SELECT calendar.datefield AS date_new,
SUM(IFNULL(IF(DATE(Tickets.Created) > '1970-01-01', 1, 0), 0)) AS new
+ FROM
+ Tickets
+RIGHT JOIN
+ calendar ON DATE(calendar.datefield) BETWEEN Tickets.Created AND
IF(DATE(Tickets.Started) = '1970-01-01', CURDATE(), Tickets.Started)
+GROUP BY
+ date_new)
+AS
+ new_query
+ON
+ calendar.datefield = new_query.date_new
+SET
+ calendar.new = new_query.new;
+
+-- Days that the ticket was marked open
+UPDATE
+ calendar
+JOIN (SELECT calendar.datefield AS date_open,
SUM(IFNULL(IF(DATE(Tickets.Started) > '1970-01-01', 1, 0), 0)) AS open
+FROM
+ Tickets
+RIGHT JOIN
+ calendar
+ON
+ DATE(calendar.datefield) BETWEEN Tickets.Started AND
IF(DATE(Tickets.Resolved) = '1970-01-01', CURDATE(), Tickets.Resolved)
+GROUP BY
+ date_open)
+AS
+ open_query
+ON
+ calendar.datefield = open_query.date_open
+SET
+ calendar.open = open_query.open;
+
+-- Days that the ticket was marked resolved
+UPDATE
+ calendar
+JOIN (SELECT calendar.datefield AS date_resolved,
SUM(IFNULL(IF(DATE(Tickets.Resolved) > '1970-01-01', 1, 0), 0)) AS resolved
+FROM
+ Tickets
+RIGHT JOIN
+ calendar
+ON
+ DATE(calendar.datefield) >= IF(DATE(Tickets.Resolved) = '1970-01-01',
CURDATE(), Tickets.Resolved)
+GROUP BY
+ date_resolved)
+AS
+ resolved_query
+ON
+ calendar.datefield = resolved_query.date_resolved
+SET
+ calendar.resolved = resolved_query.resolved;
+" >> /tmp/rt-cdf.log 2>&1
+
+
--
To view, visit https://gerrit.wikimedia.org/r/111152
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ie3a5f19c25d2828e64c8c6383525ab3e9dd4bfe7
Gerrit-PatchSet: 1
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Diederik <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
