Microsoft Sentinel Schema & Tables

One of the key features of Microsoft Sentinel is the ability to ingest data from different sources, such as Azure services, Microsoft 365, third-party solutions, and custom connectors. Logs and Tables can store, query, and visualize this data in the Microsoft Sentinel portal. Logs and Tables are based on Azure Data Explorer, a fast and scalable data analytics service.

To use Microsoft Sentinel data source, you need to create a workspace in Microsoft Sentinel and connect it to the data sources you want to monitor. You can use the built-in connectors or create your own using the Azure Sentinel REST API or PowerShell cmdlets. Once you have connected your data sources, you can use the Logs blade in the Azure portal to access the Tables that store your data. Each Table has a schema that defines the columns and data types of the records.

You can use the query blade to write queries using the KQL, create reusable query blocks that you can call from other queries, use the Chart tab in the Query editor to visualize your query results using various chart types, such as line, bar, pie, or map, and many actions.

Below are some of the Microsoft Sentinel tables that you can use on a daily basis.

SigninLogs

The SigninLogs table in Microsoft Sentinel contains all of the sign-in logs from Azure Active Directory. This table includes information about the user who signed in, the IP address they used, the device they used, and the result of the sign-in attempt.

Here are some of the fields that are included in the SigninLogs table:

• Timestamp – The date and time of the sign-in attempt.
• UserPrincipalName – The user’s principal name.
• IpAddress – The IP address of the device that was used for the sign-in attempt.
• UserAgent – The user agent string of the device that was used for the sign-in attempt.
• Result – The result of the sign-in attempt.
• RiskLevel – The risk level of the sign-in attempt.
• RiskEventTypes – The list of risk event types associated with the sign-in attempt.

You can use the information in the SigninLogs table to investigate suspicious sign-in attempts and to identify potential security threats.

SigninLogs
| where TimeGenerated > ago(24h)
| summarize count() by UserPrincipalName
| order by count_ desc

Here are some tips for using the SigninLogs table:

• Use the search bar to filter the data in the table to focus on specific sign-in attempts.
• Look for sign-in attempts that were made from unexpected IP addresses or that were made using unusual user agent strings.
• Pay attention to the risk level of sign-in attempts. A high risk level indicates that the sign-in attempt may be malicious.
• Investigate any suspicious sign-in attempts to determine if they pose a security threat.

The SigninLogs table is a valuable resource for security analysts who are investigating suspicious sign-in activity. By using this table, analysts can quickly identify the users, devices, and IP addresses that are associated with suspicious sign-in attempts. This information can then be used to investigate the sign-in attempts and determine whether they are malicious.

AuditLogs

The AuditLogs in Azure can be a valuable resource. They can be used to track user activity, identify suspicious behavior, and investigate security incidents.

Here are some specific ways that threat hunters can use audit logs in Azure:

• Track user activity: Audit logs can be used to track user activity in Azure, such as who logged in, what resources they accessed, and what changes they made. This information can be used to identify suspicious activity, such as unauthorized access or unauthorized changes to resources.
• Identify suspicious behavior: Audit logs can be used to identify suspicious behavior, such as repeated failed login attempts, unusual patterns of activity, or access to sensitive resources. This information can be used to investigate potential security incidents.
• Investigate security incidents: Audit logs can be used to investigate security incidents. By tracking the events leading up to and after an incident, threat hunters can identify the source of the attack and the damage that was done.

 AuditLogs
| where OperationName in~ (“Remove member from role”, “Remove eligible member from role”)
| where Identity !has “MS-PIM”
| extend roleName = trim(‘”‘ , tostring(TargetResources[0].modifiedProperties[1].oldValue))
| where roleName in~ (“Global Administrator”)
| where TargetResources[0].type =~ “User”
| extend Actor = tostring(TargetResources[0].id), removedUserUpn = tostring(TargetResources[0].userPrincipalName)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), removedAccounts = dcount(removedUserUpn), removedUserUPN=make_set(removedUserUpn) by Actor
| extend timestamp = StartTime, AccountCustomEntity = Actor

Here are some tips for using audit logs in Azure for threat hunting:

• Configure audit logging: The first step is to configure audit logging for the resources that you want to monitor. You can do this by using the Azure portal or the Azure CLI.
• Collect audit logs: Once you have configured audit logging, you need to collect the audit logs. You can do this by using Azure Monitor or by exporting the logs to a third-party SIEM solution.
• Analyze audit logs: Once you have collected the audit logs, you need to analyze them to identify suspicious activity. You can use a variety of tools and techniques to do this, such as anomaly detection, correlation, and threat intelligence.
• Take action: If you identify suspicious activity, you need to take action to investigate the incident and remediate any vulnerabilities.

Audit logs can be a valuable tool for threat hunters, but they are only one part of a comprehensive security strategy. By combining audit logs with other security tools and techniques, threat hunters can improve their ability to detect and respond to security threats.

AzureActivity

The AzureActivity is a table that stores data about all the events that occur in your Azure subscription and management group level. This includes events such as creating, updating, deleting, or accessing resources,  assigning or revoking permissions, making changes to resource configurations, etc.

The structure of the AzureActivity table varies depending on the category of the log entry. Here are some of the columns in the AzureActivity table:

• ResourceID – The Azure resource ID that the event is associated with.
• OperationName – The name of the operation that was performed.
• Status – The status of the operation. Possible values are:
  • Started
  • In Progress
  • Succeeded
  • Failed
  • Active
  • Resolved
