Skip to main content Link Menu Expand (external link) Document Search Copy Copied
TechWorkshop Understanding Sentinel datalake and graph

Task 01: Create a one-off KQL job (ad-hoc large query)

Introduction

A one-off KQL job lets you run high-volume queries once and persist their results so analysts can reuse data without rescanning the Data Lake.

Description

You’ll create a new KQL job in Microsoft Sentinel, define its query, and verify the generated results table.

Success criteria

  • A one-off KQL job named Adhoc_180d_Alert_Summary is created.
  • The job completes successfully with status Succeeded.
  • Output table AlertSummary_Weekly_KQL_CL is queryable in Sentinel.

Key steps:

  1. In the Defender portal, go to Microsoft Sentinel > Data Lake exploration > KQL queries.

  2. In the upper right of the query, select Create Job.

    Exc7__img1.png

    The Create a new KQL job wizard opens.

  3. Create a new job using the following details:

    Expand here for detailed steps

    1. On the Name & details page, enter the following and select Next:

      Item Value
      Job name: Adhoc_180d_Alert_Summary
      Description: 180-day rollup by week and severity
      Destination workspace: law-sentinel-xdr-lab
      New table name: AlertSummary_Weekly_KQL_CL

    2. On the Query page, define and validate the query by entering the following and select Next:

       SecurityAlert 
 | where TimeGenerated > ago(180d) 
 | extend Sev = tostring(column_ifexists("Severity", "Unknown")) 
 | summarize Alerts = count() by bin(TimeGenerated, 7d), Sev 
 | order by TimeGenerated desc

      Exc7__img3.png

    3. On the Schedule page, configure schedule and limits.

      • Set Job frequency to One time.

      • Review the schedule summary.

        {: .note }The schedule summary confirms: This job will run once and start after creation.

        No additional options are required for one-off jobs.

        Exc7__img4.png

    4. Select Next.

    5. Review the details and select Submit.

      Exc7__img5.png

  4. Verify job completion.

    Expand here for detailed steps
    1. In the Defender portal, go to Microsoft Sentinel > Data Lake exploration > Jobs.
    2. Review metrics such as Total jobs, Enabled jobs, Completed (last 30 days), In-progress, and Recent health issues.

    3. Verify your job, Adhoc_180d_Alert_Summary, status shows Succeeded.

      Exc7__img7.png

  5. Review job details.

    Expand here for detailed steps

    1. Select the job to open its details pane.

    2. Verify these fields:

      • Job name: Adhoc_180d_Alert_Summary
      • Description: 180-day rollup by week and severity
      • Repeat frequency: One time
      • Destination table: AlertSummary_Weekly_KQL_CL
      • Workspace: law-sentinel-xdr-lab1
      • Current run status: Succeeded
      • Target tier: Analytics
      • Kql query: Confirm the KQL query is visible for review

      Exc7__img6.png

  6. Explore the job output.

    Expand here for detailed steps

    1. In the Defender portal, go to Microsoft Sentinel > Data Lake exploration > KQL queries.

    2. Run the following query:

       AlertSummary_Weekly_KQL_CL 
 | top 20 by TimeGenerated desc

      Exc7__img8.png