Search Logs - How to get a complete list for a given date range?

data & reporting
5

I don’t know SQL, but here is what ChatGPT told me when I asked how to query search logs for the past 3 months. Would this work (I don’t have admin access to try it out, but I would like to have a solid query to pass along to my admin).

To get all search log data for the past 3 months using Discourse Data Explorer, you can create a new query with the following code:

-- [params]
-- date :datetime
SELECT
  action_logs.id AS action_log_id,
  action_logs.created_at AS action_log_date,
  action_logs.action AS action_type,
  users.id AS user_id,
  users.username AS user_username,
  users.email AS user_email,
  user_actions.details AS search_query
FROM
  action_logs
JOIN
  user_actions ON user_actions.id = action_logs.action_id
JOIN
  users ON user_actions.user_id = users.id
WHERE
  action_logs.action = 'searched'
  AND action_logs.created_at >= :date - INTERVAL '3 months'
ORDER BY
  action_logs.created_at DESC;

In this query, we use the -- [params] syntax to define a parameter named date of type datetime. This parameter allows you to specify the date for which you want to retrieve the search log data. The query then uses this parameter to filter the search logs for the past 3 months from the specified date.

To use this query in Discourse Data Explorer, follow these steps:

  1. Go to your Discourse site’s admin dashboard.
  2. Click on “Data Explorer” in the left-hand menu.
  3. Click on “New Query” to create a new query.
  4. Give your query a name and description if desired.
  5. Copy and paste the above SQL code into the “Query” text area.
  6. Click on the “Save” button to save the query.

Once saved, you can then execute the query by clicking on its name in Data Explorer. A popup will appear asking you to enter the parameter value for date. Provide a date in the format YYYY-MM-DD (e.g., 2023-07-28) and click “Run Query.”

The query will retrieve all search log data for the past 3 months from the specified date, and the results will be displayed in a table format.