+++

Courses on Kusto Query Language (KQL) Available on Pluralsight

1. Perform Basic Search Functions in Kibana 7 with Kibana Query Language (KQL)

   • Description: Analyzing, visualizing, and troubleshooting system and application logs are mandatory in a distributed application. This course will teach you how to write simple and effective queries using KQL and analyze logs for security vulnerabilities.

2. Kusto Query Language (KQL) from Scratch

   • Description: KQL, the Kusto Query Language, is used to query Azure's services. This course will teach you the basic syntax of KQL, then cover advanced topics such as machine learning and time series analysis, as well as exporting your data to various platforms.

3. Microsoft Azure Data Explorer - Advanced KQL

   • Description: In this course, you will learn advanced topics in the Kusto Query Language from within the Azure Data Explorer.

4. Kusto Query Language: Getting Started

   • Description: This course covers the fundamentals of the Kusto Query Language (KQL). You'll learn what KQL is, where it is used, and some of the terms and definitions associated with it.

5. Exploring Data in Microsoft Azure Using Kusto Query Language and Azure Data Explorer

   • Description: You will learn in detail about the data exploration service from Azure and how it integrates with other services to perform end-to-end data analytics. This course will provide you with the necessary skills and confidence as a data scientist. +++

+++

Kusto Query Language (KQL) Learning Resources

Kusto Query Language (KQL) is a powerful tool for exploring data, discovering patterns, identifying anomalies and outliers, and creating statistical models. Here are some key learning resources to help you master KQL:

General Training

• Pluralsight: KQL from scratch
• Kusto Detective Agency
• Tutorial: Learn common operators
• Tutorial: Use aggregation functions
• Tutorial: Join data from multiple tables
• Cloud Academy: Introduction to Kusto Query Language

Azure Data Explorer

• Tutorial: Create geospatial visualizations
• Data analysis in Azure Data Explorer with Kusto Query Language
• Free Pluralsight training: Azure Data Explorer

Real-Time Analytics in Microsoft Fabric

• Get started with Real-Time Analytics in Microsoft Fabric

Azure Monitor

• Tutorial: Detect and analyze anomalies using KQL in Azure Monitor
• Analyze monitoring data with Kusto Query Language
• Koenig: KQL for Azure Admins

Microsoft Sentinel

• Must learn KQL
• Udemy: Learn KQL for Microsoft Sentinel
• SC-200: Create queries for Microsoft Sentinel using Kusto Query Language (KQL)
• Infosec Train: Kusto Query Language (KQL) eLearning Training Program Online

Additional Tools

• KQL Query Generator
• KQL Overview

SC-200: Essential KQL Skills

Sample Toolkit Repository Structure

Below is a sample structure for a toolkit repository that an SC-200 certified Microsoft Security Operations Analyst might use:

/KQL_Toolkit
    /Queries
        /AzureMonitor
            anomaly_detection.kql
            log_analysis.kql
        /MicrosoftSentinel
            threat_hunting.kql
            incident_response.kql
        /ApplicationInsights
            performance_metrics.kql
            error_tracking.kql
    /LearningResources
        general_training.md
        azure_data_explorer.md
        real_time_analytics.md
        azure_monitor.md
        microsoft_sentinel.md
    /Documentation
        KQL_Syntax_Guide.pdf
        KQL_Best_Practices.pdf
        Common_Operators.md
    /Scripts
        automate_query_execution.ps1
        export_results_to_csv.ps1
    README.md

Recommended Queries

Anomaly Detection

let threshold = 10;
SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by Computer
| where count_ > threshold

Threat Hunting

SecurityEvent
| where EventID == 4625
| summarize failed_logins = count() by Account, Computer

Performance Metrics

requests
| where timestamp > ago(1h)
| summarize avg_duration = avg(duration) by cloud_RoleInstance

Incident Response

let timeRange = 1h;
SecurityEvent
| where TimeGenerated > ago(timeRange)
| where EventID == 4624
| summarize count() by Account

Error Tracking

exceptions
| where timestamp > ago(1h)
| summarize error_count = count() by problemId, cloud_RoleInstance

Best Practices

• Use specific table and column references to optimize query performance.
• Apply time filters early in the query to reduce data volume.
• Utilize summarize for aggregations and extend for calculated columns.
• Leverage the let statement for reusability and readability.
• Regularly review and format queries for maintainability.

For more detailed information and tutorials, refer to the resources listed above. Happy querying! +++

Why KQL is Crucial

KQL is the query language behind Microsoft Sentinel, Microsoft 365 Defender, and many other security tools. It allows you to:

• Hunt for Threats: Proactively search for indicators of compromise (IOCs) and malicious activity across your environment.
• Investigate Incidents: Deeply analyze security alerts, correlate events, and understand the scope of an attack.
• Monitor Security: Create custom dashboards, alerts, and reports to maintain continuous visibility into your organization's security posture.
• Automate Responses: Trigger automated actions based on specific security events, accelerating incident response.

Core KQL Skills for SC-200

1. Table Structure: Understanding the schema of tables (e.g., SecurityEvent, Syslog, OfficeActivity) in Sentinel and other security tools is fundamental.

2. Basic Operators:

   • where: Filter events based on specific criteria.
   • project: Select specific columns or fields.
   • summarize: Aggregate data (e.g., count events, distinct values).
   • join: Combine data from different tables.

3. Time-based Queries:

   • ago: Look back in time.
   • between: Filter within a time range.
   • bin: Group events into time intervals.

4. Advanced Functions:

   • parse: Extract structured data from text.
   • extend: Create new calculated columns.
   • mv-expand: Work with multi-valued fields.

5. Threat Hunting Techniques:

   • Look for anomalies: Unusual activity patterns.
   • Stack counting: Identify repeated events.
   • Rare events: Spot infrequent but potentially malicious occurrences.

Sample Toolkit Repository Structure

SC-200 Toolkit
├── Queries
│   ├── Sentinel
│   │   ├── Threat Hunting
│   │   │   ├── lateral_movement.kql
│   │   │   ├── data_exfiltration.kql
│   │   ├── Incident Response
│   │   │   ├── phishing_investigation.kql
│   │   ├── Security Monitoring
│   │   │   ├── failed_logins.kql
│   │   │   ├── high_privilege_activity.kql
│   ├── M365 Defender
│   │   ├── ... (similar structure)
│   ├── Defender for Cloud
│   │   ├── ... (similar structure)
├── Playbooks
│   ├── Sentinel Playbooks (for automated response)
│   ├── ... (for other tools)
├── Dashboards
│   ├── Sentinel Dashboards (custom visualizations)
│   ├── ... (for other tools)
├── Documentation
│   ├── KQL_cheat_sheet.md
│   ├── query_best_practices.md
│   ├── incident_response_guide.md

Explanation of Repository Structure

• Queries: Organized by tool and purpose (hunting, incident response, monitoring).
• Playbooks: Automated workflows triggered by Sentinel alerts.
• Dashboards: Visual representations of security data.
• Documentation: Reference materials, best practices, and guides.

Example KQL Query (Sentinel)

// Detect potential lateral movement using PsExec
SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4688 // Process creation
| where CommandLine contains "psexec" 
| summarize count() by AccountName, Computer, bin(TimeGenerated, 1h) 

Key Points

• Continuously Build Your Toolkit: Add new queries, playbooks, and dashboards as you encounter different threats.
• Share and Collaborate: KQL is a powerful language for security teams to share and build upon each other's work.
• Leverage Community Resources: Microsoft and the broader security community offer many KQL resources and examples.

Let me know if you'd like more specific query examples or a deeper dive into any of these KQL concepts!

To excel in the SC-200 exam (Microsoft Security Operations Analyst certification), proficiency in KQL (Kusto Query Language) is essential, especially for threat hunting in Microsoft Defender for Endpoint. Here’s a guide on the key KQL syntaxes and their usage:

Basic Query Structure

Simple Query

To start with a basic query to retrieve data from a table:

DeviceEvents
| take 10

This retrieves the top 10 records from the DeviceEvents table.

Filtering Data

Where Clause

To filter results based on conditions:

DeviceEvents
| where Timestamp > ago(7d) and ActionType == "LogonSuccess"

This retrieves records from the DeviceEvents table where the ActionType is "LogonSuccess" in the last 7 days.

Aggregating Data

Summarize

To aggregate data using functions like count(), avg(), sum(), etc.:

DeviceEvents
| where Timestamp > ago(7d)
| summarize count() by ActionType

This counts the number of events by ActionType in the last 7 days.

Sorting Data

Order By

To sort the results:

DeviceEvents
| where Timestamp > ago(7d)
| order by Timestamp desc

This sorts the events in descending order by Timestamp.

Joining Tables

Join

To combine data from two tables:

DeviceEvents
| join kind=inner (
    DeviceNetworkEvents
    | where Timestamp > ago(7d)
) on DeviceId

This performs an inner join between DeviceEvents and DeviceNetworkEvents tables on DeviceId.

Projecting Columns

Project

To select specific columns:

DeviceEvents
| project Timestamp, DeviceId, ActionType

This retrieves only the Timestamp, DeviceId, and ActionType columns from DeviceEvents.

Using Functions

Parse_JSON

To parse JSON strings:

DeviceEvents
| extend ParsedData = parse_json(RawData)

This parses the RawData column, which contains JSON strings, into a structured format.

Time-Based Analysis

Bin

To group data into bins (time intervals):

DeviceEvents
| where Timestamp > ago(1d)
| summarize count() by bin(Timestamp, 1h)

This groups the event counts by hourly intervals for the past day.

Pattern Matching

Matches Regex

To filter data based on regex patterns:

DeviceEvents
| where EventMessage matches regex "error|failure"

This filters events where the EventMessage contains "error" or "failure".

Advanced Threat Hunting Scenarios

Detecting Failed Logins

Identify accounts with multiple failed login attempts:

DeviceLogonEvents
| where LogonResult == "Failure"
| summarize FailedAttempts = count() by Account, bin(Timestamp, 1h)
| where FailedAttempts > 5

This detects accounts with more than 5 failed login attempts within any one-hour period.

Suspicious PowerShell Activity

Detect suspicious PowerShell activity:

DeviceProcessEvents
| where ProcessCommandLine has_any ("Invoke-WebRequest", "Invoke-Expression", "IEX")
| project Timestamp, DeviceId, InitiatingProcessAccountName, ProcessCommandLine

This identifies PowerShell commands that include potentially malicious activities.

Practical Tips for SC-200 Preparation

1. Understand the Tables: Familiarize yourself with the common tables in Microsoft Defender for Endpoint, like DeviceEvents, DeviceNetworkEvents, DeviceProcessEvents, etc.
2. Practice Real-World Scenarios: Regularly practice writing queries for real-world threat hunting scenarios.
3. Use the KQL Docs: Leverage the KQL documentation for detailed syntax and functions.
4. Hands-On Labs: Utilize hands-on labs and practice environments to test and refine your KQL skills.
5. Exam Objectives: Focus on the SC-200 exam objectives, ensuring you can write and interpret KQL queries relevant to each objective.

By mastering these KQL syntaxes and understanding their practical applications, you will be well-prepared for the SC-200 exam and for effective threat hunting in Microsoft Defender for Endpoint.

Absolutely! Let's dive deeper into the essential KQL (Kusto Query Language) skills that a SC-200 certified Microsoft Security Operations Analyst should have, and outline a sample toolkit repository structure to organize and leverage these skills effectively.

Core KQL Skills for SC-200

1. Understanding Table Structure

Before writing any query, it's crucial to understand the schema of the tables you are querying. Common tables in Microsoft Sentinel include:

• SecurityEvent: Windows security events.
• Syslog: Logs from Linux systems and other syslog-compliant devices.
• OfficeActivity: Office 365 activity logs.
• DeviceEvents: Events from Microsoft Defender for Endpoint.

You can use the .show command to explore the schema of a table:

SecurityEvent
| getschema

2. Basic Operators

Where Clause - Filter events based on specific criteria:

SecurityEvent
| where TimeGenerated > ago(1d) and EventID == 4625

Project - Select specific columns or fields:

SecurityEvent
| project TimeGenerated, Computer, Account

Summarize - Aggregate data, such as counting events:

SecurityEvent
| where EventID == 4625
| summarize FailedLoginAttempts = count() by Account, Computer

Join - Combine data from different tables:

SecurityEvent
| where EventID == 4625
| join kind=inner (Heartbeat | where TimeGenerated > ago(1d)) on Computer

3. Time-based Queries

Ago - Look back in time:

SecurityEvent
| where TimeGenerated > ago(7d)

Between - Filter within a time range:

SecurityEvent
| where TimeGenerated between(datetime(2023-06-01) .. datetime(2023-06-15))

Bin - Group events into time intervals:

SecurityEvent
| summarize EventCount = count() by bin(TimeGenerated, 1h)

4. Advanced Functions

Parse - Extract structured data from text:

SecurityEvent
| extend ParsedCommand = parse_commandline(CommandLine)

Extend - Create new calculated columns:

SecurityEvent
| extend IsAdmin = iff(Account contains "Admin", "Yes", "No")

Mv-expand - Work with multi-valued fields:

SecurityEvent
| extend Processes = split(CommandLine, ";")
| mv-expand Processes

5. Threat Hunting Techniques

Look for anomalies:

SecurityEvent
| where EventID == 4625
| summarize Count = count() by Account
| where Count > 100

Stack counting:

SecurityEvent
| summarize EventCount = count() by Account, bin(TimeGenerated, 1h)
| where EventCount > 50

Rare events:

SecurityEvent
| summarize EventCount = count() by Account
| where EventCount == 1

Sample Toolkit Repository Structure

SC-200 Toolkit
├── Queries
│   ├── Sentinel
│   │   ├── Threat Hunting
│   │   │   ├── lateral_movement.kql
│   │   │   ├── data_exfiltration.kql
│   │   ├── Incident Response
│   │   │   ├── phishing_investigation.kql
│   │   ├── Security Monitoring
│   │   │   ├── failed_logins.kql
│   │   │   ├── high_privilege_activity.kql
│   ├── M365 Defender
│   │   ├── threat_hunting.kql
│   │   ├── incident_response.kql
│   ├── Defender for Cloud
│   │   ├── security_alerts.kql
├── Playbooks
│   ├── Sentinel_Playbooks
│   │   ├── automatic_remediation_playbook.json
│   ├── M365_Defender_Playbooks
│   │   ├── endpoint_isolation_playbook.json
├── Dashboards
│   ├── Sentinel_Dashboards
│   │   ├── threat_overview_dashboard.json
│   ├── M365_Defender_Dashboards
│   │   ├── endpoint_activity_dashboard.json
├── Documentation
│   ├── KQL_cheat_sheet.md
│   ├── query_best_practices.md
│   ├── incident_response_guide.md

Explanation of Repository Structure

Queries: Organized by tool (e.g., Sentinel, M365 Defender) and purpose (e.g., threat hunting, incident response, security monitoring).

Playbooks: Automated workflows triggered by Sentinel or Defender alerts, useful for automated response actions.

Dashboards: Visual representations of security data for continuous monitoring and analysis.

Documentation: Reference materials, best practices, and guides to help analysts get the most out of KQL and their security tools.

Example KQL Query (Sentinel)

Detect Potential Lateral Movement Using PsExec:

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4688 // Process creation
| where CommandLine contains "psexec"
| summarize count() by AccountName, Computer, bin(TimeGenerated, 1h)

Key Points

• Continuously Build Your Toolkit: Regularly update your repository with new queries, playbooks, and dashboards as you encounter new threats.
• Share and Collaborate: Collaborate with your team and the broader security community to share and enhance KQL queries and techniques.
• Leverage Community Resources: Utilize community resources and Microsoft's documentation for additional examples and guidance.

By mastering these KQL skills and organizing your toolkit effectively, you will be well-prepared for the SC-200 exam and capable of conducting advanced threat hunting and incident response in Microsoft Defender for Endpoint.

Sure! Below are the contents for the JSON and markdown files described in the sample toolkit repository structure.

SC-200 Toolkit Directory

1. Queries/Sentinel/Threat Hunting/lateral_movement.kql

// Detect potential lateral movement using PsExec
SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4688 // Process creation
| where CommandLine contains "psexec"
| summarize count() by AccountName, Computer, bin(TimeGenerated, 1h)

2. Queries/Sentinel/Threat Hunting/data_exfiltration.kql

// Detect potential data exfiltration via abnormal data transfer
DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where RemoteIPType == "Public" and ActionType == "ConnectionSuccess"
| summarize TotalDataTransferred = sum(ReceivedBytes + SentBytes) by DeviceName, RemoteIP, bin(TimeGenerated, 1h)
| where TotalDataTransferred > 10000000 // Example threshold

3. Queries/Sentinel/Incident Response/phishing_investigation.kql

// Investigate potential phishing emails
EmailEvents
| where TimeGenerated > ago(7d)
| where SenderDomain != "trusted-domain.com" and Subject contains "invoice"
| project TimeGenerated, RecipientEmailAddress, SenderEmailAddress, Subject, NetworkMessageId

4. Queries/Sentinel/Security Monitoring/failed_logins.kql

// Monitor failed login attempts
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize FailedLoginAttempts = count() by Account, Computer, bin(TimeGenerated, 1h)

5. Queries/Sentinel/Security Monitoring/high_privilege_activity.kql

// Monitor high privilege account activities
SecurityEvent
| where TimeGenerated > ago(7d)
| where AccountType == "Privileged" and EventID in (4672, 4673, 4674)
| project TimeGenerated, AccountName, Activity = EventID
| summarize ActivityCount = count() by AccountName, bin(TimeGenerated, 1h)

6. Queries/M365 Defender/threat_hunting.kql

// General threat hunting query in M365 Defender
DeviceEvents
| where Timestamp > ago(7d)
| where ActionType in ("FileCreated", "ProcessCreated")
| project Timestamp, DeviceName, InitiatingProcessAccountName, ActionType, FileName, ProcessCommandLine

7. Queries/M365 Defender/incident_response.kql

// Incident response investigation query
AlertEvidence
| where Timestamp > ago(7d)
| where AlertType == "Malware"
| project Timestamp, DeviceName, EvidenceRole, EvidenceType, FileName, FilePath, SHA256

8. Queries/Defender for Cloud/security_alerts.kql

// Query for security alerts in Defender for Cloud
SecurityAlert
| where TimeGenerated > ago(7d)
| project TimeGenerated, AlertName, Severity, Description, CompromisedEntity, ExtendedProperties

Playbooks/Sentinel_Playbooks/automatic_remediation_playbook.json

{
    "schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {},
    "triggers": {
        "When_a_response_to_an_Azure_Sentinel_alert_is_triggered": {
            "type": "Microsoft.SecurityInsights/Alert",
            "kind": "escalation"
        }
    },
    "actions": {
        "Condition": {
            "actions": {
                "Remediate": {
                    "type": "Microsoft.Defender/Incident",
                    "runAfter": {},
                    "inputs": {
                        "incidentId": "@triggerOutputs()?['body/id']",
                        "remediationAction": "IsolateDevice"
                    }
                }
            },
            "expression": {
                "equals": [
                    "@triggerOutputs()?['body/severity']",
                    "High"
                ]
            }
        }
    }
}

Playbooks/M365_Defender_Playbooks/endpoint_isolation_playbook.json

{
    "schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {},
    "triggers": {
        "When_a_defender_alert_is_triggered": {
            "type": "Microsoft.SecurityInsights/Alert",
            "kind": "incident"
        }
    },
    "actions": {
        "Condition": {
            "actions": {
                "IsolateDevice": {
                    "type": "Microsoft.Defender/Action",
                    "runAfter": {},
                    "inputs": {
                        "DeviceId": "@triggerOutputs()?['body/deviceId']",
                        "IsolationType": "Full"
                    }
                }
            },
            "expression": {
                "equals": [
                    "@triggerOutputs()?['body/alertSeverity']",
                    "High"
                ]
            }
        }
    }
}

Dashboards/Sentinel_Dashboards/threat_overview_dashboard.json

{
    "name": "ThreatOverviewDashboard",
    "version": "1.0",
    "items": [
        {
            "type": "LineChart",
            "title": "Failed Login Attempts Over Time",
            "query": "SecurityEvent | where TimeGenerated > ago(7d) | where EventID == 4625 | summarize FailedAttempts = count() by bin(TimeGenerated, 1h)",
            "xAxis": "TimeGenerated",
            "yAxis": "FailedAttempts"
        },
        {
            "type": "PieChart",
            "title": "Distribution of Alerts by Severity",
            "query": "SecurityAlert | summarize AlertCount = count() by Severity",
            "legend": "Severity",
            "value": "AlertCount"
        },
        {
            "type": "Table",
            "title": "High Severity Alerts",
            "query": "SecurityAlert | where Severity == 'High' | project TimeGenerated, AlertName, Description, CompromisedEntity"
        }
    ]
}

Dashboards/M365_Defender_Dashboards/endpoint_activity_dashboard.json

{
    "name": "EndpointActivityDashboard",
    "version": "1.0",
    "items": [
        {
            "type": "BarChart",
            "title": "Top 10 Devices by Malicious Activity",
            "query": "DeviceEvents | where Timestamp > ago(7d) | where ActionType == 'MaliciousActivity' | summarize ActivityCount = count() by DeviceName | top 10 by ActivityCount",
            "xAxis": "DeviceName",
            "yAxis": "ActivityCount"
        },
        {
            "type": "LineChart",
            "title": "Malicious Activity Over Time",
            "query": "DeviceEvents | where Timestamp > ago(7d) | where ActionType == 'MaliciousActivity' | summarize ActivityCount = count() by bin(Timestamp, 1h)",
            "xAxis": "Timestamp",
            "yAxis": "ActivityCount"
        },
        {
            "type": "Table",
            "title": "Recent Malicious Activity",
            "query": "DeviceEvents | where Timestamp > ago(1d) | where ActionType == 'MaliciousActivity' | project Timestamp, DeviceName, ActionType, FileName, ProcessCommandLine"
        }
    ]
}

Documentation/KQL_cheat_sheet.md

# KQL Cheat Sheet

## Basic Operators

### Where Clause
```kql
SecurityEvent
| where TimeGenerated > ago(1d) and EventID == 4625

Project

SecurityEvent
| project TimeGenerated, Computer, Account

Summarize

SecurityEvent
| where EventID == 4625
| summarize FailedLoginAttempts = count() by Account, Computer

Join

SecurityEvent
| where EventID == 4625
| join kind=inner (Heartbeat | where TimeGenerated > ago(1d)) on Computer

Time-based Queries

Ago

SecurityEvent
| where TimeGenerated > ago(7d)

Between

SecurityEvent
| where TimeGenerated between(datetime(2023-06-01) .. datetime(2023-06-15))

Bin

SecurityEvent
| summarize EventCount = count() by bin(TimeGenerated, 1h)

Advanced Functions

Parse

SecurityEvent
| extend ParsedCommand = parse_commandline(CommandLine)

Extend

SecurityEvent
| extend IsAdmin = iff(Account contains "Admin", "Yes", "No")

Mv-expand

SecurityEvent
| extend Processes = split(CommandLine, ";")
| mv-expand Processes

Threat Hunting Techniques

Look for anomalies

SecurityEvent
| where EventID == 4625
| summarize Count = count() by Account
| where Count > 100

Stack counting

SecurityEvent
| summarize

 EventCount = count() by Account, bin(TimeGenerated, 1h)
| where EventCount > 50

Rare events

SecurityEvent
| summarize EventCount = count() by Account
| where EventCount == 1

### `Documentation/query_best_practices.md`
```markdown
# Query Best Practices

## Optimize Performance

1. **Filter Early:** Apply `where` clauses as early as possible to reduce the amount of data being processed.
   ```kql
   SecurityEvent
   | where TimeGenerated > ago(1d) and EventID == 4625
   | summarize FailedLoginAttempts = count() by Account, Computer

2. Project Columns: Use project to select only the columns you need.

   SecurityEvent
   | where EventID == 4625
   | project TimeGenerated, Computer, Account
   

3. Summarize Wisely: Aggregate data before joining or performing complex calculations.

   SecurityEvent
   | where EventID == 4625
   | summarize FailedLoginAttempts = count() by Account
   

Maintainability

1. Comment Your Queries: Use comments to explain complex logic.

   // Count failed login attempts by account
   SecurityEvent
   | where EventID == 4625
   | summarize FailedLoginAttempts = count() by Account
   

2. Use Meaningful Names: Name your variables and columns descriptively.

   SecurityEvent
   | extend IsPrivilegedAccount = iff(Account contains "Admin", "Yes", "No")
   

Security Considerations

1. Minimize Data Exposure: Project only necessary columns to limit sensitive data exposure.

   SecurityEvent
   | where EventID == 4625
   | project TimeGenerated, Account
   

2. Anonymize Data: Mask or anonymize sensitive data where possible.

   SecurityEvent
   | extend AnonymizedAccount = hash(Account)
   

Testing and Validation

1. Test with Sample Data: Validate your queries with a small dataset before running on large datasets.

   SecurityEvent
   | where TimeGenerated > ago(1h)
   

2. Check Query Results: Ensure the results match expected outcomes.

   SecurityEvent
   | where EventID == 4625
   | summarize FailedLoginAttempts = count() by Account
   | where FailedLoginAttempts > 10
   

Reusability

1. Modular Queries: Break down complex queries into smaller, reusable parts.

   let FailedLogins = SecurityEvent
   | where EventID == 4625;
   FailedLogins
   | summarize FailedLoginAttempts = count() by Account
   

2. Parameterization: Use parameters to create flexible, reusable queries.

   let TimeRange = ago(1d);
   SecurityEvent
   | where TimeGenerated > TimeRange and EventID == 4625
   | summarize FailedLoginAttempts = count() by Account
   

Documentation

1. Maintain Query Documentation: Keep a record of what each query does and its purpose.

   ### Failed Login Attempts Query
   - **Description:** Counts the number of failed login attempts by account.
   - **Tables Used:** SecurityEvent
   - **Key Columns:** TimeGenerated, Account
   

By following these best practices, you can write efficient, maintainable, and secure KQL queries.

### `Documentation/incident_response_guide.md`
```markdown
# Incident Response Guide

