Dashboard Report - Consolidated API Requests

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Consolidated API Requests.

This report shows API usage statistics by date, tracking both regular API requests and user API requests. This report helps you monitor the API usage patterns on your Discourse instance over time. It distinguishes between regular API requests and user API requests, showing both separately along with the total combined count.

The data is sourced from the application_requests table which tracks different request types across the system. For API usage, we focus on request types 11 (regular API) and 12 (user API).

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-12-31

SELECT 
  ar.date,
  -- API requests
  SUM(CASE WHEN ar.req_type = 11 THEN ar.count ELSE 0 END) AS api,
  SUM(CASE WHEN ar.req_type = 12 THEN ar.count ELSE 0 END) AS user_api,
  -- Total API requests
  SUM(CASE WHEN ar.req_type IN (11, 12) THEN ar.count ELSE 0 END) AS total_api_requests
FROM 
  application_requests ar
WHERE 
  ar.date BETWEEN :start_date AND :end_date
GROUP BY 
  ar.date
ORDER BY 
  ar.date DESC

Parameters:

  • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.

Results

  • date: Date of the request counts
  • api: Count of regular API requests (req_type = 11)
  • user_api: Count of user API requests (req_type = 12)
  • total_api_requests: Sum of all API requests

What’s the difference between regular API and user API requests?

  • Regular API requests (req_type = 11): These are API calls made to the system that don’t require user authentication. They include public API endpoints, system integrations, or third party services accessing public data.
  • User API requests (req_type = 12): These are API calls made with user authentication, typically representing actions performed on behalf of specific users and require the user of an API key.

Example Results

date api user_api total_api_requests
2023-09-15 34,582 7,249 41,831
2023-09-14 32,104 6,893 38,997
2023-09-13 33,756 7,122 40,878
2023-09-12 35,122 8,562 43,684
2023-09-11 36,475 9,234 45,709
2023-09-10 12,563 3,298 15,861
2023-09-09 10,782 2,982 13,764
2023-09-08 33,892 7,456 41,348
2023-09-07 31,765 7,120 38,885
2023-09-06 32,451 7,321 39,772
5 Likes