SQL Server / Cognite API v1 / Aggregate events
Back to Collection Items
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
-- Use this online tool to generate code from sample JSON: Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "fields": [
-- "subtype"
-- ],
-- "aggregate": "uniqueValues",
-- "filter": {
-- "startTime": {
-- "max": 80468070,
-- "min": 36316784
-- },
-- "endTime": {
-- "max": 41454407,
-- "min": 22256402
-- },
-- "activeAtTime": {
-- "max": 99761365,
-- "min": 19522733
-- },
-- "metadata": {},
-- "assetIds": [
-- 7366433780129810,
-- 4231159671616230
-- ],
-- "assetExternalIds": [
-- "laboris amet ex deserunt et",
-- "Lorem"
-- ],
-- "rootAssetIds": [
-- {
-- "id": 5301845202166120
-- },
-- {
-- "externalId": "eiusmod exercitation"
-- }
-- ],
-- "assetSubtreeIds": [
-- {
-- "id": 532532664250766
-- },
-- {
-- "externalId": "minim consequat"
-- }
-- ],
-- "dataSetIds": [
-- {
-- "externalId": "m"
-- },
-- {
-- "id": 7462419378685606
-- }
-- ],
-- "source": "proident",
-- "type": "incididunt anim reprehenderi",
-- "subtype": "Lo",
-- "createdTime": {
-- "max": 52541729,
-- "min": 71476601
-- },
-- "lastUpdatedTime": {
-- "max": 55306689,
-- "min": 35839070
-- },
-- "externalIdPrefix": "ad esse Ut consequat ut"
-- }
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'fields[0]', 'subtype'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'aggregate', 'uniqueValues'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.startTime.max', 80468070
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.startTime.min', 36316784
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.endTime.max', 41454407
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.endTime.min', 22256402
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.activeAtTime.max', 99761365
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.activeAtTime.min', 19522733
EXEC sp_OAMethod @json, 'UpdateNewObject', @success OUT, 'filter.metadata'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.assetIds[0]', 7366433780129810
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.assetIds[1]', 4231159671616230
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.assetExternalIds[0]', 'laboris amet ex deserunt et'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.assetExternalIds[1]', 'Lorem'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.rootAssetIds[0].id', 123
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.rootAssetIds[1].externalId', 'eiusmod exercitation'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.assetSubtreeIds[0].id', 123
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.assetSubtreeIds[1].externalId', 'minim consequat'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.dataSetIds[0].externalId', 'm'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.dataSetIds[1].id', 123
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.source', 'proident'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.type', 'incididunt anim reprehenderi'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.subtype', 'Lo'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.createdTime.max', 52541729
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.createdTime.min', 71476601
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.lastUpdatedTime.max', 55306689
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'filter.lastUpdatedTime.min', 35839070
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filter.externalIdPrefix', 'ad esse Ut consequat ut'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'content-type', 'application/json'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'api-key', '{{api-key}}'
DECLARE @resp int
EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://domain.com/api/v1/projects/{{project}}/events/aggregate', 'application/json', @json
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
END
GO
Curl Command
curl -X POST
-H "api-key: {{api-key}}"
-H "content-type: application/json"
-d '{
"fields": [
"subtype"
],
"aggregate": "uniqueValues",
"filter": {
"startTime": {
"max": 80468070,
"min": 36316784
},
"endTime": {
"max": 41454407,
"min": 22256402
},
"activeAtTime": {
"max": 99761365,
"min": 19522733
},
"metadata": {},
"assetIds": [
7366433780129810,
4231159671616230
],
"assetExternalIds": [
"laboris amet ex deserunt et",
"Lorem"
],
"rootAssetIds": [
{
"id": 5301845202166120
},
{
"externalId": "eiusmod exercitation"
}
],
"assetSubtreeIds": [
{
"id": 532532664250766
},
{
"externalId": "minim consequat"
}
],
"dataSetIds": [
{
"externalId": "m"
},
{
"id": 7462419378685606
}
],
"source": "proident",
"type": "incididunt anim reprehenderi",
"subtype": "Lo",
"createdTime": {
"max": 52541729,
"min": 71476601
},
"lastUpdatedTime": {
"max": 55306689,
"min": 35839070
},
"externalIdPrefix": "ad esse Ut consequat ut"
}
}'
https://domain.com/api/v1/projects/{{project}}/events/aggregate
Postman Collection Item JSON
{
"id": "aggregateEvents",
"name": "Aggregate events",
"request": {
"url": {
"host": "{{baseUrl}}",
"path": [
"api",
"v1",
"projects",
"{{project}}",
"events",
"aggregate"
],
"query": [
],
"variable": [
]
},
"method": "POST",
"header": [
{
"key": "api-key",
"value": "{{api-key}}",
"description": "An admin can create API keys in the Cognite console."
},
{
"key": "content-type",
"value": "application/json"
}
],
"description": "The aggregation API allows you to compute aggregated results on events \nlike getting the count of all events in a project or checking what are all the \ndifferent types and subtypes of events in your project, along with \nthe count of events in each of those aggregations. By specifying an additional \nfilter, you can also aggregate only among events matching the specified filter.\n\nThe default behavior, when you do not specify \nthe `aggregate` field in the request body, is to return the count \nof events.\n\nSetting `aggregate` to `uniqueValues` will return all unique values (up to a \nmaximum of 1000) and the count of each in the field specified in \n`fields: []`. Note that, currently, you can only request for unique \nvalues on a single field. Also, in the case of text fields, the values are \naggregated in a case-insensitive manner. For example:\n\n```\n{\n \"aggregate\": \"uniqueValues\",\n \"fields\": [ \"type\" ]\n}\n```\n\nwill return all unique 'types' in the events in your project.\n\nSimilarly,\n\n```\n{\n \"aggregate\": \"uniqueValues\",\n \"fields\": [ \"dataSetId\" ],\n \"filter\": {\n \"subType\": \"subtype_1\"\n }\n}\n```\nwill return all unique dataSetIds in events of subtype 'subtype_1'\n",
"body": {
"mode": "raw",
"raw": "{\n \"fields\": [\n \"subtype\"\n ],\n \"aggregate\": \"uniqueValues\",\n \"filter\": {\n \"startTime\": {\n \"max\": 80468070,\n \"min\": 36316784\n },\n \"endTime\": {\n \"max\": 41454407,\n \"min\": 22256402\n },\n \"activeAtTime\": {\n \"max\": 99761365,\n \"min\": 19522733\n },\n \"metadata\": {},\n \"assetIds\": [\n 7366433780129810,\n 4231159671616230\n ],\n \"assetExternalIds\": [\n \"laboris amet ex deserunt et\",\n \"Lorem\"\n ],\n \"rootAssetIds\": [\n {\n \"id\": 5301845202166120\n },\n {\n \"externalId\": \"eiusmod exercitation\"\n }\n ],\n \"assetSubtreeIds\": [\n {\n \"id\": 532532664250766\n },\n {\n \"externalId\": \"minim consequat\"\n }\n ],\n \"dataSetIds\": [\n {\n \"externalId\": \"m\"\n },\n {\n \"id\": 7462419378685606\n }\n ],\n \"source\": \"proident\",\n \"type\": \"incididunt anim reprehenderi\",\n \"subtype\": \"Lo\",\n \"createdTime\": {\n \"max\": 52541729,\n \"min\": 71476601\n },\n \"lastUpdatedTime\": {\n \"max\": 55306689,\n \"min\": 35839070\n },\n \"externalIdPrefix\": \"ad esse Ut consequat ut\"\n }\n}"
}
}
}
