Azure Sentinel News
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence
No Result
View All Result
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence
No Result
View All Result
Azure Sentinel News
No Result
View All Result
Home KQL

KQL Methods to Display a Per-Day Occurrence in Azure Sentinel

Azure Sentinel News Editor by Azure Sentinel News Editor
December 29, 2020
in KQL
0
Microsoft announces security, identity, management, and compliance updates across Azure and Office
1.9kViews
521 Shares Share on Facebook Share on Twitter

A customer recently wanted to show in a Workbook those users that used MFA to login and format the results so that it showed how many times per day it happened overall. There’s multiple ways to get this done. You can parse the raw output of the TimeGenerated, use format_datetime, or bin with TimeGenerated.

Here’s each of those methods and what the output looks like so you can choose the one that fits your needs most.

Thanks go out to several people both externally and internally for helping source the information to split out per-day occurrences in Azure Sentinel data.

Here’s the parsing route, working with the raw data instead of the displayed result data:

| extend myDAY = split(TimeGenerated, "T", 0) //using split to parse
Using Extend to parse the raw output

The following uses the format_datetime against TimeGenerated to display the full date:

| extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd') //using datetime
Using datetime to display full date

The next one uses format_datetime to show how to display just the “day”:

| extend myDAY = format_datetime(TimeGenerated, 'dd') //using datetime, just the day
Using datetime to display just the day

And, lastly, the following utilize bin, but still displays the time with the full date:

| extend myDAY = bin(TimeGenerated, 1d) //using bin, but still displays the time, too
Using bin with TimeGenerated

The most current query with all the options is available from my GitHub repo: https://github.com/rod-trent/SentinelKQL/blob/master/SigninLogsByDay%20-%20parsing%20UTC.txt

Have a different method that you use? Let me know.

Here’s each of those methods and what the output looks like so you can choose the one that fits your needs most.

Thanks go out to several people both externally and internally for helping source the information to split out per-day occurrences in Azure Sentinel data.

Here’s the parsing route, working with the raw data instead of the displayed result data:

| extend myDAY = split(TimeGenerated, "T", 0) //using split to parse
Using Extend to parse the raw output

The following uses the format_datetime against TimeGenerated to display the full date:

| extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd') //using datetime
Using datetime to display full date

The next one uses format_datetime to show how to display just the “day”:

| extend myDAY = format_datetime(TimeGenerated, 'dd') //using datetime, just the day
Using datetime to display just the day

And, lastly, the following utilize bin, but still displays the time with the full date:

| extend myDAY = bin(TimeGenerated, 1d) //using bin, but still displays the time, too
Using bin with TimeGenerated

The most current query with all the options is available from my GitHub repo: https://github.com/rod-trent/SentinelKQL/blob/master/SigninLogsByDay%20-%20parsing%20UTC.txt

Have a different method that you use? Let me know.

Reference: https://azurecloudai.blog/2020/08/11/different-ways-to-use-kql-to-show-a-per-day-occurrence-in-your-azure-sentinel-data/

Azure Sentinel News Editor

Azure Sentinel News Editor

Related Posts

What’s new: Microsoft Teams connector in Public Preview
KQL

New Azure Sentinel Learning Modules Released

February 1, 2021
What’s new: Microsoft Teams connector in Public Preview
KQL

How to Connect the New Intune Devices Log Azure Sentinel

January 26, 2021
What’s new: Microsoft Teams connector in Public Preview
KQL

How to Create a Backup Notification in the Event an Unauthorized User Accesses Azure Sentinel

January 11, 2021
Next Post
Microsoft introduces integrated Darktrace-a-like, Azure Sentinel

Download and Backup Your Azure Sentinel Playbooks

ForgeRock Joins Microsoft Intelligent Security Association

How to Export and Backup Azure Sentinel Tables Using PowerShell

Introducing the Microsoft Azure Modular Datacenter

