In the previous lesson you configured the diagnostic settings and everything started flowing into log-contoso-pro. Now there is data: HTTP requests from the bookings website, slow queries from db-reservas, access to kv-contoso-pro, logs from the containers in aks-contoso-operaciones and the activity log from both subscriptions. Storing it is worth nothing if nobody knows how to interrogate it, and that is exactly the point where many teams get stuck: they pay for the ingestion and never exploit the data.

This lesson teaches KQL — the Kusto Query Language — from scratch and step by step, always over real Contoso Airlines data. You will finish by using it to solve the complaint that opened the module: "I have paid and my boarding pass has not arrived". And you will learn to control the bill, because ingestion and retention in Log Analytics are the line item that surprises people most when the following month comes around.

1. What a Log Analytics workspace is
2. Workspace design: one or several, and RBAC
3. The tables you are going to find
4. KQL from scratch: filter, project and extend
5. Aggregating: summarize, bin() and time series
6. Combining and transforming: join, union, let, parse and mv-expand
7. The full investigation of a boarding pass that never arrived
8. Saved functions and log search alerts
9. Cross-workspace queries and data export
10. Table plans, retention and the three cost levers
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

A workspace is the container where logs are stored and queried: an Azure resource, in a specific region, with its own RBAC, its own retention and its own bill. Inside there are tables, each with a fixed schema of typed columns, and you query them with KQL, a read-only, pipeline-style language designed for enormous volumes of time series data.

log-contoso-pro lives in rg-contoso-seguridad-pro and is deliberately the single point of convergence. That is where the resource logs from the whole platform arrive, along with Application Insights telemetry (07-03), Container Insights data from AKS, the activity log and the Microsoft Defender for Cloud alerts from module 4. That convergence is what lets you write a single query that cross-references the passenger's HTTP request with the function's exception and with the queue message. If that data lived in four different workspaces, the investigation would be an exercise in copying and pasting between tabs.

The first architecture decision is how many workspaces to create:

Contoso uses a single production workspace. Before deciding, the argument against was access control: not everybody should see the kv-contoso-pro logs. The answer lies in the two access modes:

• Workspace context: whoever has permission on the workspace sees every table and every resource; that is what the central operations team needs.
• Resource context: the user queries from the resource's blade, or with resource(), and sees only the logs of the resources they have RBAC on. A developer with Reader on app-contoso-reservas-dev sees those and nothing else, even though they sit in the same workspace.

It is switched on with az monitor log-analytics workspace update --workspace-name log-contoso-pro --resource-group rg-contoso-seguridad-pro --set properties.features.enableLogAccessUsingOnlyResourcePermissions=true. With that option, permission on the resource governs and permission on the workspace stops granting universal access: the Contoso-Desarrollo group gets Reader on rg-contoso-reservas-dev and sees its own; Contoso-Operaciones gets the Log Analytics Reader role on the workspace and sees everything. A single workspace, permissions per resource: exactly the RBAC from module 4 applied to observability.

Two warnings. AzureDiagnostics is a wide table shared by many services, with columns of the field_s, field_d or field_b variety and a column limit that can cause data loss: always prefer the dedicated tables with --export-to-resource-specific true, as you did in 07-01. And the App* names are those of a workspace-based Application Insights resource: the telemetry lives in log-contoso-pro and is queried alongside everything else, which is precisely what makes section 7 possible.

A KQL query starts with a table and chains operators with the pipe character |. Each operator receives a table and returns another one.

Line by line:

1. AppServiceHTTPLogs — the source table. Always start from a specific table, never from search.
2. where TimeGenerated > ago(1h) — a time filter; it accepts 5m, 7d, 30d. It always goes first: it is the one that reduces the volume scanned and therefore the cost and the runtime of the query.
3. The successive where clauses filter by site and by status code: == is case-sensitive, =~ is not.
4. project selects columns and discards the rest, reducing what travels to the browser; order by ... desc sorts — without it the order is not guaranteed — and take 20 truncates the result.

The search "XR7742" operator looks for a piece of text across all tables: useful once, when you do not know where the data is, and extremely expensive because it scans everything. As soon as you know the table, use it directly. For its part, extend adds computed columns without losing the existing ones:

