Kusto/KQL: summarize by time bucket AND count(string) column

16.8k views Asked by At

I have a table of http responses including timestamp, service name and the http response code I want to query using KQL/Kusto.

My goal is to have a table that tells me "How many http responses of a certain type (2xx, 4xx etc) did a particular service have within the last 5 minutes over time"

I want to summarize the rows by a time bucket of 5min and the ResponseType (basically the response code class) as well - but I can't seem to make it work. When I add count(ResponseType) to the summarize clause, it returns the error message Function 'count' cannot be invoked in current context.

My KQL looks like this

InsightsMetrics 
| extend Tags = parse_json(Tags)
| extend Responsecode = tostring(Tags.["code"]) 
| extend ResponseType = strcat(substring(Responsecode, 0, 1), "XX")
| extend Service = tostring(Tags.["service"]) 
| where TimeGenerated >= now(-4h)
| where Namespace == "prometheus"
| where Name contains "traefik_service_requests_total"
| project TimeGenerated, Responsecode, Service, ResponseType
| summarize by bin(TimeGenerated, 5m), ResponseType

which returns data like this:

| TimeGenerated       | ResponseType | Service                                                  |
|---------------------|--------------|----------------------------------------------------------|
| 2020-10-01 10:25:00 | 3XX          | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |
| 2020-10-01 10:30:00 | 2XX          | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |
| 2020-10-01 10:30:00 | 2XX          | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |
| 2020-10-01 10:30:00 | 4XX          | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |

When I want something like this instead

| TimeGenerated       | ResponseType | count(ResponseType) | Service                                                  |
|---------------------|--------------|---------------------|----------------------------------------------------------|
| 2020-10-01 10:25:00 | 3XX          | 1                   | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |
| 2020-10-01 10:30:00 | 2XX          | 2                   | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |
| 2020-10-01 10:30:00 | 4XX          | 1                   | prod-service-internal-50f0bab542c7d81ed22e@kubernetescrd |
1

There are 1 answers

5
Slavik N On BEST ANSWER

All you have to do is replace

| summarize by bin(TimeGenerated, 5m), ResponseType

with

| summarize count() by bin(TimeGenerated, 5m), ResponseType, Service