Metrics/Bugzilla queries

Contents 1 BZ bugs opened and resolved month by month 2 BZ active users last month 3 BZ active users month by month 4 BZ top 20 bug reporters last month 5 BZ top 20 active users last month

BZ bugs opened and resolved month by month

select 
	opened.monthnum as monthnum,
	opened.monthstr as monthstr,
	opened_count,
	resolved_count
from
(
	SELECT
		date_format(creation_ts,'%Y%m') as monthnum, 
		date_format(creation_ts,'%b %y') as monthstr, 
		count(bugs.bug_id) as opened_count
	FROM bugs 
	GROUP BY monthnum
) AS opened
INNER JOIN
(
	SELECT 
		date_format(bug_when, '%Y%m') AS monthnum, 
		date_format(bug_when, '%b %y') AS monthstr, 
		COUNT(*) as resolved_count 
	FROM bugs_activity
	WHERE 
		fieldid=9 AND 
		added='RESOLVED' 
	GROUP BY monthnum
) AS resolved
ON 
	opened.monthnum = resolved.monthnum
where opened.monthnum <= date_format(${last_month},'%Y%m')
ORDER BY opened.monthnum;

BZ active users last month

select
	count(distinct userid)
from
(
	select 
		ba.who as userid,
		ba.bug_when as action_date 
	from bugs_activity ba 
	where  
		date_format(ba.bug_when,'%Y%m')=date_format(${last_month},'%Y%m') and
		ba.fieldid in (2,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,30,35,36,37,38,40,41,42,47,55,56,57,58) 
	group by action_date,userid 
	union all 
		select 
			b.reporter,
			b.creation_ts 
		from bugs b 
		where 
			date_format(b.creation_ts,'%Y%m')=date_format(${last_month},'%Y%m')
) as filtered_actions;

BZ active users month by month

select
	date_format(action_date,'%Y%m') as monthnum,
	date_format(action_date,'%b %y') as monthstr,
	count(distinct userid)
from
(
	select 
		ba.who as userid,
		ba.bug_when as action_date 
	from bugs_activity ba 
	where  
		ba.fieldid in (2,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,30,35,36,37,38,40,41,42,47,55,56,57,58) 
	group by action_date,userid 
	union all 
		select 
			b.reporter,
			b.creation_ts 
		from bugs b 
) as filtered_actions 
where date_format(action_date,'%Y%m') <= date_format(${last_month},'%Y%m')
group by monthnum
order by monthnum;

BZ top 20 bug reporters last month

SELECT 
	profiles.realname AS username,
	COUNT(bugs.bug_id) AS bugcount 
FROM bugs      
INNER JOIN profiles 
ON 
	bugs.reporter = profiles.userid 
WHERE date_format(creation_ts,'%Y%m')=date_format(${last_month},'%Y%m') 
GROUP BY username 
ORDER BY bugcount DESC 
LIMIT 20;

BZ top 20 active users last month

select 
	p.realname, 
	count(*) as activity_count 
from 
(
	select 
		ba.bug_id as bug_id,
		ba.who as userid,
		ba.bug_when as date 
	from bugs_activity ba 
	where  
		date_format(ba.bug_when,'%Y%m')=date_format(${last_month},'%Y%m') and 
		ba.fieldid in (2,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,30,35,36,37,38,40,41,42,47,55,56,57,58) 
	group by ba.bug_when,ba.who 
	union all 
		select 
			b.bug_id, 
			b.reporter,
			b.creation_ts 
		from bugs b 
		where 
			date_format(b.creation_ts,'%Y%m')=date_format(${last_month},'%Y%m')
	order by bug_id
) as filtered_actions 
inner join profiles p 
on 
(
	p.userid=filtered_actions.userid
) 
group by filtered_actions.userid 
order by activity_count desc 
limit 20;