Step 1 - Set up Ocelot/Slate Connector: Installing "Get Queries"

Modified on Fri, 4 Oct at 5:20 PM


Step 1 - Set up Ocelot/Slate Connector: Installing "Get Queries"



In order to use the Ocelot/Slate connector, there are several setup steps necessary. This article provides instructions on how to install Get Queries into Slate and capture the Web Services URL. 


This article is broken down into two sections:


You will need the IP addresses to set up the OcelotAPI User in step 9. Submit a support ticket requesting the IP addresses that need to be whitelisted in the Slate connector.



Installing Get Queries in Slate


Install the Get Queries Saved Query inside of Slate so that it can be utilized in Ocelot's connector. The "Get Queries" Saved Query is an important part of the automatic registration of Saved Queries into Ocelot.

  1. On the Navigation Bar, under the Database menu, select Database.

  2. On the Queries page, select New Query.
  3. Enter the following information:
    1. Name: Get Queries
    2. User: OcelotAPI
    3. Sharing: Select the checkbox.
    4. Folder: Ocelot Integration
    5. Type: Local
    6. Base: Custom SQL
  4. Select Save.

  5. In the Custom SQL field, enter the following SQL code:

    declare @CurrentTimeZone varchar(50)
    
     
    
    /* Until the current_timezone_id() function becomes available, we are reduced to
    
    using this conversion logic.
    
    See https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview
    
    for the conversion table if your timezome is not included in the logic.
    
    */
    
    declare @current_timezone nvarchar(50) = current_timezone();
    
     
    
    select  @CurrentTimeZone =
    
      case @current_timezone
    
        when '(UTC-04:00) Atlantic Time (Canada)' then 'Atlantic Standard Time'
    
        when '(UTC-04:00) Georgetown, La Paz, Manaus, San Juan' then 'SA Western Standard Time'
    
          when '(UTC-05:00) Eastern Time (US & Canada)' then 'Eastern Standard Time'
    
        when '(UTC-05:00) Indiana (East)' then 'US Eastern Standard Time'
    
          when '(UTC-06:00) Central Time (US & Canada)' then 'Central Standard Time'
    
          when '(UTC-07:00) Mountain Time (US & Canada)' then 'Mountain Standard Time'
    
        when '(UTC-07:00) Arizona' then 'US Mountain Standard Time'
    
        when '(UTC-08:00) Pacific Time (US & Canada)' then 'Pacific Standard Time'
    
        when '(UTC-09:00) Alaska' then 'Alaskan Standard Time'
    
        when '(UTC-10:00) Hawaii' then 'Hawaiian Standard Time'
    
        when '(UTC+10:00) Guam, Port Moresby' then 'West Pacific Standard Time'
    
        when '(UTC+13:00) Samoa' then 'Samoa Standard Time'
    
        else 'UTC'
    
        end
    
     
    
    select
    
        Q.[name] as [Query Name],
    
        dbo.toGuidString(Q.[id]) as [Query Guid],
    
        Q.folder as [Folder],
    
        L.name as [Query Base],
    
        A1.[grantee_token] as [Token],
    
        A1.[name] as [Token Name],
    
        A1.[permissions] as [Token Permissions],
    
        /* Return [update] in UTC to assist with disambiguating same named queries in 'All Queries' page.
    
        The first 'at time zone' adds a timezone to the datetime, the second 'at time zone'
    
        shifts the timezone.
    
        */
    
        convert(datetime, (Q.[updated] at time zone (@CurrentTimeZone)) at time zone 'UTC') as [Query Updated],
    
        /* Return [sql] to detect non-OOB tweaking to the GetQuery query.. */
    
        Q.[sql] as [Query Sql]
    
    from [query] Q
    
    inner join [access] A1
    
    on A1.[entity] = Q.[id]
    
    left join [lookup.base] L
    
    on Q.[base] = L.[id]
    
    where
    
    (
    
        /* Disallow archived queries even if their permissions
    
        have been reanimated */
    
        (Q.[archived] != 1)
    
        and
    
        /* Exclude quick queries */
    
        (Q.[folder] != 'Temp')
    
        and
    
        (
    
        /* Web Service Type must be 'json' */
    
        (isnull(Q.[config].value('(p[k = "share_type"]/v)[1]', 'varchar(max)'), 0) IN ('json'))
    
        )
    
        /* Grant must be active */
    
        and A1.[active] = 1
    
        /* Grantee name must be 'OcelotAPI' */
    
        and lower(A1.[name]) = 'ocelotapi'
    
        /* Grantee type must be 'token' */
    
        and A1.[grantee_type] = 'token'
    
        /* Permission 'service' must be selected */
    
        and A1.[permissions].value('t[1]', 'varchar(max)') in ('service')
    
    )
    
    orderby[QueryName],[Token]

  6. Select Save.

  7. Select the Edit Permissions button on the upper right corner of the page.

  8. On the Edit Permissions dialog page, select the Add Grantee link.

  9.   On the Edit Grantee dialog page, set the Type to User Token.
  10. On the Edit Grantee dialog page, the Name, Token, and Permissions fields will appear.
    1. In the Name field, enter OcelotAPI.
    2. Submit a support ticket to request the IP addresses to enter into the Allowed Networks field.
    3. In the Permissions field, select the Web Service box.
    4. Select Save
  11. On the Edit Permissions dialog page, the Ocelot Grantee will now be listed. Select Save.

Capture Web Service URL


  1. On the Edit Query page, select Edit Web Service, in the Service Type field, select JSON.

  2. On the Web Services dialog box, from the Service Account dropdown, select User Token - Ocelot.
  3. The URL field will appear. Copy the full URL and paste it into a safe location. The full URL will be used when registering the Get Queries query in the Ocelot Admin Portal.
    For information on registering the Get Queries query in the Ocelot Admin Portal, review the Step 4 - Set up Ocelot/Slate Connector: Registering Slate Web Service article.

  4. Select Close.


Continue on to Step 2 - Set up Ocelot/Slate Connector: Installing Contact Lookup Query to set up the Contact Lookup Query.


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article