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