Closed Bug 790845 Opened 12 years ago Closed 12 years ago

Daily report ADU numbers are wrong

Categories

(Socorro :: Webapp, task)

task
Not set
normal

Tracking

(Not tracked)

VERIFIED FIXED

People

(Reporter: lonnen, Assigned: adrian)

References

Details

For both by build date and by crash date:

/daily report results broken down by individual OS will display ADU counts per OS. However, when multiple check boxes are selected the largest ADU count of the OS's is displayed, rather than the sum of the ADU counts.

All: https://crash-stats.mozilla.com/daily?form_selection=by_version&p=Firefox&v[]=18.0a1&v[]=17.0a2&v[]=16.0b2&v[]=15.0.1&hang_type=any&os[]=Windows&os[]=Mac+OS+X&os[]=Linux&date_range_type=build&date_start=2012-09-07&date_end=2012-09-07&submit=Generate

Linux: https://crash-stats.mozilla.com/daily?form_selection=by_version&p=Firefox&v[]=18.0a1&v[]=17.0a2&v[]=16.0b2&v[]=15.0.1&hang_type=any&os[]=Linux&date_range_type=build&date_start=2012-09-07&date_end=2012-09-07&submit=Generate

OSX: https://crash-stats.mozilla.com/daily?form_selection=by_version&p=Firefox&v[]=18.0a1&v[]=17.0a2&v[]=16.0b2&v[]=15.0.1&hang_type=any&os[]=Mac+OS+X&date_range_type=build&date_start=2012-09-07&date_end=2012-09-07&submit=Generate

Win: https://crash-stats.mozilla.com/daily?form_selection=by_version&p=Firefox&v[]=18.0a1&v[]=17.0a2&v[]=16.0b2&v[]=15.0.1&hang_type=any&os[]=Windows&date_range_type=build&date_start=2012-09-07&date_end=2012-09-07&submit=Generate

OSX + Lin: https://crash-stats.mozilla.com/daily?form_selection=by_version&p=Firefox&v[]=18.0a1&v[]=17.0a2&v[]=16.0b2&v[]=15.0.1&hang_type=any&os[]=Mac+OS+X&os[]=Linux&date_range_type=build&date_start=2012-09-07&date_end=2012-09-07&submit=Generate
Sounds to me like this and bug 790844 could be related.
Assignee: nobody → sneethling
It seems the problems comes either from my SQL query in the middleware, or from the database itself. For example: 

SELECT product_name, 
version_string, report_date, report_count, adu, crash_hadu 
FROM home_page_graph_view 
WHERE product_name='Firefox' 
AND version_string='16.0a2'
AND report_date BETWEEN '2012-09-06'::date AND '2012-09-13'::date
ORDER BY report_date;

 product_name | version_string | report_date | report_count |  adu  | crash_hadu 
--------------+----------------+-------------+--------------+-------+------------
 Firefox      | 16.0a2         | 2012-09-06  |          766 | 37700 |      2.032
 Firefox      | 16.0a2         | 2012-09-07  |          626 | 32651 |      1.917
 Firefox      | 16.0a2         | 2012-09-08  |          546 | 27019 |      2.021
 Firefox      | 16.0a2         | 2012-09-11  |            2 | 23844 |      0.008
(4 rows)

---------------------------------------------------

SELECT product_name, version_string, report_date, sum(adjusted_report_count)::bigint as report_count, sum(adu)::bigint as adu, crash_hadu(sum(adjusted_report_count)::bigint, sum(adu)::bigint, avg(throttle)) as crash_hadu
FROM crashes_by_user_view 
WHERE product_name='Firefox' 
AND version_string='16.0a2'
AND report_date BETWEEN '2012-09-06'::date AND '2012-09-13'::date 
GROUP BY product_name, version_string, report_date
ORDER BY report_date;

 product_name | version_string | report_date | report_count |  adu   | crash_hadu 
--------------+----------------+-------------+--------------+--------+------------
 Firefox      | 16.0a2         | 2012-09-06  |          819 | 146742 |      0.558
 Firefox      | 16.0a2         | 2012-09-07  |          671 | 126501 |      0.530
 Firefox      | 16.0a2         | 2012-09-08  |          541 | 102691 |      0.527
 Firefox      | 16.0a2         | 2012-09-11  |            2 |   1311 |      0.153
(4 rows)

The results here should be exactly similar. The first query uses the home page graph's dedicated table, and the second the more complete crashes_by_user table. 

Josh, am I doing something wrong in those queries? Did I miss something in the second one, or do I do the aggregation wrong? Or could it be that there is a problem with the way data is inserted into the database?
Btw those results come from dev.db.
Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/2f621627b4bcb9a93e1f43ad4c5ed1b1b9c7983e
Fixes bug 790845 - Do not separate by OS or crash type when filtering.

https://github.com/mozilla/socorro/commit/04b86ba2fd5903c2de020a9da53c874a3cf1a18e
Merge pull request #822 from AdrianGaudebert/790845-fix-os-filter-in-daily-crashes

Fixes bug 790845 - Do not separate by OS or crash type when filtering.
Status: NEW → RESOLVED
Closed: 12 years ago
Resolution: --- → FIXED
Per phone conference, the issue with OS totals is due to a wrong assumption in the middleware about totals.  Adrian has already written draft code to fix this: https://github.com/mozilla/socorro/blob/master/socorro/external/postgresql/crashes.py#L155

However, this needs to wait on the fix for 7790844.
From testing against dev.db after applying Adrian's code, the totals for crashes and ADU is now totaling up and not just using the highest ADU. As for ratio, there still seems to be a problem there but, speaking to Adrian on IRC, it seems this is what is going to be fixed after the DB change.
I just did a query for Crashes Per User by Crash Data and it seems the 16 beta 2 data is still off if you look at the chart.
(In reply to Marcia Knous [:marcia] from comment #7)
> I just did a query for Crashes Per User by Crash Data and it seems the 16
> beta 2 data is still off if you look at the chart.

From https://crash-stats-dev.allizom.org/daily?p=Firefox&v[]=16.0b2 ?
Marcia, I hope you didn't check the producution version, as "fixed" doesn't mean it actually was pushed there. It doesn't make much sense to push this right now, as we need bug 7790844 fixed as well to have actually reasonable results.
Assignee: sneethling → adrian
Steps to verify:

load the daily report (Crashes per User)
for build_date and crash_date:
    get the total ADU count
    get the ADU count for each individual OS (seperate queries)
    verify that the individual ADU counts sum to equal the total ADU count
Verified on stage -- ADU counts and sum Totals are correct
Status: RESOLVED → VERIFIED
You need to log in before you can comment on or make changes to this bug.