730038 - [TRACKER] Need example queries for data to be used in nightly reports

Status: RESOLVED WONTFIX

(Socorro :: Data request, task)

Description

[Daniel Einspanjer [:dre] [:deinspanjer]]

- Nightly Reports / Rapid Release reports:

We need to get crashdata numbers by product (Firefox, Fennec, Thunderbird), by version and by channel (nightly, aurora, beta, release, esr) so we can show "Crashes/100 users" for d, d/d-7, d/d-28 and 3 month trend.

We also need those values broken down by OS (Windows, OS X, Linux), as we can see on the "Stability" section of Nightly Report

Comment 1

[Daniel Einspanjer [:dre] [:deinspanjer]]

One additional note:

in our previous system, we were getting the total number of crashes.  Since these queries will return the throttled number of crashes for release, it would be good if the query could take that throttling into account and deliver the adjusted number.  Otherwise, we'll need to also have a query to run to get the throttling number and perform that computation on our end.


Because we will be displaying data for several dates either as a comparison or as a trend, I am wondering whether it would be better to get a query we run once per day and use it to populate a datawarehouse on our end, or if we should just perform the queries directly on your database daily.

Comment 2

[Paula Clemente [:paula]]

Attachment: Nightly Report -> "Stability" and "Channel Uptake and Stability" sections

Comment 3

[Laura Thomson :laura]

Josh, can you help out here?

Comment 4

[[:jberkus] Josh Berkus]

Daniel, Paula,

We have some matviews which sort of correspond to what you want, but I think you should draw from the base fact and dimension tables so that changes I make in the future to the matviews won't break your stuff.  

The main dimension table is reports_clean (not "reports", which contains raw uncleaned data).  The table product_versions contains details about products and versions.  product_version_channels contains the canonical throttling info.   Thus:

SELECT product_versions.product_name, 
	product_versions.version_string,
	reports_clean.os_name,
	product_versions.build_type AS release_channel,
	reports_clean.date_processed::date as report_date,
	round(count(*) / product_release_channels.throttle) as adjusted_crash_count
FROM reports_clean
	join product_versions USING (product_version_id)
	join product_release_channels ON
		product_versions.product_name = product_release_channels.product_name
		AND product_versions.build_type = product_release_channels.release_channel
WHERE utc_day_is(date_processed, '2012-02-15')
GROUP BY product_versions.product_name, version_string, os_name, 
	build_type, date_processed::date, throttle;

Since this runs in around 10s for 1 day of data, I see no reason why you wouldn't simply pull it from Postgres.

Comment 5

[[:jberkus] Josh Berkus]

Sorry, I meant the main *fact* table is reports_clean.

Comment 6

[Robert Kaiser]

BTW, you probably want the same number like is being used for the Socorro front page graph or the /daily pages by default, i.e. one that only counts one half of hang pairs.

Comment 7

[Paula Clemente [:paula]]

Sorry but I need some help here. I'm getting weird results...

I tried the above query (added  "and product_versions.product_name = 'Firefox' ")
and all I got was this:
http://paula.pastebin.mozilla.org/1491472

Why am I getting all those "<Other>" values?


Also, when I try:
select distinct product_name from product_versions;
I got 5 rows, all with "<Other>" value.



I'm using the driver: postgresql-9.1-901.jdbc4.jar

Comment 8

[[:jberkus] Josh Berkus]

Paula,

Your pastebin expired, I can no longer access it.

Comment 9

[[:jberkus] Josh Berkus]

Paula,

Might I suggest that we schedule a time to do this live/interactively?

Find me on #breakpad.

Comment 10

[[:jberkus] Josh Berkus]

The problem is that Paula is using SQuirrelSQL, which is not fully PostgreSQL-compatible.  I've advised switching to pgAdmin (www.pgadmin.org).

Comment 11

[[:jberkus] Josh Berkus]

I'm making this bug a tracker and spawning new bugs off of it for database changes which need to be deployed.

Comment 12

[Lonnen :lonnen]

This particular kind of query is better sourced from the crash-ping and we're not interested in implementing it in crash-stats