## Introduction

Incident response is a critical function in cybersecurity operations. This guide provides a step-by-step approach to handling security incidents using KQL queries and automated playbooks in Microsoft Sentinel and Microsoft 365 Defender.

## Step-by-Step Incident Response Process

### 1. Detection

1. **Identify Alerts:** Start by identifying and acknowledging security alerts.
   ```kql
   SecurityAlert
   | where TimeGenerated > ago(1h)

2. Prioritize Alerts: Focus on high-severity alerts first.

   SecurityAlert
   | where TimeGenerated > ago(1h) and Severity == "High"
   

2. Investigation

1. Gather Context: Collect additional context around the alert.

   SecurityAlert
   | where TimeGenerated > ago(1h) and Severity == "High"
   | join kind=inner (SecurityEvent | where EventID == 4625) on $left.Computer == $right.Computer
   

2. Analyze Suspicious Activity: Use specific queries to investigate suspicious activity.

   SecurityEvent
   | where TimeGenerated > ago(1h) and EventID == 4625 and Account contains "admin"
   

3. Containment

1. Isolate Affected Systems: Use automated playbooks to isolate compromised devices.

   {
       "type": "Microsoft.Defender/Action",
       "inputs": {
           "DeviceId": "device-id-here",
           "IsolationType": "Full"
       }
   }
   

4. Eradication

1. Remove Malicious Files: Identify and remove malicious files.

   DeviceEvents
   | where Timestamp > ago(1h) and ActionType == "FileCreated" and FileName endswith ".exe"
   

2. Terminate Malicious Processes: Identify and terminate malicious processes.

   DeviceProcessEvents
   | where Timestamp > ago(1h) and ProcessCommandLine contains "malicious-process-name"
   

5. Recovery

1. Restore Systems: Ensure systems are restored to a known good state.

   Heartbeat
   | where TimeGenerated > ago(1h) and DeviceId == "device-id-here"
   

2. Verify Remediation: Confirm that remediation actions were successful.

   SecurityEvent
   | where TimeGenerated > ago(1h) and EventID == 4688 and ProcessCommandLine contains "cleanup-script"
   

6. Lessons Learned

1. Document Incident: Record details of the incident and response actions.
2. Review and Improve: Analyze the incident response process to identify improvements.

Automated Playbooks

Example: Automatic Remediation Playbook

{
    "schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {},
    "triggers": {
        "When_a_response_to_an_Azure_Sentinel_alert_is_triggered": {
            "type": "Microsoft.SecurityInsights/Alert",
            "kind": "escalation"
        }
    },
    "actions": {
        "Condition": {
            "actions": {
                "Remediate": {
                    "type": "Microsoft.Defender/Incident",
                    "runAfter": {},
                    "inputs": {
                        "incidentId": "@triggerOutputs()?['body/id']",
                        "remediationAction": "IsolateDevice"
                    }
                }
            },
            "expression": {
                "equals": [
                    "@triggerOutputs()?['body/severity']",
                    "High"
                ]
            }
        }
    }
}

By following this guide, you can effectively manage security incidents and enhance your organization's security posture.

Feel free to copy these contents into your respective JSON and markdown files to create a comprehensive SC-200 toolkit repository structure.

### KQL for Monitoring and Digital Investigation

This section outlines KQL queries and strategies relevant to monitoring and digital investigation, structured around the themes, content, and learning outcomes described.

#### Theme: Monitoring

**Content: Monitoring**

**Strategy for Monitoring**

1. **Develop a Monitoring Plan**: Define key metrics and logs to monitor, such as failed login attempts, privilege escalations, and network anomalies.
2. **Use KQL Queries for Continuous Monitoring**: Implement KQL queries to track these metrics.

**Example KQL Query for Monitoring Failed Logins:**
```kql
SecurityEvent
| where EventID == 4625
| summarize FailedLoginAttempts = count() by Account, bin(TimeGenerated, 1h)

Identify Security Breaches

1. Anomalous Activity Detection: Identify deviations from normal behavior, such as unusual login locations or times.
2. Correlation of Events: Combine data from multiple sources to detect complex threats.

Example KQL Query for Anomalous Activity:

SecurityEvent
| where EventID == 4624 and LogonType == 10 // Remote interactive logons
| summarize LogonCount = count() by Account, IpAddress, bin(TimeGenerated, 1d)
| where LogonCount > 10 // Threshold for anomaly

Monitoring Tools

• Microsoft Sentinel: For collecting, correlating, and analyzing security data.
• Microsoft Defender for Endpoint: For endpoint protection and detection.

Presentation of Results

1. Dashboards: Create visual representations of monitoring data.
2. Reports: Generate detailed reports on security posture.

Example KQL Query for a Dashboard Visualization:

SecurityEvent
| where TimeGenerated > ago(7d)
| summarize EventCount = count() by EventID, bin(TimeGenerated, 1h)

Analysis of Data

1. Trend Analysis: Identify trends over time to understand patterns and detect emerging threats.
2. Root Cause Analysis: Investigate the underlying causes of detected anomalies.

Example KQL Query for Trend Analysis:

SecurityEvent
| where EventID == 4625
| summarize FailedAttempts = count() by bin(TimeGenerated, 1d)
| order by TimeGenerated asc

Theme: Digital Investigation

Content: Digital Investigation

IDS/IPS

1. Intrusion Detection Systems (IDS): Monitor and alert on suspicious activities.
2. Intrusion Prevention Systems (IPS): Actively block detected threats.

Example KQL Query for IDS/IPS Activity:

DeviceNetworkEvents
| where ActionType in ("Alert", "Blocked")
| summarize count() by ActionType, bin(TimeGenerated, 1h)

Security Analysis Tools

• Microsoft Sentinel: For correlation and analysis of security data.
• Azure Security Center: For security posture management and threat protection.

Example KQL Query for Security Alerts:

SecurityAlert
| where TimeGenerated > ago(7d)
| project TimeGenerated, AlertName, Severity, Description, CompromisedEntity

Action Analysis

1. Incident Response: Analyze actions taken during incident response to improve processes.
2. Post-Incident Review: Evaluate the effectiveness of the response and identify areas for improvement.

Example KQL Query for Incident Response Analysis:

SecurityIncident
| where TimeGenerated > ago(7d)
| summarize IncidentCount = count() by IncidentType, bin(TimeGenerated, 1d)

Tools for Analyzing Security Breaches

• Log Analytics: For in-depth log analysis and query capabilities.
• Microsoft Defender for Identity: For detecting identity-based threats.

Example KQL Query for Analyzing Identity Threats:

IdentityLogonEvents
| where TimeGenerated > ago(7d)
| where LogonType == "Malicious"
| project TimeGenerated, Account, IpAddress, LogonType

Learning Outcomes

Knowledge

• Understand key terminology, strategies, and tools for monitoring and digital investigation.
• Comprehend concepts, theories, processes, systems, and tools used in digital forensics and investigations.

Skills

• Utilize and instruct others on relevant monitoring tools.
• Assess situations, identify issues, and determine necessary actions.
• Reflect on and adjust forensic and investigative practices based on feedback.
• Locate and reference information on forensic and investigative procedures.

General Competence

• Participate in planning, executing, and presenting monitoring systems.
• Plan and conduct forensic investigations, individually or as part of a team.
• Exchange viewpoints and engage in discussions on monitoring and digital investigation.

Example KQL Cheat Sheet

# KQL Cheat Sheet

## Monitoring Queries

### Failed Logins
```kql
SecurityEvent
| where EventID == 4625
| summarize FailedLoginAttempts = count() by Account, bin(TimeGenerated, 1h)

Anomalous Activity Detection

SecurityEvent
| where EventID == 4624 and LogonType == 10
| summarize LogonCount = count() by Account, IpAddress, bin(TimeGenerated, 1d)
| where LogonCount > 10

Security Alerts Overview

SecurityAlert
| where TimeGenerated > ago(7d)
| project TimeGenerated, AlertName, Severity, Description, CompromisedEntity

Trend Analysis of Failed Logins

SecurityEvent
| where EventID == 4625
| summarize FailedAttempts = count() by bin(TimeGenerated, 1d)
| order by TimeGenerated asc

Digital Investigation Queries

IDS/IPS Activity

DeviceNetworkEvents
| where ActionType in ("Alert", "Blocked")
| summarize count() by ActionType, bin(TimeGenerated, 1h)

Incident Response Analysis

SecurityIncident
| where TimeGenerated > ago(7d)
| summarize IncidentCount = count() by IncidentType, bin(TimeGenerated, 1d)

Identity Threat Analysis

IdentityLogonEvents
| where TimeGenerated > ago(7d)
| where LogonType == "Malicious"
| project TimeGenerated, Account, IpAddress, LogonType

This comprehensive approach ensures that candidates have a strong understanding of both monitoring and digital investigation using KQL, with practical examples and structured guidance on how to apply these skills effectively.

### KQL for Supporting Cloud Services

#### Theme: Cloud Services

**Content: Basic Cloud Services, IaaS, PaaS, and SaaS**

**Azure Administrator**

1. **Resource Monitoring and Management**:
   - Track resource utilization and health.
   - Monitor performance and identify bottlenecks.

**Example KQL Query for Resource Utilization**:
```kql
AzureDiagnostics
| where ResourceType == "VM" and TimeGenerated > ago(1d)
| summarize AvgCPU = avg(CPUUsagePercentage) by ResourceId, bin(TimeGenerated, 1h)
| order by AvgCPU desc

Security in Cloud Services

1. Monitor Security Events:
   • Identify and respond to security incidents.
   • Track unauthorized access attempts.

Example KQL Query for Unauthorized Access Attempts:

AzureActivity
| where ActivityStatusValue == "Failed" and ActivitySubstatusValue == "AuthorizationFailed"
| summarize count() by Caller, ResourceGroup, bin(TimeGenerated, 1h)
| where count_ > 10 // Threshold for anomaly

Infrastructure as Code (IaC)

1. Track Changes in Infrastructure:
   • Monitor changes made to the cloud infrastructure.
   • Ensure compliance with IaC definitions.

Example KQL Query for Infrastructure Changes:

AzureActivity
| where ResourceProvider == "Microsoft.Resources" and OperationName == "Write"
| summarize Changes = count() by ResourceId, bin(TimeGenerated, 1h)

Learning Outcomes

Knowledge

• Understanding Key Concepts:
  • Basic understanding of cloud services, IaaS, PaaS, and SaaS models.
  • Familiarity with cloud service providers and their offerings.

Example KQL Query for Understanding Service Usage:

AzureDiagnostics
| where ResourceType in ("StorageAccount", "SQLDatabase", "AppService")
| summarize UsageCount = count() by ResourceType, bin(TimeGenerated, 1d)

• Administering Cloud Services:
  • Knowledge of managing cloud services using Azure.

Example KQL Query for Monitoring Administrator Activities:

AzureActivity
| where Caller contains "admin"
| summarize ActivityCount = count() by OperationName, bin(TimeGenerated, 1d)

• Defining Infrastructure as Code:
  • Understanding how to use IaC to automate cloud deployments.

Example KQL Query for Monitoring IaC Deployment Events:

AzureActivity
| where OperationName == "Deployments"
| summarize DeploymentCount = count() by ResourceId, bin(TimeGenerated, 1d)

• Security in Cloud Services:
  • Knowledge of implementing and maintaining security in cloud environments.

Example KQL Query for Security Alerts:

SecurityAlert
| where TimeGenerated > ago(7d)
| project TimeGenerated, AlertName, Severity, Description, CompromisedEntity

• Tailoring Cloud Services to Organizational Needs:
  • Understanding how to adapt cloud services for stability, functionality, and security.

Example KQL Query for Service Performance Monitoring:

AzureMetrics
| where TimeGenerated > ago(7d) and ResourceType == "AppService"
| summarize AvgResponseTime = avg(TotalRequestDuration) by ResourceId, bin(TimeGenerated, 1h)

Skills

• Evaluating and Selecting Cloud Services:
  • Assess the suitability of different cloud services for organizational needs.

Example KQL Query for Cost Management:

AzureCostManagement
| where TimeGenerated > startofmonth(now())
| summarize TotalCost = sum(Cost) by ServiceName, ResourceGroup

• Utilizing Cloud Service Providers:
  • Implement and manage services from cloud providers.

Example KQL Query for Service Utilization:

AzureDiagnostics
| where ResourceType == "VM" and TimeGenerated > ago(1d)
| summarize AvgDiskRead = avg(DiskReadBytes), AvgDiskWrite = avg(DiskWriteBytes) by ResourceId, bin(TimeGenerated, 1h)

• Advising on Scaling and Procurement:
  • Recommend appropriate scaling and procurement strategies based on analysis.

Example KQL Query for Scaling Recommendations:

AzureDiagnostics
| where ResourceType == "VM" and TimeGenerated > ago(7d)
| summarize MaxCPU = max(CPUUsagePercentage) by ResourceId
| where MaxCPU > 80 // Threshold for scaling

• Communicating Cloud Administration Knowledge:
  • Effectively share knowledge about cloud service administration.

Example KQL Query for Reporting Usage Trends:

AzureMetrics
| where TimeGenerated > ago(30d)
| summarize AvgCPU = avg(CPUPercentage), AvgMemory = avg(MemoryPercentage) by ResourceId, bin(TimeGenerated, 1d)

General Competence

• Assessing Knowledge and Skills Development:
  • Continuously evaluate and improve knowledge and skills in cloud services.

Example KQL Query for Training Progress Tracking:

TrainingData
| where TimeGenerated > ago(1y)
| summarize TrainingSessions = count() by Skill, bin(TimeGenerated, 1m)

• Evaluating Work Against Laws and Regulations:
  • Ensure compliance with relevant laws and regulations.

Example KQL Query for Compliance Auditing:

AuditLogs
| where TimeGenerated > ago(30d)
| summarize AuditEvents = count() by ComplianceStatus, bin(TimeGenerated, 1d)

• Participating in Industry Discussions:
  • Exchange views and participate in discussions about cloud services and digital investigation.

Example KQL Query for Community Engagement Tracking:

CommunityActivity
| where TimeGenerated > ago(1y)
| summarize EngagementCount = count() by ActivityType, bin(TimeGenerated, 1m)

• Evaluating IT Systems for Cloud Suitability:
  • Assess the suitability of IT systems for migration to the cloud.

Example KQL Query for Migration Feasibility Analysis:

SystemMetrics
| where TimeGenerated > ago(30d)
| summarize AvgUtilization = avg(CPUPercentage), AvgMemory = avg(MemoryPercentage) by ResourceId
| where AvgUtilization < 50 and AvgMemory < 50 // Example threshold for cloud migration

Example KQL Cheat Sheet

# KQL Cheat Sheet for Cloud Services

## Resource Monitoring

### VM Resource Utilization
```kql
AzureDiagnostics
| where ResourceType == "VM" and TimeGenerated > ago(1d)
| summarize AvgCPU = avg(CPUUsagePercentage) by ResourceId, bin(TimeGenerated, 1h)
| order by AvgCPU desc

Unauthorized Access Attempts

AzureActivity
| where ActivityStatusValue == "Failed" and ActivitySubstatusValue == "AuthorizationFailed"
| summarize count() by Caller, ResourceGroup, bin(TimeGenerated, 1h)
| where count_ > 10

Infrastructure Changes

AzureActivity
| where ResourceProvider == "Microsoft.Resources" and OperationName == "Write"
| summarize Changes = count() by ResourceId, bin(TimeGenerated, 1h)

Security Monitoring

Security Alerts

SecurityAlert
| where TimeGenerated > ago(7d)
| project TimeGenerated, AlertName, Severity, Description, CompromisedEntity

Service Performance Monitoring

AzureMetrics
| where TimeGenerated > ago(7d) and ResourceType == "AppService"
| summarize AvgResponseTime = avg(TotalRequestDuration) by ResourceId, bin(TimeGenerated, 1h)

Cost Management

Service Costs

AzureCostManagement
| where TimeGenerated > startofmonth(now())
| summarize TotalCost = sum(Cost) by ServiceName, ResourceGroup

Scaling Recommendations

AzureDiagnostics
| where ResourceType == "VM" and TimeGenerated > ago(7d)
| summarize MaxCPU = max(CPUUsagePercentage) by ResourceId
| where MaxCPU > 80

By mastering these KQL queries and understanding their applications, candidates can effectively manage cloud services, ensure security, and optimize resource utilization within an organization. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in cloud services.

### KQL for Linux and Container Management

#### Theme: Linux and Container

**Content: System Administration**

**System Operations**

1. **Monitor System Logs**:
   - Track system events, errors, and warnings to maintain health and performance.
   
**Example KQL Query for Monitoring System Logs**:
```kql
Syslog
| where TimeGenerated > ago(1d)
| where Facility == "syslog" and SeverityLevel <= 3
| summarize EventCount = count() by SeverityLevel, bin(TimeGenerated, 1h)

Linux Distributions

1. Track Distribution-Specific Logs:
   • Analyze logs specific to different Linux distributions to identify issues and performance metrics.

Example KQL Query for Ubuntu System Logs:

Syslog
| where TimeGenerated > ago(1d) and Facility == "auth"
| summarize count() by SyslogMessage, bin(TimeGenerated, 1h)

User Administration

1. Monitor User Login Attempts:
   • Track successful and failed login attempts to manage user access and detect unauthorized access.

Example KQL Query for User Login Attempts:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "auth" and (SyslogMessage contains "Failed password" or SyslogMessage contains "Accepted password")
| summarize LoginAttempts = count() by User, bin(TimeGenerated, 1h)

Installation

1. Track Package Installations:
   • Monitor software installations and updates to ensure systems are up-to-date and secure.

Example KQL Query for Package Installations:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon" and SyslogMessage contains "installed"
| summarize count() by Package, bin(TimeGenerated, 1h)

Network

1. Monitor Network Connections:
   • Track incoming and outgoing network connections to detect potential security issues.

Example KQL Query for Network Connections:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "kern" and (SyslogMessage contains "IN=" or SyslogMessage contains "OUT=")
| summarize ConnectionCount = count() by SourceIP, DestinationIP, bin(TimeGenerated, 1h)

Processes

1. Track Process Activity:
   • Monitor process creation and termination to manage system resources and detect malicious activities.

Example KQL Query for Process Activity:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon" and (SyslogMessage contains "started" or SyslogMessage contains "stopped")
| summarize ProcessCount = count() by ProcessName, bin(TimeGenerated, 1h)

Terminal

1. Monitor Terminal Commands:
   • Track commands executed in the terminal to audit user activities and detect unauthorized actions.

Example KQL Query for Terminal Commands:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "authpriv" and SyslogMessage contains "COMMAND="
| summarize CommandCount = count() by Command, User, bin(TimeGenerated, 1h)

Package Systems

1. Monitor Package Updates:
   • Track updates to packages to ensure all software is up-to-date and secure.

Example KQL Query for Package Updates:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon" and SyslogMessage contains "upgrade"
| summarize count() by Package, bin(TimeGenerated, 1h)

Storage and Filesystems

1. Monitor Disk Usage:
   • Track disk usage and file system changes to prevent storage issues.

Example KQL Query for Disk Usage:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "kern" and SyslogMessage contains "EXT4-fs"
| summarize DiskEventCount = count() by Event, bin(TimeGenerated, 1h)

Server Services

1. Monitor Service Status:
   • Track the status of critical services to ensure they are running correctly.

Example KQL Query for Service Status:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon" and (SyslogMessage contains "service started" or SyslogMessage contains "service stopped")
| summarize ServiceEventCount = count() by Service, bin(TimeGenerated, 1h)

Containers

1. Monitor Container Activity:
   • Track container creation, deletion, and runtime statistics.

Example KQL Query for Container Activity:

ContainerLog
| where TimeGenerated > ago(1d)
| where LogType == "container" and (LogMessage contains "Started" or LogMessage contains "Stopped")
| summarize ContainerEventCount = count() by ContainerName, bin(TimeGenerated, 1h)

Automation

1. Monitor Automation Scripts:
   • Track execution of automation scripts to ensure they run as expected and troubleshoot any issues.

Example KQL Query for Automation Script Execution:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "cron" and SyslogMessage contains "CRON"
| summarize ScriptExecutionCount = count() by ScriptName, bin(TimeGenerated, 1h)

Security

1. Monitor Security Events:
   • Track security-related events such as unauthorized access attempts and suspicious activities.

Example KQL Query for Security Events:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "auth" and (SyslogMessage contains "Failed" or SyslogMessage contains "Unauthorized")
| summarize SecurityEventCount = count() by Event, bin(TimeGenerated, 1h)

Learning Outcomes

Knowledge

• Understanding Operating Systems Functions and Mechanisms:
  • Knowledge of the core functions and workings of different operating systems, including Linux.

Example KQL Query for System Events:

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon"
| summarize EventCount = count() by Event, bin(TimeGenerated, 1h)

• Linux Distributions:
  • Understanding various Linux distributions and their specific features and uses.

Example KQL Query for Distribution-Specific Logs:

Syslog
| where TimeGenerated > ago(1d) and Facility == "auth" and SyslogMessage contains "Ubuntu"
| summarize EventCount = count() by SyslogMessage, bin(TimeGenerated, 1h)

• Package Administration:
  • Knowledge of how to manage packages in Linux using package managers like APT, YUM, etc.

Example KQL Query for Package Management:

Syslog
| where TimeGenerated > ago(1d) and Facility == "daemon" and (SyslogMessage contains "install" or SyslogMessage contains "remove")
| summarize PackageEventCount = count() by Package, bin(TimeGenerated, 1h)

• Terminal Interface in Linux:
  • Understanding the usage and importance of the terminal in Linux for system administration.

Example KQL Query for Terminal Command Monitoring:

Syslog
| where TimeGenerated > ago(1d) and Facility == "authpriv" and SyslogMessage contains "COMMAND="
| summarize CommandEventCount = count() by Command, User, bin(TimeGenerated, 1h)

• Shell Programming:
  • Understanding the benefits and uses of shell scripting for automating tasks in Linux.

Example KQL Query for Shell Script Execution:

Syslog
| where TimeGenerated > ago(1d) and Facility == "cron" and SyslogMessage contains "sh"
| summarize ScriptExecutionCount = count() by ScriptName, bin(TimeGenerated, 1h)

• Container Mechanisms and Operations:
  • Knowledge of how containers work, their structure, and management.

Example KQL Query for Container Operations:

ContainerLog
| where TimeGenerated > ago(1d)
| where LogType == "container" and (LogMessage contains "Created" or LogMessage contains "Destroyed")
| summarize ContainerEventCount = count() by ContainerName, bin(TimeGenerated, 1h)

Skills

• Explaining Linux Filesystem Structure:
  • Ability to explain the structure and components of the Linux filesystem.

Example KQL Query for Filesystem Events:

Syslog
| where TimeGenerated > ago(1d) and Facility == "kern" and SyslogMessage contains "EXT4-fs"
| summarize FilesystemEventCount = count() by Event, bin(TimeGenerated, 1h)

• Installing, Configuring, and Maintaining Linux Systems:
  • Skills to install, configure, and maintain Linux systems in small to medium-sized businesses.

Example KQL Query for System Configuration Changes:

Syslog
| where TimeGenerated > ago(1d) and Facility == "daemon" and SyslogMessage contains "config"
| summarize ConfigChangeCount = count() by ConfigFile, bin(TimeGenerated, 1h)

• Administering Filesystem, Users, Groups, Processes, and Packages via Terminal:
  • Proficiency in managing filesystems, users, groups, processes, and packages through the terminal.

Example KQL Query for User and Group Administration:

kql
Syslog
| where TimeGenerated > ago(1d) and Facility == "auth" and (SyslogMessage contains "useradd" or SyslogMessage contains "groupadd")
| summarize AdminEventCount = count() by AdminAction, bin(TimeGenerated, 1h)

• Using Help Pages and Manuals:
  • Ability to use help pages and manuals to find information about commands.

Example KQL Query for Command Help Usage:

Syslog
| where TimeGenerated > ago(1d) and Facility == "authpriv" and SyslogMessage contains "man"
| summarize HelpUsageCount = count() by Command, User, bin(TimeGenerated, 1h)

• Developing Bash Scripts for Automation:
  • Skills to develop and use Bash scripts to automate repetitive tasks.

Example KQL Query for Monitoring Script Automation:

Syslog
| where TimeGenerated > ago(1d) and Facility == "cron" and SyslogMessage contains "sh"
| summarize ScriptExecutionCount = count() by ScriptName, bin(TimeGenerated, 1h)

