Addshore has uploaded a new change for review.
https://gerrit.wikimedia.org/r/242845
Change subject: Add admin & crat count metrics
......................................................................
Add admin & crat count metrics
Change-Id: If2db17714d75bb29591cd4b364e1966c0cf83f96
---
M cron
A src/site_stats/admins/README.md
A src/site_stats/admins/create_table.sql
A src/site_stats/admins/export.sh
A src/site_stats/admins/generate.sh
A src/site_stats/bureaucrats/README.md
A src/site_stats/bureaucrats/create_table.sql
A src/site_stats/bureaucrats/export.sh
A src/site_stats/bureaucrats/generate.sh
9 files changed, 70 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-wikidata-data
refs/changes/45/242845/1
diff --git a/cron b/cron
index 6f22b80..ced6c0d 100644
--- a/cron
+++ b/cron
@@ -19,3 +19,5 @@
13 6 * * * cd ~/wikidata-data/src/site_stats/total_pages && ./generate.sh &&
./export.sh
14 6 * * * cd ~/wikidata-data/src/site_stats/total_views && ./generate.sh &&
./export.sh
15 6 * * * cd ~/wikidata-data/src/site_stats/users && ./generate.sh &&
./export.sh
+16 6 * * * cd ~/wikidata-data/src/site_stats/admins && ./generate.sh &&
./export.sh
+17 6 * * * cd ~/wikidata-data/src/site_stats/bureaucrats && ./generate.sh &&
./export.sh
diff --git a/src/site_stats/admins/README.md b/src/site_stats/admins/README.md
new file mode 100644
index 0000000..055cf0c
--- /dev/null
+++ b/src/site_stats/admins/README.md
@@ -0,0 +1,8 @@
+- - -
+site stats admins
+====================
+
+The number of users in the admins on a give day.
+Generated using the user_groups table.
+
+SELECT ug_group, count(*) AS count FROM user_groups GROUP BY ug_group;
\ No newline at end of file
diff --git a/src/site_stats/admins/create_table.sql
b/src/site_stats/admins/create_table.sql
new file mode 100644
index 0000000..c18f0df
--- /dev/null
+++ b/src/site_stats/admins/create_table.sql
@@ -0,0 +1,5 @@
+CREATE TABLE IF NOT EXISTS wikidata_site_stats_admins
+ (
+ date DATE NOT NULL,
+ count BIGINT(20) NOT NULL
+ );
\ No newline at end of file
diff --git a/src/site_stats/admins/export.sh b/src/site_stats/admins/export.sh
new file mode 100644
index 0000000..41ab36b
--- /dev/null
+++ b/src/site_stats/admins/export.sh
@@ -0,0 +1,7 @@
+#!/bin/bash
+
+mysqldefaults="/etc/mysql/conf.d/analytics-research-client.cnf"
+mysqlhost="analytics-store.eqiad.wmnet"
+
+mysql --defaults-file=$mysqldefaults -h $mysqlhost -e "SELECT * FROM
staging.wikidata_site_stats_admins" > data.tsv
+cp data.tsv /a/aggregate-datasets/wikidata/site_stats_admins.tsv
diff --git a/src/site_stats/admins/generate.sh
b/src/site_stats/admins/generate.sh
new file mode 100644
index 0000000..fcaa650
--- /dev/null
+++ b/src/site_stats/admins/generate.sh
@@ -0,0 +1,14 @@
+#!/bin/bash
+
+dateISO=`date --date=today --iso-8601=date`
+
+metric=$(mysql --defaults-file=/etc/mysql/conf.d/analytics-research-client.cnf
-h analytics-store.eqiad.wmnet -A -se "select count(*) as count from
user_groups where ug_group = 'sysop' group by ug_group" wikidatawiki)
+
+# Start building the SQL
+sql='INSERT INTO wikidata_site_stats_admins (date,count) VALUES '
+sql="$sql ('$dateISO', '$metric');"
+
+# Commit the SQL
+mysql --defaults-file=/etc/mysql/conf.d/analytics-research-client.cnf -h
analytics-store.eqiad.wmnet -A -e "$sql" staging
+
+echo "All done!"
diff --git a/src/site_stats/bureaucrats/README.md
b/src/site_stats/bureaucrats/README.md
new file mode 100644
index 0000000..8bf6863
--- /dev/null
+++ b/src/site_stats/bureaucrats/README.md
@@ -0,0 +1,8 @@
+- - -
+site stats bureaucrats
+====================
+
+The number of users in the bureaucrats on a give day.
+Generated using the user_groups table.
+
+SELECT ug_group, count(*) AS count FROM user_groups GROUP BY ug_group;
\ No newline at end of file
diff --git a/src/site_stats/bureaucrats/create_table.sql
b/src/site_stats/bureaucrats/create_table.sql
new file mode 100644
index 0000000..8cf0bc0
--- /dev/null
+++ b/src/site_stats/bureaucrats/create_table.sql
@@ -0,0 +1,5 @@
+CREATE TABLE IF NOT EXISTS wikidata_site_stats_bureaucrats
+ (
+ date DATE NOT NULL,
+ count BIGINT(20) NOT NULL
+ );
\ No newline at end of file
diff --git a/src/site_stats/bureaucrats/export.sh
b/src/site_stats/bureaucrats/export.sh
new file mode 100644
index 0000000..4dc0325
--- /dev/null
+++ b/src/site_stats/bureaucrats/export.sh
@@ -0,0 +1,7 @@
+#!/bin/bash
+
+mysqldefaults="/etc/mysql/conf.d/analytics-research-client.cnf"
+mysqlhost="analytics-store.eqiad.wmnet"
+
+mysql --defaults-file=$mysqldefaults -h $mysqlhost -e "SELECT * FROM
staging.wikidata_site_stats_bureaucrats" > data.tsv
+cp data.tsv /a/aggregate-datasets/wikidata/site_stats_bureaucrats.tsv
diff --git a/src/site_stats/bureaucrats/generate.sh
b/src/site_stats/bureaucrats/generate.sh
new file mode 100644
index 0000000..48076b4
--- /dev/null
+++ b/src/site_stats/bureaucrats/generate.sh
@@ -0,0 +1,14 @@
+#!/bin/bash
+
+dateISO=`date --date=today --iso-8601=date`
+
+metric=$(mysql --defaults-file=/etc/mysql/conf.d/analytics-research-client.cnf
-h analytics-store.eqiad.wmnet -A -se "select count(*) as count from
user_groups where ug_group = 'bureaucrats' group by ug_group" wikidatawiki)
+
+# Start building the SQL
+sql='INSERT INTO wikidata_site_stats_bureaucrats (date,count) VALUES '
+sql="$sql ('$dateISO', '$metric');"
+
+# Commit the SQL
+mysql --defaults-file=/etc/mysql/conf.d/analytics-research-client.cnf -h
analytics-store.eqiad.wmnet -A -e "$sql" staging
+
+echo "All done!"
--
To view, visit https://gerrit.wikimedia.org/r/242845
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: If2db17714d75bb29591cd4b364e1966c0cf83f96
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-wikidata-data
Gerrit-Branch: master
Gerrit-Owner: Addshore <addshorew...@gmail.com>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
