574711 - Add "Install Age" to daily .csv reports

Status: VERIFIED FIXED

(Socorro :: General, task)

Description

[chris hofmann]

This would be useful for analyzing first time installs and migration bugs as users move up to new releases, and for overall release trends as in
https://bugzilla.mozilla.org/show_bug.cgi?id=411424#c32

lets add as the 26th value on the end of the current format which is

1 signature
2 url
3 uuid_url
4 client_crash_date
5 date_processed
6 last_crash
7 product
8 version
9 build
10 branch
11 os_name
12 os_version
13 cpu_name
14 address
15 bug_list
16 user_comments
17 uptime_seconds
18 email
19 adu_count
20 topmost_filenames
21 addons_checked
22 flash_version
23 hangid
24 reason
25 process_type

Comment 1

[chris hofmann]

we added app_notes as #26, so this will need to be #27.

wonder if we can get this into the next socorro release?

it would help in fingerprinting common issues related to new installations and updates and in some analysis to figure out how widespread the "duplicate report" problem is using the .csv files an algorithm based on the suggestion in https://bugzilla.mozilla.org/show_bug.cgi?id=579136#c31

Comment 2

[chris hofmann]

if we can get this into the .csv I could do some analysis on how frequently we are seeing these kind of dups in the processed reports.

I can work from "install age" to get started.

Comment 3

[[:jberkus] Josh Berkus]

It looks like we don't have any origin IP address information in PostgreSQL.  Does this data exist somewhere else?

Knowing the source IP of the crash would make deduping a lot more determinative. Any other method will involve a significant number of false positives.

Comment 4

[[:jberkus] Josh Berkus]

Please ignore the last comment.  Due to too many browser tabs, I put it on the wrong bug.

Comment 5

[Robert Helmer [:rhelmer]]

Attachment: add install_age to csv reports

