Ottomata has submitted this change and it was merged. Change subject: Added domain referer info to blog query, and ignore search and preview urls. ......................................................................
Added domain referer info to blog query, and ignore search and preview urls. Change-Id: I86fd40027c9c1fa98d993b411313f51b2bd81601 --- M templates/misc/email-blog-pageviews.erb 1 file changed, 19 insertions(+), 1 deletion(-) Approvals: Ottomata: Verified; Looks good to me, approved Milimetric: Looks good to me, but someone else must approve jenkins-bot: Verified diff --git a/templates/misc/email-blog-pageviews.erb b/templates/misc/email-blog-pageviews.erb index dfda018..ae655e8 100644 --- a/templates/misc/email-blog-pageviews.erb +++ b/templates/misc/email-blog-pageviews.erb @@ -4,7 +4,25 @@ echo "Page view count for the 24 hour period starting at midnight, $DATE UTC" > $OUTPUT echo "" >> $OUTPUT -mysql log -h <%= db_host %> -p<%= db_pass %> -u<%= db_user %> -e "select count(event_requestUrl) as count, date(timestamp), event_requestUrl from WikimediaBlogVisit_5308166 where date(timestamp)=date('$DATE') group by event_requestUrl order by date(timestamp) desc, count desc;" >> $OUTPUT +mysql log -h <%= db_host %> -p<%= db_pass %> -u<%= db_user %> -e " +SELECT + COUNT(event_requestUrl) AS count, + DATE(timestamp), + event_requestUrl, + SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(event_referrerUrl, '/', 3), '/', -1), '.', -2) AS referer +FROM WikimediaBlogVisit_5308166 + WHERE DATE(timestamp)=DATE('$DATE') + AND event_requestUrl NOT LIKE '/?s=%' + AND event_requestUrl NOT LIKE '/?p=%' +GROUP BY + event_requestUrl, referer +ORDER BY + DATE(timestamp) DESC, + event_requestUrl, + referer, + count DESC;" >> $OUTPUT + + cat $OUTPUT | mailx -s "Daily Blog Pageviews" <%= recipient_email %> rm $OUTPUT -- To view, visit https://gerrit.wikimedia.org/r/55390 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I86fd40027c9c1fa98d993b411313f51b2bd81601 Gerrit-PatchSet: 3 Gerrit-Project: operations/puppet Gerrit-Branch: production Gerrit-Owner: Diederik <dvanli...@wikimedia.org> Gerrit-Reviewer: Milimetric <dandree...@wikimedia.org> Gerrit-Reviewer: Ottomata <o...@wikimedia.org> Gerrit-Reviewer: jenkins-bot _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits