1 In-app reports
2 API
- 2.1 Documentation
- 2.2 API Call
  - 2.2.1 Recommended programs
  - 2.2.2 Examples
3 SQL
- 3.1 FOLIO database
- 3.2 Metadb

FOLIO offers various options for extracting data. The data can be extracted via in-app reports, API queries and database queries. The following sections use examples to explain how this can be done in the eUsage application.

In-app reports

Currently, eUsage does not yet offer in-app reports.

API

An API, short for Application Programming Interface, is an interface that enables different software applications to communicate with each other and exchange data. The interface can also be used to extract specific data.

Documentation

At FOLIO, the API for each application is documented under https://dev.folio.org/reference/api/ . The API documentation has several chapters. The modules for eUsage can be found in the chapter Resource management > mod-erm-usage and Resource management > mod-erm-usage-harvester.

API Call

An API call is a request made by one software application to another application or server to request specific data or services. There are various ways to do this, for example using programming languages or special programs that even offer a graphical user interface for creating the API call.

Recommended programs

Postman is a very popular program with a graphical user interface that is widely used by the FOLIO community. Informations about the configuration for FOLIO can be found on the page https://folio-org.atlassian.net/wiki/x/NI5W
FOLIO Client is a simple python (3) wrapper over the FOLIO LMS system API:s. The library can be loaded via pip https://pypi.org/project/pyfolioclient/. The repository can be found at https://github.com/FOLIO-FSE/FolioClient . In combination with the pandas library, it offers an excellent way to extract and process data and export it in various formats.

Examples

Show all Counter reports with errors

{{base_url}}/counter-reports?query=(failedReason ="" NOT failedReason =="")&limit=10000

Show all Counter reports with specific error codes

{{base_url}}/counter-reports?limit=10000&query=(failedReason=="*3030*")

Please note: We plan to offer the subfields separately.

Show all usage data provider with errors

{{base_url}}/usage-data-providers?query=(reportErrorCodes ="" NOT reportErrorCodes =="")

Show all usage data providers that have specific error codes in the reports.

{{base_url}}/usage-data-providers?query=(reportErrorCodes== "*\"3032\"*")

SQL

Please note: You can query data from eUsage via SQL, but you should exercise caution when doing so with stored Counter reports. Counter reports can be very large, which can affect performance.

FOLIO database

Show all providers and their Counter reports with errors. Please note: Before executing the query, change the prefix of the schema in the FROM clause (line 10 and 12).

WITH error_reports AS (
SELECT
    jsonb_extract_path_text(usage_data_providers.jsonb, 'label') AS usage_data_provider,
    jsonb_extract_path_text(usage_data_providers.jsonb, 'hasFailedReport') AS has_failed_report,
    string_agg( DISTINCT 
    report_error_codes.jsonb, ', '
    ) errors,
    counter_reports.jsonb AS reports
FROM 
    de15_mod_erm_usage.usage_data_providers
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(usage_data_providers.jsonb, 'reportErrorCodes')) WITH ORDINALITY AS report_error_codes (jsonb)
    LEFT JOIN de15_mod_erm_usage.counter_reports ON jsonb_extract_path_text(counter_reports.jsonb, 'providerId') :: UUID = usage_data_providers.id
GROUP BY 
    usage_data_provider,
    has_failed_report,
    reports
)
SELECT 
    error_reports.usage_data_provider,
    error_reports.errors,
    jsonb_extract_path_text(error_reports.reports, 'yearMonth') AS year_month,
    jsonb_extract_path_text(error_reports.reports, 'release') AS release,
    jsonb_extract_path_text(error_reports.reports, 'reportName') AS report_name,
    jsonb_extract_path_text(error_reports.reports, 'failedAttempts') :: INTEGER AS failed_attempts,
    jsonb_extract_path_text(error_reports.reports, 'downloadTime') :: TIMESTAMPTZ AS download_time,
    jsonb_extract_path_text(error_reports.reports, 'failedReason') AS failed_reason
FROM 
    error_reports
WHERE 
    jsonb_extract_path_text(error_reports.reports, 'failedReason') IS NOT NULL 
ORDER BY 
    error_reports.usage_data_provider,
    jsonb_extract_path_text(error_reports.reports, 'yearMonth'),
    error_reports.errors

Metadb

WITH error_reports AS (
SELECT
    jsonb_extract_path_text(usage_data_providers.jsonb, 'label') AS usage_data_provider,
    jsonb_extract_path_text(usage_data_providers.jsonb, 'hasFailedReport') AS has_failed_report,
    string_agg( DISTINCT 
    report_error_codes.jsonb, ', '
    ) errors,
    counter_reports.jsonb AS reports
FROM 
    folio_erm_usage.usage_data_providers
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(usage_data_providers.jsonb, 'reportErrorCodes')) WITH ORDINALITY AS report_error_codes (jsonb)
    LEFT JOIN folio_erm_usage.counter_reports ON jsonb_extract_path_text(counter_reports.jsonb, 'providerId') :: UUID = usage_data_providers.id
GROUP BY 
    usage_data_provider,
    has_failed_report,
    reports
)
SELECT 
    error_reports.usage_data_provider,
    error_reports.errors,
    jsonb_extract_path_text(error_reports.reports, 'yearMonth') AS year_month,
    jsonb_extract_path_text(error_reports.reports, 'release') AS release,
    jsonb_extract_path_text(error_reports.reports, 'reportName') AS report_name,
    jsonb_extract_path_text(error_reports.reports, 'failedAttempts') :: INTEGER AS failed_attempts,
    jsonb_extract_path_text(error_reports.reports, 'downloadTime') :: TIMESTAMPTZ AS download_time,
    jsonb_extract_path_text(error_reports.reports, 'failedReason') AS failed_reason
FROM 
    error_reports
WHERE 
    jsonb_extract_path_text(error_reports.reports, 'failedReason') IS NOT NULL 
ORDER BY 
    error_reports.usage_data_provider,
    jsonb_extract_path_text(error_reports.reports, 'yearMonth'),
    error_reports.errors

Extract data