• between (datetime(...) .. datetime(...)) bounds an absolute window, so you can reconstruct an incident that is already closed. Other frequent time operators: startofday(now()), startofweek(), now()-2d.
• extend creates ResponseSeconds from milliseconds; the .0 forces decimal division.
• case(...) evaluates conditions in order and returns the first match; the last value is the "otherwise".

summarize is the operator that turns millions of rows into an answer.

• count() counts rows; countif(condition) counts only the ones that meet something, saving you a second query.
• avg() gives the average, which lies about latency: a handful of 20-second requests barely move the mean. percentile(DurationMs, 95) is the value below which 95% of requests fall: the honest metric, and the one that underpins the SLO from 07-01.
• dcount() counts distinct values approximately — a probabilistic algorithm, with an error of around 1% — which makes it blazingly fast over billions of rows; dcount(UserId, 4) raises the accuracy at the expense of resources.
• by Name groups by operation: one row per endpoint.

To see the evolution over time you need bin(), which rounds each timestamp down to a multiple of the interval:

bin(TimeGenerated, 5m) groups everything that happened in the same 5-minute slot; the result is a time series. render timechart draws it in the portal — barchart, columnchart, piechart and areachart also exist. And top 10 by P95 desc is the shorthand for order by plus take.

let declares reusable constants and subqueries — let window = 2h; or let apps = dynamic(["app-contoso-reservas-pro", "app-contoso-api-disponibilidad-pro"]);, then usable with where AppRoleName in (apps) — which makes long queries readable and is indispensable in the investigation in section 7. union stacks tables with different schemas — columns missing from one are left empty: union AppExceptions, ContainerLogV2 merges application exceptions and container output into a single timeline. Inside a union, $table returns which table each row came from and coalesce() takes the first non-empty value across several equivalent columns.

join cross-references two sets by a common key. Its types:

The default innerunique is the number one source of baffling results in KQL: it deduplicates silently and the counts come out lower than expected. Always write the type explicitly.

This query answers the business question with a precision no metric could ever give: which booking references were paid for and have no boarding pass. leftanti keeps exactly those left-hand rows with no match on the right.

Finally, parse extracts fields from a string without writing regular expressions:

The parse pattern reads literally: the leading * ignores whatever comes before, then it looks for the text flight=, captures up to seats= into Flight, and so on. If the message format changes, the capture returns empty instead of failing, so it is worth validating.

Its companion is mv-expand, which turns a field with several values — usually a nested JSON read with todynamic() — into several rows, one per element. A booking with three legs produces three rows, and that is how you can aggregate routes even though the data arrived nested; you will apply it in exercise 3.

This is the journey that opened the module. The passenger gives their booking reference, XR7742. Five chained steps.

Step 1: find the purchase and its operation identifier.

OperationId is the key to everything: it identifies the complete business operation and is propagated across components. You will see it in detail in 07-03.

Step 2: follow that operation across every component.

toscalar() reduces a subquery to a single reusable value, and the union sorted by time produces the complete timeline of that booking: the web request, the call to sql-contoso-reservas-pro, the write to cola-emision-tarjetas and whatever happened afterwards.

Step 3: check whether the function ever ran. It is enough to filter AppRequests by AppRoleName == "func-contoso-tarjetas-pro" and Name == "GenerarTarjetaEmbarque" with the same booking reference. If it returns no rows, the message was queued but nobody processed it and the problem is in the trigger; if it returns rows with Success == false, move on to step 4.

Steps 4 and 5: the error and its root cause.

make_set(column, N) collects up to N distinct values into a list: it reveals at a glance whether the failure affects only XR7742 or another two hundred bookings. If the exception is an access denied, the cause is one step away, in the Key Vault audit logs:

The real ending at Contoso: the PDF signing certificate in kv-contoso-pro had expired, the id-contoso-api-pro managed identity was getting an error when retrieving it, the function was failing after five retries and the message ended up in the dead-letter queue. Five queries, from complaint to root cause, without opening a single session on a server.

That journey should not be reinvented every time. A saved function turns a query into a new operator, with parameters, available to the whole team:

With the function saved under the "Contoso - Operations" category, anybody in the Contoso-Operaciones group types SeguirLocalizador("XR7742") and gets the whole journey. It is the difference between the investigation depending on Marta Ríos and it being done by whoever picks up the call first. These functions and the shared example queries are versioned in the contoso-infra repository, alongside the workbooks and the Bicep templates from module 5.

