Tutorial / Cram Notes
Configuring custom scheduled queries allows security analysts to automate threat detection and improve their security posture by creating timely alerts based on specific criteria.
Introduction to Custom Scheduled Queries
Custom scheduled queries are created using KQL (Kusto Query Language), the powerful query language used by Azure Monitor Logs and Microsoft 365 Defender. These queries can be scheduled to run at regular intervals, ensuring that the latest data is analyzed and any potential threats are identified promptly.
How to Configure Custom Scheduled Queries
1. Accessing Microsoft Defender for Cloud or Microsoft 365 Defender
- In Microsoft Defender for Cloud: Go to the ‘Alerts’ section and select ‘Create a custom alert rule.’
- In Microsoft 365 Defender: Navigate to ‘Advanced hunting’ and choose ‘Custom detection.’
2. Writing the KQL Query
Create your KQL query based on the log data you wish to analyze. The query should be tailored to the specific patterns or behaviors indicative of a security threat or issue you are monitoring.
SigninLogs
| where ResultType == “50126” // Indicates a failed sign-in attempt due to invalid username or password
| summarize Count = count() by UserPrincipalName, bin(TimeGenerated, 5m)
| where Count > 5
3. Scheduling the Query
After crafting your KQL query, you must decide the frequency at which it should run. Common intervals include every 5 minutes, hourly, or daily, depending on the nature of the data and the threat you’re watching.
4. Configuring the Alert
Decide on the alert details:
- Alert name: Choose a descriptive name for the alert.
- Severity: Assign severity levels (High, Medium, Low, or Informational) based on the potential impact.
- Description: Provide a clear description of the alert for future reference.
- Remediation steps: Offer guidance on what to do when the alert is triggered.
5. Testing the Query
Before finalizing your schedule, test the query to ensure it returns the expected results and does not produce excessive false positives.
6. Deploying the Query
Save and enable the custom scheduled query to start monitoring your environment. The system will alert you when the conditions of the query are met.
Best Practices for Configuring Custom Scheduled Queries
- Granularity: Strive for a balance between granularity and performance; more frequent queries require more system resources.
- Query Optimization: Write efficient KQL queries to minimize performance impact and avoid timeouts.
- Thresholds: Set appropriate thresholds to reduce false positives.
- Alert Fatigue: Craft quality queries to prevent excess alerting, which could lead to alert fatigue.
- Maintenance: Regularly review and update your custom queries to adjust for changes in your environment or to address new threats.
Examples of Custom Scheduled Queries:
| Scenario | Query Example | Frequency | Severity |
|---|---|---|---|
| Detecting unusual login locations | SigninLogs | where CountryOrRegion !in (‘US’, ‘CA’) | Hourly | Medium |
| Identifying potential data exfil | NetworkCommunicationEvents | where ActionType == ‘FileDownloaded’ | Daily | High |
| Monitoring for privilege escalation | AuditLogs | where OperationName == ‘Add member to role’ | Every 5 minutes | High |
By configuring custom scheduled queries, security analysts can tailor their monitoring to the unique requirements of their environment, ensuring they are alerted to threats in a proactive and efficient manner. This bespoke approach to threat detection is crucial for maintaining a strong security posture and is a significant component of the knowledge base for anyone pursuing certification as a Microsoft Security Operations Analyst.
Practice Test with Explanation
True or False: In Microsoft Sentinel, you must use Kusto Query Language (KQL) to write custom scheduled queries.
- A) True
- B) False
Answer: A) True
Explanation: Custom scheduled queries in Microsoft Sentinel are written using Kusto Query Language (KQL).
Which of the following is needed to create a custom scheduled query in Microsoft Sentinel?
- A) A Log Analytics workspace
- B) The Microsoft Sentinel connector
- C) A predefined query template
- D) A valid storage account
Answer: A) A Log Analytics workspace
Explanation: A Log Analytics workspace is required to store the data that custom scheduled queries will analyze in Microsoft Sentinel.
True or False: Custom scheduled queries can only be run on a daily basis and cannot be configured to run at different intervals.
- A) True
- B) False
Answer: B) False
Explanation: Custom scheduled queries can be configured to run at various intervals, not just daily.
Which Azure role permissions are required to create custom scheduled queries in Microsoft Sentinel?
- A) Reader
- B) Contributor
- C) Sentinel Responder
- D) Sentinel Contributor
Answer: D) Sentinel Contributor
Explanation: The Sentinel Contributor role has the necessary permissions to create and manage custom scheduled queries in Microsoft Sentinel.
True or False: Alerts can be directly generated from custom scheduled queries in Microsoft Sentinel.
- A) True
- B) False
Answer: A) True
Explanation: Alerts can be generated based on the results of custom scheduled queries through analytics rule configurations in Microsoft Sentinel.
In Microsoft Sentinel, what is the maximum execution time for a custom scheduled query?
- A) 5 minutes
- B) 10 minutes
- C) 15 minutes
- D) There is no time limit
Answer: C) 15 minutes
Explanation: The maximum execution time for a custom scheduled query in Microsoft Sentinel is 15 minutes.
True or False: You can use custom functions within your custom scheduled queries in Microsoft Sentinel.
- A) True
- B) False
Answer: A) True
Explanation: Custom functions can be created and used within KQL to provide reusable query logic in Microsoft Sentinel.
What is the purpose of the “Query scheduling” option when configuring a custom scheduled query in Microsoft Sentinel?
- A) To determine the query’s runtime priority
- B) To specify the frequency at which the query should run
- C) To allocate more resources to the query
- D) To choose the time range of data the query should analyze
Answer: B) To specify the frequency at which the query should run
Explanation: The “Query scheduling” option allows you to specify how often the custom scheduled query should execute.
Which of the following actions can be taken in response to an alert generated by a custom scheduled query in Microsoft Sentinel?
- A) Log the incident
- B) Trigger automated responses
- C) Send an email notification
- D) All of the above
Answer: D) All of the above
Explanation: Microsoft Sentinel can log the incident, trigger automated responses, and send an email notification as actions in response to an alert from a custom scheduled query.
True or False: When a custom scheduled query is saved, it is automatically enabled to run according to its schedule.
- A) True
- B) False
Answer: B) False
Explanation: After saving a custom scheduled query, you must enable it to start running according to its defined schedule.
Which of the following would trigger an alert from a custom scheduled query in Microsoft Sentinel?
- A) The query result is empty
- B) The query result contains data that meets the alert criteria
- C) The query fails to run
- D) None of the above
Answer: B) The query result contains data that meets the alert criteria
Explanation: An alert would be triggered if the results of the custom scheduled query contain data that meet the defined criteria indicating a potential security issue.
How can you validate the syntax of your KQL query before deploying it in a custom scheduled rule?
- A) By running it in the Log Analytics workspace
- B) By using a KQL syntax checker tool
- C) By reviewing it with a colleague
- D) All of the above
Answer: D) All of the above
Explanation: You can validate the syntax of KQL by running it in the Log Analytics workspace, using a syntax checker tool, or having it reviewed by a knowledgeable colleague.
Interview Questions
What is Kusto Query Language (KQL)?
Kusto Query Language (KQL) is a query language used for querying Azure data services, such as Azure Log Analytics, Azure Security Center, and Azure Sentinel.
What are some benefits of using KQL?
KQL is a simple, intuitive, and easy-to-learn language that allows users to query, analyze, and visualize data in a flexible and efficient manner. It can be used to perform various operations, such as searching, filtering, aggregating, and joining data from multiple sources.
What are some key concepts in KQL?
KQL is based on a set of key concepts, such as tables, columns, functions, operators, expressions, and commands. These concepts are used to construct queries that perform specific actions on the data.
What are some basic query operations in KQL?
Some basic query operations in KQL include selecting data from tables, filtering data based on criteria, sorting and grouping data, and joining data from multiple tables.
What are some common data sources for KQL queries?
KQL queries can be used to analyze data from a wide range of sources, such as logs, metrics, events, and traces. Some common data sources include Azure Log Analytics, Azure Security Center, Azure Sentinel, and Azure Application Insights.
What is the syntax for a KQL query?
A KQL query is composed of various elements, such as keywords, operators, functions, and literals. The syntax of a KQL query is similar to that of SQL, with some differences.
What is the difference between a table and a view in KQL?
In KQL, a table is a collection of data that is stored in a tabular format, while a view is a virtual table that is created by querying one or more tables. Views can be used to simplify complex queries and to improve query performance.
What are some common functions in KQL?
KQL provides a wide range of built-in functions that can be used to manipulate and analyze data, such as aggregation functions, string functions, date and time functions, and mathematical functions.
How can KQL be used to visualize data?
KQL can be used to generate charts, tables, and other visualizations that can help users to better understand and analyze their data. Visualizations can be created using tools such as Azure Log Analytics, Power BI, and Excel.
How can I learn more about KQL?
There are various resources available to help you learn more about KQL, including online tutorials, documentation, and training courses. Microsoft also provides a community forum where users can ask questions, share best practices, and collaborate with others.
Great post on configuring custom scheduled queries for SC-200! Very informative.
Does anyone know if scheduled queries impact overall system performance?
Can anyone share best practices for setting up alerts with custom queries?
Appreciate the detailed walkthrough on query configurations!
I’m struggling with the KQL syntax for custom queries. Do you have any tips?
How do you integrate these scheduled queries with existing SIEM solutions?
What’s the difference between using Log Analytics and Azure Sentinel for scheduled queries?
Thanks for the post!