Azure Sentinel tables explained

In the past year I build several SOCs for my customers and the organisation I work for. A question I get asked quite often is: “What data is in which Sentinel table?” and “Where can I find X or Y data?”

In this post I will try to go over the most important tables and explain what data is in there.

• SigninLogs — This table contains al the signin logs of the Azure Active Directory. You will find here which user have tried to login using the Azure Active Directory, from which IP and with what device. Also the result of the login attempt is logged over here.

// show all successful logins in the Azure portal
SigninLogs
| where AppDisplayName == "Azure Portal"
    and ResultType == 0

• AuditLogs — This table contains the audit log of the Azure Active Directory. You will find here all changes that happened to the Azure Active Directory (e.g users created, users added to groups etc.). Azure AD Privileged Identity Management is also sending its logs to this table.

// show all newly created users
AuditLogs
| where OperationName == "Add user"
// show all PIM requests
AuditLogs
| where OperationName == "Add member to role requested (PIM activation)"
     or OperationName == "Remove member from role completed (PIM deactivate)"
| extend Account = tostring(InitiatedBy.["user"].["userPrincipalName"])
| distinct TimeGenerated, Account, ResultDescription

• AzureActivity — This table contains the audit log of the Azure Resource Manager. All activities executed trough the Azure Resource Manager send to this table (e.g. VM deployed, App Service updated, ARM template deployed etc.). Note that Azure Policy is sending log data to this table and permission/RBAC changes in Azure are also logged into this table.

// show all password resets on linux VMs
AzureActivity
| where Resource contains "VMAccessLinuxPasswordReset"
    and ActivityStatus == "Succeeded"
// Show all new Azure Role assignments
AzureActivity
| where ResourceProvider == "Microsoft.Authorization'
    and OperationNameValue == "Microsoft.Authorization/roleAssignments/write"
    and ActivityStatus == "Succeeded"

• OfficeActivity — This is the table that contains al Office 365 related events. At the moment of writing the following applications will write logs to this table: Microsoft Exchange 365, Microsoft SharePoint 365 and OneDrive. In this table you wil find both the operational events as the audit events as well.

// Get all Exchange related events
OfficeActivity
| where OfficeWorkload == "Exchange"
// Get all SharePoint and OneDrive related events
OfficeActivity
| where OfficeWorkload == "SharePoint"
// Get all OneDrive related Events
OfficeActivity
| where OfficeWorkload == "SharePoint" or OfficeWorkload == "OneDrive"
| sort by TimeGenerated

• SecurityEvents — As you have connected Windows machines to the Log Analytics workspace that is being used by Azure Sentinel, security events out of the Windows Eventlog are forwarded to this table.

// Get all login events that are not produced by NT Authority\System
SecurityEvent
| where EventID == "4624"
    and Account <> "NT AUTHORITY\\SYSTEM"

• Syslog — If you have any Linux machines connected to Log Analytics, this table contains all events that are forwarded by Syslog. Note that Syslog is not limited to Linux machines. Some network devices will also use syslog. You probably need a syslog forwarder service in order to hook these devices up to Log Analytics.

// Get all logs from the cron deamon in linux
Syslog
| where ProcessName == "CRON"

• SecurityAlerts — As you have connected Microsoft security products such as Defender ATP, Azure AD Identity Protection, Cloud App Security etc. this table will contain the alerts that have been generated by these products. Sentinel self will also log its incidents to this table.

// Get all alerts reported by Microsoft Cloud App Security
SecurityAlert
| where ProviderName == "MCAS"
// Get all alerts reported by Sentinel
SecurityAlert
| where ProviderName == "Azure Sentinel"

• AzureDiagnostics — This table contains diagnostic data from all resources that have been configured to send diagnostic logs to your log analytics workspace. E.g. logs of your Azure SQL database resource is logged to this table. This is the legacy table where a lot of resources write their logs to. Most of the new resources will write their logs to their own dedicated tables.

// Get all SQL successfull authentications
AzureDiagnostics
| where clientIP_s != ""
| where ResourceProvider == "MICROSOFT.SQL"
and action_name_s == "DATABASE AUTHENTICATION SUCCEEDED"

ThreatIntelligenceIndicator — This is a table that is being used by Azure Sentinel to store custom threat intelligence. Threat intelligence of various services such as MISP and Minemeld can be forwarded to Azure Sentinel. As data is forwarded, it is stored in this table. You can use this table to match ip-addresses, file hashes etc. that are threat indicators with ip addresses that are being used in your environment.

// Show a limited set of data that is in the threat intelligence table
ThreatIntelligenceIndicator
| limit 50

The tables described in this blog post are the tables that I used the most in the past time. If I encounter any new tables that are useful in your security detection operations, I will add them to this post.

Aside from showing the example queries in this blogpost, I also have uploaded them to the following GitHub repository: https://github.com/wortell/KQL