0

I created aggregations includes a terms aggregate with a sum aggregate for each term bucket and will sort on the sum value. This works fine. However if I add a nested terms aggregation which is the second commented block in the code snippet. It fails with this error message "{"type":"aggregation_execution_exception","reason":"Invalid aggregation order path [AggCPUTimeMS].}" and after I remove the "Order" line it works just fine. Is it not possible to sort the bucket by a sum if there is sub aggregation?

Another question is about the first commented block: what is the difference between "Order" and "BucketSort"? My guess is "Order" will sort the buckets and pick the top n while "BucketSort" will sort the buckets already picked, randomly or not.

 var response = client.Search<SearchResultBucket>(search => search .Size(0) .RequestConfiguration(r => r.DisableDirectStreaming()) .Aggregations(aggContainer => aggContainer .Terms("topLevelAggregation", termsAgg => termsAgg .Field(new Field("System.keyword")) .Size(5) .Aggregations(aggContainer => aggContainer .Sum("AggCPUTimeMS", sumAgg => sumAgg .Field(new Field("CpuTimeMilliseconds")) ) // .Sort(s => s // ) ) .Order(o => o.Descending("AggCPUTimeMS")) // .Terms("SubAggregation1", termsAgg => termsAgg // ) ) ) 

Mapping here:

{ "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043" : { "mappings" : { "dynamic_templates" : [ { "message_field" : { "path_match" : "message", "match_mapping_type" : "string", "mapping" : { "norms" : false, "type" : "text" } } }, { "string_fields" : { "match" : "*", "match_mapping_type" : "string", "mapping" : { "fields" : { "keyword" : { "ignore_above" : 256, "type" : "keyword" } }, "norms" : false, "type" : "text" } } } ], "properties" : { "@timestamp" : { "type" : "date" }, "@version" : { "type" : "keyword" }, "BatchResult" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "ClientAppName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "ClientHostname" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "ClientProcessId" : { "type" : "long" }, "CollectSystemDateUtc" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "CollectSystemTimeUtc" : { "type" : "date" }, "CpuTimeMilliseconds" : { "type" : "long" }, "CurrentVersion" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "DatabaseName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "Duration" : { "type" : "long" }, "EventName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "ExeDate" : { "type" : "date" }, "IncrementalId" : { "type" : "long" }, "LogicalReads" : { "type" : "long" }, "Owner" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "OwnerId" : { "type" : "long" }, "PhysicalReads" : { "type" : "long" }, "Qty" : { "type" : "long" }, "QueryHash" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "QueryPlanHash" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "QueryType" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "QueryTypeId" : { "type" : "long" }, "ResourceGroupID" : { "type" : "long" }, "ResourcePoolID" : { "type" : "long" }, "RowCount" : { "type" : "long" }, "ServerInstanceName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "ServerPrincipalName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "SessionID" : { "type" : "long" }, "SourceTableName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "SqlText" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 32766 } }, "norms" : false }, "System" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } }, "norms" : false }, "SystemId" : { "type" : "long" }, "TaskTime" : { "type" : "long" }, "Writes" : { "type" : "long" }, "geoip" : { "dynamic" : "true", "properties" : { "ip" : { "type" : "ip" }, "latitude" : { "type" : "half_float" }, "location" : { "type" : "geo_point" }, "longitude" : { "type" : "half_float" } } } } } } } 

document example:

{ "_index": "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043", "_id": "lDoxiYEBssAtKosd_uKJM", "_version": 1, "_score": 1, "_source": { "QueryType": "Regular", "SqlText": "BACKUP LOG @DbName TO DISK = @FilePath WITH INIT, NOSKIP, RETAINDAYS=1, NAME ='LogBackup', NO_COMPRESSION;\n\t\t\t", "LogicalReads": 417, "QueryTypeId": 1, "QueryHash": "0", "CollectSystemDateUtc": "2022.06.22", "DatabaseName": "FO8PRD", "ResourceGroupID": 280, "System": "Unknown", "SessionID": 3027, "@version": "1", "Writes": 8, "ClientAppName": "ServiceRunner", "ClientProcessId": 40792, "Owner": "CORE", "CollectSystemTimeUtc": "2022-06-22T02:02:50.293", "QueryPlanHash": "0", "Duration": 1133, "SystemId": 14, "Qty": 1, "@timestamp": "2022-06-22T02:15:45.711Z", "CpuTimeMilliseconds": 16, "EventName": "sp_statement_completed", "PhysicalReads": 99, "ResourcePoolID": 257, "TaskTime": 43216568, "CurrentVersion": "2.17.41", "RowCount": 8, "IncrementalId": 18894454167, "ClientHostname": "SPRC-015", "ServerInstanceName": "INSTANCE1", "ExeDate": "2022-06-16T23:00:00+10", "OwnerId": 3, "ServerPrincipalName": "Admin" } 
3
  • Could you share mappings + one exemple document
    – LeBigCat
    Jun 15 at 14:33
  • updated my question, please advice, thanks.
    – xiao
    Jun 22 at 2:23
  • Hmm for the first question you need to split aggregation in 2 parts. Your 2 sub agreg are on the same level so es wont know how to sort the second one. .Aggregations(aggContainer => aggContainer .Terms("topLevelAggregation" => sub agreg1) with order .Aggregations(aggContainer => aggContainer .Terms("SecondTopLevelAggregation" => sub agreg2) For the second question, i have no idea :)
    – LeBigCat
    Jun 24 at 10:58

0

Browse other questions tagged or ask your own question.