Client Connections

There are several ways to connect to Graylog API Security and run SQL queries, depending on your requirements.

Each connection method supports the same SQL dialect and the same TLS and user authentication options to protect your data.

Use JSON API

This is the easiest way to submit external queries but has some key limitations.

Benefits

  • REST interface requiring no client libraries
  • Accepts a single SQL statement (from POST data)
  • Supports complex statements including WITH and UNION ALL
  • Supports multiple statements (encoded as array)
  • Returns query results as JSON document

Limitations

  • Only string and number types supported
  • Not suitable for huge result sets
  • Simple error handling (empty document on failure)

Query when no authentication is configured:

Copy 
curl -X POST --user 'rob:' --data 'select count(*) as total from resurface.data.messages' http://localhost/ui/api/resurface/runsql

Query with basic authentication:

Copy 
curl -X POST --user 'rob:blah1234' --data 'select count(*) as total from resurface.data.messages' https://localhost/ui/api/resurface/runsql

In the previous two examples, curl converts the user parameter into a valid Authorization header. If you aren't using curl, you'll have to calculate the Authorization header by appending username:password and applying base64 encoding.

Query with Authorization header:

Copy 
curl -X POST -H "Authorization: cm9iOmJsYWgxMjM0" --data 'select count(*) as total from resurface.data.messages' https://localhost/ui/api/resurface/runsql

Use Trino Client Libraries

This is the most flexible way to submit external queries but requires more work to integrate.

Benefits

  • All client libraries are free and open-source
  • Exposes native types for numbers, dates, arrays, and maps
  • Easier iterating through very large datasets
  • Custom error handling supported

Limitations

  • Requires Trino JDBC, ODBC, Java, Python, Node, or R library
  • No automatic conversion to JSON
  • More difficult to integrate

Trino documentation provides a JDBC example to follow.

Start with a count query:

Copy 
select count(*) as total from resurface.data.messages

Use Common Database Tools

Trino works with DBeaver, dbt, DataGrip, Metabase, Tableau, Looker, Superset, and many other database and ETL tools. Some of these tools are preconfigured with Trino client libraries, but it's recommended to use the library version that matches the bundled Trino version.

Start with a count query:

Copy 
select count(*) as total from resurface.data.messages

Generate SQL

Now that you can connect and execute a basic count query, the obvious question is how to build SQL statements for more interesting cases.

While there are lots of examples shown in this documentation, API Security allows you to copy SQL statements for any data shown the web interface. This is typically easier than writing SQL by hand, especially since any relevant WHERE and GROUP BY clauses will be generated for you.

You'll find this Copy SQL function in the Share menu and in the Copy button displayed in most charts. You can then paste this SQL into your client or editor of choice.