General Competence

• Implementing Directory Services for Various Operating Systems:
  • Plan and implement directory services for various operating systems in small and medium businesses.

Example KQL Query for Directory Service Events:

Syslog
| where TimeGenerated > ago(1d) and Facility == "auth" and SyslogMessage contains "LDAP"
| summarize DirectoryEventCount = count() by Event, bin(TimeGenerated, 1h)

• Improving System and Service Security:
  • Assess, propose, and implement security improvements for systems and services.

Example KQL Query for Security Improvement Actions:

Syslog
| where TimeGenerated > ago(1d) and Facility == "auth" and SyslogMessage contains "security"
| summarize SecurityActionCount = count() by Action, bin(TimeGenerated, 1h)

• Documenting and Using Documentation:
  • Responsible for documentation within their field of expertise and using it effectively.

Example KQL Query for Documentation Access:

Syslog
| where TimeGenerated > ago(1d) and Facility == "authpriv" and SyslogMessage contains "doc"
| summarize DocumentationAccessCount = count() by DocumentType, bin(TimeGenerated, 1h)

• Assessing the Need for Development of Knowledge and Skills:
  • Continuously evaluate the need for further development of knowledge, skills, and competence.

Example KQL Query for Training and Development Activities:

TrainingLogs
| where TimeGenerated > ago(1y)
| summarize TrainingSessions = count() by Topic, bin(TimeGenerated, 1m)

• Planning Tasks in Line with Company Requirements:
  • Plan tasks according to company requirements and guidelines.

Example KQL Query for Task Planning Activities:

TaskLogs
| where TimeGenerated > ago(1d) and LogType == "task"
| summarize TaskCount = count() by TaskType, bin(TimeGenerated, 1h)

Example KQL Cheat Sheet

# KQL Cheat Sheet for Linux and Containers

## System Operations

### Monitoring System Logs
```kql
Syslog
| where TimeGenerated > ago(1d)
| where Facility == "syslog" and SeverityLevel <= 3
| summarize EventCount = count() by SeverityLevel, bin(TimeGenerated, 1h)

Monitoring User Login Attempts

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "auth" and (SyslogMessage contains "Failed password" or SyslogMessage contains "Accepted password")
| summarize LoginAttempts = count() by User, bin(TimeGenerated, 1h)

Package Systems

Monitoring Package Installations

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon" and SyslogMessage contains "installed"
| summarize count() by Package, bin(TimeGenerated, 1h)

Monitoring Package Updates

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "daemon" and SyslogMessage contains "upgrade"
| summarize count() by Package, bin(TimeGenerated, 1h)

Storage and Filesystems

Monitoring Disk Usage

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "kern" and SyslogMessage contains "EXT4-fs"
| summarize DiskEventCount = count() by Event, bin(TimeGenerated, 1h)

Containers

Monitoring Container Activity

ContainerLog
| where TimeGenerated > ago(1d)
| where LogType == "container" and (LogMessage contains "Started" or LogMessage contains "Stopped")
| summarize ContainerEventCount = count() by ContainerName, bin(TimeGenerated, 1h)

Automation

Monitoring Automation Scripts

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "cron" and SyslogMessage contains "CRON"
| summarize ScriptExecutionCount = count() by ScriptName, bin(TimeGenerated, 1h)

Security

Monitoring Security Events

Syslog
| where TimeGenerated > ago(1d)
| where Facility == "auth" and (SyslogMessage contains "Failed" or SyslogMessage contains "Unauthorized")
| summarize SecurityEventCount = count() by Event, bin(TimeGenerated, 1h)

By mastering these KQL queries and understanding their applications, candidates can effectively manage Linux systems, ensure security, and optimize resource utilization within an organization. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in Linux and container management.

### KQL for Windows Server and Virtualization Technology

#### Theme: Windows Server and Virtualization Technology

**Content: Windows Server**

**Installation, Configuration, and Administration**

1. **Monitor Installation and Configuration Logs**:
   - Track installation and configuration activities to ensure compliance and identify issues.
   
**Example KQL Query for Installation Logs**:
```kql
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4624 and LogonType == 2 // Logons to monitor installation activities
| summarize InstallationActivities = count() by Account, bin(TimeGenerated, 1h)

Security in Windows Server

1. Monitor Security Events:
   • Track security-related events like unauthorized access attempts and policy changes.

Example KQL Query for Security Events:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (4625, 4648, 4672) // Failed login, explicit credential logon, special privileges assigned
| summarize SecurityEventCount = count() by EventID, bin(TimeGenerated, 1h)

Client and Server

1. Monitor Client-Server Interactions:
   • Track interactions between clients and servers to ensure smooth operations and identify issues.

Example KQL Query for Client-Server Interactions:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4624 and LogonType == 3 // Network logon
| summarize LogonCount = count() by Account, Computer, bin(TimeGenerated, 1h)

Active Directory

1. Monitor Active Directory Changes:
   • Track changes in Active Directory to ensure security and compliance.

Example KQL Query for Active Directory Changes:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (5136, 5137, 5138, 5139) // Directory service changes
| summarize ADChangeCount = count() by OperationType, bin(TimeGenerated, 1h)

Group Policy

1. Monitor Group Policy Changes:
   • Track changes in Group Policy to ensure policies are applied correctly and identify unauthorized changes.

Example KQL Query for Group Policy Changes:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 5136 and ObjectClass == "groupPolicyContainer"
| summarize GPOChangeCount = count() by ObjectName, bin(TimeGenerated, 1h)

PowerShell

1. Monitor PowerShell Script Execution:
   • Track the execution of PowerShell scripts to detect potential security issues and unauthorized actions.

Example KQL Query for PowerShell Activity:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4104 // PowerShell command execution
| summarize ScriptExecutionCount = count() by Command, bin(TimeGenerated, 1h)

Storage

1. Monitor Storage Utilization:
   • Track storage usage and identify potential issues with disk space.

Example KQL Query for Storage Utilization:

Performance
| where CounterName == "Free Megabytes"
| summarize AvgFreeSpace = avg(CounterValue) by InstanceName, bin(TimeGenerated, 1h)

VPN and Firewall

1. Monitor VPN Connections:
   • Track VPN connections to ensure secure remote access.

Example KQL Query for VPN Connections:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 20225 // VPN connection event
| summarize VPNConnectionCount = count() by Account, bin(TimeGenerated, 1h)

2. Monitor Firewall Activity:
   • Track firewall activity to identify and block potential threats.

Example KQL Query for Firewall Activity:

FirewallLog
| where TimeGenerated > ago(1d)
| summarize FirewallEventCount = count() by Action, bin(TimeGenerated, 1h)

Server Services

1. Monitor Server Service Status:
   • Track the status of critical server services to ensure they are running as expected.

Example KQL Query for Server Service Status:

Heartbeat
| where TimeGenerated > ago(1d)
| summarize ServiceCount = count() by Computer, bin(TimeGenerated, 1h)

Content: Virtualization

Hypervisor Installation, Configuration, and Security

1. Monitor Hypervisor Activity:
   • Track hypervisor operations and configuration changes to ensure security and compliance.

Example KQL Query for Hypervisor Activity:

HypervisorLog
| where TimeGenerated > ago(1d)
| summarize HypervisorEventCount = count() by EventType, bin(TimeGenerated, 1h)

Monitoring and Optimization

1. Monitor Virtual Machine Performance:
   • Track the performance of virtual machines to optimize resource usage.

Example KQL Query for VM Performance:

Perf
| where ObjectName == "Hyper-V Virtual Machine"
| summarize AvgCPUUsage = avg(CounterValue) by InstanceName, bin(TimeGenerated, 1h)

IaaS, PaaS, SaaS, and DaaS

1. Monitor Cloud Service Usage:
   • Track the usage of various cloud services to ensure they meet organizational needs.

Example KQL Query for Cloud Service Usage:

AzureDiagnostics
| where ResourceType in ("Microsoft.Compute/virtualMachines", "Microsoft.Web/sites")
| summarize ServiceUsageCount = count() by ResourceType, bin(TimeGenerated, 1h)

Availability and Redundancy

1. Monitor Service Availability:
   • Track the availability of critical services to ensure high availability and redundancy.

Example KQL Query for Service Availability:

Heartbeat
| where TimeGenerated > ago(1d)
| summarize ServiceAvailabilityCount = count() by Computer, bin(TimeGenerated, 1h)

Learning Outcomes

Knowledge

• Functions and Operations of Server and Client Operating Systems:
  • Understanding the core functionalities and mechanisms of various server and client operating systems.

Example KQL Query for System Events:

SecurityEvent
| where TimeGenerated > ago(1d)
| summarize EventCount = count() by EventID, bin(TimeGenerated, 1h)

• Windows Directory Services:
  • Knowledge of configuring and securing Windows directory services like Active Directory.

Example KQL Query for Active Directory Events:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (4720, 4722, 4723, 4724) // User account management
| summarize ADEventCount = count() by EventID, bin(TimeGenerated, 1h)

• Virtualization Environments:
  • Understanding the setup, management, and security of virtualized environments.

Example KQL Query for Virtualization Events:

HypervisorLog
| where TimeGenerated > ago(1d)
| summarize VirtualizationEventCount = count() by EventType, bin(TimeGenerated, 1h)

• Desktop, Application, and OS Virtualization:
  • Knowledge of configuring and managing desktop, application, and operating system virtualization.

Example KQL Query for Virtual Desktop Infrastructure (VDI) Events:

VDILog
| where TimeGenerated > ago(1d)
| summarize VDIEventCount = count() by EventType, bin(TimeGenerated, 1h)

• Documentation in IT Environments:
  • Ability to create and update necessary documentation in different IT environments and networks.

Example KQL Query for Documentation Access:

AuditLogs
| where TimeGenerated > ago(1d)
| where Activity == "DocumentAccess"
| summarize DocAccessCount = count() by DocumentName, bin(TimeGenerated, 1h

Skills

• Installing, Configuring, and Maintaining Directory Services:
  • Ability to securely install, configure, and manage directory services for various operating systems.

Example KQL Query for Directory Service Installation:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4720 // User account creation in Active Directory
| summarize InstallEventCount = count() by AccountName, bin(TimeGenerated, 1h)

• Managing Virtualized Environments:
  • Skills to securely install, configure, and maintain virtualized environments in small and medium businesses.

Example KQL Query for Virtual Machine Management:

HypervisorLog
| where TimeGenerated > ago(1d)
| where EventType == "VMCreation" or EventType == "VMDeletion"
| summarize VMManagementCount = count() by EventType, bin(TimeGenerated, 1h)

• Creating and Updating Documentation:
  • Ability to create and maintain documentation in various IT environments and networks.

Example KQL Query for Documentation Updates:

AuditLogs
| where TimeGenerated > ago(1d)
| where Activity == "DocumentUpdate"
| summarize DocUpdateCount = count() by DocumentName, bin(TimeGenerated, 1h)

• Collaboration with Team Members:
  • Ability to collaborate with other team members, considering the specific needs of different technical areas.

Example KQL Query for Team Collaboration Events:

CollaborationLogs
| where TimeGenerated > ago(1d

)
| summarize CollaborationCount = count() by TeamMember, bin(TimeGenerated, 1h)

General Competence

• Planning and Securing Directory Services:
  • Ability to plan, project, and secure directory service setups for various operating systems in small and medium businesses.

Example KQL Query for Directory Service Planning:

PlanningLogs
| where TimeGenerated > ago(1d)
| summarize PlanningEventCount = count() by ProjectName, bin(TimeGenerated, 1h)

• Improving System and Service Security:
  • Assess and propose improvements for the security of systems and services.

Example KQL Query for Security Improvement Actions:

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (4719, 4738, 4740) // Policy change, user account changes
| summarize SecurityImprovementCount = count() by EventID, bin(TimeGenerated, 1h)

• Documentation Responsibility:
  • Take responsibility for creating and maintaining documentation within their field of expertise.

Example KQL Query for Documentation Responsibilities:

AuditLogs
| where TimeGenerated > ago(1d)
| where Activity == "DocumentCreation" or Activity == "DocumentDeletion"
| summarize DocResponsibilityCount = count() by DocumentName, bin(TimeGenerated, 1h)

• Continuous Knowledge and Skill Development:
  • Continuously evaluate and improve their knowledge, skills, and competencies.

Example KQL Query for Training Activities:

TrainingLogs
| where TimeGenerated > ago(1y)
| summarize TrainingSessionCount = count() by Topic, bin(TimeGenerated, 1m)

• Task Planning According to Company Guidelines:
  • Plan tasks in line with company requirements and guidelines.

Example KQL Query for Task Planning Activities:

TaskLogs
| where TimeGenerated > ago(1d) and LogType == "task"
| summarize TaskCount = count() by TaskType, bin(TimeGenerated, 1h)

Example KQL Cheat Sheet

# KQL Cheat Sheet for Windows Server and Virtualization

## Windows Server

### Installation Logs
```kql
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4624 and LogonType == 2
| summarize InstallationActivities = count() by Account, bin(TimeGenerated, 1h)

Security Events

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (4625, 4648, 4672)
| summarize SecurityEventCount = count() by EventID, bin(TimeGenerated, 1h)

Active Directory Changes

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (5136, 5137, 5138, 5139)
| summarize ADChangeCount = count() by OperationType, bin(TimeGenerated, 1h)

Group Policy Changes

SecurityEvent
| where TimeGenerated > ago(

KQL for Project Management

Theme: Project Management

Content:

1. Problem Statement, Results Goals, and Effect Goals
2. Development of Project Groups
3. Project Administration
4. Creating Requirement Specifications
5. Initiation and Planning Process
6. Legal Requirements and Standards in Database Project Management Tools
7. Resource Management, Work Scope, Duration, and Resource Correlation in Project Planning
8. Risk Assessment, Deviation Handling, and Changes
9. Bids, Offers, and Contracts
10. Documentation and Project Reports, Service, and User Documentation

KQL Queries for Project Management

Problem Statement, Results Goals, and Effect Goals

1. Monitoring Project Progress:
   • Track key milestones and deliverables to ensure they are met within the project timeline.

Example KQL Query for Milestone Tracking:

ProjectManagementLogs
| where EventType == "MilestoneReached"
| summarize MilestonesCompleted = count() by ProjectName, bin(TimeGenerated, 1d)

Development of Project Groups

1. Tracking Team Member Assignments:
   • Monitor the allocation of team members to different tasks and projects.

Example KQL Query for Team Assignments:

ProjectManagementLogs
| where EventType == "TeamAssignment"
| summarize TeamAssignments = count() by ProjectName, TeamMember, bin(TimeGenerated, 1d)

Project Administration

1. Monitoring Task Completion:
   • Track the completion status of tasks assigned to team members.

Example KQL Query for Task Completion:

ProjectManagementLogs
| where EventType == "TaskCompleted"
| summarize TasksCompleted = count() by ProjectName, TeamMember, bin(TimeGenerated, 1d)

Creating Requirement Specifications

1. Tracking Requirement Changes:
   • Monitor changes to project requirements to ensure they are documented and approved.

Example KQL Query for Requirement Changes:

ProjectManagementLogs
| where EventType == "RequirementChange"
| summarize RequirementChanges = count() by ProjectName, bin(TimeGenerated, 1d)

Initiation and Planning Process

1. Monitoring Project Initiation and Planning Activities:
   • Track the completion of initiation and planning activities to ensure the project is on track.

Example KQL Query for Planning Activities:

ProjectManagementLogs
| where EventType in ("ProjectInitiated", "PlanningCompleted")
| summarize PlanningActivities = count() by ProjectName, EventType, bin(TimeGenerated, 1d)

Legal Requirements and Standards in Database Project Management Tools

1. Compliance Monitoring:
   • Track activities to ensure they comply with legal requirements and standards.

Example KQL Query for Compliance Monitoring:

ComplianceLogs
| where EventType == "ComplianceCheck"
| summarize ComplianceEvents = count() by Standard, bin(TimeGenerated, 1d)

Resource Management, Work Scope, Duration, and Resource Correlation in Project Planning

1. Monitoring Resource Utilization:
   • Track the allocation and utilization of resources to ensure optimal use.

Example KQL Query for Resource Utilization:

ResourceLogs
| where EventType == "ResourceAllocation"
| summarize ResourceAllocation = count() by ResourceName, ProjectName, bin(TimeGenerated, 1d)

Risk Assessment, Deviation Handling, and Changes

1. Tracking Risk Events and Mitigation Actions:
   • Monitor risks and the effectiveness of mitigation actions.

Example KQL Query for Risk Events:

RiskLogs
| where EventType == "RiskIdentified" or EventType == "MitigationAction"
| summarize RiskEvents = count() by RiskType, EventType, bin(TimeGenerated, 1d)

Bids, Offers, and Contracts

1. Tracking Bids and Contracts:
   • Monitor the status of bids and contracts to ensure timely approvals and executions.

Example KQL Query for Bids and Contracts:

ContractLogs
| where EventType in ("BidSubmitted", "ContractSigned")
| summarize ContractEvents = count() by ProjectName, EventType, bin(TimeGenerated, 1d)

Documentation and Project Reports, Service, and User Documentation

1. Monitoring Documentation Updates:
   • Track the creation and updates of project documentation to ensure accuracy and completeness.

Example KQL Query for Documentation Updates:

DocumentationLogs
| where EventType == "DocumentUpdated"
| summarize DocumentationUpdates = count() by DocumentType, ProjectName, bin(TimeGenerated, 1d)

Learning Outcomes

Knowledge

• Understanding Project Theory and Principles:
  • Knowledge of project management theories and key principles for effective project execution.

Example KQL Query for Project Principles Application:

ProjectManagementLogs
| where EventType == "PrincipleApplied"
| summarize PrincipleApplicationCount = count() by Principle, bin(TimeGenerated, 1d)

• Challenges and Opportunities in Project Work:
  • Understanding the challenges and opportunities that arise in project work and how to leverage them for success.

Example KQL Query for Project Challenges and Opportunities:

ProjectManagementLogs
| where EventType in ("ChallengeIdentified", "OpportunityLeveraged")
| summarize ChallengeOpportunityCount = count() by EventType, bin(TimeGenerated, 1d)

Skills

• Using Basic Project Methodology:
  • Applying basic project methodologies and innovative skills through partnerships with businesses.

Example KQL Query for Project Methodology Application:

ProjectManagementLogs
| where EventType == "MethodologyApplied"
| summarize MethodologyApplicationCount = count() by Methodology, bin(TimeGenerated, 1d)

• Using Methods, Models, and IT Tools:
  • Utilizing methods, models, and IT tools for project execution.

Example KQL Query for IT Tools Usage:

ITToolLogs
| where EventType == "ToolUsed"
| summarize ToolUsageCount = count() by ToolName, bin(TimeGenerated, 1d)

• Planning and Executing Real Projects:
  • Planning and executing real projects effectively.

Example KQL Query for Project Execution:

ProjectManagementLogs
| where EventType == "ProjectExecution"
| summarize ProjectExecutionCount = count() by ProjectName, bin(TimeGenerated, 1d)

General Competence

• Working in Project Teams Systematically:
  • Working systematically in project teams using recognized methods and models for problem-solving and project management.

Example KQL Query for Teamwork Activities:

TeamworkLogs
| where EventType == "TeamActivity"
| summarize TeamActivityCount = count() by ActivityType, bin(TimeGenerated, 1d)

• Understanding the Importance of Teamwork:
  • Recognizing the importance of teamwork for successful project outcomes.

Example KQL Query for Teamwork Importance:

ProjectManagementLogs
| where EventType == "TeamworkAcknowledged"
| summarize TeamworkAcknowledgmentCount = count() by ProjectName, bin(TimeGenerated, 1d)

• Practicing Good Project Management:
  • Practicing good project management within the field through project experience.

Example KQL Query for Project Management Practices:

ProjectManagementLogs
| where EventType == "BestPracticeApplied"
| summarize BestPracticeCount = count() by Practice, bin(TimeGenerated, 1d)

Example KQL Cheat Sheet for Project Management

# KQL Cheat Sheet for Project Management

## Monitoring Project Progress

### Milestone Tracking
```kql
ProjectManagementLogs
| where EventType == "MilestoneReached"
| summarize MilestonesCompleted = count() by ProjectName, bin(TimeGenerated, 1d)

Development of Project Groups

Team Assignments

ProjectManagementLogs
| where EventType == "TeamAssignment"
| summarize TeamAssignments = count() by ProjectName, TeamMember, bin(TimeGenerated, 1d)

Project Administration

Task Completion

ProjectManagementLogs
| where EventType == "TaskCompleted"
| summarize TasksCompleted = count() by ProjectName, TeamMember, bin(TimeGenerated, 1d)

Creating Requirement Specifications

Requirement Changes

ProjectManagementLogs
| where EventType == "RequirementChange"
| summarize RequirementChanges = count() by ProjectName, bin(TimeGenerated, 1d)

Initiation and Planning Process

Planning Activities

ProjectManagementLogs
| where EventType in ("ProjectInitiated", "PlanningCompleted")
| summarize PlanningActivities = count() by ProjectName, EventType, bin(TimeGenerated, 1d)

Legal Requirements and Standards

Compliance Monitoring

ComplianceLogs
| where EventType == "ComplianceCheck"
| summarize ComplianceEvents = count() by Standard, bin(TimeGenerated, 1d)

Resource Management

Resource Utilization

ResourceLogs
| where EventType == "ResourceAllocation"
| summarize ResourceAllocation = count() by ResourceName, ProjectName, bin(TimeGenerated, 1d)

Risk Assessment and Handling

Risk Events

RiskLogs
| where EventType == "RiskIdentified" or EventType == "MitigationAction"
| summarize RiskEvents = count() by RiskType, EventType, bin(TimeGenerated, 1d

)

Bids and Contracts

Bids and Contracts

ContractLogs
| where EventType in ("BidSubmitted", "ContractSigned")
| summarize ContractEvents = count() by ProjectName, EventType, bin(TimeGenerated, 1d)

Documentation and Reports

Documentation Updates

DocumentationLogs
| where EventType == "DocumentUpdated"
| summarize DocumentationUpdates = count() by DocumentType, ProjectName, bin(TimeGenerated, 1d)

By mastering these KQL queries and understanding their applications, candidates can effectively manage projects, ensure compliance, and optimize resource utilization within an organization. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in project management.

### KQL for Azure Networking and Network Security

#### Theme: Networking 2 and Network Security

**Content:**

1. **Documentation**
2. **Security Routines**
3. **Cisco IOS**
4. **Routing Protocols**
5. **IPv4 and IPv6**
6. **Network Components**
7. **VLAN**
8. **Network Services**
9. **VPN**
10. **ACL**
11. **Local Network Security**
12. **Threats**
13. **Security Routines**
14. **Measures**
15. **Penetration Testing Tools**

### KQL Queries for Azure Networking and Security

**Networking 2**

**Documentation**

1. **Monitoring Configuration Changes**:
   - Track configuration changes to network components and document them.

**Example KQL Query for Configuration Changes**:
```kql
AzureDiagnostics
| where Category == "NetworkSecurityGroupRuleCounter"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

Security Routines

1. Monitoring Security Policies:
   • Ensure security policies are applied and monitor their effectiveness.

Example KQL Query for Security Policy Monitoring:

AzureDiagnostics
| where Category == "SecurityAssessment"
| summarize count() by RecommendationName, bin(TimeGenerated, 1h)

Routing Protocols

1. Monitoring Route Tables:
   • Track changes and performance of route tables.

Example KQL Query for Route Table Monitoring:

AzureDiagnostics
| where Category == "NetworkRouteTable"
| summarize count() by RouteTable, bin(TimeGenerated, 1h)

IPv4 and IPv6

1. Monitoring IP Traffic:
   • Track IPv4 and IPv6 traffic to detect anomalies.

Example KQL Query for IP Traffic Monitoring:

AzureDiagnostics
| where Category == "NetworkTrafficAnalysis"
| summarize IPv4Traffic = countif(Protocol == "IPv4"), IPv6Traffic = countif(Protocol == "IPv6") by bin(TimeGenerated, 1h)

Network Components

1. Monitoring Network Interface:
   • Track the status and performance of network interfaces.

Example KQL Query for Network Interface Monitoring:

AzureDiagnostics
| where Category == "NetworkInterface"
| summarize count() by Interface, bin(TimeGenerated, 1h)

VLAN

1. Monitoring VLAN Configuration:
   • Track VLAN configurations and changes.

Example KQL Query for VLAN Configuration Monitoring:

AzureDiagnostics
| where Category == "NetworkSecurityGroupRuleCounter"
| summarize count() by VLAN, bin(TimeGenerated, 1h)

Network Services

1. Monitoring DNS Requests:
   • Track DNS requests to detect malicious activities.

Example KQL Query for DNS Request Monitoring:

AzureDiagnostics
| where Category == "DnsQueryLog"
| summarize count() by DomainName, bin(TimeGenerated, 1h)

Network Security

VPN

1. Monitoring VPN Connections:
   • Track VPN connections to ensure secure remote access.

Example KQL Query for VPN Connections:

AzureDiagnostics
| where Category == "VpnGatewayDiagnosticLog"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

ACL

1. Monitoring ACL Changes:
   • Track changes to Access Control Lists (ACLs).

Example KQL Query for ACL Changes:

AzureDiagnostics
| where Category == "NetworkSecurityGroupRuleCounter"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

Local Network Security

1. Monitoring Local Network Traffic:
   • Track traffic within the local network to detect potential threats.

Example KQL Query for Local Network Traffic:

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), FlowAction, SourceIP, DestinationIP

Threats

1. Monitoring Threat Detection Alerts:
   • Track alerts related to network threats.

Example KQL Query for Threat Detection:

AzureDiagnostics
| where Category == "SecurityAlert"
| summarize count() by AlertName, Severity, bin(TimeGenerated, 1h)

Security Routines

1. Monitoring Security Audits:
   • Track security audits and compliance checks.

Example KQL Query for Security Audits:

AzureDiagnostics
| where Category == "AuditLogs"
| summarize count() by bin(TimeGenerated, 1h), OperationName, ResultDescription

Measures

1. Monitoring Security Measures Implementation:
   • Track the implementation and effectiveness of security measures.

Example KQL Query for Security Measures:

AzureDiagnostics
| where Category == "SecurityControl"
| summarize count() by ControlName, ControlStatus, bin(TimeGenerated, 1h)

Penetration Testing Tools

1. Monitoring Penetration Testing Activities:
   • Track activities related to penetration testing.

Example KQL Query for Penetration Testing:

AzureDiagnostics
| where Category == "PenTestLogs"
| summarize count() by bin(TimeGenerated, 1h), ToolName, TestType

Learning Outcomes

Knowledge

• Installation, Operation, and Troubleshooting of Network Components:
  • Understanding how to install, operate, and troubleshoot critical network components.

Example KQL Query for Network Component Logs:

AzureDiagnostics
| where Category == "NetworkWatchlist"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

• Adaptation of IKT Networks for Organizational Needs:
  • Knowledge of adapting IKT networks for stability, functionality, and security.

Example KQL Query for Network Adaptation:

AzureDiagnostics
| where Category == "NetworkAdaptation"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

• Concepts and Methods for Troubleshooting Data Networks:
  • Understanding the concepts and methods used in troubleshooting data networks.

Example KQL Query for Troubleshooting Logs:

AzureDiagnostics
| where Category == "NetworkTroubleshooting"
| summarize count() by bin(TimeGenerated, 1h), IssueType, ResolutionStatus

• Threats and Attack Methods Against IKT Networks:
  • Knowledge of threats and attack methods against IKT networks and measures to counter them.

Example KQL Query for Threat Detection Logs:

AzureDiagnostics
| where Category == "ThreatIntelligence"
| summarize count() by bin(TimeGenerated, 1h), ThreatType, MitigationAction

Skills

• Designing, Installing, Operating, and Troubleshooting IKT Networks:
  • Skills in designing, installing, operating, and troubleshooting IKT networks.

Example KQL Query for Network Design and Operation:

AzureDiagnostics
| where Category == "NetworkDesign"
| summarize count() by bin(TimeGenerated, 1h), DesignPhase, OperationStatus

• Providing Recommendations on Scaling and Acquisitions:
  • Ability to provide recommendations on scaling and necessary acquisitions.

Example KQL Query for Scaling Recommendations:

AzureDiagnostics
| where Category == "ResourceScaling"
| summarize count() by bin(TimeGenerated, 1h), ResourceType, Recommendation

• Communicating and Referring to IT Administration Information:
  • Skills in communicating and referring to information about IT administration effectively.

Example KQL Query for IT Administration Communication:

AuditLogs
| where Category == "AdminCommunication"
| summarize count() by bin(TimeGenerated, 1h), CommunicationType, DocumentReferenced

• Participating in and Leading IT Projects:
  • Ability to participate in, lead, and assess solutions in IT projects based on standard methodologies.

Example KQL Query for Project Participation:

ProjectLogs
| where Category == "ITProject"
| summarize count() by bin(TimeGenerated, 1h), ProjectPhase, Role

• Recognizing and Configuring Measures Against Threats:
  • Skills in recognizing threats and configuring appropriate measures against them.

Example KQL Query for Threat Mitigation:

AzureDiagnostics
| where Category == "ThreatMitigation"
| summarize count() by bin(TimeGenerated, 1h), ThreatType, MitigationStatus

• Coordinating with Vendors and Specialists:
  • Ability to coordinate formal agreements and collaboration among vendors and specialists relevant to IT projects/solutions.

Example KQL Query for Vendor Coordination:

VendorLogs
| where Category == "VendorCoordination"
| summarize count() by bin(TimeGenerated, 1h), Vendor, AgreementType

General Competence

• Planning Tasks According to Company Requirements:
  • Plan tasks according to company requirements and ethical guidelines.

Example KQL Query for Task Planning:

TaskLogs
| where Category == "TaskPlanning"
| summarize count() by bin(TimeGenerated, 1h), TaskType, ComplianceStatus

• Evaluating the Need for Knowledge and Skill Development:
  • Continuously evaluate and develop knowledge, skills, and general competence.

Example KQL Query for Training Needs Assessment:

TrainingLogs
| where Category == "TrainingNeeds"
| summarize count() by bin(TimeGenerated,

 1h), SkillType, DevelopmentStatus

• Exchanging Views and Participating in Industry Discussions:
  • Exchange views and participate in discussions on the development of good practices in the industry.

Example KQL Query for Industry Discussions:

DiscussionLogs
| where Category == "IndustryDiscussion"
| summarize count() by bin(TimeGenerated, 1h), Topic, Participant

• Communicating Solutions with Customers:
  • Communicate solutions with customers and explain the choice of technology and solutions.

Example KQL Query for Customer Communication:

CustomerLogs
| where Category == "SolutionCommunication"
| summarize count() by bin(TimeGenerated, 1h), SolutionType, CommunicationMethod

Example KQL Cheat Sheet for Azure Networking and Network Security

# KQL Cheat Sheet for Azure Networking and Network Security

## Monitoring Configuration Changes

### Configuration Changes
```kql
AzureDiagnostics
| where Category == "NetworkSecurityGroupRuleCounter"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

Security Routines

Security Policy Monitoring

AzureDiagnostics
| where Category == "SecurityAssessment"
| summarize count() by RecommendationName, bin(TimeGenerated, 1h)

Routing Protocols

Route Table Monitoring

AzureDiagnostics
| where Category == "NetworkRouteTable"
| summarize count() by RouteTable, bin(TimeGenerated, 1h)

IPv4 and IPv6

IP Traffic Monitoring

AzureDiagnostics
| where Category == "NetworkTrafficAnalysis"
| summarize IPv4Traffic = countif(Protocol == "IPv4"), IPv6Traffic = countif(Protocol == "IPv6") by bin(TimeGenerated, 1h)

Network Components

Network Interface Monitoring

AzureDiagnostics
| where Category == "NetworkInterface"
| summarize count() by Interface, bin(TimeGenerated, 1h)

VLAN

VLAN Configuration Monitoring

AzureDiagnostics
| where Category == "NetworkSecurityGroupRuleCounter"
| summarize count() by VLAN, bin(TimeGenerated, 1h)

Network Services

DNS Request Monitoring

AzureDiagnostics
| where Category == "DnsQueryLog"
| summarize count() by DomainName, bin(TimeGenerated, 1h)

VPN

VPN Connections

AzureDiagnostics
| where Category == "VpnGatewayDiagnosticLog"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

ACL

ACL Changes

AzureDiagnostics
| where Category == "NetworkSecurityGroupRuleCounter"
| summarize count() by bin(TimeGenerated, 1h), Resource, OperationName

Local Network Security

Local Network Traffic

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), FlowAction, SourceIP, DestinationIP

