KQL Query Quest (work in progress)

Your self-guide to crafting better queries. Uncover tips, tricks, and insights to level up your skills 🚀

KQL stands for "Kusto Query Language." It's a query language developed by Microsoft for use with Azure Data Explorer and other Microsoft services like Azure Monitor and Azure Log Analytics.

In simple terms, KQL is a language that allows you to ask questions and extract information from large datasets efficiently. It enables you to search, filter, and manipulate data to find specific answers to your queries.

For example, if you're analyzing server logs in a cloud environment, you can use this to write queries that help you find relevant information such as any errors that occurred, resource usage patterns, or trends over time.

In summary, KQL is a powerful tool for analyzing large datasets and extracting useful insights from them.

learn more: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query

---

---

• Counting Successful Requests

let startDateTime = datetime(2024-04-01);
let endDateTime = datetime(2024-04-04);
let successStatusCode = 200;
requests
| where timestamp >= startDateTime and timestamp <= endDateTime
| where resultCode == successStatusCode
| summarize Count = count() by bin(timestamp, 1d)
| order by timestamp asc

• Count the number of failed requests in the last hour

let agoDuration = 1h;
let failedStatusCode = 500;
requests
| where timestamp >= ago(agoDuration)
| where resultCode == failedStatusCode
| summarize Count = count()

• Count the number of failed requests in the last day

let agoDuration = 1d;
let failedStatusCode = 500;
requests
| where timestamp >= ago(agoDuration)
| where resultCode == failedStatusCode
| summarize Count = count()

• Number of requests per country/region of origin, including the use of Alias for return fields

requests
| summarize Qtde=count() by AreaOrigem=client_CountryOrRegion

• Pie chart with requests by country/region of origin

requests
| summarize Qtde=count() by CidadeOrigem=client_CountryOrRegion 
| render piechart

• Simple query with number of requests per city of origin requests

| summarize count() by client_City

• Query with number of requests per city of origin including the use of Alias for the requests return fields

| summarize Qtde=count() by CidadeOrigem=client_City 

• Pie chart with requests by city of origin

requests
| summarize Qtde=count() by CidadeOrigem=client_City 
| render piechart

• Simple query with number of requests per container

requests
| summarize count() by cloud_RoleInstance 

• Number of requests per container, including the use of Alias for return fields

requests
| summarize Qtde=count() by Container=cloud_RoleInstance 

• Number of requests and average response time per container, including the use of Alias for return fields

requests
| summarize Qtde=count(), TempoMedio=avg(duration) by Container=cloud_RoleInstance 

• Pie chart with requests per container

requests
| summarize Qtde=count() by Container=cloud_RoleInstance 
| render piechart

• Aggregates the total count of requests by client city and renders the result as a pie chart

requests
| summarize totalCount=sum(itemCount) by client_City
| render piechart

• Retrieves dependency telemetry data for the "GET Bolsas/Get" operation where the duration of the operation is greater than 150 milliseconds

dependencies 
| limit 1000 
| where operation_Name == "GET Bolsas/Get" and duration > 150 
| project operation_Name, timestamp, data, duration 

• Counting the total number of requests made, separating them into successful and failed ones. It also calculates the number of impacted users. The results are grouped by operation name, URL, and source, then shown in a pie chart based on the total number of requests

requests
| where 
//(operation_Name contains_cs "app-name-xxx" or operation_Name contains_cs "app-name-xxx")
and (url contains "ambiente-hml" or url contains "caogs")
| summarize TotalRequests = count(), 
            SuccessfulRequests = sum(iff(success == true, 1, 0)),
            FailedRequests = sum(iff(success == false, 1, 0)),
            ImpactedUsers = dcount(user_Id) by operation_Name, url, source
| order by TotalRequests desc
| render piechart

• Filters request data for the past 30 days and then summarizes it. It counts the total number of requests, separates them into successful and failed requests based on the success field, and groups the results by the operation name. The summarized data is then ordered based on the total number of requests in descending order and displayed in a table format

requests
| where timestamp >= ago(30d)
| summarize
    TotalRequests = count(), 
    SuccessfulRequests = countif(success == true),
    FailedRequests = countif(success == false)
    by operation_Name
| order by TotalRequests desc
| render table

• This query filters request data from the last 1 day and then summarizes it based on performance buckets. It counts the number of requests in each performance bucket and orders the results in ascending order based on the performance bucket. Finally, the summarized data is visualized as a pie chart

requests
| where timestamp > ago(1d)
| summarize count() by performanceBucket
| order by performanceBucket asc
| render piechart 

• This query filters request data from the 1 day hours and then calculates the total count of requests. It then categorizes the requests into different performance buckets based on their duration. After summarizing the count of requests in each performance bucket, it calculates the percentage of requests in each bucket relative to the total count. Finally, it projects the bucket names, count, and percentage of requests into a pie chart for visualization.

let dataSet = requests
| where timestamp > ago(1d);
let totalCount = toscalar(dataSet | count);
dataSet
| extend requestPerformanceBucket = 
    iff(duration <= 300, "0-300ms",
    iff(duration <= 600, "301-600ms",
    iff(duration <= 1500, "601ms-1.5s",
    iff(duration <= 2000, "1.501s-2s",
    iff(duration <= 4000, "2.001s-4s", ">4s")))))
| summarize Count = count() by requestPerformanceBucket
| extend Percentage = strcat(round(Count / totalCount * 100, 2), "%")
| project Bucket = requestPerformanceBucket, Count, Percentage
| render piechart

let AllAPIs = datatable(APIName:string) 
[
    "api-abc-qa",
    "api-abc-dev",
    "api-deploy-dev",
    "api-chdeploy-qa"
] | extend APIName = tostring(APIName);
let ConsumedAPIs =
    requests
    | where timestamp > ago(7d)
    | extend APIName = strcat(tostring(split(operation_Name, ";")[0]))
    | summarize
        TotalRequests = count(), 
        SuccessfulRequests = countif(success == true),
        FailedRequests = countif(success == false)
    by APIName;
AllAPIs
| join kind=leftouter (ConsumedAPIs) on APIName
| project APIName, TotalRequests = iff(isnotempty(TotalRequests), TotalRequests, 0), SuccessfulRequests = iff(isnotempty(SuccessfulRequests), SuccessfulRequests, 0), FailedRequests = iff(isnotempty(FailedRequests), FailedRequests, 0)