Log Analytics - Azure Activity Table - Resources
The following can be used as a reference for quering into a Log Analytics workspace for AzureActivity Table
// This shows the count of rows in the table. This depends on the time range you choose in Log Analytics
AzureActivity
| count
// Here you are returning the rows based on the TimeGenerated
AzureActivity
| where TimeGenerated < datetime(2/8/2022 00:00:00)
| count
// Here you are returning the rows based on the TimeGenerated
AzureActivity
| where TimeGenerated < ago(1h)
| count
// You can also print the values to the workspace
print(ago(1h))
print(datetime(2/8/2022 00:00:00))
// Searching for failure
// Free Text Search
AzureActivity
| search "Failure"
AzureActivity
| search "Failure"
| where TimeGenerated < ago(1h)
// Here you are using the where clause to filter on a specific column value
AzureActivity
| where ActivityStatusValue contains "Failure"
AzureActivity
| where ActivityStatusValue contains "Failure"
|count
// Group by ActivityStatusValue
AzureActivity
| summarize count() by ActivityStatusValue
// You can summarize by multiple columns
AzureActivity
| where TimeGenerated < ago(1h)
| summarize count() by ResourceGroup,ActivityStatusValue
| sort by ResourceGroup
// count - Returns the number of records in the input record set.
// count() - Aggregation function
// Grouping by buckets of TimeGenerated
AzureActivity
| summarize count() by bin(TimeGenerated, 1h)
| sort by TimeGenerated
AzureActivity
| where TimeGenerated between (datetime(2/8/2022 03:00:00) .. datetime(2/8/2022 04:00:00))
| count
// Summarize operations per OperationNameValue and resource group
AzureActivity
| summarize count() by OperationNameValue,ResourceGroup
// Project only certain columns
AzureActivity
| project OperationNameValue,ResourceGroup
// Accessing the details of an object
AzureActivity
| project OperationNameValue,ResourceGroup,Properties_d["eventCategory"]
// Converting string to a dynamic object
AzureActivity
| extend r=todynamic(HTTPRequest)
| project OperationNameValue,ResourceGroup,r["clientIpAddress"]
Log Analytics - Exploring other Tables - Resources
-
For the Sign-in logs table
SigninLogs | extend s=todynamic(Status) | where s["errorCode"]<>0
-
For the Firewall logs in the Azure Diagnostics table
AzureDiagnostics | parse msg_s with Protocol 'request from ' * | extend l_protocol=Protocol | project l_protocol
AzureDiagnostics | parse msg_s with Protocol 'request from ' SourceIP ':' * 'to ' DestinationIP ':' * | extend l_protocol=Protocol,l_sourceip=SourceIP,l_destination=DestinationIP | project l_protocol,l_sourceip,l_destination
-
For the Security Events table
SecurityEvent | where EventID ==4673 | project Account
SecurityEvent | summarize count() by bin(TimeGenerated, 1h) | sort by TimeGenerated