997214 - Summary of windows XP update data broken down by service pack

Status: RESOLVED WORKSFORME

(Mozilla Metrics :: Data/Backend Reports, defect)

Description

[Benjamin Smedberg]

We are discussing a compiler change that will bump our minimum supported version of Windows from XPSP2 to XPSP3.

Separately, I am looking at updating users of old versions back to the current version with an addon hotfix.

In order to know how many users might be affected by either of those actions, I'd like to have a report on these criterion/facets:

criterion: Firefox on Windows
faceted by Windows version including service pack
faceted by Firefox version

I don't see the service-pack data in the "ADI Daily" datasource in dataviz, and I don't think it's in the blocklist ping at all: all we have is OS_VERSION.

The service-pack information definitely *is* in the update ping; we added it in bug 668436 and so it looks like this:

https://aus4.mozilla.org/update/3/Firefox/31.0a1/20140415030203/WINNT_x86-msvc/en-US/nightly/Windows_NT%206.1.1.0%20(x64)/default/default/update.xml

So the versions from AUS would be "Windows NT 6.1.1.0 (x64)" which is "osname osversion.spmajor.spminor (arch)".

Is there a vertica datasource for the AUS counts I can use? Especially if the os/osversion/SP/arch data is already normalized out that would be great, but I can do that after aggregation if necessary.

Comment 1

[Anurag Phadke[:[e-mail]]]

bsmedberg:
I assume you want the ADIs and thereby the data from blocklist ping (addons.mozilla.org). AUS pings more than once and isn't an accurate source to know counts.

Here's a sample of the ADI ping:
2014-04-14:21:00:00 +0000	14/Apr/2014:21:00:00 +0000	SE	Sweden	57.266693	16.433304	09	telia.com	TeliaSonera AB	TeliaSonera AB	GET	/blocklist/3/%7Bec8030f7-c20a-464f-9b0e-13a3a9e97384%7D/28.0/Firefox/20140314220517/WINNT_x86-msvc/sv-SE/release/Windows_NT%206.1/default/default/13/24/1/	200	116409	-	Firefox	28	0	Windows 7	NULL_OS_MAJOR	NULL_OS_MINOR	Other	DNT:-	-	-	addons.mozilla.org	2014-04-14	addons.mozilla.org


we don't have this data in vertica, but we do have this data in HIVE. 

bsmedberg - you should have access to HIVE via peach-gw. Let me know if you need any help running those queries/getting the data.

-anurag

Comment 2

[Benjamin Smedberg]

Anurag, as I said above I don't think the ADI (blocklist) data has the information needed. The data you mention only has the OS major version but not the SP version.

AUS is the only datasource I know of which has the service pack data.

Comment 3

[Benjamin Smedberg]

bhearsum/rstrong: Anurag and I don't know which server logs we should be looking at. Do you know which server is being used for release updates back to Firefox 3.6?

Comment 4

[[e-mail] (:bhearsum)]

(In reply to Benjamin Smedberg  [:bsmedberg] from comment #3)
> bhearsum/rstrong: Anurag and I don't know which server logs we should be
> looking at. Do you know which server is being used for release updates back
> to Firefox 3.6?

If you can care about betas/releases, it's 100% AUS3. If you care about Nightly/Aurora it's a bit more complicated.

Comment 5

[Robert Strong (they/them - no direct email)]

Beyond AUS3 as bhearsum said I don't know.

Comment 6

[Benjamin Smedberg]

Got instructions from aphadke. Used this query on peach:

hive> INSERT OVERWRITE LOCAL DIRECTORY 'ausquery' SELECT domain, request_url, count(1) FROM v2_raw_logs WHERE domain = 'aus2.mozilla.org' OR domain = 'aus3.mozilla.org' AND ds >= '2014-04-10' GROUP BY domain, request_url;
...
Total MapReduce CPU Time Spent: 0 days 10 hours 55 minutes 47 seconds 540 msec
OK
Time taken: 598.569 seconds