Threats

Threat Detection

AzureDiagnostics
| where Category == "SecurityAlert"
| summarize count() by AlertName, Severity, bin(TimeGenerated, 1h)

Security Routines

Security Audits

AzureDiagnostics
| where Category == "AuditLogs"
| summarize count() by bin(TimeGenerated, 1h), OperationName, ResultDescription

Measures

Security Measures Implementation

AzureDiagnostics
| where Category == "SecurityControl"
| summarize count() by ControlName, ControlStatus, bin(TimeGenerated, 1h)

Penetration Testing

Penetration Testing

AzureDiagnostics
| where Category == "PenTestLogs"
| summarize count() by bin(TimeGenerated, 1h), ToolName, TestType

By mastering these KQL queries and understanding their applications, candidates can effectively manage and secure Azure networking environments, ensuring stability, functionality, and security within an organization. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in networking and network security.

### KQL for Database Management and Security in Azure

#### Theme: Database

**Content:**

1. **Database System**
2. **SQL**
3. **Normalization**
4. **ER-Diagram**
5. **User Administration**
6. **Indexing**
7. **Logging**
8. **Backup and Restore**
9. **Encryption**
10. **Data Visualization**
11. **Security**

### KQL Queries for Azure Database Management

**Database System**

1. **Monitoring Database Performance**:
   - Track database performance metrics to ensure optimal operation.

**Example KQL Query for Database Performance**:
```kql
AzureDiagnostics
| where Category == "SQLDatabasePerformance"
| summarize avg(CPUPercent), avg(DataIOPercent), avg(LogWritePercent) by bin(TimeGenerated, 1h)

SQL

1. Tracking SQL Query Execution:
   • Monitor the execution of SQL queries to identify long-running queries and optimize performance.

Example KQL Query for SQL Query Execution:

AzureDiagnostics
| where Category == "SQLRequests"
| summarize count() by bin(TimeGenerated, 1h), QueryId, Duration, ClientIP

Normalization

1. Analyzing Database Schema:
   • Track schema changes to ensure proper normalization and data integrity.

Example KQL Query for Schema Changes:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), OperationName, SchemaName, ObjectName

ER-Diagram

1. Visualizing Entity Relationships:
   • Track relationships and modifications between entities in the database.

Example KQL Query for Entity Relationships:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), ObjectName, ActionType

User Administration

1. Monitoring User Activities:
   • Track user activities and permissions to ensure security and compliance.

Example KQL Query for User Activities:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), EventClass, ServerPrincipalName, DatabasePrincipalName

Indexing

1. Tracking Index Usage:
   • Monitor the usage of indexes to optimize query performance.

Example KQL Query for Index Usage:

AzureDiagnostics
| where Category == "SQLInsights"
| summarize count() by bin(TimeGenerated, 1h), IndexName, IndexType

Logging

1. Analyzing Database Logs:
   • Track logs for auditing and troubleshooting purposes.

Example KQL Query for Database Logs:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), EventClass, EventSubClass

Backup and Restore

1. Monitoring Backup Operations:
   • Track the status and performance of database backups.

Example KQL Query for Backup Operations:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and OperationName == "BACKUP DATABASE"
| summarize count() by bin(TimeGenerated, 1h), DatabaseName, Result

Encryption

1. Monitoring Encryption Status:
   • Track the status of encryption to ensure data security.

Example KQL Query for Encryption Status:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and OperationName == "ENCRYPTION"
| summarize count() by bin(TimeGenerated, 1h), DatabaseName, Status

Data Visualization

1. Visualizing Query Performance:
   • Create visualizations for query performance metrics.

Example KQL Query for Data Visualization:

AzureDiagnostics
| where Category == "SQLInsights"
| summarize avg(Duration) by bin(TimeGenerated, 1h), QueryType
| render timechart

Security

1. Monitoring Security Events:
   • Track security-related events to ensure database security.

Example KQL Query for Security Events:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and EventClass in ("FailedLogin", "SuccessfulLogin")
| summarize count() by bin(TimeGenerated, 1h), EventClass, ServerPrincipalName

Learning Outcomes

Knowledge

• Understanding Basic Database Theory:
  • Knowledge of fundamental database concepts and theory.

Example KQL Query for Database Theory Application:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), OperationName

• Database Structure and Operation:
  • Understanding the structure, operation, and use of databases in various environments.

Example KQL Query for Database Structure Analysis:

AzureDiagnostics
| where Category == "SQLInsights"
| summarize count() by bin(TimeGenerated, 1h), ObjectName, Operation

• Terminology, Theories, Models, Processes, and Tools in Databases:
  • Knowledge of terminology, theories, models, processes, and tools used in database management.

Example KQL Query for Database Tool Usage:

AzureDiagnostics
| where Category == "SQLInsights"
| summarize count() by bin(TimeGenerated, 1h), ToolName

• Learning New Database/SQL Knowledge:
  • Ability to acquire new knowledge in databases/SQL using relevant literature and resources.

Example KQL Query for Learning Resources:

LearningLogs
| where Category == "SQLLearning"
| summarize count() by bin(TimeGenerated, 1h), ResourceType

• Basic Security in MySQL:
  • Knowledge of fundamental security practices related to MySQL.

Example KQL Query for MySQL Security:

AzureDiagnostics
| where Category == "MySQLSecurity"
| summarize count() by bin(TimeGenerated, 1h), EventClass

Skills

• Creating and Using Databases:
  • Skills in creating and managing databases.

Example KQL Query for Database Creation and Usage:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and OperationName in ("CREATE DATABASE", "USE DATABASE")
| summarize count() by bin(TimeGenerated, 1h), DatabaseName

• Developing Data Models with Keys, Tables, and Relationships:
  • Ability to create data models including keys, tables, and relationships.

Example KQL Query for Data Model Development:

AzureDiagnostics
| where Category == "SQLInsights" and OperationName == "SchemaUpdate"
| summarize count() by bin(TimeGenerated, 1h), ObjectName, ObjectType

• Visualizing Data from a Database:
  • Skills in visualizing data extracted from databases.

Example KQL Query for Data Visualization:

AzureDiagnostics
| where Category == "SQLInsights"
| summarize avg(Duration) by bin(TimeGenerated, 1h), QueryType
| render timechart

• Making Informed Decisions Regarding Data Storage:
  • Ability to make informed decisions on data storage adhering to laws and regulations.

Example KQL Query for Data Storage Decisions:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), StorageAction, ComplianceStatus

• Installing, Configuring, and Administering Small Database Environments:
  • Skills to install, configure, and manage small database environments.

Example KQL Query for Database Administration:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and OperationName in ("INSTALL DATABASE", "CONFIGURE DATABASE")
| summarize count() by bin(TimeGenerated, 1h), DatabaseName

General Competence

• Working According to Client Needs and Establishing Professional Networks:
  • Ability to work based on client needs, establish professional networks, and collaborate across disciplines.

Example KQL Query for Client Interaction:

ClientInteractionLogs
| where Category == "ClientRequest"
| summarize count() by bin(TimeGenerated, 1h), ClientName, RequestType

• Exchanging Industry-Related Views and Information:
  • Ability to exchange views and information related to the industry.

Example KQL Query for Industry Communication:

IndustryLogs
| where Category == "IndustryDiscussion"
| summarize count() by bin(TimeGenerated, 1h), Topic, Participant

• Building Relationships Internally and Externally:
  • Ability to build relationships internally, externally, and across disciplines.

Example KQL Query for Relationship Building:

RelationshipLogs
| where Category == "Networking"
| summarize count() by bin(TimeGenerated, 1h), RelationshipType, Participant

• Exchanging Views with Industry Professionals:
  • Ability to exchange views with other industry professionals and participate in discussions on best practices.

Example KQL Query for Professional Discussions:

ProfessionalLogs
| where Category == "ProfessionalDiscussion"
| summarize count() by bin(TimeGenerated, 1h), Topic, Professional

Example KQL Cheat Sheet for Database Management

# KQL Cheat Sheet for Database Management

## Database System

### Monitoring Database Performance
```kql
AzureDiagnostics
| where Category == "SQLDatabasePerformance"
| summarize avg(CPUPercent), avg(DataIOPercent), avg

(LogWritePercent) by bin(TimeGenerated, 1h)

SQL

Tracking SQL Query Execution

AzureDiagnostics
| where Category == "SQLRequests"
| summarize count() by bin(TimeGenerated, 1h), QueryId, Duration, ClientIP

Normalization

Analyzing Database Schema

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), OperationName, SchemaName, ObjectName

ER-Diagram

Visualizing Entity Relationships

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), ObjectName, ActionType

User Administration

Monitoring User Activities

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), EventClass, ServerPrincipalName, DatabasePrincipalName

Indexing

Tracking Index Usage

AzureDiagnostics
| where Category == "SQLInsights"
| summarize count() by bin(TimeGenerated, 1h), IndexName, IndexType

Logging

Analyzing Database Logs

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| summarize count() by bin(TimeGenerated, 1h), EventClass, EventSubClass

Backup and Restore

Monitoring Backup Operations

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and OperationName == "BACKUP DATABASE"
| summarize count() by bin(TimeGenerated, 1h), DatabaseName, Result

Encryption

Monitoring Encryption Status

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and OperationName == "ENCRYPTION"
| summarize count() by bin(TimeGenerated, 1h), DatabaseName, Status

Data Visualization

Visualizing Query Performance

AzureDiagnostics
| where Category == "SQLInsights"
| summarize avg(Duration) by bin(TimeGenerated, 1h), QueryType
| render timechart

Security

Monitoring Security Events

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and EventClass in ("FailedLogin", "SuccessfulLogin")
| summarize count() by bin(TimeGenerated, 1h), EventClass, ServerPrincipalName

By mastering these KQL queries and understanding their applications, candidates can effectively manage and secure database environments, ensuring stability, functionality, and security within an organization. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in database management.

### KQL for Networking in Azure

#### Theme: Networking 1

**Content:**

1. **LAN/WAN Technology**
2. **Network Topologies**
3. **OSI Model**
4. **Network Components**
5. **IPv4 and IPv6**
6. **Network Protocols**
7. **Network Services**
8. **Wireless Technology**
9. **Simulation Tools**

### KQL Queries for Azure Networking

**LAN/WAN Technology**

1. **Monitoring Network Traffic**:
   - Track network traffic within LAN and WAN to ensure efficient data flow and detect anomalies.

**Example KQL Query for Network Traffic**:
```kql
AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), SourceIP, DestinationIP, FlowType
| order by TimeGenerated desc

Network Topologies

1. Visualizing Network Topologies:
   • Monitor network topologies to understand the structure and connections between network components.

Example KQL Query for Network Topologies:

AzureDiagnostics
| where Category == "NetworkWatcherTopology"
| summarize count() by bin(TimeGenerated, 1h), ResourceGroup, TopologyType

OSI Model

1. Tracking Layer-specific Traffic:
   • Monitor traffic and events specific to different OSI layers to identify and troubleshoot issues.

Example KQL Query for OSI Layer Monitoring:

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), Layer, FlowType
| order by TimeGenerated desc

Network Components

1. Monitoring Network Interfaces:
   • Track the status and performance of network interfaces.

Example KQL Query for Network Interfaces:

AzureDiagnostics
| where Category == "NetworkInterfaceLogs"
| summarize count() by bin(TimeGenerated, 1h), InterfaceName, OperationName
| order by TimeGenerated desc

IPv4 and IPv6

1. Monitoring IP Traffic:
   • Track IPv4 and IPv6 traffic to ensure proper routing and detect potential issues.

Example KQL Query for IP Traffic:

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize IPv4Traffic = countif(Protocol == "IPv4"), IPv6Traffic = countif(Protocol == "IPv6") by bin(TimeGenerated, 1h)
| order by TimeGenerated desc

Network Protocols

1. Monitoring Protocol-specific Traffic:
   • Track traffic for different network protocols to ensure compliance and performance.

Example KQL Query for Protocol Traffic:

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), Protocol
| order by TimeGenerated desc

Network Services

1. Monitoring Network Services:
   • Track the status and performance of critical network services.

Example KQL Query for Network Services:

AzureDiagnostics
| where Category == "NetworkServiceLogs"
| summarize count() by bin(TimeGenerated, 1h), ServiceName, Status
| order by TimeGenerated desc

Wireless Technology

1. Monitoring Wireless Network Traffic:
   • Track wireless network traffic to ensure connectivity and performance.

Example KQL Query for Wireless Traffic:

AzureDiagnostics
| where Category == "WirelessNetworkLogs"
| summarize count() by bin(TimeGenerated, 1h), SSID, SignalStrength
| order by TimeGenerated desc

Simulation Tools

1. Simulating Network Traffic:
   • Use simulation logs to model and analyze network traffic patterns.

Example KQL Query for Network Simulation:

SimulationLogs
| summarize count() by bin(TimeGenerated, 1h), SimulationType, Result
| order by TimeGenerated desc

Learning Outcomes

Knowledge

• Understanding Network Concepts and Components:
  • Knowledge of different types of networks and their components, as well as their operations.

Example KQL Query for Network Component Logs:

AzureDiagnostics
| where Category == "NetworkComponentLogs"
| summarize count() by bin(TimeGenerated, 1h), ComponentType, Operation
| order by TimeGenerated desc

• Relevant Network Standards:
  • Knowledge of standards and best practices within networking.

Example KQL Query for Standards Compliance:

ComplianceLogs
| where Category == "NetworkStandardsCompliance"
| summarize count() by bin(TimeGenerated, 1h), Standard, ComplianceStatus
| order by TimeGenerated desc

• Troubleshooting Methods in Data Networks:
  • Knowledge of common methods and tools used for troubleshooting data networks.

Example KQL Query for Troubleshooting Logs:

AzureDiagnostics
| where Category == "NetworkTroubleshooting"
| summarize count() by bin(TimeGenerated, 1h), IssueType, ResolutionStatus
| order by TimeGenerated desc

• Basic Network Security:
  • Knowledge of fundamental security principles and practices in networking.

Example KQL Query for Security Events:

AzureDiagnostics
| where Category == "NetworkSecurityAudit"
| summarize count() by bin(TimeGenerated, 1h), EventType, Severity
| order by TimeGenerated desc

Skills

• Setting Up Basic Networks:
  • Skills to set up and configure basic networks.

Example KQL Query for Network Setup Logs:

AzureDiagnostics
| where Category == "NetworkSetup"
| summarize count() by bin(TimeGenerated, 1h), NetworkType, SetupStatus
| order by TimeGenerated desc

• Troubleshooting Basic Networks:
  • Skills to identify and resolve issues in basic network configurations.

Example KQL Query for Troubleshooting Activities:

AzureDiagnostics
| where Category == "NetworkTroubleshooting"
| summarize count() by bin(TimeGenerated, 1h), IssueType, ResolutionStatus
| order by TimeGenerated desc

• Assessing Network Security:
  • Skills to evaluate and implement basic security measures in networks.

Example KQL Query for Security Assessments:

AzureDiagnostics
| where Category == "NetworkSecurityAssessment"
| summarize count() by bin(TimeGenerated, 1h), AssessmentType, Result
| order by TimeGenerated desc

General Competence

• Exchanging Views with Industry Professionals:
  • Ability to communicate and share insights with others in the IT industry to foster organizational development.

Example KQL Query for Professional Communications:

ProfessionalLogs
| where Category == "IndustryCommunication"
| summarize count() by bin(TimeGenerated, 1h), CommunicationType, Topic
| order by TimeGenerated desc

• Maintaining and Developing Competence:
  • Commitment to continuous learning and professional development in networking.

Example KQL Query for Training and Development:

TrainingLogs
| where Category == "ProfessionalDevelopment"
| summarize count() by bin(TimeGenerated, 1h), TrainingType, CompletionStatus
| order by TimeGenerated desc

Example KQL Cheat Sheet for Networking

# KQL Cheat Sheet for Networking

## Network Traffic

### Monitoring Network Traffic
```kql
AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), SourceIP, DestinationIP, FlowType
| order by TimeGenerated desc

Network Topologies

Visualizing Network Topologies

AzureDiagnostics
| where Category == "NetworkWatcherTopology"
| summarize count() by bin(TimeGenerated, 1h), ResourceGroup, TopologyType

OSI Model

Tracking Layer-specific Traffic

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), Layer, FlowType
| order by TimeGenerated desc

Network Components

Monitoring Network Interfaces

AzureDiagnostics
| where Category == "NetworkInterfaceLogs"
| summarize count() by bin(TimeGenerated, 1h), InterfaceName, OperationName
| order by TimeGenerated desc

IPv4 and IPv6

Monitoring IP Traffic

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize IPv4Traffic = countif(Protocol == "IPv4"), IPv6Traffic = countif(Protocol == "IPv6") by bin(TimeGenerated, 1h)
| order by TimeGenerated desc

Network Protocols

Monitoring Protocol-specific Traffic

AzureDiagnostics
| where Category == "NetworkSecurityGroupFlowEvent"
| summarize count() by bin(TimeGenerated, 1h), Protocol
| order by TimeGenerated desc

Network Services

Monitoring Network Services

AzureDiagnostics
| where Category == "NetworkServiceLogs"
| summarize count() by bin(TimeGenerated, 1h), ServiceName, Status
| order by TimeGenerated desc

Wireless Technology

Monitoring Wireless Network Traffic

AzureDiagnostics
| where Category == "WirelessNetworkLogs"
| summarize count() by bin(TimeGenerated, 1h), SSID, SignalStrength
| order by TimeGenerated desc

Simulation Tools

Simulating Network Traffic

SimulationLogs
| summarize count() by bin(TimeGenerated, 1h), SimulationType, Result


| order by TimeGenerated desc

By mastering these KQL queries and understanding their applications, candidates can effectively manage and secure Azure networking environments, ensuring stability, functionality, and security within an organization. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in networking.

### KQL for Cybersecurity in Microsoft 365 Defender, Azure, and Sentinel

#### Theme: Cybersecurity

**Content:**

