Difference Between Azure Activity and Azure Diagnostic logs

Azure Activity and Azure Diagnostic logs are two different services in Azure that provide different types of logs.

Azure Activity logs

It provide insight into operations that were performed on Azure resources, such as creating, updating, or deleting a resource. These logs contain information such as who performed the operation, what operation was performed, and when it was performed.

Azure Activity logs can be used to monitor and audit activity in your Azure environment.

Some query Examples:

Query1:

AzureActivity
| where ResourceType == ""
| where OperationName == ""
| where TimeGenerated > ago(7d)
| project ResourceId, ResourceGroupName, ResourceProviderName, ResourceTypeName, OperationName, Caller, TimeGenerated

This query retrieves Azure Activity logs for a specific resource type and operation name, within the past 7 days. You’ll need to replace <your_resource_type> and <your_operation_name> with the actual resource type and operation name you want to search for. For example, you could use “Microsoft.Compute/virtualMachines” as the resource type and “Create or Update Virtual Machine” as the operation name.

The query filters the results to show only the Resource ID, Resource Group name, Resource Provider name, Resource Type name, Operation name, Caller (i.e., the user who performed the operation), and TimeGenerated (i.e., when the operation was performed).

Query2:

AzureActivity
| where TimeGenerated > ago(7d)
| project TimeGenerated, ResourceId, ResourceGroupName, ResourceProviderName, ResourceTypeName, OperationName, OperationStatus, Caller, CorrelationId, ActivityId, SubscriptionId, ResourceLocation, ResourceSize, ResourceUri, Category, EventDataId, Level, Description, Properties

This query retrieves all Azure Activity logs generated within the past 7 days. The query projects several fields including the TimeGenerated, ResourceId, ResourceGroupName, ResourceProviderName, ResourceTypeName, OperationName, OperationStatus, Caller, CorrelationId, ActivityId, SubscriptionId, ResourceLocation, ResourceSize, ResourceUri, Category, EventDataId, Level, Description, and Properties.

You can customize the query to include additional fields or filter criteria based on your specific needs.

Query3:

AzureActivity
| where OperationName == "AuditLogs"
| where Category == "SQLSecurityAuditEvents"
| where schema_name_s == ""
| where action_s == "SCHEMA_OBJECT_ACCESS_GROUP"
| where succeeded_b == true
| where principal_name_s != ""
| project principal_name_s, client_ip_s, server_instance_name_s, database_name_s, schema_name_s, object_name_s, event_time_t

This query uses the Azure Activity log to retrieve audit logs related to SQL security events. It filters the results to show only events related to the specified schema name, and the action of accessing a schema object. The query also shows the name of the user who accessed the schema, the client IP address, the server instance name, the database name, the schema name, the object name, and the event time.

Note that you’ll need to replace <your_schema_name> with the actual name of the schema you want to search for.

Some available fields and parameters that you can use in an Azure Activity query:

Fields

• time: The timestamp of the event, in UTC time.
• resourceId: The unique identifier of the Azure resource associated with the event.
• operationName: The name of the operation associated with the event.
• category: The category of the event, which corresponds to a specific type of operation or activity.
• resultType: The result type of the operation associated with the event, which can be “Succeeded”, “Failed”, or “Other”.
• caller: The identity of the user or service that initiated the operation associated with the event.
• correlationId: The unique identifier used to correlate events associated with the same operation or activity.
• subscriptionId: The unique identifier of the Azure subscription associated with the event.
• resourceGroupName: The name of the resource group associated with the event.
• resourceProviderName: The name of the Azure resource provider associated with the event.
• resourceType: The type name of the Azure resource associated with the event.
• resourceLocation: The location of the Azure resource associated with the event.
• status: The status of the operation associated with the event.
• durationMs: The duration of the operation associated with the event, in milliseconds.
• callerIpAddress: The IP address of the client that initiated the operation associated with the event.
• httpMethod: The HTTP method used to perform the operation associated with the event.
• httpRequestUrl: The URL used to perform the operation associated with the event.

Parameters

• startTime: The start time of the time range to retrieve activity logs.
• endTime: The end time of the time range to retrieve activity logs.
• interval: The time interval of the activity logs to retrieve.
• filter: A filter expression to apply to the activity logs. This can be used to filter by specific fields and values.
• select: A comma-separated list of fields to retrieve in the activity logs.
• expand: A comma-separated list of fields to expand in the activity logs. This can be used to retrieve additional information about the event or resource.
• orderBy: The field to use for sorting the activity logs.
• top: The number of activity logs to retrieve.
• skip: The number of activity logs to skip before retrieving results. This can be used for pagination.
• count: A Boolean value indicating whether to include a count of the number of activity logs in the response. If true, the response will include a totalRecords field with the count value.

Azure Diagnostic logs

It provide detailed diagnostic and troubleshooting information for Azure resources. These logs contain information such as performance metrics, system events, and application logs. Azure Diagnostic logs can be used to identify and troubleshoot issues in your Azure environment.