Azure Sentinel: Sending an Email Each Morning with the List of Daily Incidents Created

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Follow Us

  • 21.8M Fans
  • 81 Followers

Recommended

Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

Azure Sentinel Sigma & SOC Prime Integration (Part 3): Deploy to multiple workspaces and tenants

3 months ago
Enriching Windows Security Events with Parameterized Function

How to Link to Related Workbooks within the Current Azure Sentinel Workbook

2 months ago
Understanding How Azure Sentinel and Entity Behavior Analytics Deliver Actionable Intelligence

Understanding How Azure Sentinel and Entity Behavior Analytics Deliver Actionable Intelligence

4 months ago
New Azure Kubernetes Service (AKS) Security Workbook

New Azure Kubernetes Service (AKS) Security Workbook

5 months ago

Instagram

    Please install/update and activate JNews Instagram plugin.

Categories

  • AI & ML
  • Artificial Intelligence
  • Incident Response
  • IR
  • KQL
  • Security and Compliance
  • Security Ochestration & Automated Response
  • Security Operations
  • SIEM
  • SOAR
  • SOC
  • Threat Intelligence
  • Uncategorized

Topics

anomaly automation Azure Azure DevOps Azure Security Center Azure Sentinel Azure Sentinel API Azure Sentinel Connector BlueVoyant Call cybersecurity Detection file GitHub Hunting Huntingy IAC incident response Incident Triage infrastructure as code Investigation jupyter LAQueryLogs MDR Microsoft microsoft 365 mssp Multitenancy Notebooks Pester Playbooks PowerShell python Records Security Sentinel Sharing SIEM signin Supply Chain teams Threat hunting Watchlists Workbooks XDR
No Result
View All Result

Highlights

New Items of Note on the Azure Sentinel GitHub Repo

Tuning the MCAS Analytics Rule for Azure Sentinel: System Alerts and Feature Deprecation

New Search Capability for Azure Sentinel Incidents

Follow-up: Microsoft Tech Talks Practical Sentinel : A Day in the Life of a Sentinel Analyst

Changes in How Running Hunting Queries Works in Azure Sentinel

Azure Sentinel can now Analyze All Available Azure Active Directory Log Files

Trending

What’s new: Microsoft Teams connector in Public Preview
IR

How to Generate Azure Sentinel Incidents for Testing

by Azure Sentinel News Editor
February 26, 2021
0

Do you want to generate an Incident in Azure Sentinel for testing/demoing? Here’s a couple easy ways...

What’s new: Microsoft Teams connector in Public Preview

Azure Sentinel Notebooks Loses It’s Preview Tag

February 25, 2021
Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

The Holy Grail of Azure Sentinel Data Connections: The Azure Service Diagnostic Setting

February 22, 2021
Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

New Items of Note on the Azure Sentinel GitHub Repo

February 18, 2021
Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

Tuning the MCAS Analytics Rule for Azure Sentinel: System Alerts and Feature Deprecation

February 17, 2021

We bring you the best, latest and perfect Azure Sentinel News, Magazine, Personal Blogs, etc. Visit our landing page to see all features & demos.
LEARN MORE »

Recent News

  • How to Generate Azure Sentinel Incidents for Testing February 26, 2021
  • Azure Sentinel Notebooks Loses It’s Preview Tag February 25, 2021
  • The Holy Grail of Azure Sentinel Data Connections: The Azure Service Diagnostic Setting February 22, 2021

Categories

  • AI & ML
  • Artificial Intelligence
  • Incident Response
  • IR
  • KQL
  • Security and Compliance
  • Security Ochestration & Automated Response
  • Security Operations
  • SIEM
  • SOAR
  • SOC
  • Threat Intelligence
  • Uncategorized

[mc4wp_form]

Copyright © 2020 - Azure Sentinel News

No Result
View All Result
  • Home
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence

Copyright © 2020 Azure Sentinel News