1. **Fundamental Security Principles**
2. **Basic Security**
3. **Threat Landscape in IT Security**
4. **Attack and Defense Methods**
5. **Risk Management**
6. **IT Security Frameworks**
7. **Laws and Regulations**

### KQL Queries for Cybersecurity Monitoring and Analysis

**Fundamental Security Principles**

1. **Monitoring Security Posture**:
   - Track the overall security posture of your environment to ensure compliance with security policies.

**Example KQL Query for Security Posture Monitoring**:
```kql
SecurityAlert
| where TimeGenerated > ago(1d)
| summarize AlertsCount = count(), HighSeverityCount = countif(Severity == "High") by bin(TimeGenerated, 1h)

Basic Security

1. Tracking User Authentication Events:
   • Monitor user authentication events to detect unusual login activities.

Example KQL Query for User Authentication:

SigninLogs
| where TimeGenerated > ago(1d)
| summarize LoginAttempts = count() by UserPrincipalName, ResultType, bin(TimeGenerated, 1h)

Threat Landscape in IT Security

1. Monitoring Threat Intelligence:
   • Integrate threat intelligence to identify known malicious indicators.

Example KQL Query for Threat Intelligence:

ThreatIntelligenceIndicator
| where TimeGenerated > ago(1d)
| join kind=inner (SecurityAlert) on $left.ThreatType == $right.ThreatType
| project TimeGenerated, IndicatorId, ThreatType, Description, Severity

Attack and Defense Methods

1. Detecting Suspicious PowerShell Activities:
   • Monitor PowerShell command executions to detect potentially malicious activities.

Example KQL Query for PowerShell Activities:

DeviceEvents
| where TimeGenerated > ago(1d)
| where ActionType == "PowerShellCommand"
| summarize count() by DeviceName, InitiatingProcessAccountName, bin(TimeGenerated, 1h)

2. Identifying Lateral Movement:
   • Detect lateral movement attempts within the network.

Example KQL Query for Lateral Movement Detection:

DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where ActionType == "NetworkConnectionInitiated" and RemoteUrl endswith ".internal"
| summarize count() by DeviceName, InitiatingProcessAccountName, bin(TimeGenerated, 1h)

Risk Management

1. Risk Assessment and Mitigation Tracking:
   • Track risk assessments and implemented mitigation actions.

Example KQL Query for Risk Assessments:

RiskyUsers
| where TimeGenerated > ago(1d)
| summarize RiskEventCount = count() by UserPrincipalName, RiskLevel, bin(TimeGenerated, 1h)

IT Security Frameworks

1. Compliance with Security Frameworks:
   • Monitor compliance with security frameworks like CIS, NIST, etc.

Example KQL Query for Security Framework Compliance:

SecurityComplianceAssessment
| where TimeGenerated > ago(1d)
| summarize FrameworkComplianceCount = count() by FrameworkName, ControlStatus, bin(TimeGenerated, 1h)

Laws and Regulations

1. Monitoring GDPR Compliance:
   • Track activities to ensure compliance with GDPR regulations.

Example KQL Query for GDPR Compliance:

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName contains "GDPR"
| summarize count() by UserPrincipalName, OperationName, bin(TimeGenerated, 1h)

Learning Outcomes

Knowledge

• Fundamental IT Security Principles:
  • Understanding basic principles and practices in IT security.

Example KQL Query for Security Principles Application:

SecurityAlert
| where TimeGenerated > ago(1d)
| summarize count() by SecurityControl, Severity, bin(TimeGenerated, 1h)

• Current Threats and Actors:
  • Knowledge of current threats and threat actors in the cybersecurity landscape.

Example KQL Query for Threat Actor Tracking:

ThreatIntelligenceIndicator
| where TimeGenerated > ago(1d)
| summarize count() by ThreatType, ThreatActor, bin(TimeGenerated, 1h)

• Attack and Defense Methods:
  • Understanding various attack methods and corresponding defense mechanisms.

Example KQL Query for Attack Methods Monitoring:

SecurityIncident
| where TimeGenerated > ago(1d)
| summarize count() by AttackMethod, MitigationAction, bin(TimeGenerated, 1h)

• IT Security Frameworks:
  • Knowledge of relevant IT security frameworks and standards.

Example KQL Query for Framework Compliance:

SecurityComplianceAssessment
| where TimeGenerated > ago(1d)
| summarize count() by FrameworkName, ControlStatus, bin(TimeGenerated, 1h)

• Risk Management:
  • Understanding risk management processes and practices.

Example KQL Query for Risk Management:

RiskyUsers
| where TimeGenerated > ago(1d)
| summarize count() by RiskLevel, RiskEventType, bin(TimeGenerated, 1h)

• Relevant Laws and Regulations:
  • Knowledge of laws and regulations relevant to IT security.

Example KQL Query for Legal Compliance:

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName contains "ComplianceCheck"
| summarize count() by Regulation, ComplianceStatus, bin(TimeGenerated, 1h)

Skills

• Keeping Updated on IT Threat Landscape:
  • Continuously monitoring and updating knowledge on the latest IT threats.

Example KQL Query for Threat Landscape Monitoring:

ThreatIntelligenceIndicator
| where TimeGenerated > ago(1d)
| summarize count() by ThreatType, IndicatorType, bin(TimeGenerated, 1h)

• Identifying Attack Methods and Implementing Countermeasures:
  • Ability to detect attack methods and implement appropriate defenses.

Example KQL Query for Attack Detection and Mitigation:

SecurityIncident
| where TimeGenerated > ago(1d)
| summarize count() by AttackMethod, MitigationAction, bin(TimeGenerated, 1h)

• Conducting Risk Assessments and Planning Risk Mitigation:
  • Skills in performing risk assessments and planning for risk mitigation.

Example KQL Query for Risk Assessment and Planning:

RiskyUsers
| where TimeGenerated > ago(1d)
| summarize count() by RiskLevel, RiskEventType, MitigationStatus, bin(TimeGenerated, 1h)

• Referencing Laws and Regulations:
  • Ability to find and reference relevant laws and regulations for IT security.

Example KQL Query for Legal References:

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName contains "LegalReference"
| summarize count() by Regulation, ReferenceStatus, bin(TimeGenerated, 1h)

General Competence

• Participating in Security Projects:
  • Ability to actively participate in cybersecurity projects.

Example KQL Query for Project Participation:

ProjectLogs
| where TimeGenerated > ago(1d)
| summarize count() by ProjectName, Role, bin(TimeGenerated, 1h)

• Evaluating IT Solutions Against Requirements:
  • Skills to assess IT solutions for compliance with business needs and regulatory requirements.

Example KQL Query for IT Solution Evaluation:

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName contains "SolutionEvaluation"
| summarize count() by SolutionType, ComplianceStatus, bin(TimeGenerated, 1h)

• Exchanging Industry-related Views:
  • Ability to exchange views and information with industry professionals.

Example KQL Query for Industry Communication:

ProfessionalLogs
| where TimeGenerated > ago(1d)
| summarize count() by Topic, Participant, bin(TimeGenerated, 1h)

• Maintaining and Developing Competence:
  • Commitment to continuous learning and development in the field of cybersecurity.

Example KQL Query for Professional Development:

TrainingLogs
| where TimeGenerated > ago(1d)
| summarize count() by TrainingType, CompletionStatus, bin(TimeGenerated, 1h)

Example KQL Cheat Sheet for Cybersecurity

# KQL Cheat Sheet for Cybersecurity

## Security Posture

### Monitoring Security Posture
```kql
SecurityAlert
| where TimeGenerated > ago(1d)
| summarize AlertsCount = count(), HighSeverityCount = countif(Severity == "High") by bin(TimeGenerated, 1h)

Basic Security

Tracking User Authentication Events

SigninLogs
| where TimeGenerated > ago(1d)
| summarize LoginAttempts = count() by UserPrincipalName, ResultType, bin(TimeGenerated, 1h)

Threat Landscape

Monitoring Threat Intelligence

ThreatIntelligenceIndicator
| where TimeGenerated > ago(1d)
| join kind=inner (SecurityAlert) on $left.ThreatType == $right.ThreatType
| project TimeGenerated, IndicatorId, ThreatType, Description, Severity

Attack and Defense Methods

Detecting Suspicious PowerShell Activities

Device

Events
| where TimeGenerated > ago(1d)
| where ActionType == "PowerShellCommand"
| summarize count() by DeviceName, InitiatingProcessAccountName, bin(TimeGenerated, 1h)

Identifying Lateral Movement

DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where ActionType == "NetworkConnectionInitiated" and RemoteUrl endswith ".internal"
| summarize count() by DeviceName, InitiatingProcessAccountName, bin(TimeGenerated, 1h)

Risk Management

Risk Assessment and Mitigation Tracking

RiskyUsers
| where TimeGenerated > ago(1d)
| summarize RiskEventCount = count() by UserPrincipalName, RiskLevel, bin(TimeGenerated, 1h)

IT Security Frameworks

Compliance with Security Frameworks

SecurityComplianceAssessment
| where TimeGenerated > ago(1d)
| summarize FrameworkComplianceCount = count() by FrameworkName, ControlStatus, bin(TimeGenerated, 1h)

Laws and Regulations

Monitoring GDPR Compliance

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName contains "GDPR"
| summarize count() by UserPrincipalName, OperationName, bin(TimeGenerated, 1h)

By mastering these KQL queries and understanding their applications, candidates can effectively manage and secure their environments using Microsoft 365 Defender, Azure, and Sentinel, ensuring they stay ahead of potential threats and comply with relevant laws and regulations. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in cybersecurity.

### KQL for IoT and ITD-Lab in Azure and Microsoft Sentinel

#### Theme: IoT and ITD-Lab

**Content:**

1. **IoT**
2. **Azure IoT**
3. **Simulation Tools**
4. **Microcontrollers, Actuators, and Sensors**
5. **Protocols and Standards**
6. **Smart Home**
7. **Data Security**
8. **Hardware and ITD-Lab**
9. **Server Hardware**
10. **General Troubleshooting**
11. **Network Devices**
12. **UPS and Other Devices Related to Server Rooms**
13. **Virtualization**
14. **Using Administration Tools and Virtualization Solutions**
15. **Security**

### KQL Queries for IoT Monitoring and Analysis

**IoT and Azure IoT**

1. **Monitoring IoT Device Connectivity**:
   - Track the connectivity status of IoT devices to ensure they are online and functioning properly.

**Example KQL Query for IoT Device Connectivity**:
```kql
AzureDiagnostics
| where Category == "IoTHubLogs" and Level == "Information"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeviceId, Status
| order by TimeGenerated desc

2. Monitoring Sensor Data:
   • Track data from various sensors connected to IoT devices.

Example KQL Query for Sensor Data:

IoTHubMessages
| where TimeGenerated > ago(1d)
| project TimeGenerated, DeviceId, Temperature = tostring(parse_json(Telemetry).temperature), Humidity = tostring(parse_json(Telemetry).humidity)
| summarize avg(todouble(Temperature)), avg(todouble(Humidity)) by bin(TimeGenerated, 1h), DeviceId
| order by TimeGenerated desc

Simulation Tools

1. Simulating IoT Device Data:
   • Simulate data for testing and validating IoT device behavior.

Example KQL Query for Simulated Data:

SimulationLogs
| where Category == "IoTSimulation"
| summarize count() by bin(TimeGenerated, 1h), DeviceType, SimulationStatus
| order by TimeGenerated desc

Microcontrollers, Actuators, and Sensors

1. Monitoring Actuator Status:
   • Track the status and activities of actuators connected to IoT devices.

Example KQL Query for Actuator Status:

AzureDiagnostics
| where Category == "IoTHubLogs" and Level == "Information"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeviceId, ActuatorStatus
| order by TimeGenerated desc

Protocols and Standards

1. Monitoring Communication Protocols:
   • Ensure that IoT devices are communicating using the correct protocols.

Example KQL Query for Protocol Monitoring:

AzureDiagnostics
| where Category == "IoTHubLogs"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), Protocol, DeviceId
| order by TimeGenerated desc

Smart Home

1. Tracking Smart Home Device Activities:
   • Monitor activities of smart home devices for functionality and security.

Example KQL Query for Smart Home Activities:

IoTHubMessages
| where TimeGenerated > ago(1d)
| where DeviceId startswith "smarthome"
| summarize count() by bin(TimeGenerated, 1h), DeviceId, Activity
| order by TimeGenerated desc

Data Security

1. Monitoring Data Integrity:
   • Track data integrity checks to ensure data from IoT devices is not tampered with.

Example KQL Query for Data Integrity Monitoring:

AzureDiagnostics
| where Category == "IoTHubLogs" and Level == "Warning"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeviceId, IntegrityStatus
| order by TimeGenerated desc

Learning Outcomes

Knowledge

• Understanding IoT Concepts and Applications:
  • Knowledge of IoT infrastructure, applications, and terminology.

Example KQL Query for IoT Application Monitoring:

AzureDiagnostics
| where Category == "IoTHubLogs"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), Application, Status
| order by TimeGenerated desc

• Knowledge of Network Components and Hardware:
  • Understanding the components and hardware used in networks and IoT systems.

Example KQL Query for Network Component Monitoring:

AzureDiagnostics
| where Category == "NetworkInterfaceLogs"
| summarize count() by bin(TimeGenerated, 1h), ComponentType, Status
| order by TimeGenerated desc

• Techniques for Securing Infrastructure:
  • Knowledge of techniques and best practices for securing IoT infrastructure.

Example KQL Query for Security Events:

SecurityAlert
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), AlertType, Severity
| order by TimeGenerated desc

Skills

• Explaining IoT Concepts and Infrastructure:
  • Ability to explain the concepts, applications, and infrastructure of IoT.

Example KQL Query for IoT Concept Explanation:

LearningLogs
| where Category == "IoTLearning"
| summarize count() by bin(TimeGenerated, 1h), Topic, CompletionStatus
| order by TimeGenerated desc

• Recognizing and Troubleshooting Hardware Components:
  • Skills to identify and troubleshoot basic hardware components.

Example KQL Query for Hardware Troubleshooting:

AzureDiagnostics
| where Category == "DeviceHealth"
| summarize count() by bin(TimeGenerated, 1h), DeviceId, IssueType
| order by TimeGenerated desc

• Evaluating Technical Innovations:
  • Ability to assess new technologies and established equipment.

Example KQL Query for Innovation Assessment:

AzureDiagnostics
| where Category == "InnovationLogs"
| summarize count() by bin(TimeGenerated, 1h), InnovationType, EvaluationStatus
| order by TimeGenerated desc

• Explaining Security Concepts and Techniques:
  • Ability to explain security concepts and techniques for infrastructure and IoT.

Example KQL Query for Security Technique Explanation:

LearningLogs
| where Category == "SecurityLearning"
| summarize count() by bin(TimeGenerated, 1h), Topic, CompletionStatus
| order by TimeGenerated desc

• Connecting, Testing, and Troubleshooting Networks:
  • Skills to connect, test, and troubleshoot simple networks in labs or simulations.

Example KQL Query for Network Testing:

SimulationLogs
| summarize count() by bin(TimeGenerated, 1h), TestType, Result
| order by TimeGenerated desc

General Competence

• Participating in IoT System Design Discussions:
  • Ability to engage in discussions on IoT system design.

Example KQL Query for IoT Design Discussions:

ProfessionalLogs
| where Category == "IoTDesignDiscussion"
| summarize count() by bin(TimeGenerated, 1h), Topic, Participant
| order by TimeGenerated desc

• Participating in Security Design Discussions for IoT:
  • Ability to engage in discussions on security requirements and design for IoT.

Example KQL Query for Security Design Discussions:

ProfessionalLogs
| where Category == "SecurityDesignDiscussion"
| summarize count() by bin(TimeGenerated, 1h), Topic, Participant
| order by TimeGenerated desc

• Awareness of Innovation Processes in IoT and Infrastructure:
  • Knowledge of innovation processes and new thinking in IoT and infrastructure.

Example KQL Query for Innovation Process Monitoring:

InnovationLogs
| summarize count() by bin(TimeGenerated, 1h), InnovationType, ProcessStage
| order by TimeGenerated desc

• Conducting Lab Exercises in IT Lab:
  • Ability to perform lab exercises and use IT lab facilities effectively.

Example KQL Query for Lab Exercise Monitoring:

LabExerciseLogs
| summarize count() by bin(TimeGenerated, 1h), ExerciseType, CompletionStatus
| order by TimeGenerated desc

Example KQL Cheat Sheet for IoT and ITD-Lab

# KQL Cheat Sheet for IoT and ITD-Lab

## IoT Device Connectivity

### Monitoring IoT Device Connectivity
```kql
AzureDiagnostics
| where Category == "IoTHubLogs" and Level == "Information"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeviceId, Status
| order by TimeGenerated desc

Sensor Data

Monitoring Sensor Data

IoTHubMessages
| where TimeGenerated > ago(1d)
| project TimeGenerated, DeviceId, Temperature = tostring(parse_json(Telemetry).temperature), Humidity = tostring(parse_json(Telemetry).humidity)
| summarize avg(todouble(Temperature)), avg(todouble(Humidity)) by bin(TimeGenerated, 1h), DeviceId
| order by TimeGenerated desc

Simulation Tools

Simulating IoT Device Data

SimulationLogs
| where Category == "IoTSimulation"
|

 summarize count() by bin(TimeGenerated, 1h), DeviceType, SimulationStatus
| order by TimeGenerated desc

Actuators

Monitoring Actuator Status

AzureDiagnostics
| where Category == "IoTHubLogs" and Level == "Information"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeviceId, ActuatorStatus
| order by TimeGenerated desc

Communication Protocols

Monitoring Communication Protocols

AzureDiagnostics
| where Category == "IoTHubLogs"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), Protocol, DeviceId
| order by TimeGenerated desc

Smart Home

Tracking Smart Home Device Activities

IoTHubMessages
| where TimeGenerated > ago(1d)
| where DeviceId startswith "smarthome"
| summarize count() by bin(TimeGenerated, 1h), DeviceId, Activity
| order by TimeGenerated desc

Data Security

Monitoring Data Integrity

AzureDiagnostics
| where Category == "IoTHubLogs" and Level == "Warning"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeviceId, IntegrityStatus
| order by TimeGenerated desc

Hardware Troubleshooting

Recognizing and Troubleshooting Hardware Components

AzureDiagnostics
| where Category == "DeviceHealth"
| summarize count() by bin(TimeGenerated, 1h), DeviceId, IssueType
| order by TimeGenerated desc

Lab Exercises

Conducting Lab Exercises in IT Lab

LabExerciseLogs
| summarize count() by bin(TimeGenerated, 1h), ExerciseType, CompletionStatus
| order by TimeGenerated desc

By mastering these KQL queries and understanding their applications, candidates can effectively manage and secure IoT environments, use ITD-Lab facilities, and ensure that their IoT devices and infrastructure are functioning correctly and securely. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in IoT and ITD-Lab activities.

### KQL for Programming Monitoring and Analysis in Azure and Microsoft Sentinel

#### Theme: Programming

**Content:**

1. **Program Structures**
2. **Data Structures**
3. **Libraries**
4. **Functions and Methods**
5. **Object-Oriented Programming**
6. **Debugging, Testing, and Exception Handling**
7. **API**
8. **GUI**
9. **UML**
10. **File Handling**
11. **Data Security**

### KQL Queries for Monitoring Programming Activities

**Program Structures**

1. **Monitoring Code Deployment**:
   - Track code deployments to ensure proper deployment and identify any issues.

**Example KQL Query for Code Deployment**:
```kql
AzureDiagnostics
| where Category == "DeploymentLogs"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeploymentId, Status
| order by TimeGenerated desc

Data Structures

1. Monitoring Data Structure Usage:
   • Track the usage of various data structures to optimize performance and detect issues.

Example KQL Query for Data Structure Usage:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.DataStructure in ("Array", "List", "Dictionary")
| summarize count() by bin(timestamp, 1h), customDimensions.DataStructure
| order by timestamp desc

Libraries

1. Tracking Library Imports:
   • Monitor the import of libraries to ensure the use of approved and secure libraries.

Example KQL Query for Library Imports:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.LibraryImported != ""
| summarize count() by bin(timestamp, 1h), customDimensions.LibraryImported
| order by timestamp desc

Functions and Methods

1. Monitoring Function Calls:
   • Track function calls to ensure they are performing as expected.

Example KQL Query for Function Calls:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.FunctionName != ""
| summarize count() by bin(timestamp, 1h), customDimensions.FunctionName
| order by timestamp desc

Object-Oriented Programming

1. Monitoring Class Instantiations:
   • Track the instantiation of classes to ensure objects are created and used correctly.

Example KQL Query for Class Instantiations:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.ClassName != ""
| summarize count() by bin(timestamp, 1h), customDimensions.ClassName
| order by timestamp desc

Debugging, Testing, and Exception Handling

1. Monitoring Debug Logs:
   • Track debug logs to identify and resolve issues.

Example KQL Query for Debug Logs:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.LogLevel == "Debug"
| summarize count() by bin(timestamp, 1h), customDimensions.Message
| order by timestamp desc

2. Monitoring Exception Handling:
   • Track exceptions to ensure they are handled properly.

Example KQL Query for Exception Handling:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.LogLevel == "Error"
| summarize count() by bin(timestamp, 1h), customDimensions.ExceptionType
| order by timestamp desc

API

1. Monitoring API Calls:
   • Track API calls to ensure they are functioning correctly and identify any issues.

Example KQL Query for API Calls:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.ApiEndpoint != ""
| summarize count() by bin(timestamp, 1h), customDimensions.ApiEndpoint, customDimensions.ResponseStatus
| order by timestamp desc

GUI

1. Monitoring User Interface Interactions:
   • Track interactions with the user interface to ensure a smooth user experience.

Example KQL Query for GUI Interactions:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.InteractionType != ""
| summarize count() by bin(timestamp, 1h), customDimensions.InteractionType
| order by timestamp desc

UML

1. Tracking UML Diagram Changes:
   • Monitor changes to UML diagrams to ensure design consistency.

Example KQL Query for UML Diagram Changes:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.ChangeType == "UMLDiagram"
| summarize count() by bin(timestamp, 1h), customDimensions.DiagramName
| order by timestamp desc

File Handling

1. Monitoring File Operations:
   • Track file read/write operations to ensure data integrity and security.

Example KQL Query for File Operations:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.OperationType in ("Read", "Write")
| summarize count() by bin(timestamp, 1h), customDimensions.FileName, customDimensions.OperationType
| order by timestamp desc

Data Security

1. Monitoring Data Access:
   • Track access to sensitive data to ensure security and compliance.

Example KQL Query for Data Access:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.DataAccessType != ""
| summarize count() by bin(timestamp, 1h), customDimensions.UserId, customDimensions.DataAccessType
| order by timestamp desc

Learning Outcomes

Knowledge

• Fundamental Programming Concepts:
  • Understanding basic programming concepts and tools.

Example KQL Query for Programming Concept Monitoring:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.Concept != ""
| summarize count() by bin(timestamp, 1h), customDimensions.Concept
| order by timestamp desc

• Basic Programming Techniques:
  • Knowledge of variable declarations, control structures, loops, and functions.

Example KQL Query for Programming Techniques:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.Technique in ("VariableDeclaration", "ControlStructure", "Loop", "Function")
| summarize count() by bin(timestamp, 1h), customDimensions.Technique
| order by timestamp desc

• User Interface Development:
  • Understanding the development of user interfaces.

Example KQL Query for UI Development:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.DevelopmentType == "UI"
| summarize count() by bin(timestamp, 1h), customDimensions.Component
| order by timestamp desc

• Communication Between Program Interfaces:
  • Knowledge of communication between different program interfaces.

Example KQL Query for Program Interface Communication:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.InterfaceType != ""
| summarize count() by bin(timestamp, 1h), customDimensions.InterfaceType
| order by timestamp desc

• Application Security:
  • Understanding the importance of application security.

Example KQL Query for Application Security Monitoring:

SecurityAlert
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), AlertType, Severity
| order by TimeGenerated desc

• Learning New Programming Knowledge:
  • Ability to learn new programming concepts using relevant literature and resources.

Example KQL Query for Programming Learning:

LearningLogs
| where timestamp > ago(1d)
| summarize count() by bin(timestamp, 1h), Topic, CompletionStatus
| order by timestamp desc

Skills

• Developing User Interfaces with Code:
  • Skills to develop user interfaces using code and development tools.

Example KQL Query for UI Development Skills:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.DevelopmentType == "UI"
| summarize count() by bin(timestamp, 1h), customDimensions.Component
| order by timestamp desc

• Writing Code with Variables, Control Structures, Loops, and Functions:
  • Skills to write code with basic programming constructs.

Example KQL Query for Writing Code:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.CodeType in ("Variable", "ControlStructure", "Loop", "Function")
| summarize count() by bin(timestamp, 1h), customDimensions.CodeType
| order by timestamp desc

• Reading and Interpreting Code:
  • Ability to read and understand code written by others.

Example KQL Query for Code Interpretation:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.Action == "CodeReview"
| summarize count() by bin(timestamp, 1h), customDimensions.FileName
| order by timestamp desc

• Maintaining and Improving Existing Applications:
  • Skills to maintain and enhance existing applications.

Example KQL Query for Application Maintenance:

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.Action == "Maintenance"
| summarize count() by bin(timestamp, 1h), customDimensions.ApplicationName
| order by timestamp desc

• Discussing Application Security:
  • Ability to discuss and implement basic security measures for applications.

Example KQL Query for Security Discussion:

ApplicationInsights
|

 where timestamp > ago(1d)
| where customDimensions.Action == "SecurityDiscussion"
| summarize count() by bin(timestamp, 1h), customDimensions.Topic
| order by timestamp desc

General Competence

• Maintaining and Developing Programming Competence:
  • Commitment to continuous learning and development in programming.

Example KQL Query for Professional Development:

LearningLogs
| where timestamp > ago(1d)
| summarize count() by bin(timestamp, 1h), Topic, CompletionStatus
| order by timestamp desc

• Active Participation in Projects:
  • Ability to actively participate in programming projects.

Example KQL Query for Project Participation:

ProjectLogs
| where timestamp > ago(1d)
| summarize count() by bin(timestamp, 1h), ProjectName, Role
| order by timestamp desc

• Working According to Client Needs:
  • Ability to work according to the needs of the client.

Example KQL Query for Client Interaction:

ClientInteractionLogs
| where timestamp > ago(1d)
| summarize count() by bin(timestamp, 1h), ClientName, RequestType
| order by timestamp desc

• Exchanging Industry-related Views and Information:
  • Ability to exchange views and information related to the programming industry.

Example KQL Query for Industry Communication:

ProfessionalLogs
| where timestamp > ago(1d)
| summarize count() by bin(timestamp, 1h), CommunicationType, Topic
| order by timestamp desc

• Building Relationships Across Fields:
  • Ability to build relationships internally and externally, across different fields.

Example KQL Query for Relationship Building:

RelationshipLogs
| where timestamp > ago(1d)
| summarize count() by bin(timestamp, 1h), RelationshipType, Participant
| order by timestamp desc

Example KQL Cheat Sheet for Programming

# KQL Cheat Sheet for Programming

## Code Deployment

### Monitoring Code Deployment
```kql
AzureDiagnostics
| where Category == "DeploymentLogs"
| where TimeGenerated > ago(1d)
| summarize count() by bin(TimeGenerated, 1h), DeploymentId, Status
| order by TimeGenerated desc

