When running PaperCut on one of the supported external databases SQL queries may be run to find specific data or produce reports beyond the many standard reports available. Below is a collection of SQL select statements that may assist when writing a custom query.

Any Supported External Database (standard SQL)

Print logs

SELECT u.user_id, u.user_name, u.full_name AS user_full_name, p.server_name AS printer_server_name, p.printer_name, p.display_name AS printer_display_name, p.location AS printer_location, pul.printer_usage_log_id, pul.usage_date, pul.usage_day, pul.used_by_user_id, pul.charged_to_account_id, pul.usage_cost, pul.usage_allowed, pul.printer_id, pul.job_id, pul.document_name, pul.client_machine, pul.total_pages, pul.total_sheets, pul.copies, pul.paper_size, pul.paper_height_mm, pul.paper_width_mm, pul.printer_language, pul.document_size_kb, pul.denied_reason, pul.duplex, pul.gray_scale, pul.printed, pul.cancelled, pul.refunded, pul.assoc_with_account_id, pul.total_color_pages, pul.color_pages_estimated, pul.job_type, pul.invoiced, pul.job_comment, pul.protocol, pul.original_usage_cost, pul.refund_status, pul.refund_request_id
  FROM tbl_printer_usage_log pul
  JOIN tbl_printer p ON pul.printer_id = p.printer_id
  JOIN tbl_user u ON pul.used_by_user_id = u.user_id;

Database Specific (uses specific functions)

Summary by month by department

Microsoft SQL Server

SELECT u.department "Department", Month(pul.usage_date) "Month", SUM(pul.usage_cost) "Cost", COUNT(pul) "Jobs", SUM(pul.total_sheets) "Sheets", SUM(pul.total_pages) "Pages", SUM(pul.total_color_pages) "Color Pages", SUM(pul.total_pages - pul.total_color_pages) "Grayscale Pages"
  FROM tbl_printer_usage_log pul
  JOIN tbl_user u ON pul.used_by_user_id = u.user_id
  GROUP BY u.department, Month(pul.usage_date)
  ORDER BY u.department, Month(pul.usage_date);

PostgreSQL

SELECT u.department "Department", date_part('month', pul.usage_date) "Month", SUM(pul.usage_cost) "Cost", COUNT(pul) "Jobs", SUM(pul.total_sheets) "Sheets", SUM(pul.total_pages) "Pages", SUM(pul.total_color_pages) "Color Pages", SUM(pul.total_pages - pul.total_color_pages) "Grayscale Pages"
  FROM tbl_printer_usage_log pul
  JOIN tbl_user u ON pul.used_by_user_id = u.user_id
  GROUP BY u.department, date_part('month', pul.usage_date)
  ORDER BY u.department, date_part('month', pul.usage_date);