Create Custom Crash Report

RESOLVED FIXED

Status

RESOLVED FIXED
9 years ago
7 years ago

People

(Reporter: kkovash, Unassigned)

Tracking

Firefox Tracking Flags

(Not tracked)

Details

Attachments

(2 attachments)

(Reporter)

Description

9 years ago
Created attachment 431257 [details]
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.
(Reporter)

Comment 1

9 years ago
Any update here?  Thanks.
(Reporter)

Comment 2

9 years ago
Can someone/anyone respond here?

Comment 3

9 years ago
Ken, are you wanting this in 1.7 (end of April)?
(Reporter)

Comment 4

9 years ago
(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

9 years ago
OK, I'll see what we can do.
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
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;
Created attachment 437078 [details]
result of the query from Comment #7

please note that the percent column is *100
change the status of this bug if there's something wrong with the data..
Status: NEW → RESOLVED
Last Resolved: 9 years ago
Resolution: --- → FIXED
(Reporter)

Comment 10

9 years ago
Thanks Lars!
(Assignee)

Updated

7 years ago
Component: Socorro → General
Product: Webtools → Socorro
You need to log in before you can comment on or make changes to this bug.