• Substatus – A more detailed description of the status of the operation.
• Authorization – A blob of RBAC properties of the event. This usually includes the “action”, “role”, and “scope” properties.
• TimeCreated – The date and time the event occurred.

You can use the Azure Monitor Log Analytics queries to retrieve data from the AzureActivity table. For example, the following query will return all events that occurred in the past hour:

You can also create alerts based on events in the AzureActivity table. For example, you could create an alert that sends an email notification if a resource is deleted.

OfficeActivity

The OfficeActivity table in Microsoft Sentinel is a valuable resource for investigations. The table contains a wealth of information about user activity in Office 365, which can be used to identify suspicious activity, such as unauthorized access to user accounts or data.

Here are some specific examples of how the OfficeActivity table can be used for DFIR:

• To identify unauthorized access to user accounts, you can use the OfficeActivity table to look for activities such as:
  • A user logging in from an unusual location
  • A user logging in with a different IP address than usual
  • A user logging in from a different device than usual
• To identify unauthorized access to data, you can use the OfficeActivity table to look for activities such as:
  • A user accessing a document that they are not authorized to access
  • A user making changes to a document that they are not authorized to make
  • A user deleting a document that they are not authorized to delete

OfficeActivity
| where TimeGenerated > ago(7d)
| where Operation =~ ‘MemberRoleChanged’
| where Members contains ‘Role’ and Members contains ‘1’

The OfficeActivity table can also be used to identify other types of suspicious activity, such as:

• Brute force attacks
• Phishing attacks
• Malware infections

By analyzing the data in the OfficeActivity table, DFIR analysts can gain a deeper understanding of the threat landscape and identify potential security incidents.

SecurityEvent

The SecurityEvent table in Microsoft Sentinel stores security events collected from Windows machines by Azure Security Center or Azure Sentinel. The table contains a variety of columns that provide detailed information about each event, such as the account that was involved, the activity that was performed, and the source of the event.

The SecurityEvent table is stored in an Azure Monitor Log Analytics workspace. Log Analytics workspaces are used to collect and store telemetry data from Azure resources, including security events.

To access the SecurityEvent table, you can use the Azure Monitor Log Analytics query language. For example, the following query returns all security events that were logged in the past 24 hours:

You can also use the Microsoft Sentinel portal to view and analyze the data in the SecurityEvent table.

Here are some of the things you can do with the SecurityEvent table:

• Use the data to identify and investigate security incidents.
• Use the data to create alerts that notify you of potential security threats.
• Use the data to generate reports that help you track your security posture.

The SecurityEvent table is a valuable resource for security analysts who need to monitor and protect their Azure environments.

SecurityAlerts

The SecurityAlerts table in Microsoft Sentinel is a centralized repository for all security alerts generated by Microsoft security products, as well as third-party solutions that are integrated with Sentinel. This table contains a wealth of information about each alert, including the following:

• The source of the alert
• The type of alert
• The severity of the alert
• The description of the alert
• The time and date the alert was generated
• The impacted resources
• The recommended remediation steps

Security analysts can use the SecurityAlerts table to quickly identify and investigate potential security threats. The table can be queried using the Azure Monitor Log Analytics query language, or it can be explored using the Microsoft Sentinel dashboard.

Here are some tips for exploring the SecurityAlerts table in Microsoft Sentinel:

• Use the AlertSeverity column to filter alerts by severity.
• Use the AlertType column to filter alerts by type.
• Use the Description column to search for specific keywords or phrases.
• Use the TimeGenerated column to filter alerts by date and time.
• Use the ResourceId column to filter alerts by resource ID.

Once you have identified an alert that you want to investigate, you can use the information in the SecurityAlerts table to quickly understand the threat and take appropriate action.

SecurityIncident

The SecurityIncident table in Microsoft Sentinel is a critical resource for DFIR teams. It stores information about all incidents that have been created in Sentinel, including the incident’s title, description, severity, status, and owner. The table also stores information about the events that triggered the incident, as well as the tasks that were performed during the incident.

DFIR teams can use the SecurityIncident table to track the progress of incidents, identify trends, and improve their incident response processes. The table can also be used to generate reports and dashboards that provide insights into the security posture of an organization.

Here are some of the key benefits of using the SecurityIncident table for DFIR:

• Track the progress of incidents: The SecurityIncident table provides a chronological record of all changes that have been made to an incident, including the time and date of the change, the user who made the change, and the details of the change. This information can be used to track the progress of an incident and identify any areas where the incident response process can be improved.
• Identify trends: The SecurityIncident table can be used to identify trends in incident activity. For example, DFIR teams can use the table to identify the types of incidents that are most common, the severity of incidents, and the time of day when incidents are most likely to occur. This information can be used to develop proactive measures to prevent incidents from happening.
• Improve incident response processes: The SecurityIncident table can be used to improve incident response processes by providing insights into the strengths and weaknesses of the current process. For example, DFIR teams can use the table to identify areas where incidents are taking longer to resolve, the types of tasks that are most frequently performed on incidents, and the users who are most involved in incident response.
• Generate reports and dashboards: The SecurityIncident table can be used to generate reports and dashboards that provide insights into the security posture of an organization. For example, DFIR teams can use the table to generate reports on the number of incidents that have occurred, the severity of incidents, the time of day when incidents are most likely to occur, and the users who are most involved in incident response. This information can be used to identify areas where security improvements are needed.

Overall, the SecurityIncident table is a valuable resource. It provides a comprehensive view of all incidents that have been created in Sentinel, and it can be used to track the progress of incidents, identify trends, improve incident response processes, and generate reports and dashboards.

Additional Microsoft Sentinel posts