Any KQL query that returns rows can be turned into an alert. That is what lets you alert on things no platform metric can express:

Three decisions. A --window-size larger than the frequency allows for ingestion latency — data takes one to five minutes to become available, and a tight window generates false negatives. --evaluation-frequency 15m instead of 1m because these alerts are billed per rule and per frequency. And severity 2 with ag-equipo-contoso: five boarding passes not issued is a genuine problem, but it does not justify a text message in the middle of the night.

Even though Contoso uses a single workspace, there are two ways to reach outside it. workspace("log-contoso-millas").AppRequests points at another workspace by name or identifier and combines with union to query several at once; resource("/subscriptions/.../sites/app-contoso-reservas-pro") queries a specific resource's logs without naming the workspace, and is the natural way to work in resource context.

The workspace's data export sends whole tables continuously to a storage account or to Event Hubs, without writing code. Contoso exports AzureActivity and AppServiceHTTPLogs to stlagocontosopro, the bronce layer: seven-year compliance at cold storage prices and availability for Synapse (module 3) without paying for the ingestion again. The combination to internalize is that one: Log Analytics for the last 30-90 days, which is when investigation happens; cheap storage for the rest.

Here is the module's central warning. Ingestion and retention in Log Analytics is one of the bills that surprises people most in Azure. There is no warning at all: you switch on some debug logging during an investigation, you forget to switch it off, and thirty days later the Log Analytics line exceeds the compute line that generates it. Every table has a plan that fixes what you can do with it and how much it costs:

Retention, in addition, splits into two stretches: interactive (queryable immediately, more expensive) and long-term archive (much cheaper, up to 12 years, from which you have to "rehydrate" or query with search jobs). With that, you can already pull the three levers of cost, in order of impact:

1. What gets ingested. The dominant lever; debug and console logs are the big culprits. Transformations in the DCRs let you discard heartbeats, health probes and requests for static resources at ingestion time — they contribute nothing and they are half the volume.
2. On which plan. Moving ContainerLogV2 to Basic when it is only used to read the last few days cuts the bill for Contoso's most voluminous table dramatically.
3. How long it is kept. Retention is set per table, not just per workspace: AzureActivity at 365 days for compliance, AppTraces at 30.

The Usage table is free and says exactly where the money is going:

Quantity comes in megabytes, hence the division. The estimated cost is indicative — the real price depends on the plan and on the commitment — but it is enough to rank by impact. At Contoso, this query revealed that AppServiceConsoleLogs was 41% of the total volume and nobody was querying it. Swapping the summarize for by bin(TimeGenerated, 1d), DataType and rendering it as a columnchart also shows the exact day something took off. Applying the cut:

Finally, the workspace's pricing model: pay-as-you-go per gigabyte versus a commitment tier. From around 100 GB a day, committing to a fixed daily tier applies a notable discount, and from there it grows in steps. Contoso's rule: measure for three months with Usage, cut what nobody queries first, and only then commit capacity on the already optimized volume. Committing capacity on junk data is paying a discount for junk. A daily ingestion cap also acts as a safety net against a runaway logging loop, though you have to accept that once the cap is reached data stops being received until the next day: do not put one on the security tables.

• Not filtering by time up front, or using search in production. These are the two mistakes that cost most in performance: always put where TimeGenerated > ago(...) as the first operator and start from a specific table, because search scans them all.
• Leaving the join on its default. innerunique deduplicates silently and produces incorrect counts. Always write kind=.
• Alerting on a window equal to the frequency. Ingestion latency will make you lose events: the window must be larger.
• Confusing the average with a percentile. Average latency hides the long tail, which is what the passenger sees.
• Forgetting debug logging you switched on. The number one source of surprise bills: review Usage every month and put it in the calendar.
• Tip: use | take 10 while you are developing and remove it at the end; iterating over 10 rows is instantaneous. And prefer the dedicated tables (--export-to-resource-specific), which migrate out of AzureDiagnostics with a better schema and per-table plans.
• Tip: save as a function any query you have written twice. Investigation knowledge belongs in the workspace, not in somebody's history.

Exercise 1. Write a KQL query that, over the last week, returns for each operation on app-contoso-api-disponibilidad-pro the number of requests, the error percentage and the 95th percentile of the duration, showing only operations with more than 1,000 requests and a p95 above 800 ms. Explain every line.

