In order to use the Ocelot/Slate integration, 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 integration.
Installing Get Queries in Slate
Install the Get Queries Saved Query inside of Slate so that it can be utilized in Ocelot's integration. The "Get Queries" Saved Query is an important part of the automatic registration of Saved Queries into Ocelot.
- On the Queries page, select New Query.
- Name: Get Queries
- User: OcelotAPI
- Sharing: Select the checkbox.
- Folder: Ocelot Integration
- Type: Local
- Base: Custom SQL
- Select Save.
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)', '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', 'varchar(max)') in ('service') ) orderby[QueryName],[Token]
Select the Edit Permissions button on the upper right corner of the page.
On the Edit Permissions dialog page, select the Add Grantee link.
- On the Edit Grantee dialog page, set the Type to User Token.
- On the Edit Grantee dialog page, the Name, Token, and Permissions fields will appear.
- In the Name field, enter OcelotAPI.
- Submit a support ticket to request the IP addresses to enter into the Allowed Networks field.
- In the Permissions field, select the Web Service box.
- Select Save
- On the Edit Permissions dialog page, the Ocelot Grantee will now be listed. Select Save.
Capture Web Service URL
- On the Edit Query page, select Edit Web Service, in the Service Type field, select JSON.
- On the Web Services dialog box, from the Service Account dropdown, select User Token - Ocelot.
- 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 quiery in the Ocelot Admin Portal, review the Step 4 - Set up Ocelot/Slate Integration: Registering Slate Web Service article.
- Select Close.
Continue on to to set up the Contact Lookup Query.