To retrieve logs from Azure, you can use the Azure Portal, Azure PowerShell, Azure CLI, or Azure REST APIs. For example, to retrieve Azure Activity logs, you can use the Azure Portal or Azure PowerShell. To retrieve Azure Diagnostic logs, you can use Azure PowerShell, Azure CLI, or the Azure Portal.

Once you retrieve the logs, you can use tools like Azure Log Analytics or Azure Monitor to analyze and visualize the data. You can also create custom queries to extract specific information from the logs.

Some query Examples

Query1:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where action_name_s == "SCHEMA_OBJECT_ACCESS_GROUP"
| where succeeded_b == true
| where schema_name_s == ""
| where principal_name_s != ""
| project principal_name_s, client_ip_s, server_instance_name_s, database_name_s, schema_name_s, object_name_s, event_time_t

This query uses the Azure Diagnostic logs to retrieve SQL Security Audit events related to schema object access. It filters the results to show only events related to the specified schema name and where the access was successful. The query also shows the name of the user who accessed the schema, the client IP address, the server instance name, the database name, the schema name, the object name, and the event time.

You’ll need to replace <your_schema_name> with the actual name of the schema you want to search for.

Note that you’ll need to have the Azure Diagnostic logs enabled and configured for your SQL server in order to run this query.

Query2:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" 
| where Category == "SQLSecurityAuditEvents" 
| where TimeGenerated > ago(7d)
| where action_name_s == "DATABASE_OBJECT_ACCESS_GROUP" 
| where succeeded_b == true 
| where principal_name_s != ""
| where database_name_s == "<your_database_name>"
| project principal_name_s, client_ip_s, server_instance_name_s, database_name_s, schema_name_s, object_name_s, event_time_t, event_subtype_s, action_id_s, action_name_s, session_id_s, server_principal_name_s, database_principal_name_s, target_username_s, target_principal_name_s, target_server_name_s, target_database_name_s, application_name_s, application_id_s, severity_s, event_sequence_s, additional_information_s, user_defined_event_id_s, instance_name_s, management_event_name_s, schema_owner_s, statement_s, additional_data_json_s

This query retrieves Azure Diagnostic logs related to SQL Security Audit events, specifically for database object access, within the past 7 days. It filters the results to show only events related to the specified database name and where the access was successful. The query also shows a variety of other fields, including the name of the user who accessed the object, the client IP address, the server instance name, the schema name, the object name, the event time, the event subtype, the action ID, the action name, the session ID, the server principal name, the database principal name, the target username, the target principal name, the target server name, the target database name, the application name, the application ID, the severity, the event sequence, the additional information, the user-defined event ID, the instance name, the management event name, the schema owner, the statement, and the additional data in JSON format.

You’ll need to replace <your_database_name> with the actual name of the database you want to search for.

Some available fields and parameters that you can use in an Azure Diagnostic query:

Fields

• TimeGenerated: The timestamp of the event, in UTC time.
• ResourceId: The unique identifier of the Azure resource associated with the event.
• Category: The category of the event, which corresponds to a specific type of operation or activity.
• Level: The severity level of the event, which can be “Informational”, “Warning”, “Error”, or “Critical”.
• OperationName: The name of the operation associated with the event.
• OperationId: The unique identifier of the operation associated with the event.
• Status: The status of the operation associated with the event.
• Caller: The identity of the user or service that initiated the operation associated with the event.
• CorrelationId: The unique identifier used to correlate events associated with the same operation or activity.
• ActivityId: The unique identifier used to identify the activity associated with the event.
• SubscriptionId: The unique identifier of the Azure subscription associated with the event.
• ResourceGroupName: The name of the resource group associated with the event.
• ResourceProviderName: The name of the Azure resource provider associated with the event.
• ResourceTypeName: The type name of the Azure resource associated with the event.
• ResourceLocation: The location of the Azure resource associated with the event.
• ResourceUri: The URI of the Azure resource associated with the event.
• Properties: Additional properties associated with the event, in JSON format.

Parameters

• ResourceProvider: The name of the Azure resource provider associated with the event.
• Category: The category of the event, which corresponds to a specific type of operation or activity.
• TimeGenerated: The time range of the events to retrieve, specified using a time interval or a specific time value.
• Level: The severity level of the events to retrieve.
• OperationName: The name of the operation associated with the events to retrieve.
• Status: The status of the operation associated with the events to retrieve.
• Caller: The identity of the user or service that initiated the operation associated with the events to retrieve.
• CorrelationId: The unique identifier used to correlate events associated with the same operation or activity.
• ActivityId: The unique identifier used to identify the activity associated with the events to retrieve.
• SubscriptionId: The unique identifier of the Azure subscription associated with the events to retrieve.
• ResourceId: The unique identifier of the Azure resource associated with the events to retrieve.
• ResourceGroupName: The name of the resource group associated with the events to retrieve.
• ResourceTypeName: The type name of the Azure resource associated with the events to retrieve.
• ResourceLocation: The location of the Azure resource associated with the events to retrieve.
• Properties: Additional properties associated with the events to retrieve, specified using a combination of field name and value filters.