Wish list: SQL for canned reports

[ganncamp (G Ann Campbell)]

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.

[merefield (Robert)]

It’s not built with SQL, but that’s ok, we can derive it …

Here’s the overall guide on Meta:

Admin Dashboard Report Reference Guide

Looking for information about the Admin Dashboard Reports? You’re in the right place! This is a reference guide for describing how the Admin Dashboard Reports function, the data they’re displaying, the corresponding Data Explorer SQL queries, and where to find the Ruby code for each report. Admin Dashboard Reports This post is a Wiki! Feel free to add information here you feel may be helpful for other admins, moderators, or developers using Discourse Reports. Dis…

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.