Tested on staging (with "--day=2011-01-12" since we haven't had new data from PHX yet).

Also on github:
https://github.com/rhelmer/socorro/commit/db5df1f1ac299558aafc3195899f4024dea84d19

Comment 6

[chris hofmann]

lars, as part of the review can you assess risk of pushing this independent of a full socorro release?  looks like a simple addition of data that already comes from the db, and involves no processor changes or post processing computation.

we should push this as an update to the night cron script if it's the equivalent of asking IT to run some extra SQL to give us a one-off report.

Comment 7

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

Comment on attachment 507297 [details] [diff] [review]
add install_age to csv reports

this looks fine to me

Comment 8

[Robert Helmer [:rhelmer]]

Committed r2901

Comment 9

[Laura Thomson :laura]

(In reply to comment #6)
> lars, as part of the review can you assess risk of pushing this independent of
> a full socorro release?  looks like a simple addition of data that already
> comes from the db, and involves no processor changes or post processing
> computation.
> 
> we should push this as an update to the night cron script if it's the
> equivalent of asking IT to run some extra SQL to give us a one-off report.

As part of improving the stability of Socorro, we will not in future apply patches to production.  Also note that in PHX the code on boxes is not a svn checkout: it's formally released software.

That said, we can roll a release for this bug (would be 1.7.6.1) - just want to check how important it is to you before we start the tag/release/QA cycle.  We are blocked until next week by not having a staging environment at all.

Comment 10

[chris hofmann]

>  just want to check how important it is to you before we start the tag/release/QA cycle.

very important.  it will help to give us the first insights into the breadth and depth of the duplicate problem.

Comment 11

[Laura Thomson :laura]

Ok, then once we have a stage setup next week we'll roll a 1.7.6.1.

Comment 12

[chris hofmann]

all I need really to start the analysis is a sample of one days worth of data.  one day's worth of data would give us some insight into the distribution and frequency of incoming duplicate reports.

aravind used to be able to run this script by hand, or by cron.  do we still have the ability to to that in the PHX set up?

if we do, all that's needed is to:
- get a copy of the newly updated script over to the production server
- run the script and redirect the output of the sql commands to somewhere.
- get me a copy of this output file.

that would save having to do a full blown 1.7.6.1 release, and its more like the suggestion in comment 6.  the difference is that the script wouldn't be put into place to run every night on the cron job.

Comment 13

[Robert Helmer [:rhelmer]]

(In reply to comment #12)
> all I need really to start the analysis is a sample of one days worth of data. 
> one day's worth of data would give us some insight into the distribution and
> frequency of incoming duplicate reports.
> 
> aravind used to be able to run this script by hand, or by cron.  do we still
> have the ability to to that in the PHX set up?
> 
> if we do, all that's needed is to:
> - get a copy of the newly updated script over to the production server
> - run the script and redirect the output of the sql commands to somewhere.
> - get me a copy of this output file.
> 
> that would save having to do a full blown 1.7.6.1 release, and its more like
> the suggestion in comment 6.  the difference is that the script wouldn't be put
> into place to run every night on the cron job.

Socorro devs have enough (read-only) access to do this kind of thing now, so I could do this on Monday. Would you mind filing a bug specifically for this one-off so I don't forget? If you need it more urgently (e.g. before Monday) please make sure the bug says so.

Also, you do want this to go into the next release and be part of the regular report, right?

Comment 14

[chris hofmann]

> Socorro devs have enough (read-only) access to do this kind of thing now, so I
> could do this on Monday. Would you mind filing a bug specifically for this
> one-off so I don't forget? If you need it more urgently (e.g. before Monday)
> please make sure the bug says so.
>

any time next week would be great, I'm on and off line traveling next time but will have some down time to start looking at the data in between if we can generate it.

filed https://bugzilla.mozilla.org/show_bug.cgi?id=629972
 
> Also, you do want this to go into the next release and be part of the regular
> report, right?

yeah, once we get a peek at one set of data and some number about breath and depth of the dup problem I'm sure there will be more questions that rise,. so we want it to become part of the regular reporting as soon as possible, but that is slightly lower priority.

Comment 15

[Laura Thomson :laura]

Thanks Rob.

Comment 16

[Robert Helmer [:rhelmer]]

Just noticed that I broke the unittests, they needed install_age added:
Committed r2904

Comment 17

[chris hofmann]

Attachment: sample of dups with matching install time resovlved to minute

haven't had a chance to look in-depth at the data, but a few quick scans are turning up some interesting stuff.   even if we don't convert back to actual time of install and we just round off time since install in seconds to time since install in minutes it will make dups easier to spot visually.

attachment shows candidate dups at around install time 104,115,1513 minutes prior to crash for the signature MultiByteToWideChar, and can be further authenticated and sorted out by looking at firefox buildid and os version info.

thanks a lot for doing this rob.

Comment 18

[Robert Helmer [:rhelmer]]

Notes for QA:

This report is not currently being generated on staging, we ran it as a one-time event and chofmann confirmed in comment 17.

We should have these published in staging for testing purposes soon. Let me know if you'd like another one-off to verify.

Comment 19

[chris hofmann]

or, when 1.7.7 gets pushed the install_age field will be part of the daily .csv's in cm-fs01://data/security_group/crash_urls/DATE

Comment 20

[chris hofmann]

and the daily .csv files under https://crash-analysis.mozilla.com/crash_analysis/

Comment 21

[Stephen Donner [:stephend] Not actively reading bugmail]

(In reply to comment #18)
> Notes for QA:
> 
> This report is not currently being generated on staging, we ran it as a
> one-time event and chofmann confirmed in comment 17.
> 
> We should have these published in staging for testing purposes soon. Let me
> know if you'd like another one-off to verify.

Yes, please -- it'd be nice to verify this working before we ship tomorrow :-)

Comment 22

[Robert Helmer [:rhelmer]]

(In reply to comment #21)
> (In reply to comment #18)
> > Notes for QA:
> > 
> > This report is not currently being generated on staging, we ran it as a
> > one-time event and chofmann confirmed in comment 17.
> > 
> > We should have these published in staging for testing purposes soon. Let me
> > know if you'd like another one-off to verify.
> 
> Yes, please -- it'd be nice to verify this working before we ship tomorrow :-)

Sent privately to mbrandt.

Comment 23

[Matt Brandt [:mbrandt]]

Thx rhelmer, verified with by looking at the data in tab delimited csv file.