Any Slate query can be routinely called as a web service or scheduled to push data into another system.

Calling a Slate query as a web service

🔑 Requires Administrator (All Access) Role

To generate an endpoint that can be called via web services:

1. Go to Queries / Reports.

2. Select New Query.

3. Configure the following settings:

   • Type: Configurable Joins

   • Category/Base: Select according to the data you want to export.

4. Select Save.

5. Add exports and filters as needed.

6. Select Edit Web Service.

Edit web service

Configure the following settings:

Custom parameters

If you wish to pass in values through the query string, add to the Parameters field using the following format:

<param id="variable_name" type="data_type" label="My Variable:"/>

The parameter id can be used in a formula by entering @variable_name.

• id: The variable name used in the query string, and SQL query with @

  • The following parameter IDs are reserved and should never be used: callback, base, run, query, user, identity, id, h, and output. You will notice the use of id, h, and output in the service endpoints. These parameters should be static and represent the following:

    • id: the query ID

    • h: a salted hash of the query ID and an additional authentication check

    • output: the service type.

• type: Parameter data type (Acceptable Parameter Types)

  • date

  • datetime

  • varchar

  • int

  • real

  • uniqueidentifier

  • bit

• label: Value display to the user in the query tool user interface

Service type

Select the appropriate service type:

• JSON

• XML

• Excel

• CSV/Google Sheets

• Disabled

Include NULLs

Select Include NULL Values to include NULLs in your exported data.

Select Save.

Edit permissions

Authentication is required for web services.

To add authentication:

1. Select Edit Permissions

2. Select Add Grantee.

3. From the Type list, select User Token or Username. If your query exposes sensitive data, you must select Username to keep your data secure.

4. If you select User Token, configure the following settings:

   • Name: Enter a descriptive name. This will appear in the Edit Permissions window alongside any other custom query permissions.

   • Token: When calling this web service, provide the Token using the h query string parameter.

   • Allowed Networks: If you want to limit the IP addresses that can call this web service, enter individual IP addresses or CIDR subnets .

   • Permissions: Select Web Service.
     

5. If you select Username, configure the following settings:

   • Name: Give it a descriptive name. This will appear in the Edit Permissions window alongside any other custom query permissions.

   • Username/Password: Enter a Username and a Password. These must be provided when the web service is called.

   • Token: When calling this web service, provide the Token using the h query string parameter.

   • Allowed Networks: If you want to limit the IP addresses that can call this web service, enter individual IP addresses or CIDR subnets.

   • Permissions: Select Web Service.

6. Select Save.

Exports and filters

To pass in query string parameters, such as dtstart and dtend, append a string like &dtstart=1/1/2000&dtend=1/1/2099 to the service.

If you prefer to export the data via JSON with a callback (also known as JSONP), you can pass a callback function name to the callback query string parameter.

The query may use the parameters by using the @ symbol with the parameter ID.

You can do this with a direct filter:

Or in a formula subquery:

Service endpoint

To locate your service endpoint:

1. Select the service type link (varies depending on your choice) next to Web Services.

2. Select a service account to display connection instructions.

The endpoint URL appears.

Call rate limit

Each database is rate limited to 300 requests and 300 seconds of web service request processing within any 5-minute interval.

Default encoding

If you need to change the default encoding (UTF-8) when calling a Slate query as a web service, add the encoding parameter to the call URL in the following format:

 &encoding=iso-8859-1

Pushing data to remote web services

1. Go to Queries / Reports.

2. Select the query.

3. Select Edit Query.

4. Select Schedule Export.

5. Configure the following settings:

   • Destination: Web Service (Remote)

   • Endpoint: Paste the endpoint

   • Headers: Add optional HTTP headers

   • Notification: You can opt to receive notifications for success, failure, and late delivery

   • Format: Select JSON or XML

   • Requested Delivery Window (Eastern Time): Select one of the available delivery windows

6. Select Save.

The query runs at the time of the delivery window and push the data to the endpoint.

Headers

All Web Service transactions take place across the Hyper Text Transfer Protocol (HTTP & HTTPS).

Part of this protocol is the optional inclusion of HTTP headers which can be used to, among many other uses, authenticate users and communicate query parameters.

In Slate, custom headers may also be defined for the web service. In the Headers configuration setting, the placeholder text displays the format in which a header can be added, i.e.:

 HEADER_VALUE

Multiple headers can be added by repeating the format for each header name/value pair.

Consult the documentation for the Web Service or API you are attempting to reach to determine which headers, if any, ought to be included with the Web Request.

Examples of frequently used HTTP headers

Authorization header using basic authentication

To produce an HTTP Authorization Header using basic authentication, create a string composed of your username, a colon(:) and your password for the remote Web Service. This string will then need to be encoded as Base 64 and included in an Authorization Header as follows:

 basic IamBase64hash

Authorization header using bearer authentication

To produce an HTTP Authorization Header using a bearer token. Simply include bearer as the authentication scheme and your bearer token:

 bearer I-AM-A-BEARER-TOKEN

Including an API key in an HTTP header

Each Web Service or API can choose to implement their own authentication structure but a common structure is to use API "Keys", essentially large cryptographically-generated numbers unique to each user.

These keys can be passed to the Remote Web Service or API in a number of ways but a common way is to include the API Key in the HTTP Headers.

The following is an example where the Remote Web Service is expecting an HTTP Header with the name of apiKey and the value of a user's API Key:

 1234API-KEY9876