The custom reports API can be used to programmatically execute queries against the MedChat custom reports data warehouse.

Prerequisites

To call the custom reports API, first obtain your organization's OrgId and Access Token, as described in the API Authentication documentation.

Execute an Ad-hoc Query

API documentation for the execute ad-hoc query endpoint can be found here.

The call is a POST to https://medchatapp.com/analyticsapi/external/orgs/{orgId}/queries/adhoc, and the request body can be as simple as the following:

{
    "queryText": "YOUR KQL QUERY HERE",
    "limitDataFromDateTime": "2021-10-01T00:00:00Z"
}

Query Results

The results will be returned in one of two ways, depending upon whether an optional querystring parameter asDynamicList=true is included in the URI.

If asDynamicList=true, the results will be returned as an array of JSON objects, each with a named property for each column in the result set.

If asDynamicList=false or it is not provided, the results will be returned as an object with a collection of columns that contains the ordered column definitions, and a collection of rows that contains the rows and their ordered values. This result format might be more appropriate for some use cases.

Limiting Data to Query

A limitDataFromDateTime value, and optionally a limitDataToDateTime value, should be included with each request. These datetimes limit the universe of data that will be queried by date and time, resulting in better performance and a reduced likelihood of the request timing out.

Each transaction table defines a datetime field that will be used in conjunction with the limitDataFromDateTime/limitDataToDateTime values to limit the number of records in the data warehouse that will be queried.

As a general rule of thumb, use the limitDataFromDateTime/ limitDataToDateTime parameters as coarse-grained control to limit the amount of data to query, and use where conditions in the KQL query itself as finer-grained control to precisely select the specific records of interest.

For short-lived entities (like LiveChats, which are limited by the InitiatedDateTime, and last at most a few hours), a narrower window might be appropriate. For longer-lived entities (like SmsChats, which are limited by LastActivityDateTime, and can last months), a wider window is probably appropriate. If in doubt, err on the side of a wider window.

Pagination

The result set will be paged if the number of query results exceeds 1000.

In cases where the result set is paged, a response header x-continuation-token will be present on the response that can be used to retrieve the next page of results. For requests where the results were not requested as a dynamic list (see prior section), the continuation token will also be present as a property in the response body. The response for a paged result set will resemble the Response Body in the following example:

POST  https://medchatapp.com/analyticsapi/external/orgs/{orgId}/queries/adhoc

Request Body:
{
    "queryText": "LiveChats | where InitiatedDateTime > datetime(10/1/2021)",
    "limitDataFromDateTime": "2021-10-01T00:00:00Z"
}

Response Headers:
x-continuation-token: eyJTdG9yZWRRdWVyeVJlc3VsdE5hbWUiOiI0ZGVhOWI5MTk5N2Q0MGQyYjk1ZjgwOTYzZGJjNWMxMiIsIlNraXAiOjEwMDAsIlRvcCI6MTAwMCwiVG90YWxDb3VudCI6MjMxOH0=
x-total-count: 2318

Response Body:
{
    "primaryResult": {
        ...
    },
    "mayContainPhi": false,
    "continuationToken":"eyJTdG9yZWRRdWVyeVJlc3VsdE5hbWUiOiI0ZGVhOWI5MTk5N2Q0MGQyYjk1ZjgwOTYzZGJjNWMxMiIsIlNraXAiOjEwMDAsIlRvcCI6MTAwMCwiVG90YWxDb3VudCI6MjMxOH0=",
    "totalCount": 2318
}

To retrieve the next page of a paged result set, issue the same request as the original query, but include the continuationToken obtained from the prior response in the request body, as shown below:

POST  https://medchatapp.com/analyticsapi/external/orgs/{orgId}/queries/adhoc

Request Body:
{
    "queryText": "LiveChats | where InitiatedDateTime > datetime(10/1/2021)",
    "limitDataFromDateTime": "2021-10-01T00:00:00Z",
    "continuationToken": "eyJTdG9yZWRRdWVyeVJlc3VsdE5hbWUiOiJlMzE2MjIzMzNiZTE0ZGI3YmFmYTFjY2E1NjMzZTFiYyIsIlNraXAiOjEwMDAsIlRvcCI6MTAwMCwiVG90YWxDb3VudCI6MjMxOH0="
}

This process can be repeated for each page until the last page is returned. The last page's response will not include a continuationToken.