Data Structures

Monitoring Data Structure Usage

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.DataStructure in ("Array", "List", "Dictionary")
| summarize count() by bin(timestamp, 1h), customDimensions.DataStructure
| order by timestamp desc

Libraries

Tracking Library Imports

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.LibraryImported != ""
| summarize count() by bin(timestamp, 1h), customDimensions.LibraryImported
| order by timestamp desc

Functions and Methods

Monitoring Function Calls

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.FunctionName != ""
| summarize count() by bin(timestamp, 1h), customDimensions.FunctionName
| order by timestamp desc

Object-Oriented Programming

Monitoring Class Instantiations

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.ClassName != ""
| summarize count() by bin(timestamp, 1h), customDimensions.ClassName
| order by timestamp desc

Debugging and Exception Handling

Monitoring Debug Logs

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.LogLevel == "Debug"
| summarize count() by bin(timestamp, 1h), customDimensions.Message
| order by timestamp desc

Monitoring Exception Handling

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.LogLevel == "Error"
| summarize count() by bin(timestamp, 1h), customDimensions.ExceptionType
| order by timestamp desc

API

Monitoring API Calls

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.ApiEndpoint != ""
| summarize count() by bin(timestamp, 1h), customDimensions.ApiEndpoint, customDimensions.ResponseStatus
| order by timestamp desc

GUI

Monitoring User Interface Interactions

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.InteractionType != ""
| summarize count() by bin(timestamp, 1h), customDimensions.InteractionType
| order by timestamp desc

UML

Tracking UML Diagram Changes

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.ChangeType == "UMLDiagram"
| summarize count() by bin(timestamp, 1h), customDimensions.DiagramName
| order by timestamp desc

File Handling

Monitoring File Operations

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.OperationType in ("Read", "Write")
| summarize count() by bin(timestamp, 1h), customDimensions.FileName, customDimensions.OperationType
| order by timestamp desc

Data Security

Monitoring Data Access

ApplicationInsights
| where timestamp > ago(1d)
| where customDimensions.DataAccessType != ""
| summarize count() by bin(timestamp, 1h), customDimensions.UserId, customDimensions.DataAccessType
| order by timestamp desc

By mastering these KQL queries and understanding their applications, candidates can effectively monitor and analyze programming activities in their environments, ensuring that their applications are secure, functional, and well-maintained. This supports the learning outcomes of gaining knowledge, developing skills, and achieving general competence in programming.

For å vise hvordan du praktisk kan bruke KQL (Kusto Query Language) i ditt Azure-miljø for å understøtte økonomistyring, organisasjon og ledelse, og markedsføringsledelse, vil vi gå gjennom konkrete eksempler. Du kan kjøre disse KQL-forespørslene i Azure Monitor Logs, Azure Sentinel, eller et annet logganalyseverktøy som støtter KQL.

### Økonomistyring

#### 1. Overvåke lønnskostnader

