ChartsDataCollecting
This page describes how are collected data from issuezilla replica. These data are used for generating charts available at http://statistics.netbeans.org/metrics
Weekly Defect Priorities chart
Strategy:
- In History table - for each issue find the record with last timestamp before the end of the desired week.
- Take only such Defects that have in record status New, Started or Reopened.
- Filter out issues with keywords INCOMPLETE, NO65, 6.5_WAIVER_APPROVED and target milestone 'Sierra'.
Old sample query for installers:
SELECT priority, count(IF( ((component='nbi' ) or (component='installer' and subcomponent not in ( 'sunstudio' ) )), 1, NULL)) as ooo_installer from history, (SELECT issue_id as id,MAX(whn) as kkk from history where whn < str_to_date('2008-03-16 23:59:59','%Y-%m-%d %T') group by issue_id) as XYZ where ( history.issue_id=id and whn=kkk) AND issue_status IN ('NEW','STARTED','REOPENED') AND issue_type = 'DEFECT' AND version in ('6.5','6.1','6.0','5.5.1','5.5','5.0','4.2','4.1','4.0','3.6','3.5','3.4','3.4+dev','3.3.2','3.3.1','3.3.1+dev','3.3','3.3+dev','3.2.1','3.2','3.1','FFJ+2.0','FFJ+3.0+ea','FFJ+3.0','FFJ+4.0+ea','FFJ+4.0','S1S+4.1','S1S+5','current','Other') AND keyword not like ('%INCOMPLETE%') and keyword not like('%NO65%') and keyword not like('%6.5_WAIVER_APPROVED%') and target_milestone != 'Sierra' group by priority
New sample query for installers:
select issue.priority, count(IF( ((component='nbi' ) or
(component='installer' and
subcomponent not in ( 'sunstudio' ) )), 1, NULL))
as ooo_installer from issue,
(select history.issue_id as my_id from history,
(SELECT distinct issue_id as id, MAX(whn) as max_whn
from history
where whn < str_to_date('2008-09-16 23:59:59','%Y-%m-%d %T')
group by issue_id
) as inner_table
where history.whn = inner_table.max_whn and
history.issue_id = inner_table.id and
history.issue_status IN ('NEW','STARTED','REOPENED')
) as outer_table
where issue.issue_type = 'DEFECT' AND
version in ('6.5','6.1','6.0','5.5.1','5.5','5.0','4.2','4.1','4.0','3.6','3.5','3.4','3.4+dev',
'3.3.2','3.3.1','3.3.1+dev','3.3','3.3+dev','3.2.1','3.2','3.1','FFJ+2.0','FFJ+3.0+ea',
'FFJ+3.0','FFJ+4.0+ea','FFJ+4.0','S1S+4.1','S1S+5','current','Other') AND
keyword not like ('%INCOMPLETE%') and
keyword not like('%NO65%') and
keyword not like('%6.5_WAIVER_APPROVED%') and
target_milestone != 'Sierra' AND
outer_table.my_id = issue.issue_id
group by issue.priority
Bugs Filed vs. Resolved chart
Strategy for Filed:
- In issue table select defects with desired creation timestamp.
- Do not include issues that have resolution INVALID or DUPLICATE in the time of generating.
- Filter out issues with keywords INCOMPLETE, NO65, 6.5_WAIVER_APPROVED and target milestone 'Sierra'.
Old sample query for installers:
select extract(week from a.creation_ts) as 'week', count(*) as 'new issues', extract(year from a.creation_ts) from issue a, issue_user b where a.creation_ts >= str_to_date('2008-03-09 23:59:59','%Y-%m-%d %T') and a.reporter = b.username AND ((component='nbi' ) or (component='installer' and subcomponent not in ( 'sunstudio' ) )) and a.resolution not in ('INVALID', 'DUPLICATE') and a.issue_type = 'Defect' and version in ('6.5','6.1','6.0','5.5.1','5.5','5.0','4.2','4.1','4.0','3.6','3.5','3.4','3.4+dev','3.3.2','3.3.1','3.3.1+dev','3.3','3.3+dev','3.2.1','3.2','3.1','FFJ+2.0','FFJ+3.0+ea','FFJ+3.0','FFJ+4.0+ea','FFJ+4.0','S1S+4.1','S1S+5','current','Other') AND keyword not like ('%INCOMPLETE%') and keyword not like('%NO65%') and keyword not like('%6.5_WAIVER_APPROVED%') and target_milestone != 'Sierra' group by 1 order by 3,1
New sample query for installers:
select extract(week from a.creation_ts) as 'week', count(*) as 'new issues',
extract(year from a.creation_ts) from issue a,
issue_user b
where a.creation_ts >= str_to_date('2008-03-09 23:59:59','%Y-%m-%d %T') and
a.reporter = b.username AND
((component='nbi' ) or (component='installer' and
subcomponent not in ( 'sunstudio' ) )) and
a.issue_type = 'Defect' and
version in('6.5','6.1','6.0','5.5.1','5.5','5.0','4.2','4.1',
'4.0','3.6','3.5','3.4','3.4+dev','3.3.2','3.3.1',
'3.3.1+dev','3.3','3.3+dev','3.2.1','3.2','3.1',
'FFJ+2.0','FFJ+3.0+ea','FFJ+3.0','FFJ+4.0+ea',
'FFJ+4.0','S1S+4.1','S1S+5','current','Other')
AND keyword not like ('%INCOMPLETE%')
and keyword not like('%NO65%')
and keyword not like('%6.5_WAIVER_APPROVED%')
and target_milestone != 'Sierra'
group by 1
order by 3,1
Strategy for Resolved:
- From activity table select records which are indicating that the issue changed issue_status to RESOLVED.
- Take only these issues which are in status ('closed', 'resolved', 'verified') in the time of generating.
- Filter out issues with keywords INCOMPLETE, NO65, 6.5_WAIVER_APPROVED and target milestone 'Sierra'.
Old sample query for installers:
select extract(week from b.whn) as 'week', count(*) as 'fixed issues', extract(year from b.whn) from issue a, activity b where a.issue_id = b.issue_id and a.issue_status in ('closed', 'resolved', 'verified') and b.whn >= str_to_date('2008-03-09 23:59:59','%Y-%m-%d %T') and b.what = 'issue_status' and b.newvalue = 'resolved' AND ((component='nbi' ) or (component='installer' and subcomponent not in ( 'sunstudio' ) )) and a.issue_type = 'defect' AND version in ('6.5','6.1','6.0','5.5.1','5.5','5.0','4.2','4.1','4.0','3.6','3.5','3.4','3.4+dev','3.3.2','3.3.1','3.3.1+dev','3.3','3.3+dev','3.2.1','3.2','3.1','FFJ+2.0','FFJ+3.0+ea','FFJ+3.0','FFJ+4.0+ea','FFJ+4.0','S1S+4.1','S1S+5','current','Other') AND keyword not like ('%INCOMPLETE%') and keyword not like('%NO65%') and keyword not like('%6.5_WAIVER_APPROVED%') and target_milestone != 'Sierra' group by 1 order by 3,1
New sample query for installers:
select extract(week from b.whn) as 'week',
count(*) as 'fixed issues',
extract(year from b.whn)
from issue a, activity b
where a.issue_id = b.issue_id and
a.issue_status in ('closed', 'resolved', 'verified') and
b.whn >= str_to_date('2008-03-09 23:59:59','%Y-%m-%d %T') and
b.what = 'issue_status' and b.newvalue = 'resolved' AND
((component='nbi' ) or (component='installer' and subcomponent not in ( 'sunstudio' ) )) and
a.issue_type = 'defect' AND
version in ('6.5','6.1','6.0','5.5.1','5.5','5.0','4.2','4.1','4.0',
'3.6','3.5','3.4','3.4+dev','3.3.2','3.3.1','3.3.1+dev',
'3.3','3.3+dev','3.2.1','3.2','3.1','FFJ+2.0','FFJ+3.0+ea',
'FFJ+3.0','FFJ+4.0+ea','FFJ+4.0','S1S+4.1','S1S+5','current','Other') AND
keyword not like ('%INCOMPLETE%') and
keyword not like('%NO65%') and keyword not like('%6.5_WAIVER_APPROVED%') and
target_milestone != 'Sierra'
group by 1
order by 3,1
How to fix it
- In OPENED - bugs assigned currently to a component/subcomponent is always included only to this component/subcomponent
- In FILED - bugs count all bugs (including INVALID and DUPLICATE) as filed
- RESOLVED remain the same so far
Weekly Defect Priorities chart
- seems to be fine. Understandable
Bugs Filed vs. Resolved chart
I think numbers are calculated wrong for Filed vs Fixed graph: we simply count different sets for filed and fixed. Let me explain: For filed page says: 'Do not include issues that have resolution INVALID or DUPLICATE in the time of generating' But for resolved we count them. As the result we count less number of filed bugs, but _all_ resolved bugs, whatever the resolution was. Obviously this is a favor to resolved bugs number.
There can be two solutions:
1. Count all bugs (including INVALID and DUPLICATE) as filed
2. Do not count bugs resolved as INVALID and DUPLICATE as resolved. Both solutions are fine for me. Probably number 1 is preferrable as INVALID and DUPLICATE bugs require developer's time as well so we need to mention them.
