551081 - Create Custom Crash Report

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[ken kovash]

Attachment: example

I'd like to pull a crash report that looks like the attachment (getting it from http://crash-stats.mozilla.com is too time consuming).  It'll help us answer the question, "how have crashes been trending over the past four or five months and what does the pattern look like across major Fx versions?"

The attachment shows a date range of 11/1/09 through today.  If it's easy to pull a date range starting earlier than that (e.g. August or September), that'd be even better.

Thanks.

Comment 1

[ken kovash]

Any update here?  Thanks.

Comment 2

[ken kovash]

Can someone/anyone respond here?

Comment 3

[Laura Thomson :laura]

Ken, are you wanting this in 1.7 (end of April)?

Comment 4

[ken kovash]

(In reply to comment #3)
> Ken, are you wanting this in 1.7 (end of April)?

No, I was envisioning just a one-off, one-time export.

Comment 5

[Laura Thomson :laura]

OK, I'll see what we can do.

Comment 6

[K Lars Lohn [:lars] [:klohn]]

This looks like it should be simple, but its deceptive.  Our aggregate data materialized views are about to get truncated to 56 days.  So we'd have to go back to the original reports tables and regenerate the aggregates.  A first cut at this SQL is below.  I'm testing subparts now over a two week date range and it is taking a loooong time.  Executed over the whole date range may be really expensive. 

Since this is a "one off", I will try to get a version that uses the aggregation table before it gets truncated on Saturday...

select
    rep.report_date,
    rep.report_v30,
    adu.adu_v30,
    (rep.report_v30 as float / adu.adu_v30) * 100.0,
    rep.report_v35,
    adu.adu_v35,
    (rep.report_v35 as float / adu.adu_v35) * 100.0,
    rep.report_v36,
    adu.adu_v36,
    (rep.report_v36 as float / adu.adu_v36) * 100.0
from
    (select 
      date_trunc('day', r.date_processed) as report_date,
      sum(case when r.version like '3.0%' then 1 else 0 end) as report_v30,
      sum(case when r.version like '3.5%' then 1 else 0 end) as report_v35,
      sum(case when r.version like '3.6%' then 1 else 0 end) as report_v36
    from
      reports r 
    where
      '2009-11-01' <= date_trunc('day', r.date_processed + interval '8 hours')
      and date_trunc('day', r.date_processed + interval '8 hours') <= '2010-04-01'
      and r.product = 'Firefox'
    group by 1) as rep
        join 
            (select 
              ra.date as adu_date, 
              sum(case when ra.product_version like '3.0%' then ra.adu_count else 0 end) as adu_v30,
              sum(case when ra.product_version like '3.5%' then ra.adu_count else 0 end) as adu_v35,
              sum(case when ra.product_version like '3.6%' then ra.adu_count else 0 end) as adu_v36
            from
              raw_adu ra 
            where
              '2009-11-01' <= ra.date
              and ra.date <= '2010-04-01'
              and ra.product_name = 'Firefox'
            group by 1) as adu
                on rep.report_date = adu.adu_date
order by
    1

Comment 7

[K Lars Lohn [:lars] [:klohn]]

ok, I pasted the wrong version of that query:

select
    rep.report_date,
    rep.report_v30,
    adu.adu_v30,
    (cast(rep.report_v30 as float) / adu.adu_v30) * 100.0,
    rep.report_v35,
    adu.adu_v35,
    (cast(rep.report_v35 as float) / adu.adu_v35) * 100.0,
    rep.report_v36,
    adu.adu_v36,
    (cast(rep.report_v36 as float) / adu.adu_v36) * 100.0
from
    (select 
      date_trunc('day', r.date_processed + interval '8 hours') as report_date,
      sum(case when r.version like '3.0%' then 1 else 0 end) as report_v30,
      sum(case when r.version like '3.5%' then 1 else 0 end) as report_v35,
      sum(case when r.version like '3.6%' then 1 else 0 end) as report_v36
    from
      reports r 
    where
      '2009-11-01' <= date_trunc('day', r.date_processed + interval '8 hours')
      and date_trunc('day', r.date_processed + interval '8 hours') <= '2009-11-14'
      and r.product = 'Firefox'
    group by 1) as rep
        join 
            (select 
              ra.date as adu_date, 
              sum(case when ra.product_version like '3.0%' then ra.adu_count else 0 end) as adu_v30,
              sum(case when ra.product_version like '3.5%' then ra.adu_count else 0 end) as adu_v35,
              sum(case when ra.product_version like '3.6%' then ra.adu_count else 0 end) as adu_v36
            from
              raw_adu ra 
            where
              '2009-11-01' <= ra.date
              and ra.date <= '2009-11-14'
              and ra.product_name = 'Firefox'
            group by 1) as adu
                on rep.report_date = adu.adu_date
order by
    1;

Comment 8

[K Lars Lohn [:lars] [:klohn]]

Attachment: result of the query from Comment #7

please note that the percent column is *100

Comment 9

[K Lars Lohn [:lars] [:klohn]]

change the status of this bug if there's something wrong with the data..

Comment 10

[ken kovash]

Thanks Lars!