Exercise 2. The monthly bill for log-contoso-pro has gone from 400 to 1,750 euros with nothing new deployed. Describe how to investigate it with KQL and propose four concrete measures, stating which lever each one pulls.

Exercise 3. Nuria Peña wants a weekly report with the five most booked routes and their average revenue, for the Contoso Miles project (centro-coste=CC-2077). The data is in custom events with a nested tramos field. Write the query and explain how you would deliver it on a recurring basis.

Solution 1:

The time filter goes first to reduce the volume scanned. The second where narrows to the API. summarize aggregates per operation with by Name, using countif so a second query is not needed and percentile instead of avg because the average hides the tail. extend computes the percentage with 100.0 to force decimals. The volume and latency filter goes after summarize, because it operates on columns that did not exist before; putting it earlier would raise an error. order by leaves the worst cases at the top.

Solution 2: you investigate with Usage, grouping by DataType and by bin(TimeGenerated, 1d) over 60 days and rendering as columns; the chart shows the exact day of the jump and which table caused it. It is usually one of three things: debug logging switched on during an investigation and forgotten, a new diagnostic setting deployed by the diagnostico-app-service assignment onto freshly created resources, or an exception loop generating thousands of traces per minute. Cross-check with AzureActivity to see what changed on those days. Measures: (1) switch off the diagnostic categories nobody queries — the "what gets ingested" lever, the highest impact one; (2) add a transformation in the DCR that discards health probes and heartbeats — the same lever, at the source; (3) move ContainerLogV2 to the Basic plan — the "which plan" lever; (4) lower the retention on AppTraces to 30 days while keeping AzureActivity at 365 — the "how long" lever. Plus a daily ingestion cap as a safety net, never on the security tables.

Solution 3:

mv-expand unfolds each leg into its own row and strcat composes the readable route. Recurring delivery: the query is saved as a function in log-contoso-pro, embedded in a parameterized workbook with the time range as a parameter, and that workbook is sent automatically every Monday. Automating the delivery is solved with an Azure Automation runbook or a Logic App — the comparison between the two options is exactly the subject of lesson 07-04. Tags on the resource involved: entorno, proyecto=contoso-millas, centro-coste=CC-2077 and propietario.

You now know what a Log Analytics workspace is and why log-contoso-pro is the single point where everything converges: resource logs, the activity log, application telemetry and security alerts, with the decisive advantage of being able to cross-reference them in a single query. You know the criteria for separating or unifying workspaces, and the piece that makes the single workspace viable — resource context versus workspace context, with enableLogAccessUsingOnlyResourcePermissions, so each team sees only its own with the RBAC it already has — plus the map of the tables you are going to find, with the recommendation to prefer the dedicated ones over AzureDiagnostics. Above all, you know KQL: filtering with where and the time operators, selecting with project, computing with extend and case, aggregating with summarize using count, countif, avg, percentile and dcount, building time series with bin() and drawing them with render, combining with union, let and the six kinds of join — with the warning about innerunique — and extracting structure with parse and mv-expand. You have applied it to the complete investigation journey that opened the module: from the complaint about booking reference XR7742 to an expired certificate in kv-contoso-pro, in five chained queries, and you have turned it into the shared SeguirLocalizador function so the investigation no longer depends on a single person. You have built a log search alert on top of a query, alerta-tarjetas-no-emitidas, and you know how to export data to stlagocontosopro to keep cheaply what you no longer investigate.

And you carry the money lesson, which is the one most often learned too late: the table plans Analytics, Basic and Auxiliary, interactive retention versus long-term archive, and the three levers — what gets ingested, on which plan and for how long it is kept — with the Usage table as the instrument for knowing where the money is going before touching anything, and a capacity commitment only after having cut. One gap remains. Every query in section 7 leaned on OperationId, on the AppRequests and AppDependencies tables, and on properties such as localizador that somebody had to emit from the code. That data does not appear by magic: it is generated by Application Insights, and that is what the next lesson is about — the telemetry model, instrumenting app-contoso-reservas-pro and func-contoso-tarjetas-pro, distributed correlation with the W3C Trace Context, the application map, sampling, and the custom telemetry that turns a confirmed booking into a queryable piece of data.