I semi-regularly find myself wanting to take one of the standard reports (today it’s Consolidated Pageviews) and go a little farther with it.
Rather than having to start from scratch, it would be an amazing 
if I could get from the report to its SQL to iterate.
It’s not built with SQL, but that’s ok, we can derive it …
Here’s the overall guide on Meta:
Specifically: discourse/app/models/concerns/reports/consolidated_page_views.rb at main · discourse/discourse · GitHub
It’s built using a Rails Model, ApplicationRequest , e.g.
[3] pry(main)> ApplicationRequest.last(10)
=> [#<ApplicationRequest:0x000055c8edcf99f8 id: 4798, date: Thu, 17 Nov 2022, req_type: "http_total", count: 3080>,
#<ApplicationRequest:0x000055c8edcf9ac0 id: 4799, date: Thu, 17 Nov 2022, req_type: "http_background", count: 1014>,
#<ApplicationRequest:0x000055c8edcf9bb0 id: 4800, date: Thu, 17 Nov 2022, req_type: "page_view_crawler", count: 539>,
#<ApplicationRequest:0x000055c8edcf9c78 id: 4801, date: Thu, 17 Nov 2022, req_type: "http_2xx", count: 1929>,
#<ApplicationRequest:0x000055c8edcf9d68 id: 4802, date: Thu, 17 Nov 2022, req_type: "http_4xx", count: 52>,
#<ApplicationRequest:0x000055c8edcf9e30 id: 4803, date: Thu, 17 Nov 2022, req_type: "http_3xx", count: 85>,
#<ApplicationRequest:0x000055c8edcf9f20 id: 4804, date: Thu, 17 Nov 2022, req_type: "page_view_anon", count: 40>,
#<ApplicationRequest:0x000055c8edcf9fe8 id: 4805, date: Thu, 17 Nov 2022, req_type: "page_view_logged_in", count: 148>,
#<ApplicationRequest:0x000055c8edcfa0d8 id: 4806, date: Thu, 17 Nov 2022, req_type: "page_view_logged_in_mobile", count: 134>,
#<ApplicationRequest:0x000055c8edd00a00 id: 4807, date: Thu, 17 Nov 2022, req_type: "page_view_anon_mobile", count: 2>]
The corresponding table you need is application_requests (but I don’t see this exposed in Data Explorer, maybe I missed it?)
The req_type(s) in this report are:
page_view_logged_in
page_view_anon
page_view_crawler
It is regular a sum and group by, so probably, you could start off with, on the Rails console:
ApplicationRequest.where(req_type:["page_view_logged_in", "page_view_anon", "page_view_crawler"]).where('date BETWEEN ? AND ?', '11/14/2022', '11/17/2022')
for example
or the SQL equivalent:
SELECT * FROM application_requests WHERE req_type IN (7, 8, 6) AND (date BETWEEN '11/14/2022' AND '11/17/2022')
Some of the aggregation is already done for you as the data is in a count column.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.