Lesson 21 — KQL Assistance with Copilot
Security Copilot can generate, explain, refine and troubleshoot KQL for Microsoft Defender XDR and Microsoft Sentinel.
The assistance can reduce query-writing time and help analysts learn unfamiliar schemas, but a query can run successfully while using the wrong table, weak filter, unsafe join or misleading logic.
This lesson explains how to turn an investigation question into tested KQL while validating every table, field, operator and returned result.
What you will learn
This lesson begins Module 3 with a schema-first KQL assistance and validation workflow.
KQL assistance workflow
↓
Select Defender XDR or Sentinel
↓
Specify entities, time range and expected output
↓
Ask Security Copilot to generate or explain KQL
↓
Review every table, field, function and operator
↓
Run a small scoped test
↓
Inspect raw rows and compare with known evidence
↓
Refine filters, parsing, joins and output
↓
Check false positives, false negatives and missing telemetry
↓
Optimise performance
↓
Save or operationalise only after analyst approval
KQL assistance domains
| Domain | Evidence | Validation question |
|---|---|---|
| Question | Security hypothesis, entity, time range and expected evidence. | What are we trying to prove or disprove? |
| Product | Defender XDR or Sentinel portal, workspace and data source. | Where should the evidence exist? |
| Schema | Tables, fields, types and product availability. | Can the query access the required telemetry? |
| Logic | Filters, parsing, aggregation, joins and exclusions. | Does the query ask the intended question? |
| Results | Raw rows, counts, entities and timestamps. | What did the query actually return? |
| Validation | Portal comparison, known events, false positives and missing evidence. | Can the result support an investigation conclusion? |
Learning objectives
- Generate KQL from natural language.
- Explain and refine existing queries.
- Choose the correct Defender or Sentinel schema.
- Debug syntax and recognition errors.
- Validate joins, filters and dynamic parsing.
- Review performance and resource use.
- Approve results through source evidence.
What is KQL assistance?
KQL assistance uses Security Copilot to generate, explain, refine and troubleshoot Kusto Query Language queries.
Assistance is not autopilot
Copilot can accelerate query creation, but analysts remain responsible for schema, logic, scope and interpretation.
Defender Query assistant
Microsoft Defender advanced hunting includes a Query assistant that converts natural-language requests into KQL.
Sentinel natural-language KQL
Microsoft Sentinel supports natural-language KQL generation through Security Copilot in supported hunting experiences.
Defender and Sentinel are different
Queries can look similar while using different tables, schemas, workspaces and data availability.
Start with the investigation question
Write the exact security question before asking Copilot for KQL.
Specify the product
Tell Copilot whether the query is intended for Defender XDR or Sentinel.
Specify the time range
Use a clear start and end time, including time zone where relevant.
Specify the entity
Identify the user, device, mailbox, IP address, domain, file or resource in scope.
Specify the expected output
List the fields, grouping and evidence needed from the results.
Specify exclusions
State known administrative activity, test systems or expected traffic that should be handled carefully.
Choose the correct table
The table determines which events and entities the query can return.
Check table availability
Some advanced hunting tables depend on connected Defender products and licensing.
Use the schema reference
Confirm table names, column names and data types before running the query.
Use IntelliSense
Editor suggestions help identify valid tables, fields, functions and operators.
Do not trust invented fields
Copilot can propose columns that do not exist in the selected environment.
Recognition errors
Unrecognised tables, fields, functions or variables can produce recognition errors.
Syntax errors
Missing quotes, brackets, pipes or malformed functions can prevent query execution.
Semantic errors
A query can run successfully while answering the wrong question.
Empty results
No results can indicate no activity, the wrong time range, the wrong table or missing telemetry.
Too many results
Broad terms and large time windows can create noise that looks like widespread compromise.
Start small
Develop the query using a narrow time range and limited entity scope.
Use take for exploration
Return a small sample to understand the table before building complex logic.
Use count for prevalence
Count events to understand scale before retrieving every row.
Filter early
Place strong time and entity filters near the beginning of the query.
Project required fields
Return only the columns needed for the investigation.
Use exact matching where possible
Exact values reduce noise when stable identifiers are available.
Use has for token matching
Token-aware matching can be more precise and efficient than broad substring searches.
Use contains carefully
Contains can match many legitimate values and should be validated.
Case sensitivity
Select case-sensitive or case-insensitive operators deliberately.
Use stable identifiers
Prefer DeviceId, AccountObjectId, NetworkMessageId, AlertId and resource IDs.
Names are weaker identifiers
Usernames, hostnames, subjects and filenames can change or collide.
Use datetime filters
Apply explicit timestamp filters instead of relying only on default portal ranges.
Use let statements
Named values make indicators, time ranges and reusable subqueries easier to review.
Use summarize
Aggregate by user, device, process, domain or time period to reveal patterns.
Use bin
Time bucketing can reveal spikes, repetition and sequence.
Use project and extend
Shape the output and create readable calculated values.
Dynamic fields
Some tables store additional information in dynamic or JSON-like fields.
Inspect dynamic content first
Review sample values before extracting keys that might not exist consistently.
Parsing errors
Incorrect parsing can silently return null values or incomplete evidence.
Use joins cautiously
Joins can create false relationships and multiply rows.
Filter before joining
Reduce both sides before combining datasets.
Choose stable join keys
Join on immutable identifiers rather than names where possible.
Check join cardinality
Understand whether the relationship is one-to-one, one-to-many or many-to-many.
Use union correctly
Union combines similar rows but does not prove that events are related.
Use regex sparingly
Regular expressions can be powerful but expensive and difficult to validate.
Prefer simpler operators
Use exact, token and prefix matching before regex where practical.
Explain existing KQL
Ask Copilot to describe each stage, assumption and expected result.
Refine existing KQL
Ask Copilot to improve clarity, filters, field selection and performance.
Debug failed KQL
Provide the query and exact error message when requesting assistance.
Do not hide the error
The original error often reveals which table, field or function requires correction.
Validate with known evidence
Test the query against a confirmed incident or event.
Inspect raw rows
Review representative records before summarising the output.
Compare with the portal
Returned evidence should align with the original incident, alert or entity page.
Check false positives
Review legitimate administration, deployment, scanning and normal user activity.
Check false negatives
Ask which tables, devices, products or event types the query may miss.
Review time-zone handling
Confirm that query times align with the incident timeline.
Review ingestion delay
Recent events may not yet be available in all tables.
Review retention
Available history depends on the product, table, workspace and configuration.
Optimise query performance
Efficient queries reduce throttling and improve analyst productivity.
Review resource usage
Advanced hunting resource reporting can identify expensive queries.
Avoid unnecessary joins
Many investigations can be answered from one table with precise filters.
Avoid returning every column
Wide result sets reduce readability and can increase processing.
Save approved queries
Store validated queries with clear names, descriptions and owners.
Version query changes
Track changes to tables, fields, filters, joins and intended output.
Do not create detections too early
A hunting query should be tested and tuned before becoming a detection rule.
Custom detection considerations
Detection frequency, lookback, ingestion delay and duplicate alerts require careful design.
Threat Hunting Agent distinction
The Threat Hunting Agent supports broader end-to-end hunting, while Query assistant focuses on KQL generation.
Keep lesson boundaries clear
This lesson focuses on KQL assistance; full Defender and Sentinel hunting workflows follow in Lessons 22 and 23.
Document Copilot contribution
Record when Copilot generated, explained or modified the KQL.
Maintain KQL skills
Analysts still need enough KQL knowledge to recognise incorrect logic.
Final analyst validation
A query is trusted only after tables, fields, logic, results and interpretation have been verified.
Example KQL-assistance prompt
Requirements:
1. Use the appropriate advanced hunting table
2. Filter by immutable DeviceId where available
3. Return Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine and initiating-process fields
4. Look for encoded commands, download activity and execution-policy changes
5. Explain every operator and filter
6. Identify any fields that might not exist in all environments
7. Avoid unnecessary joins
8. Keep the query suitable for investigation, not automatic detection
After generating the KQL, provide a checklist for validating the returned rows.
Agent Foskett investigation: “The query ran perfectly…”
↓
The query executed without errors
↓
It returned 4,218 devices
↓
The result appeared to show widespread compromise
↓
Agent Foskett inspected the logic
↓
The query used contains “http” on all PowerShell command lines
↓
It matched legitimate update scripts, help text and internal repositories
↓
The query joined records using DeviceName
↓
Renamed and reimaged endpoints created duplicate relationships
↓
The time range covered 30 days instead of the two-hour incident window
↓
The query was rewritten with DeviceId, stronger indicators and precise time filters
↓
The revised result contained three devices
↓
Two were approved administration
↓
One matched the incident endpoint
↓
The first query was valid KQL
↓
Its investigation logic was wrong
KQL validation checklist
| Area | Question | Validation action |
|---|---|---|
| Product | Is this Defender XDR or Sentinel KQL? | Confirm portal, workspace and source. |
| Table | Does the table contain the required evidence? | Review schema and sample rows. |
| Fields | Do the fields exist and use the expected types? | Use schema reference and IntelliSense. |
| Time | Does the query use the correct incident window? | Apply explicit datetime filters. |
| Filters | Are string and entity filters precise? | Test narrow and broad variants. |
| Parsing | Do dynamic keys exist in representative records? | Inspect raw values before extraction. |
| Join | Are stable identifiers used? | Check keys and row multiplication. |
| Results | Do rows match known portal evidence? | Compare incidents and entity pages. |
| Performance | Is the query unnecessarily expensive? | Filter early and project required fields. |
| Conclusion | Do the results support the hypothesis? | Separate returned facts from interpretation. |
Key takeaways
- Microsoft Defender advanced hunting includes a Security Copilot Query assistant for natural-language KQL generation.
- Microsoft Sentinel supports natural-language KQL generation in supported Security Copilot hunting experiences.
- Defender and Sentinel queries can use different schemas and data sources.
- Generated KQL must be checked for valid tables, fields, functions and data types.
- A query can execute successfully while using incorrect logic.
- Small time ranges, stable identifiers and early filters improve accuracy and performance.
- Joins and broad string matching can create false relationships and excessive results.
- Known events and original portal evidence provide essential validation.
- Hunting queries should be tuned before becoming detections.
- Security Copilot accelerates KQL work but does not replace analyst KQL knowledge.
What Agent Foskett checked
- Defender schema
- Table availability
- DeviceId
- Time range
- String operators
- Join keys
- Duplicate rows
- Known administration
- Raw events
- Final hypothesis
Best practices
- Start with the question.
- Specify the product.
- Confirm the schema.
- Use stable identifiers.
- Filter early.
- Inspect raw rows.
- Test known events.
- Validate joins.
- Optimise before saving.
- Approve the conclusion.
Related Agent Foskett resources
Continue the Microsoft Security Copilot Academy
How do you generate KQL with Microsoft Security Copilot?
Microsoft Defender advanced hunting includes a Security Copilot Query assistant that converts natural-language security questions into KQL using the advanced hunting schema.
Security Copilot KQL for Microsoft Sentinel
Microsoft Sentinel supports natural-language KQL generation through Security Copilot in supported hunting experiences using Sentinel workspace data.
Validate AI-generated KQL
Analysts should verify tables, fields, types, time ranges, filters, parsing, joins, performance and returned evidence before using generated KQL in an investigation or detection.