**Forespørsel for å hente lønnskostnader fra økonomisystemet:**
```kql
let payrollData = externaldata(
    EmployeeId: string,
    EmployeeName: string,
    Department: string,
    Salary: real,
    Bonus: real,
    PayDate: datetime
) [ 
    h@"https://yourstorageaccount.blob.core.windows.net/yourcontainer/payroll.csv"
    with (format="csv", ignoreFirstRecord=True)
];
payrollData
| summarize TotalSalary = sum(Salary), TotalBonus = sum(Bonus) by Department
| extend TotalCost = TotalSalary + TotalBonus
| order by TotalCost desc

Organisasjon og ledelse

2. Analysere organisasjonsstruktur

Forespørsel for å hente organisasjonsdata og analysere struktur:

let orgData = externaldata(
    EmployeeId: string,
    EmployeeName: string,
    Department: string,
    ManagerId: string,
    Role: string,
    JoinDate: datetime
) [
    h@"https://yourstorageaccount.blob.core.windows.net/yourcontainer/organization.csv"
    with (format="csv", ignoreFirstRecord=True)
];
orgData
| summarize EmployeesCount = count() by Department, Role
| order by Department asc

3. Overvåke og motivere personalet

Forespørsel for å hente personaldata og analysere arbeidsmiljø:

let employeeSatisfaction = externaldata(
    EmployeeId: string,
    SatisfactionScore: int,
    SurveyDate: datetime
) [
    h@"https://yourstorageaccount.blob.core.windows.net/yourcontainer/satisfaction.csv"
    with (format="csv", ignoreFirstRecord=True)
];
employeeSatisfaction
| summarize AvgSatisfaction = avg(SatisfactionScore) by bin(SurveyDate, 1d)
| order by SurveyDate desc

Markedsføringsledelse

4. Overvåke markedsføringskampanjer

Forespørsel for å hente data om markedsføringskampanjer og analysere effektivitet:

let marketingCampaigns = externaldata(
    CampaignId: string,
    CampaignName: string,
    StartDate: datetime,
    EndDate: datetime,
    Budget: real,
    ActualSpend: real,
    LeadsGenerated: int,
    Conversions: int
) [
    h@"https://yourstorageaccount.blob.core.windows.net/yourcontainer/campaigns.csv"
    with (format="csv", ignoreFirstRecord=True)
];
marketingCampaigns
| summarize TotalSpend = sum(ActualSpend), TotalLeads = sum(LeadsGenerated), TotalConversions = sum(Conversions) by CampaignName
| extend ConversionRate = TotalConversions * 100.0 / TotalLeads
| order by ConversionRate desc

Eksempel på praktisk kjøring av KQL i Azure

1. Azure Monitor Logs:

   • Gå til Azure-portalen.
   • Naviger til "Log Analytics workspaces" og velg ditt arbeidsområde.
   • Klikk på "Logs" under "General".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

2. Azure Sentinel:

   • Gå til Azure-portalen.
   • Naviger til "Microsoft Sentinel" og velg ditt Sentinel-arbeidsområde.
   • Klikk på "Logs" under "Configuration".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

3. Application Insights:

   • Gå til Azure-portalen.
   • Naviger til "Application Insights" og velg din applikasjon.
   • Klikk på "Logs" under "Monitoring".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

Disse eksemplene viser hvordan du kan bruke KQL til å trekke ut og analysere data relatert til økonomistyring, organisasjon og ledelse, og markedsføringsledelse. Ved å bruke Azure Monitor Logs, Azure Sentinel, eller Application Insights, kan du overvåke og analysere dataene på en effektiv måte.

Ja, KQL kan brukes til å understøtte temaer innen norsk og engelsk ved å overvåke og analysere data som er relevant for kommunikasjon, språkbruk, kildebruk, og prosjektarbeid. Dette kan være spesielt nyttig i et utdanningsmiljø eller en organisasjon som ønsker å forbedre språkferdigheter, kommunikasjonsflyt og prosjektledelse.

Eksempler på KQL for å understøtte temaer innen norsk og engelsk

Norsk

Studieteknikk og kritisk kildebruk

1. Overvåking av kildebruk og referanser

Forespørsel for å hente data om kildebruk i studentoppgaver:

StudentAssignments
| where TimeSubmitted > ago(30d)
| extend Sources = parse_json(References)
| mv-expand Sources
| summarize count() by Sources
| order by count_ desc

2. Analyse av skriftlig kommunikasjon og skriveregler

Forespørsel for å analysere hyppige skrivefeil i innleveringer:

StudentSubmissions
| where TimeSubmitted > ago(30d)
| summarize FrequentErrors = countif(ErrorType == "Spelling") by DocumentId, StudentId
| order by FrequentErrors desc

Struktur og leservennlig layout

3. Overvåking av dokumentstruktur

Forespørsel for å sjekke om dokumenter følger en bestemt struktur:

DocumentStructureLogs
| where TimeGenerated > ago(30d)
| summarize count() by DocumentType, IsStructuredCorrectly
| order by count_ desc

Prosjektarbeid og prosjektledelse

4. Overvåking av prosjektfremdrift

Forespørsel for å hente data om fremdrift i tverrfaglige prosjekter:

ProjectLogs
| where ProjectDeadline > now()
| summarize CompletedTasks = countif(Status == "Completed"), PendingTasks = countif(Status == "Pending") by ProjectName
| order by ProjectName asc

Refleksjon over egen læring

5. Analyse av refleksjonslogger

Forespørsel for å hente data fra refleksjonslogger for å analysere læringsutbytte:

ReflectionLogs
| where TimeGenerated > ago(30d)
| summarize AvgReflectionScore = avg(ReflectionScore) by StudentId
| order by AvgReflectionScore desc

Engelsk

Grammatikk, setningsoppbygging og fagterminologi

1. Overvåking av grammatikkbruk i tekster

Forespørsel for å analysere grammatikkfeil i innleverte tekster:

TextSubmissions
| where TimeSubmitted > ago(30d)
| summarize GrammarErrors = countif(ErrorType == "Grammar") by DocumentId, StudentId
| order by GrammarErrors desc

Forståelse av tekster og produksjon av egne tekster

2. Analyse av tekstproduksjon

Forespørsel for å overvåke tekstproduksjon og forståelse:

TextAnalysis
| where TimeGenerated > ago(30d)
| summarize AvgReadingLevel = avg(ReadingLevel), AvgWritingScore = avg(WritingScore) by StudentId
| order by AvgWritingScore desc

Diskusjoner, presentasjoner og gruppearbeid

3. Overvåking av deltakelse i gruppearbeid

Forespørsel for å hente data om deltakelse og bidrag i gruppearbeid:

GroupWorkLogs
| where TimeGenerated > ago(30d)
| summarize ParticipationCount = count() by GroupId, StudentId
| order by ParticipationCount desc

IKT-verktøy

4. Bruk av digitale verktøy

Forespørsel for å hente data om bruk av IKT-verktøy i oppgaver:

ToolUsageLogs
| where TimeGenerated > ago(30d)
| summarize ToolUsageCount = count() by ToolName, StudentId
| order by ToolUsageCount desc

Hvordan kjøre KQL-forespørsler i Azure

For å kjøre disse forespørslene i Azure, kan du bruke Azure Monitor Logs, Azure Sentinel, eller Application Insights:

1. Azure Monitor Logs:

   • Gå til Azure-portalen.
   • Naviger til "Log Analytics workspaces" og velg ditt arbeidsområde.
   • Klikk på "Logs" under "General".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

2. Azure Sentinel:

   • Gå til Azure-portalen.
   • Naviger til "Microsoft Sentinel" og velg ditt Sentinel-arbeidsområde.
   • Klikk på "Logs" under "Configuration".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

3. Application Insights:

   • Gå til Azure-portalen.
   • Naviger til "Application Insights" og velg din applikasjon.
   • Klikk på "Logs" under "Monitoring".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

Disse eksemplene viser hvordan KQL kan brukes til å overvåke og analysere aktiviteter og data relatert til temaene norsk og engelsk, inkludert skriftlig og muntlig kommunikasjon, kildebruk, prosjektarbeid, og bruk av IKT-verktøy. Dette vil hjelpe deg med å oppnå læringsutbyttene ved å få innsikt i hvordan disse elementene fungerer i praksis.

KQL for Supporting Mathematics and Physics Topics in Azure

KQL (Kusto Query Language) can be used to analyze and visualize data related to various mathematics and physics topics. Here's how you can leverage KQL for different subjects in mathematics and physics.

Mathematics

Algebra

1. Solve Linear Equations

While KQL itself does not solve equations, it can be used to analyze datasets containing algebraic solutions or results. For instance, you can use KQL to visualize the solutions of linear equations stored in a dataset.

Example: Visualizing Solutions of Linear Equations:

let linearEquations = datatable (Equation: string, Solution: real)
[
    "2x + 3 = 7", 2,
    "x - 5 = 10", 15,
    "3x + 6 = 12", 2
];
linearEquations
| project Equation, Solution

Regneregler

2. Analyzing Arithmetic Operations in a Dataset

Example: Summarizing Arithmetic Operations:

let arithmeticOperations = datatable (Operation: string, Operand1: real, Operand2: real, Result: real)
[
    "Addition", 5, 3, 8,
    "Subtraction", 10, 4, 6,
    "Multiplication", 7, 2, 14,
    "Division", 8, 2, 4
];
arithmeticOperations
| summarize count() by Operation

Trigonometry and Geometry

3. Calculate Areas and Perimeters

Example: Calculate Areas and Perimeters of Shapes:

let shapes = datatable (Shape: string, Dimension1: real, Dimension2: real)
[
    "Rectangle", 5, 10,
    "Triangle", 6, 8,
    "Circle", 7, 0
];
shapes
| extend Area = case(Shape == "Rectangle", Dimension1 * Dimension2,
                     Shape == "Triangle", 0.5 * Dimension1 * Dimension2,
                     Shape == "Circle", 3.14 * Dimension1 * Dimension1,
                     0)
| extend Perimeter = case(Shape == "Rectangle", 2 * (Dimension1 + Dimension2),
                          Shape == "Triangle", Dimension1 + Dimension2 + sqrt(Dimension1*Dimension1 + Dimension2*Dimension2),
                          Shape == "Circle", 2 * 3.14 * Dimension1,
                          0)
| project Shape, Area, Perimeter

Functions

4. Analyze Polynomial Functions

Example: Visualizing Polynomial Functions:

let polynomialFunctions = datatable (Function: string, Coefficients: dynamic)
[
    "y = x^2 + 2x + 1", dynamic([1, 2, 1]),
    "y = 2x^2 - 3x + 5", dynamic([2, -3, 5]),
    "y = -x^2 + 4x - 4", dynamic([-1, 4, -4])
];
polynomialFunctions
| project Function, Coefficients

Physics

Kraft og rettlinjet bevegelse

1. Analyze Motion Data

Example: Visualizing Motion Data:

let motionData = datatable (Time: real, Position: real, Velocity: real)
[
    0, 0, 0,
    1, 5, 5,
    2, 20, 10,
    3, 45, 15,
    4, 80, 20
];
motionData
| project Time, Position, Velocity

Energi

2. Calculate Kinetic and Potential Energy

Example: Calculating Energy Values:

let energyData = datatable (Mass: real, Height: real, Velocity: real)
[
    10, 5, 10,
    15, 10, 20,
    20, 15, 25
];
energyData
| extend KineticEnergy = 0.5 * Mass * Velocity * Velocity
| extend PotentialEnergy = Mass * 9.81 * Height
| project Mass, Height, Velocity, KineticEnergy, PotentialEnergy

Newton's Laws

3. Applying Newton's Second Law

Example: Visualizing Force Calculations:

let forceData = datatable (Mass: real, Acceleration: real)
[
    5, 10,
    10, 20,
    15, 25
];
forceData
| extend Force = Mass * Acceleration
| project Mass, Acceleration, Force

Running KQL Queries in Azure

To run these KQL queries in Azure, you can use Azure Monitor Logs or Azure Sentinel:

1. Azure Monitor Logs:

   • Go to Azure Portal.
   • Navigate to "Log Analytics workspaces" and select your workspace.
   • Click on "Logs" under "General".
   • Paste your KQL query in the query window and click "Run".

2. Azure Sentinel:

   • Go to Azure Portal.
   • Navigate to "Microsoft Sentinel" and select your Sentinel workspace.
   • Click on "Logs" under "Configuration".
   • Paste your KQL query in the query window and click "Run".

These examples demonstrate how KQL can be used to support topics in mathematics and physics by analyzing and visualizing relevant data. By leveraging Azure Monitor Logs or Azure Sentinel, you can monitor and analyze data effectively to support your learning and teaching objectives.

For å støtte temaene matematikk og fysikk i IT-drift og sikkerhet med KQL, kan vi bruke Kusto Query Language for å analysere og visualisere data som er relevant for disse fagområdene. Nedenfor finner du konkrete eksempler på hvordan du kan bruke KQL til å utføre beregninger, analysere data og støtte læringsutbytte innen matematikk og fysikk.

Matematikk

Algebra

Løse ligninger av første og andre grad

Selv om KQL ikke løser ligninger direkte, kan vi bruke KQL til å visualisere resultater og analysere data fra ligninger som er løst.

Eksempel: Visualisering av løsninger på lineære og kvadratiske ligninger

let equations = datatable (Ligning: string, Løsning: real)
[
    "2x + 3 = 7", 2.0,
    "x^2 - 4x + 4 = 0", 2.0
];
equations
| project Ligning, Løsning

Brøk og prosentregning

Eksempel: Beregning og visualisering av prosentandel

let percentageData = datatable (Kategori: string, Antall: int)
[
    "A", 40,
    "B", 30,
    "C", 20,
    "D", 10
];
percentageData
| extend Total = sum(Antall) over ()
| extend Prosent = Antall * 100.0 / Total
| project Kategori, Antall, Prosent

Fysikk

Kraft og rettlinjet bevegelse

Anvende Newtons lover

Eksempel: Beregning av kraft ved hjelp av Newtons andre lov

let bevegelsesdata = datatable (Masse: real, Akselerasjon: real)
[
    5.0, 2.0,
    10.0, 1.5,
    15.0, 3.0
];
bevegelsesdata
| extend Kraft = Masse * Akselerasjon
| project Masse, Akselerasjon, Kraft

Energi

Beregne kinetisk og potensiell energi

Eksempel: Beregning av kinetisk og potensiell energi

let energidata = datatable (Masse: real, Høyde: real, Fart: real)
[
    10.0, 5.0, 10.0,
    20.0, 3.0, 15.0,
    15.0, 4.0, 12.0
];
energidata
| extend KinetiskEnergi = 0.5 * Masse * Fart * Fart
| extend PotensiellEnergi = Masse * 9.81 * Høyde
| project Masse, Høyde, Fart, KinetiskEnergi, PotensiellEnergi

Hvordan kjøre KQL-forespørsler i Azure

For å kjøre disse KQL-forespørslene i Azure, kan du bruke Azure Monitor Logs, Azure Sentinel eller Application Insights. Her er trinnene for å kjøre KQL-forespørsler i Azure Monitor Logs:

1. Azure Monitor Logs:

   • Gå til Azure-portalen.
   • Naviger til "Log Analytics workspaces" og velg ditt arbeidsområde.
   • Klikk på "Logs" under "General".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

2. Azure Sentinel:

   • Gå til Azure-portalen.
   • Naviger til "Microsoft Sentinel" og velg ditt Sentinel-arbeidsområde.
   • Klikk på "Logs" under "Configuration".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

3. Application Insights:

   • Gå til Azure-portalen.
   • Naviger til "Application Insights" og velg din applikasjon.
   • Klikk på "Logs" under "Monitoring".
   • Lim inn KQL-forespørselen i spørringsvinduet og klikk på "Run".

Disse eksemplene viser hvordan du kan bruke KQL til å analysere og visualisere data innen matematikk og fysikk for å støtte læringsutbytte og praktiske anvendelser i IT-drift og sikkerhet.

KQL for Supporting Topics in IT Operations and Security

Økonomistyring

1. Monitor and Analyze Financial Data

KQL can be used to monitor financial data, such as budgets and expenses, to ensure proper financial management.

Example: Monitoring IT Department Budget Utilization

let budgetData = datatable (Category: string, Budget: real, Expense: real)
[
    "Hardware", 50000, 45000,
    "Software", 30000, 28000,
    "Services", 20000, 21000,
    "Training", 10000, 9500
];
budgetData
| extend RemainingBudget = Budget - Expense
| project Category, Budget, Expense, RemainingBudget
| order by RemainingBudget desc

Organisasjon og ledelse

2. Track Project Progress and Resource Allocation

Use KQL to monitor the progress of various projects and the allocation of resources.

Example: Monitoring Project Progress

let projectProgress = datatable (ProjectName: string, TotalTasks: int, CompletedTasks: int, ResourceAllocated: real)
[
    "Network Upgrade", 100, 85, 30000,
    "Server Migration", 80, 70, 25000,
    "Security Audit", 60, 50, 20000,
    "Cloud Integration", 90, 80, 35000
];
projectProgress
| extend CompletionRate = CompletedTasks * 100.0 / TotalTasks
| project ProjectName, TotalTasks, CompletedTasks, CompletionRate, ResourceAllocated
| order by CompletionRate desc

3. Analyze Team Performance and Collaboration

Use KQL to analyze the performance of different teams and their collaboration efforts.

Example: Team Performance and Collaboration Analysis

let teamPerformance = datatable (Team: string, TasksAssigned: int, TasksCompleted: int, CollaborationScore: real)
[
    "Network Team", 50, 45, 4.5,
    "Security Team", 40, 35, 4.8,
    "Support Team", 60, 55, 4.6,
    "Development Team", 70, 65, 4.7
];
teamPerformance
| extend PerformanceRate = TasksCompleted * 100.0 / TasksAssigned
| project Team, TasksAssigned, TasksCompleted, PerformanceRate, CollaborationScore
| order by PerformanceRate desc

Markedsføringsledelse

4. Monitor Marketing Campaign Performance

Use KQL to monitor the performance of different marketing campaigns.

Example: Analyzing Marketing Campaign Effectiveness

let marketingCampaigns = datatable (CampaignName: string, Budget: real, Spend: real, LeadsGenerated: int, Conversions: int)
[
    "Campaign A", 10000, 9500, 500, 45,
    "Campaign B", 15000, 14000, 700, 60,
    "Campaign C", 12000, 11000, 650, 55,
    "Campaign D", 9000, 8500, 400, 35
];
marketingCampaigns
| extend ConversionRate = Conversions * 100.0 / LeadsGenerated
| project CampaignName, Budget, Spend, LeadsGenerated, Conversions, ConversionRate
| order by ConversionRate desc

Matematikk og fysikk i IT-drift

5. Network Performance Analysis using Mathematical Calculations

Use KQL to calculate and analyze network performance metrics such as throughput and latency.

Example: Calculating Network Throughput and Latency

let networkData = datatable (Device: string, DataTransferred: real, TimeTaken: real)
[
    "Router A", 500, 10,
    "Router B", 1000, 20,
    "Router C", 750, 15,
    "Router D", 1200, 25
];
networkData
| extend Throughput = DataTransferred / TimeTaken
| project Device, DataTransferred, TimeTaken, Throughput
| order by Throughput desc

6. Analyzing Energy Consumption in Data Centers

Use KQL to calculate and monitor energy consumption metrics in data centers.

Example: Calculating Energy Consumption and Efficiency

let energyData = datatable (Server: string, PowerUsage: real, HoursActive: real)
[
    "Server 1", 500, 24,
    "Server 2", 400, 24,
    "Server 3", 600, 24,
    "Server 4", 450, 24
];
energyData
| extend EnergyConsumption = PowerUsage * HoursActive
| project Server, PowerUsage, HoursActive, EnergyConsumption
| order by EnergyConsumption desc

+++

Running KQL Queries in Azure

To execute these KQL queries in Azure, follow these steps:

Azure Monitor Logs:

1. Navigate to Azure Portal.
2. Go to "Log Analytics workspaces" and select your workspace.
3. Click on "Logs" under "General".
4. Paste your KQL query in the query window and click "Run".

Azure Sentinel:

1. Navigate to Azure Portal.
2. Go to "Microsoft Sentinel" and select your Sentinel workspace.
3. Click on "Logs" under "Configuration".
4. Paste your KQL query in the query window and click "Run".

Application Insights:

1. Navigate to Azure Portal.
2. Go to "Application Insights" and select your application.
3. Click on "Logs" under "Monitoring".
4. Paste your KQL query in the query window and click "Run".

By using KQL in these environments, you can analyze and visualize.

Task 2: Run Basic KQL Statements

In this task, you will build basic KQL statements.

Important: For each query, clear the previous statement from the Query Window or open a new Query Window by selecting + after the last opened tab (up to 25).

The following statement demonstrates the search operator, which searches all columns in the table for the value.

Change the Time range to Last 30 minutes in the Query Window.

In the Query Window enter the following statement and select Run:

search "location"

Using the Search operator without specific tables or qualifying clauses is less efficient than table-specific and column-specific text filtering.

The following statement demonstrates search across tables listed within the in clause. In the Query Window enter the following statement and select Run:

search in (SecurityEvent,App*) "new"

Change back the Time range to Last 24 hours in the Query Window.

The following statements demonstrate the where operator, which filters on a specific predicate. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h)

The Time range now shows "Set in query" since we are filtering with the TimeGenerated column.

SecurityEvent
| where TimeGenerated > ago(1h) and EventID == 4624

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| where AccountType =~ "user"

SecurityEvent
| where TimeGenerated > ago(1h) and EventID in (4624, 4625)

The following statement demonstrates the use of the let statement to declare variables. In the Query Window enter the following statement and select Run:

let timeOffset = 1h; 
let discardEventId = 4688; 
SecurityEvent 
| where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset) 
| where EventID != discardEventId

The following statement demonstrates the use of the let statement to declare a dynamic list. In the Query Window enter the following statement and select Run:

let suspiciousAccounts = datatable(account: string) [ @"NA\timadmin", @"NT AUTHORITY\SYSTEM" ]; 
SecurityEvent
| where TimeGenerated > ago(1h) 
| where Account in (suspiciousAccounts)

Tip: You can re-format the query easily by selecting the ellipsis (…) in the Query window and select Format query.

The following statement demonstrates the use of the let statement to declare a dynamic table. In the Query Window enter the following statement and select Run:

let LowActivityAccounts = SecurityEvent | summarize cnt = count() by Account | where cnt < 1000; 
LowActivityAccounts | where Account contains "sql"

Change the Time range to Last hour in the Query Window. This will limit our results for the following statements.

The following statement demonstrates the extend operator, which creates a calculated column and adds it to the result set. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) 
| where ProcessName != "" and Process != "" 
| extend StartDir = substring(ProcessName,0, string_size(ProcessName)-string_size(Process))

The following statement demonstrates the order by operator, which sorts the rows of the input table by one or more columns in ascending or descending order. The order by operator is an alias to the sort by operator. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) 
| where ProcessName != "" and Process != "" 
| extend StartDir = substring(ProcessName,0, string_size(ProcessName)-string_size(Process)) 
| order by StartDir desc, Process asc

The following statements demonstrate the project operator, which selects the columns to include in the order specified. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) 
| where ProcessName != "" and Process != "" 
| extend StartDir = substring(ProcessName,0, string_size(ProcessName)-string_size(Process)) 
| order by StartDir desc, Process asc 
| project Process, StartDir

The following statements demonstrate the project-away operator, which selects the columns to exclude from the output. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) 
| where ProcessName != "" and Process != "" 
| extend StartDir = substring(ProcessName,0, string_size(ProcessName)-string_size(Process)) 
| order by StartDir desc, Process asc 
| project-away ProcessName

Task 3: Analyze Results in KQL with the Summarize Operator

In this task, you will build KQL statements to aggregate data. Summarize groups the rows according to the by group columns and calculates aggregations over each group.

The following statement demonstrates the count() function, which returns a count of the group. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) and EventID == 4688
| summarize count() by Process, Computer

The following statement demonstrates the count() function, but in this example, we name the column as cnt. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) and EventID == 4624
| summarize cnt=count() by AccountType, Computer

The following statement demonstrates the dcount() function, which returns an approximate distinct count of the group elements. In the Query Window enter the following statement and select Run:

SecurityEvent
| where TimeGenerated > ago(1h) 
| summarize dcount(IpAddress)

The following statement is a rule to detect Invalid password failures across multiple applications for the same account. In the Query Window enter the following statement and select Run:

let timeframe = 30d; 
let threshold = 1; 
SigninLogs 
| where TimeGenerated >= ago(timeframe) 
| where ResultDescription has "Invalid password" 
| summarize applicationCount = dcount(AppDisplayName) by UserPrincipalName, IPAddress 
| where applicationCount >= threshold

The following statement demonstrates the arg_max() function, which returns one or more expressions when the argument is maximized. The following statement will return the most current row from the SecurityEvent table for the computer SQL10.NA.contosohotels.com. The * in the arg_max function requests all columns for the row. In the Query Window enter the following statement and select Run:

SecurityEvent
| where Computer == "SQL10.na.contosohotels.com" 
| summarize arg_max(TimeGenerated,*) by Computer

The following statement demonstrates the arg_min() function, which returns one or more expressions when the argument is minimized. In this statement, the oldest SecurityEvent for the computer SQL10.NA.contosohotels.com will be returned as the result set. In the Query Window enter the following statement and select Run:

SecurityEvent
| where Computer == "SQL10.na.contosohotels.com" 
| summarize arg_min(TimeGenerated,*) by Computer

The following statements demonstrate the importance of understanding results based on the order of the pipe. In the Query Window enter the following queries and run each query separately:

Query 1 will have Accounts for which the last activity was a login. The SecurityEvent table will first be summarized and return the most current row for each Account. Then only rows with EventID equals 4624 (login) will be returned.

SecurityEvent
| summarize arg_max(TimeGenerated, *) by Account 
| where EventID == 4624

Query 2 will have the most recent login for Accounts that have logged in. The SecurityEvent table will be filtered to only include EventID = 4624. Then these results will be summarized for the most current login row by Account.

SecurityEvent
| where EventID == 4624
| summarize arg_max(TimeGenerated, *) by Account

You can also review the "Total CPU" and "Data used for processed query" by selecting the "Query details" link on the lower right and compare the data between both statements.

The following statement demonstrates the make_list() function, which returns a list of all the values within the group. This KQL query will first filter the EventID with the where operator.

+++

KQL Courses on Pluralsight

Perform Basic Search Functions in Kibana 7 with Kibana Query Language (KQL)

Author: Saravanan Dhandapani

Description: This course covers how to write queries using KQL in Kibana for analyzing, visualizing, and troubleshooting system and application logs. Learn to search, filter, and group logs to identify security vulnerabilities.

---

Kusto Query Language (KQL) from Scratch

Description: Gain a comprehensive understanding of Kusto Query Language for querying Azure services. This course covers basic syntax, advanced topics like machine learning, time series analysis, and data exportation.

---

Microsoft Azure Data Explorer - Advanced KQL

Description: Delve into advanced KQL topics within Azure Data Explorer. This course is designed for those who have a foundational understanding of KQL and want to deepen their knowledge.

---

Kusto Query Language: Getting Started

Description: This introductory course provides a solid foundation in Kusto Query Language, explaining what KQL is, its usage, and fundamental concepts and definitions.

---

Exploring Data in Microsoft Azure Using Kusto Query Language and Azure Data Explorer

Description: Learn about Azure's data exploration services and how to perform end-to-end data analytics using KQL and Azure Data Explorer, gaining the skills necessary for data science applications. +++

+++

Course Overview

Course Overview

Kusto Query Language from Scratch.

Major Topics Covered:

• Scalar operators and aggregations
• Working with datasets
• Machine learning
• Time series analysis

Introduction

Course is broken into nine modules, including this one, the introduction.

Introduction Module:

• Learn what KQL is and where it can be used.
• The next module will cover 80% of the operators you'll ever use, allowing you to craft a broad range of queries.
• Special commands and scalar operators: for creating calculated values, decision logic, and various string and date-time manipulations.
• Advanced aggregations: includes max, min, sum, and unique Kusto aggregations like makeset, makelist, and mvexpand.

Working with Datasets:

• Advanced techniques for datasets.
• SQL-like joins and unions, with additional techniques like data tables and materializing.

Analysis Tools:

• Time Series Module: Tools for analyzing data over time.
• Machine Learning Module: Tools for basket analysis, clustering, and more.

Exporting Data:

• How to export to CSV format.
• Incorporating a KQL query into a Power BI workbook.

Summary Module:

• Wrapping up and discussing future directions for your KQL knowledge.

KQL Overview

KQL (Kusto Query Language) uses the Kusto Engine to query big datasets, specifically those related to various offerings in Azure. With KQL, you can quickly analyze your Azure logs to look for trends, issues, and gain further insights into the operation of your Azure services.

Platforms Using KQL:

• Azure Log Analytics: Part of Microsoft Azure's monitoring solution, monitoring both cloud and on-premises environments.
• Azure Application Insights: An application performance management service for web developers.
• Windows Defender Advanced Threat Protection (ATP): A unified platform for preventative protection, post-breach detection, automated investigation, and response.
• Azure Security Center: Provides unified security management and advanced threat protection across hybrid cloud workloads.

Demo Platforms:

For this course, we'll be using demo platforms available at no cost for practicing KQL skills. You can find the available demo platforms at the links listed here. Let's jump over to the Log Analytics demo site to get a feel for the layout and run a very simple query.

+++

+++

Demo of the Portal

Here we are inside the Azure Log Analytics query portal. As mentioned in the introduction, there are many platforms for using KQL, with log analytics being one of them. The user interfaces for the other platforms may differ from the log analytics portal we'll be using in this course, but rest assured, the KQL language is identical for all of them. This portal is publicly accessible, requiring no Azure account. You can either go to the shortcut provided in the introductory slides or just go to the URL displayed on the screen.

Home Page Features:

• Getting Started Video: A short video on getting started.
• Favorite Queries: Popular queries that users frequently run.
• Common Queries: A list of common queries that can be executed by clicking the Run button.
• Tips and Tricks: Useful tips along with news about the latest updates.

By clicking the plus button, you can open a new tab to start entering queries. We'll spend a lot of time here in this module. The left area displays everything available to query on, organized at the top level as databases or collections of tables. We'll focus on LogManagement. Expanding LogManagement reveals a list of tables, including Perf, which contains columns like BucketEndTime, StartTime, Computer, and more. Each column has an icon indicating its data type: a clock for date/time, T for text, and a pound sign for numeric columns.

The folder icon on the right brings up saved queries in the Query Explorer. This includes Favorites and Saved Queries, which can be executed directly within the browser. Running a saved query renders a chart. The Home button returns to the Home Page. Logged-in users would see their ID at the top; otherwise, it shows "Guest."

Standard Query Window:

• Comments: Begin with //.
• Executing Queries: Click the Run button or use Shift+Enter.
• Time Range: Set to show data from the last 10 minutes, hour, 7 days, or a custom range.

By expanding or collapsing the schema and query filter areas, more room is available on the display. Full-screen mode can be toggled with F11.

This wraps up our basic tour of the Azure Log Analytics Interface. You now know where to use KQL, how to access the demo sites, and how to run a simple query. The next module will cover 80% of the operators you'll ever use.

80% of the Operators You’ll Ever Use

The Search Command

Welcome to the Kusto Query Language from Scratch course. In this module, you'll learn 80% of the operators you'll ever use. I'm Robert Cain, and I'll be your host.

Key Operators:

• Search: Looks across one or more tables for a specific text string.
• Case Sensitivity: Can be toggled with case_sensitive.

For example, using search on the Perf table for "Memory" would look like:

Perf | search "Memory"

This command finds instances of "Memory" in any text column of the Perf table. The search is not case sensitive by default but can be made case sensitive:

Perf | search case_sensitive="true" "Memory"

Searching Across Multiple Tables:

• Multiple Tables: You can search across tables like Perf, Event, and Alert:

search in (Perf, Event, Alert) "Contoso"

• Time Frame: Can be adjusted from the UI or within the query.

Column-Specific Searches:

• To search within a specific column:

Perf | search CounterName == "Available MBytes"

• For partial matches within a column:

Perf | search CounterName : "MBytes"

Using Wildcards and Patterns:

• Starts With: Searches for columns starting with "Bytes":

search * | startswith "Bytes"

• Ends With: Searches for columns ending with "Bytes":

search * | endswith "Bytes"

• Wildcards: Search for text starting with "Free" and ending with "bytes":

search * | search "Free*bytes"

Combining Search Logic:

• Combine search conditions using logical operators:

Perf | search "Free*bytes" and (InstanceName == "C:" or InstanceName == "D:")

Regular Expressions:

• Use regex for complex search patterns:

Perf | search InstanceName matches regex "[A-Z]:"

With these commands, you should now have a good grasp of using the search operator in KQL. This will allow you to handle a majority of your query needs effectively. +++

+++

The Where Command

The where clause is similar to the search command in that it limits the result set. However, instead of looking across columns for a specific value, the where clause filters based on one or more conditions. Here's an example using the Perf table:

Perf
| where TimeGenerated >= ago(1h)

For most tables, there's a TimeGenerated column indicating when each row was inserted. The ago function is used here to filter results from the past hour. The toolbar reflects this by setting the time range as "Set in query."

Using the ago Function

The ago function specifies a relative date range. For example:

• 1d for 1 day
• 1h for 1 hour
• 1m for 1 minute

You can combine conditions in the where clause:

Perf
| where TimeGenerated >= ago(1h)
| where CounterName == "Bytes Received per second"
| where CounterValue > 0

This query filters the Perf table for rows where:

• The timestamp is within the past hour
• The counter name is "Bytes Received per second"
• The counter value is greater than 0

Combining Conditions

You can use logical operators like and and or:

Perf
| where TimeGenerated >= ago(1h)
| where (CounterName == "Bytes Received per second" or CounterName == "% Processor Time")
| where CounterValue > 0

Stacking where Clauses

Multiple where clauses can be stacked:

Perf
| where TimeGenerated >= ago(1h)
| where CounterName == "Bytes Received per second"
| where CounterValue > 0

This approach can be useful for intermediate calculations between where clauses.

Simulating Search with where

You can simulate a search with the where clause:

Perf
| where * has "Bytes"

This searches across all columns for the term "Bytes."

Positional Matches

Using startswith and endswith for positional matches:

Perf
| where CounterName startswith "Bytes"
| where CounterName endswith "Bytes"

Using hasprefix and hassuffix:

Perf
| where CounterName hasprefix "Bytes"
| where CounterName hassuffix "Bytes"

Using contains and Regular Expressions

To look for a value anywhere within a column:

Perf
| where CounterName contains "bytes"

To use regular expressions:

Perf
| where InstanceName matches regex "[A-Z]:"

The Take Command

The take command is used to grab a random number of rows from the input data:

Perf
| take 10

This command retrieves 10 random rows. Running the query again may yield a different set of rows.

Combining take with Other Commands

Perf
| where TimeGenerated >= ago(1h)
| take 5

The limit command is a synonym for take:

Perf
| limit 10

The Count Command

The count operator returns the number of rows in the input dataset:

Perf
| count

Filtering before counting:

Perf
| where TimeGenerated >= ago(1h)
| where CounterName == "Bytes Received per second"
| where CounterValue > 0
| count

The Summarize Command

The summarize command aggregates values based on functions and specified columns.

Example with count Function

Perf
| summarize count() by CounterName

Renaming the count column:

Perf
| summarize PerfCount = count() by CounterName

Using Other Functions

Using avg for average:

Perf
| where CounterName == "% Free Space"
| summarize NumberOfEntries = count(), AverageFreeSpace = avg(CounterValue) by CounterName

Grouping by Date

Using the bin function to group by date:

Perf
| summarize NumberOfEntries = count() by bin(TimeGenerated, 1d)

Multi-Level Grouping

Perf
| summarize NumberOfEntries = count() by CounterName, bin(TimeGenerated, 1d)

Grouping by Other Values

Grouping by CounterValue into ranges:

Perf
| summarize NumberOfRowsAtThisPercentLevel = count() by bin(CounterValue, 10)

+++

+++

The Extend Command

Extend is a powerful command in KQL that allows you to create new columns within your output.

Basic Example

Here’s an example using the Perf table to create a new column called FreeGB:

Perf
| where CounterName == "Free Megabytes"
| extend FreeGB = CounterValue / 1000

This query creates a new column FreeGB by dividing the CounterValue by 1000. The new column appears at the end of the output.

Creating Multiple Columns

You can extend multiple columns simultaneously:

Perf
| where CounterName == "Free Megabytes"
| extend FreeGB = CounterValue / 1000, FreeKB = CounterValue * 1000

This query creates both FreeGB and FreeKB columns.

Repeating a Column

To rename an existing column:

Perf
| where CounterName == "Free Megabytes"
| extend FreeGB = CounterValue / 1000, FreeMB = CounterValue, FreeKB = CounterValue * 1000

Creating String Values

You can also create new string columns:

Perf
| where TimeGenerated >= ago(10m)
| extend ObjectCounter = strcat(ObjectName, " - ", CounterName)

This concatenates ObjectName and CounterName with a " - " in between.

The Project Command

Project allows you to specify which columns to include in your output, similar to SELECT in SQL.

Basic Usage

Perf
| project ObjectName, CounterName, InstanceName, CounterValue, TimeGenerated

This returns only the specified columns.

Combining project and extend

Perf
| project ObjectName, CounterName, InstanceName, CounterValue, TimeGenerated
| extend FreeGB = CounterValue / 1000, FreeKB = CounterValue * 1000

This first selects specific columns, then adds new columns using extend.

Using project to Simulate extend

Perf
| project ObjectName, CounterName, InstanceName, TimeGenerated, 
          FreeGB = CounterValue / 1000, FreeMB = CounterValue, FreeKB = CounterValue * 1000

This directly creates the new columns without using extend.

Project-Away

To exclude specific columns:

Perf
| where TimeGenerated >= ago(1h)
| project-away TenantId, SourceSystem, CounterPath, MG

This removes the specified columns from the output.

Project-Rename

To rename a column:

Perf
| where TimeGenerated >= ago(1h)
| project-renamed myRenamedComputer = Computer

This renames the Computer column to myRenamedComputer.

The Distinct Command

Distinct returns a list of unique values for the specified columns:

Perf
| distinct ObjectName, CounterName

Example with Errors

To get distinct error sources:

Event
| where EventLevelName == "Error"
| distinct Source

Module Summary

Top Command

The top command is used to return the top N rows, ordered by a specified column:

Perf
| top 20 by TimeGenerated desc

This returns the top 20 rows, ordered by the most recent TimeGenerated.

Combining Commands

Combining multiple commands to create a more complex query:

Perf
| where CounterName == "Free Megabytes" and TimeGenerated >= ago(1h)
| project Computer, TimeGenerated, CounterName, FreeMegabytes = CounterValue
| distinct Computer, TimeGenerated, CounterName, FreeMegabytes
| top 25 by FreeMegabytes asc

This query finds the computers with the least free megabytes in the past hour.

Congratulations! You now have the knowledge to write most of the queries you'll need. In the next module, you'll learn about common scalar operators in KQL for manipulating dates, strings, and more.

Take some time to practice what you've learned before moving on to the next module on Scalar Operators. +++

+++

Scalar Operators

The print and now Commands

Welcome to the Kusto Query Language From Scratch course. Scalar operators are the focus of this module. These operators allow for data manipulation such as reformatting dates, creating new string fields, and applying logical conditions. Here is a list of the keywords covered in this module:

The print Command

The print command outputs any specified value, making it useful for debugging and development.

print "Hello World"

This will output "Hello World". For calculations:

print 11 * 3

This outputs 33. You can rename the output for clarity:

print TheAnswerToLifeTheUniverseAndEverything = 42

The now Command

The now command returns the current date and time:

print now()

This returns the current UTC date and time. The now function is useful in datetime arithmetic, as demonstrated below.

The ago Command

The ago function subtracts a specified timespan from the current time:

print ago(1d)

This returns the time 1 day ago. It can be refined to hours, minutes, seconds, milliseconds, microseconds, and even nanoseconds.

print ago(1h)
print ago(1m)
print ago(1s)
print ago(1ms)
print ago(1us)
print ago(1ns)

You can also use negative values to move into the future:

print ago(-1d)

The ago function is often used in combination with the where clause to filter results based on a relative time range.

The sort Command

The sort command sorts the query output by specified columns:

Perf
| sort by Computer, TimeGenerated

By default, sorting is in descending order. To sort in ascending order:

Perf
| sort by Computer asc, TimeGenerated asc

To mix sorting orders:

Perf
| sort by Computer asc, TimeGenerated desc

The order by clause is an alias for sort by.

The extract Command

The extract operator matches a string based on a regular expression pattern and returns the matched part:

Perf
| where ObjectName == "LogicalDisk"
| extend DriveLetter = extract("([A-Z]):", 1, InstanceName)

This extracts the drive letter followed by a colon from InstanceName.

The parse Command

The parse operator extracts parts of a text string into new columns based on starting and ending markers:

Event
| parse RenderedDescription with "Event code: " myEventCode:int " Event message: " myEventMessage " Event time: " EventTime " Event sequence: " EventSequence " Event occurrence: " EventOccurrence

This breaks down RenderedDescription into individual components like myEventCode and myEventMessage.

Datetime and Timespan Arithmetic

Basic Arithmetic

Datetime math is straightforward in KQL. To calculate how long ago a performance counter was generated:

Perf
| extend HowLongAgo = now() - TimeGenerated

This calculates the time difference between now and TimeGenerated.

Using Specific Dates

To use a specific date:

Perf
| extend TimeSinceStartOfYear = TimeGenerated - datetime(2018-01-01)

Converting Time Units

Convert time to different units by dividing by the desired unit:

Perf
| extend TimeSinceStartOfYearInHours = TimeSinceStartOfYear / 1h

Calculating Durations

To calculate the duration of an operation:

Usage
| extend Duration = EndTime - StartTime

This calculates the duration based on EndTime and StartTime.

These commands and techniques provide powerful tools for manipulating and analyzing data in KQL. +++

+++

The startof... Commands

When working with dates, it is often useful to determine the beginning of a specific time period. The startof commands help in achieving this.

The startofday Command

Creates a new column indicating the start of the day for each date:

Perf
| extend DayGenerated = startofday(TimeGenerated)

This command converts the TimeGenerated to the start of the day (midnight). You can then summarize counts by day:

Perf
| extend DayGenerated = startofday(TimeGenerated)
| summarize EventCount = count() by DayGenerated, Source

The startofmonth Command

Creates a new column indicating the start of the month:

Perf
| extend MonthGenerated = startofmonth(TimeGenerated)
| summarize EventCount = count() by MonthGenerated, Source

This helps in summarizing counts by month. Sorting the results can enhance readability:

Perf
| extend MonthGenerated = startofmonth(TimeGenerated)
| summarize EventCount = count() by MonthGenerated, Source
| sort by MonthGenerated desc, Source asc

The startofyear Command

Creates a new column indicating the start of the year:

Perf
| extend YearGenerated = startofyear(TimeGenerated)
| summarize EventCount = count() by YearGenerated, Source

This summarizes counts by year.

The startofweek Command

Creates a new column indicating the start of the week:

Perf
| extend WeekGenerated = startofweek(TimeGenerated)
| summarize EventCount = count() by WeekGenerated, Source

This summarizes counts by week.

The endof... Commands

The endof commands work similarly to the startof commands but return the end of a specific time period.

The endofday Command

Perf
| extend EndOfDay = endofday(TimeGenerated)

The endofmonth Command

Perf
| extend EndOfMonth = endofmonth(TimeGenerated)

The endofyear Command

Perf
| extend EndOfYear = endofyear(TimeGenerated)

The endofweek Command

Perf
| extend EndOfWeek = endofweek(TimeGenerated)

The between Command

The between operator limits results to a specified range.

Numeric Range

Perf
| where CounterValue between (70 .. 100)

This filters rows where CounterValue is between 70 and 100.

Date Range

For date ranges, cast the range values to datetime:

Perf
| where TimeGenerated between (datetime(2018-04-01) .. datetime(2018-04-03))

Using startofday and endofday functions ensures inclusiveness:

Perf
| where TimeGenerated between (startofday(datetime(2018-04-01)) .. endofday(datetime(2018-04-03)))

not between Command

Perf
| where CounterValue not between (0 .. 69.9999)

The todynamic Command

Converts JSON stored in a column into a usable format.

Example

SecurityAlert
| extend ExtProps = todynamic(ExtendedProperties)
| project AlertName, TimeGenerated, AlertStartTime = ExtProps["Alert Start Time UTC"], Source = ExtProps.Source

Property Notation

Simplifies access if keys don't have spaces:

SecurityAlert
| extend ExtProps = todynamic(ExtendedProperties)
| project AlertName, TimeGenerated, AlertStartTime = ExtProps["Alert Start Time UTC"], Source = ExtProps.Source

The format_datetime and format_timespan Commands

These commands format datetime and timespan values for better readability.

format_datetime

Perf
| extend FormattedDate = format_datetime(TimeGenerated, 'yyyy-MM-dd hh:mm:ss')

format_timespan

Perf
| extend FormattedTimespan = format_timespan(totimespan(now() - TimeGenerated), 'hh:mm:ss')

The datetime_part Command

Extracts specific parts of a datetime.

Example

Perf
| extend Year = datetime_part("year", TimeGenerated), Month = datetime_part("month", TimeGenerated)

Grouping by Hour of Day

Perf
| where TimeGenerated >= ago(7d)
| extend HourOfDay = datetime_part("hour", TimeGenerated)
| summarize EventCount = count() by HourOfDay
| sort by HourOfDay asc

The iif Command

Implements if-then-else logic.

Example

Perf
| extend FreeState = iif(CounterValue < 50, "You might want to look at this", "You're ok")

Date Example

Perf
| extend DateState = iif(datetime_part("month", TimeGenerated) == datetime_part("month", now()), "Current Month", "Past Months")

This sets DateState based on whether TimeGenerated is in the current month.

These commands and techniques enhance the ability to manipulate and analyze data effectively using KQL. +++

+++

The case Command

The case statement in KQL is similar to the case or switch statement in other programming languages. It acts as a multi-level if-then-else clause, allowing you to create a new column based on specific conditions.

Basic Example

Here, we'll create a FreeLevel column based on the CounterValue:

Perf
| extend FreeLevel = case(
    CounterValue < 10, "Critical",
    CounterValue < 30, "Danger",
    CounterValue < 50, "Look at it",
    "You're OK"
)

This query categorizes the CounterValue into different levels:

• Less than 10: "Critical"
• Less than 30: "Danger"
• Less than 50: "Look at it"
• Otherwise: "You're OK"

Summarizing with case

You can also use the case statement to create summaries:

Perf
| extend FreeLevel = case(
    CounterValue < 10, "Critical: Less than 10%",
    CounterValue < 30, "Danger: 10% to 30%",
    CounterValue < 50, "Look at it: 30% to 50%",
    "You're OK: 50% or more"
)
| summarize ComputerCount = count() by FreeLevel

This query counts the number of computers in each FreeLevel category.

The isempty and isnull Commands

isempty and isnull are functions used to determine if data is missing. They handle string and numeric columns differently.

isempty

Checks if a string column is empty:

Perf
| where isempty(InstanceName)
| count

This counts rows where InstanceName is empty. You can combine isempty with iif for better readability:

Perf
| extend InstName = iif(isempty(InstanceName), "NO INSTANCE NAME", InstanceName)

isnull

Checks if a numeric column is null:

Perf
| where isnull(SampleCount)
| count

This counts rows where SampleCount is null. You can combine isnull with iif:

Perf
| extend SampleCountNull = iif(isnull(SampleCount), "No Sample Count", tostring(SampleCount))

The split Command

The split command breaks a string into smaller pieces based on a specified delimiter.

Basic Example

Splitting a CounterPath column:

Perf
| extend CPSplit = split(CounterPath, "\\")

This splits the CounterPath column using a backslash. You can extract specific parts:

Perf
| extend myComputer = CPSplit[2], myObjectInstance = CPSplit[3]

Using Arrays

A preferred method is to store the split values in an array and then extract them:

Perf
| extend CounterPathArray = split(CounterPath, "\\")
| extend myComputer = CounterPathArray[2], myObjectInstance = CounterPathArray[3]

String Operators

String operators like contains, in, and strcat help manipulate string data.

contains and contains_cs

Perf
| where CounterName contains "BYTES"

Case-sensitive version:

Perf
| where CounterName contains_cs "BYTES"

in and not in

Perf
| where CounterName in ("Disk Transfers/sec", "Disk Reads/sec")

Not in:

Perf
| where CounterName !in ("Disk Transfers/sec", "Disk Reads/sec")

The strcat Command

Combines multiple columns into a single column:

Perf
| extend CompObjCounter = strcat(Computer, " - ", ObjectName, " - ", CounterName)

Putting It All Together

Combining various techniques to create a summary report:

Event
| where TimeGenerated between(ago(365d) .. startofmonth(now()))
| summarize EventCount = count() by calMonth = startofmonth(TimeGenerated)
| sort by calMonth desc
| extend MonthNumber = datetime_part("month", calMonth), YearNumber = datetime_part("year", calMonth)
| extend MonthName = case(
    MonthNumber == 1, "Jan",
    MonthNumber == 2, "Feb",
    MonthNumber == 3, "Mar",
    MonthNumber == 4, "Apr",
    MonthNumber == 5, "May",
    MonthNumber == 6, "Jun",
    MonthNumber == 7, "Jul",
    MonthNumber == 8, "Aug",
    MonthNumber == 9, "Sep",
    MonthNumber == 10, "Oct",
    MonthNumber == 11, "Nov",
    MonthNumber == 12, "Dec",
    "Unknown Month"
)
| extend YearMonth = strcat(MonthName, "-", tostring(YearNumber))
| project YearMonth, EventCount

This query generates a summary of events by month for the last year, excluding the current month, and formats the output nicely.

Summary

• The case command allows for multi-level conditional logic.
• isempty and isnull determine if string or numeric data is missing.
• The split command breaks strings into parts.
• String operators like contains, in, and strcat provide powerful string manipulation capabilities.

These tools enable effective data manipulation and analysis using KQL. In the next module, we will cover advanced aggregations, including min, max, sum, and unique KQL functions like makeset, makelist, and mvexpand. +++

+++

Advanced Aggregations

The arg_max and arg_min Commands

arg_max

The arg_max command retrieves the maximum value from a query along with specified columns from the row with that maximum value.

Example

Perf
| summarize arg_max(CounterValue, *) by CounterName

This returns the maximum CounterValue for each CounterName, including all other columns from the row with that maximum value.

Specifying Columns

Perf
| summarize arg_max(CounterValue, Computer, ObjectName) by CounterName

This returns only the Computer and ObjectName columns along with the CounterValue and CounterName.

arg_min

The arg_min command retrieves the minimum value from a query along with specified columns from the row with that minimum value.

Example

Perf
| summarize arg_min(CounterValue, *) by CounterName

This returns the minimum CounterValue for each CounterName, including all other columns from the row with that minimum value.

The makeset and makelist Commands

makeset

The makeset command creates an array of unique values from a specified column.

Example

Perf
| summarize CounterNames = makeset(CounterName) by ObjectName

This returns a list of unique CounterName values for each ObjectName.

makelist

The makelist command creates an array of values from a specified column, including duplicates.

Example

Perf
| summarize CounterNames = makelist(CounterName) by ObjectName

This returns a list of CounterName values (including duplicates) for each ObjectName.

Setting Maximum Size

Both makeset and makelist can have an optional second parameter to set the maximum size of the return list.

Perf
| summarize CounterNames = makeset(CounterName, 256) by ObjectName

The mvexpand Command

The mvexpand command expands an array back into rows.

Example

Perf
| summarize Computers = makeset(Computer) by CounterName
| mvexpand Computers

This creates an array of Computer values and then expands each value into a separate row.

The percentiles Command

The percentiles command calculates values greater than specified percentages of the sample set.

Example

Perf
| summarize percentiles(CounterValue, 5, 50, 95) by Computer

This returns the 5th, 50th, and 95th percentiles for CounterValue grouped by Computer.

Renaming Columns

Perf
| summarize (Percent05, Percent50, Percent95) = percentiles(CounterValue, 5, 50, 95) by Computer

Using percentiles_array

Perf
| summarize Percentiles = percentiles_array(CounterValue, 5, 50, 95) by Computer
| mvexpand Percentiles

The dcount Command

The dcount command estimates the number of distinct values.

Example

SecurityEvent
| summarize EventCount = dcount(EventID) by Computer

This estimates the number of distinct EventID values for each Computer.

Accuracy Levels

SecurityEvent
| summarize EventCount = dcount(EventID, 2) by Computer

This specifies a higher accuracy level for the dcount estimation.

The dcountif Command

The dcountif command estimates the number of distinct values that meet a specific condition.

Example

SecurityEvent
| summarize EventCount = dcountif(EventID, EventID in (4688, 4625)) by Computer

This estimates the number of distinct EventID values that are in the specified list.

The countif Command

The countif command counts the number of rows that meet a specific condition.

Example

Perf
| summarize RowCount = countif(CounterName contains "Bytes") by CounterName

This counts rows where CounterName contains the word "Bytes".

The pivot Command

The pivot command transforms row-based data into column-based data.

Example

Event
| summarize EventCount = count() by Computer, EventLevelName
| evaluate pivot(EventLevelName, sum(EventCount))

This pivots the EventLevelName values into columns and sums the EventCount.

The top-nested Command

The top-nested command returns the top N items within nested groups.

Example

Perf
| summarize ObjectCount = count() by ObjectName
| top-nested 3 of ObjectName by ObjectCount, 3 of CounterName by CounterNameCount

This returns the top 3 ObjectName values, and for each ObjectName, the top 3 CounterName values.

Including "Others"

Perf
| summarize ObjectCount = count() by ObjectName
| top-nested 3 of ObjectName by ObjectCount with others="All Other Objects"

This includes an "All Other Objects" row summarizing the rest of the values.

The max and min Commands

The max and min commands retrieve the maximum and minimum values respectively.

Example

Perf
| summarize MaxValue = max(CounterValue)

Perf
| summarize MinValue = min(CounterValue)

The sum and sumif Commands

sum

The sum command calculates the total of a specified column.

Example

Perf
| summarize TotalFreeMB = sum(CounterValue) by CounterName

sumif

The sumif command calculates the total of a specified column based on a condition.

Example

Perf
| summarize TotalFreeMB = sumif(CounterValue, CounterName == "Free Megabytes")

The any Command

The any command selects a random row from the dataset.

Example

Perf
| summarize any(*) by CounterName

This selects a random row for each CounterName.

---

In this module, you have learned various advanced aggregation functions in KQL, enabling you to summarize and analyze data effectively. These functions are essential for working with large datasets and obtaining meaningful insights. +++

+++

Working with Datasets

The let Command

The let command in KQL is incredibly versatile. It allows you to define variables, reusable values, and even datasets. This section will demonstrate various use cases of the let command.

Defining Constants

You can use let to define constants that can be reused in your query.

Example

let minCounterValue = 300;
let counterName = "Free Megabytes";
Perf
| where CounterName == counterName and CounterValue <= minCounterValue

Reusing Variables

The let command can help in reusing variables multiple times within a query.

Example

let compName = "MyComputer";
Perf
| where Computer == compName
| extend ThisIsFor = strcat("This data is for computer ", compName)

Holding Calculated Values

You can hold calculated values in a let statement.

Example

let twelveHoursAgo = now() - 12h;
Perf
| where TimeGenerated >= twelveHoursAgo

Storing Datasets

You can store an entire dataset in a variable using let.

Example

let compName = "MyComputer";
let UpdtSum = UpdateSummary | where Computer == compName;
let Updt = Update | where Computer == compName;
union UpdtSum, Updt

Creating Functions

You can define custom functions using the let command.

Example

let dateDiffInDays = (date1:datetime, date2:datetime = datetime(2018-01-01)) {
    (date1 - date2) / 1d
};
print dateDiffInDays(now(), datetime(2018-05-01))

The join Command

The join command merges columns from two tables into a single row based on a matching key.

Basic Join

Example

Perf
| where TimeGenerated >= ago(30d)
| take 1000
| join kind=inner (
    Alert
) on Computer

Joining on Different Column Names

Example

Perf
| where TimeGenerated >= ago(30d)
| take 1000
| join kind=inner (
    Alert
) on $left.Computer == $right.ComputerName

Complex Join

Example

let PerfData = Perf
| where TimeGenerated >= ago(30d)
| project Computer, TimeGenerated, ObjectName, CounterName, CounterValue;

let AlertData = Alert
| where TimeGenerated >= ago(30d)
| project ComputerName, TimeGenerated, AlertName, Severity;

PerfData
| join kind=inner (AlertData) on $left.Computer == $right.ComputerName

Different Join Types

• innerunique: Default join, returns the first match.
• inner: Returns all combinations of the left and right rows that match.
• leftouter: Returns all rows from the left, and matching rows from the right.
• rightouter: Returns all rows from the right, and matching rows from the left.
• fullouter: Returns all rows from both sides.
• leftanti: Returns rows from the left that don't have matches on the right.
• rightanti: Returns rows from the right that don't have matches on the left.
• leftsemi: Returns rows from the left that have matches on the right.
• rightsemi: Returns rows from the right that have matches on the left.

Example with Kind

Perf
| where TimeGenerated >= ago(30d)
| take 1000
| join kind=fullouter (
    Alert
) on $left.Computer == $right.ComputerName

The union Command

The union command merges data from two or more tables together.

Basic Union

Example

UpdateSummary
| union Update

Using withsource

Example

UpdateSummary
| union withsource=SourceTable Update

Selective Union

Example

(UpdateSummary | project Id, Computer, TimeGenerated, UpdateState)
| union withsource=SourceTable
(Update | project Id, Computer, TimeGenerated, UpdateType)

Union with Multiple Tables

Example

let updateSummary = UpdateSummary | project Id, Computer, TimeGenerated, UpdateState;
let updateData = Update | project Id, Computer, TimeGenerated, UpdateType;
let perfData = Perf | project Computer, TimeGenerated, CounterName, CounterValue;
union updateSummary, updateData, perfData

Outer Union

Example

UpdateSummary
| union kind=outer Update

Creating a Datatable

You can create your own datatable in KQL using hard-coded values with the datatable keyword.

Example

datatable (ID:int, TimeGenerated:datetime, YouTubeName:string, YouTubeURL:string)
[
    1, datetime(2018-04-01), "TechGuy", "https://youtube.com/techguy",
    2, datetime(2018-05-01), "CodeMaster", "https://youtube.com/codemaster"
]

Using let with Datatable

Example

let FavoriteYouTubers = datatable (ID:int, TimeGenerated:datetime, YouTubeName:string, YouTubeURL:string)
[
    1, datetime(2018-04-01), "TechGuy", "https://youtube.com/techguy",
    2, datetime(2018-05-01), "CodeMaster", "https://youtube.com/codemaster"
];
FavoriteYouTubers
| project YouTubeName, YouTubeURL

Practical Use Case

Example

let Computers = datatable (ComputerName:string, FriendlyName:string)
[
    "ContosoSQLSrv1", "Contoso SQL Server One",
    "ContosoSQLSrv2", "Contoso SQL Server Two",
    "ContosoWebSrv1", "Contoso Web Server One",
    "ContosoWebSrv2", "Contoso Web Server Two",
    "ContosoFileSrv", "Contoso File Server"
];

let PerfInfo = Perf
| where TimeGenerated >= ago(1h)
| project Computer, TimeGenerated, CounterName, CounterValue;

Computers
| join kind=inner (PerfInfo) on $left.ComputerName == $right.Computer
| project FriendlyName, TimeGenerated, CounterName, CounterValue

In this module, you have learned various methods for working with datasets in KQL, including defining variables, joining tables, creating unions, and generating custom datatables. These techniques are essential for effective data analysis and manipulation in KQL. +++

+++

Working with Datasets

The prev and next Commands

The prev and next commands are used to access values from previous or next rows in a dataset, which is particularly useful in scenarios such as calculating moving averages or other sequential data operations.

Basic Usage of prev

Example

let SomeData = datatable(key:int, rowVal:int)
[
    1, 1,
    2, 2,
    3, 3,
    4, 4,
    5, 5
];

SomeData
| serialize
| extend prvVal = strcat("Previous Value was ", prev(rowVal))

Basic Usage of next

Example

SomeData
| serialize
| extend nxtVal = strcat("Next Value is ", next(rowVal))

Going Back Multiple Rows

Example

SomeData
| serialize
| extend prvVal = strcat("Previous Value back 2 was ", prev(rowVal, 2))

Using Default Value

Example

SomeData
| serialize
| extend prvVal = strcat("Previous Value was ", prev(rowVal, 1, "not valid for this row"))

Practical Example: Moving Average

Example

let startTime = ago(1d);
let endTime = now();
Perf
| where TimeGenerated between(startTime .. endTime)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avgCpu = avg(CounterValue) by bin(TimeGenerated, 1h)
| sort by TimeGenerated asc
| extend movAvg = (avgCpu + prev(avgCpu, 1, 0) + prev(avgCpu, 2, 0)) / 3
| render timechart

The toscalar Command

The toscalar command converts a query result into a scalar value, which can then be used in other queries.

Example

let myComputerList = toscalar(
    Perf
    | where CounterName == "Free Megabytes" and CounterValue < 100
    | summarize makeset(Computer)
);

Perf
| where Computer in (myComputerList)

The row_cumsum Command

The row_cumsum command provides a cumulative sum across rows.

Basic Usage

Example

let SomeData = datatable(a:int)
[
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10
];

SomeData
| serialize
| extend cumSum = row_cumsum(a)

Practical Example

Example

let startTime = ago(1d);
let endTime = now();
Perf
| where TimeGenerated between(startTime .. endTime)
| where ObjectName == "Network Adapter" and CounterName == "Bytes Received/sec"
| summarize bytesReceivedPerHour = sum(CounterValue) by bin(TimeGenerated, 1h), Computer
| sort by Computer, TimeGenerated asc
| serialize
| extend cumBytesReceived = row_cumsum(bytesReceivedPerHour)

The materialize Command

The materialize command captures a snapshot of a dataset in memory, which can be reused within the same query.

Example with Materialize

Example

let myEvents = materialize(
    Event
    | where TimeGenerated between (ago(1h) .. now())
);

let errorCount = toscalar(
    myEvents
    | where EventLevel == "Error"
    | summarize count()
);

let warningCount = toscalar(
    myEvents
    | where EventLevel == "Warning"
    | summarize count()
);

myEvents
| summarize errorPct = countif(EventLevel == "Error") * 100.0 / errorCount,
             warningPct = countif(EventLevel == "Warning") * 100.0 / warningCount

In this module, you learned how to use various commands to work with datasets, including prev, next, toscalar, row_cumsum, and materialize. These commands are essential for performing advanced data manipulations and calculations in KQL. +++

+++

Time Series

The range Command

The range command generates a datatable starting from a specific value, ending at a specific value, and incrementing by a specified step.

Basic Usage

range value from 1 to 8 step 1

With Step

range value from 1 to 8 step 2

With Dates

range date from ago(7d) to now() step 1d

With Hours

range date from ago(1d) to now() step 1h

Practical Example

range date from startofday(ago(7d)) to startofday(now()) step 1d
| join kind=leftouter (
    Event
    | summarize Count = count() by bin(TimeGenerated, 1d)
) on date
| extend TimeDisplay = format_datetime(date, "yyyy-MM-dd")
| sort by TimeDisplay desc

The make-series Command

The make-series command converts values into an array.

Basic Usage

Perf
| where TimeGenerated >= ago(3d)
| make-series avg(CounterValue) on TimeGenerated in range(startofday(ago(3d)), startofday(now()), 1h) by Computer

Using mvexpand

Perf
| where TimeGenerated >= ago(3d)
| make-series avg(CounterValue) on TimeGenerated in range(startofday(ago(3d)), startofday(now()), 1h) by Computer
| mvexpand avg_CounterValue, TimeGenerated

Using render timechart

Perf
| where TimeGenerated >= ago(3d)
| make-series avg(CounterValue) on TimeGenerated in range(startofday(ago(3d)), startofday(now()), 1h) by Computer
| render timechart

The series_stats Command

The series_stats command performs statistical calculations on a series.

Basic Usage

let myArray = dynamic([1,2,3,4,5,6,7,8,9,10]);
print series_stats(myArray)

Using series_stats_dynamic

Perf
| where TimeGenerated >= ago(3d)
| make-series avg(CounterValue) on TimeGenerated in range(startofday(ago(3d)), startofday(now()), 1h) by Computer
| extend stats = series_stats_dynamic(avg_CounterValue)
| project Computer, stats.min, stats.max, stats.avg, stats.stdev

The series_outliers Command

The series_outliers command detects anomalies in a series.

Basic Usage

let baseData = materialize(
    SecurityEvent
    | where EventID == 4624
    | summarize dcount_Computer = dcount(Computer) by bin(TimeGenerated, 1d), Account
);
let AnomAccounts = baseData
| extend outliers = series_outliers(dcount_Computer)
| mvexpand TimeGenerated, dcount_Computer, outliers;
AnomAccounts
| project Account, TimeGenerated, dcount_Computer, outliers

The series_fir Command

The series_fir command performs Finite Impulse Response analysis.

Basic Usage

let val = range x from 1 to 24 step 1
| summarize val = make_list(x);
extend fir = series_fir(val, dynamic([1,1,1,1,1]))

Practical Example: Moving Average

let startTime = ago(1d);
let endTime = now();
Perf
| where TimeGenerated between (startTime .. endTime)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avgCpu = avg(CounterValue) by bin(TimeGenerated, 1h)
| make-series avgCpu on TimeGenerated in range(startTime, endTime, 1h)
| extend movAvg = series_fir(avgCpu, dynamic([1, 1, 1, 1, 1]), true, true)
| mvexpand TimeGenerated, avgCpu, movAvg
| render timechart

The series_iir Command

The series_iir command performs Infinite Impulse Response analysis.

Basic Usage

let val = range x from 1 to 24 step 1
| summarize val = make_list(x);
extend iir = series_iir(val, dynamic([1, -1]))

Practical Example

Alert
| where TimeGenerated >= ago(1d)
| summarize Count = count() by bin(TimeGenerated, 1h), Computer
| make-series count = sum(Count) on TimeGenerated in range(startofday(ago(1d)), startofday(now()), 1h) by Computer
| extend CumulativeTotal = series_iir(count, dynamic([1, -1]))
| mvexpand TimeGenerated, count, CumulativeTotal
| render timechart

The series_fit_line Command

The series_fit_line command performs linear regression on a series.

Basic Usage

let x = range d from ago(24h) to now() step 1h;
let y = dynamic([10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 240]);
print series_fit_line(y)

Practical Example

Perf
| where TimeGenerated >= ago(1d)
| where ObjectName == "Memory" and CounterName == "Available MBytes"
| make-series TotalMemoryUsed = avg(CounterValue) on TimeGenerated in range(startofday(ago(1d)), startofday(now()), 1h) by Computer
| extend fit = series_fit_line(TotalMemoryUsed)
| project Computer, TimeGenerated, TotalMemoryUsed, fit_LineFit
| render timechart

The series_fit_2lines Command

The series_fit_2lines command performs two-step linear regression on a series.

Basic Usage

let y = dynamic([10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 240]);
print series_fit_2lines(y)

Practical Example

Perf
| where TimeGenerated >= ago(1d)
| where ObjectName == "Memory" and CounterName == "Available MBytes"
| make-series TotalMemoryUsed = avg(CounterValue) on TimeGenerated in range(startofday(ago(1d)), startofday(now()), 1h) by Computer
| extend fit = series_fit_2lines(TotalMemoryUsed)
| project Computer, TimeGenerated, TotalMemoryUsed, fit_LineFit
| render timechart

In this module, we explored various time series commands in KQL, including range, make-series, series_stats, series_outliers, series_fir, series_iir, series_fit_line, and series_fit_2lines. These commands enable advanced processing and statistical analysis of time series data. In the next module, we will explore how to apply machine learning algorithms to your data using KQL. +++

+++

Machine Learning

The basket Command

The basket command performs basket analysis, often used to analyze combinations of items purchased together. It uses the Apiori algorithm to determine frequent itemsets.

Basic Usage

Perf
| where TimeGenerated >= ago(7d)
| evaluate basket(Computer, ObjectName, CounterName, InstanceName)

With Threshold

Perf
| where TimeGenerated >= ago(7d)
| evaluate basket(Computer, ObjectName, CounterName, InstanceName, 0.01)

The autocluster Command

The autocluster command reduces a large dataset into common patterns.

Basic Usage

Event
| where TimeGenerated >= ago(10d)
| evaluate autocluster(0.3, Source, EventLog, Computer, EventLevelName, RenderedDescription)

The diffpatterns Command

The diffpatterns command identifies differences between two sets of data.

Basic Usage

Event
| evaluate diffpatterns(EventLevelName, "Error", "Warning")
| project CountA, CountB, Pattern

With Threshold

Event
| evaluate diffpatterns(EventLevelName, "Error", "Warning", , 0.07)

The reduce Command

The reduce command identifies unique patterns in string data.

Basic Usage

Event
| reduce by Computer

With Threshold

Event
| reduce by Computer with_threshold=0.6

With Characters

Event
| reduce by Computer with_threshold=0.7, characters="."

Exporting Data

Export to CSV Files

To export data to a CSV file:

1. Execute your query.
2. Click on the Export icon.
3. Select "Export to CSV - All Columns" or "Export to CSV - Displayed Columns".
4. Save the file to your desired location.

Exporting to PowerBI

To use PowerBI to access data:

1. Execute your query.
2. Click on the Export menu.
3. Select "Export to Power BI M Query".
4. Follow the instructions in the generated text file to import the query into PowerBI.

Course Summary

Congratulations on completing the AI version of Kusto Query Language from the Scratch course. You've learned to:

• Write basic and complex queries.
• Use advanced aggregations and work with datasets.
• Perform time series analysis.
• Apply machine learning algorithms.
• Export data to CSV and PowerBI.

Best Practices

Do:

• Use time filters as the first step.
• Filter out data in bulk early.
• Use specific column names over wildcards.
• Limit the number of columns on both sides of joins.

Don't:

• Avoid queries that return gigabytes of data without testing.
• Avoid applying conversions on datasets over 1 billion records.
• Avoid filtering on calculated columns if possible.

Next Steps

• Continue querying your Azure services.
• Stay updated with new KQL features and optimizations.
• Explore advanced machine learning algorithms as they become available.

+++