Alerts on specific commands applied to Mariadb using log analytics

[laurakate_young]

Hi,

I am trying to activate alerts when certain commands are applied to our Mariadb database hosted on Azure. I have diagnostic logs (which record the string of query text used) and I am pushing them into my log analytics workspace. I am now trying to configure the search query logic and struggling. 

The search I would like to perform is below but I would really appreciate if someone could shed some light on how this can be amended into the style necessary for the search query logic:

search in (AzureDiagnostics) "drop" or "alter" or "update" or "insert" or "delete" or "merge"

Thank you in advance!

Laura

[#2]

Hi @laurakate_young 

It would be good to know what columns of data you are looking at. 

Go to Log Analytics and Run Query

TenantId	SourceSystem	MG	ManagementGroupName	TimeGenerated	Computer	targetVmNicDetails_s	PolicyTimeZone_s	trustedService_s	audit_schema_version_d	event_time_t	sequence_number_d	succeeded_s	is_column_permission_s	session_id_d	server_principal_id_d	database_principal_id_d	target_server_principal_id_d	target_database_principal_id_d	object_id_d	user_defined_event_id_d	transaction_id_d	duration_milliseconds_d	response_rows_d	affected_rows_d	action_id_s	action_name_s	class_type_s	class_type_description_s	securable_class_type_s	client_ip_s	permission_bitmask_s	sequence_group_id_g	session_server_principal_name_s	server_principal_name_s	server_principal_sid_s	database_principal_name_s	target_server_principal_name_s	target_server_principal_sid_s	target_database_principal_name_s	server_instance_name_s	database_name_s	schema_name_s	object_name_s	statement_s	additional_information_s	user_defined_information_s	application_name_s	data_sensitivity_information_s	host_name_s	connection_id_g	event_id_g	is_server_level_audit_s	collectionName_s	databaseRid_s	originalHost_s	keyType_s	subnetId_s	lock_mode_s	resource_owner_type_s	blocked_process_filtered_s	trackingReference_s	DeploymentUnit_s	InstanceName_s	Value_s	multiVmGroupName_s	lastSuccessfulTestFailoverTime_t	Level_d	isRequestSuccess_b	location_s	lastError_elapsed_d	lastError_source_s	lastError_reason_s	lastError_message_s	lastError_section_s	method_s	url_s	responseCode_d	responseSize_d	cache_s	clientTime_d	clientProtocol_s	partitionId_s	activityId_g	requestResourceType_s	requestResourceId_s	collectionRid_s	statusCode_s	duration_s	clientIpAddress_s	requestCharge_s	requestLength_s	responseLength_s	resourceTokenUserRid_s	region_s	partitionId_g	Tenant_s	requestBytes_s	responseBytes_s	timeTaken_s	securityProtocol_s	routingRuleName_s	httpStatusCode_s	httpStatusDetails_s	RunOn_s	identity_claim_ipaddr_s	identity_claim_http_schemas_microsoft_com_claims_authnmethodsreferences_s	tags_businessowner_s	tags_compositeApp_s	tags_costCode_s	error_state_d	RecoveryJobRPLocation_s	JobOperationSubType_s	AlertConsolidationStatus_s	CountOfAlertsConsolidated_s	AlertRaisedOn_s	AlertCode_s	RecommendedAction_s	BackupItemAppVersion_s	BackupItemSourceSize_s	ProtectedContainerName_g	resultDescription_ErrorJobs_s	resultDescription_ChildJobs_s	ProtectedInstanceCount_s	StorageUniqueId_s	StorageConsumedInMBs_s	OldestRecoveryPointTime_s	OldestRecoveryPointLocation_s	LatestRecoveryPointTime_s	LatestRecoveryPointLocation_s	StorageType_s	StorageName_s	ClusterName_s	NodeRole_s	MachineName_s	AppName_s	AppTypeName_s	AdHocOrScheduledJob_s	ProtectedContainerOSType_s	ProtectedContainerOSVersion_s	ProtectedContainerUniqueId_s	ProtectedContainerFriendlyName_s	AgentVersion_s	ProtectedContainerWorkloadType_s	ProtectedContainerName_s	ProtectedContainerProtectionState_s	ProtectedContainerLocation_s	ProtectedContainerType_s	BackupItemProtectionState_s	isAccessPolicyMatch_b	LogBackupFrequency_s	LogBackupRetentionDuration_s	ResourceGroupName_s	uploadRPOInSeconds_d	uploadRPOUpdateTime_t	processedRPOInSeconds_d	processedRPOUpdateTime_t	timeStamp_t	lastRecoveryPoint_t	latestAppConsistentRecoveryPoint_t	replicatingDisksCount_d	tags_LogicAppsCategory_s	ProviderName_s	TaskName_s	keyProperties_type_s	keyProperties_curve_s	keyProperties_operations_s	keyProperties_attributes_enabled_b	keyProperties_size_d	keyProperties_attributes_exp_d	vaultProperties_s	resultDescription_ChildJobs_ContosoWeb4_Linux_60cf5ef0_42db_4036_bf34_8ad5dfb950f7_g	resultDescription_ChildJobs_ContosoWeb0_Linux_de6f0bed_8942_4674_9826_7ae305c7d534_g	resultDescription_ChildJobs_ContosoWeb3_Linux_20a30155_a894_441b_8012_858c00572623_g	recoveryServicesProviderId_g	resultDescription_ErrorJobs_G0_Win2016_1_afbdb526_62b5_4d56_a7f6_79ed4440d868_s	databaseName_s	metric_s	impact_s	detections_s	rootCauseAnalysis_s	intervalStartTime_t	intervalEndTme_t	issueId_d	value_d	user_defined_b	error_number_d	Severity	state_d	logical_io_writes_d	max_logical_io_writes_d	physical_io_reads_d	max_physical_io_reads_d	logical_io_reads_d	max_logical_io_reads_d	execution_type_d	cpu_time_d	max_cpu_time_d	dop_d	max_dop_d	rowcount_d	max_rowcount_d	query_max_used_memory_d	max_query_max_used_memory_d	duration_d	max_duration_d	num_physical_io_reads_d	max_num_physical_io_reads_d	log_bytes_used_d	max_log_bytes_used_d	wait_type_s	start_utc_date_t	end_utc_date_t	delta_max_wait_time_ms_d	delta_signal_wait_time_ms_d	delta_wait_time_ms_d	delta_waiting_tasks_count_d	LogicalServerName_s	ElasticPoolName_s	DatabaseName_s	wait_category_s	is_parameterizable_s	statement_type_s	statement_key_hash_s	query_hash_s	query_plan_hash_s	statement_sql_handle_s	interval_start_time_d	interval_end_time_d	exec_type_d	total_query_wait_time_ms_d	max_query_wait_time_ms_d	query_id_d	plan_id_d	query_param_type_d	count_executions_d	resultDescription_ErrorJobs_ContosoWeb2_ebea0ce7_21cf_4689_8641_e7b2087f188d_s	resultDescription_ErrorJobs_ContosoWeb3_62ae68a6_a33c_4945_9324_dcb7c7c5c5f8_s	resultDescription_ErrorJobs_ContosoIT2_c4469c89_da43_40e1_b6d7_b5125f304da6_s	resultDescription_ErrorJobs_ContosoWeb1_ContosoRetail_com_102df91d_ae46_40ec_a30d_cd690b612b89_s	resultDescription_ChildJobs_G0_Win2016_1_afbdb526_62b5_4d56_a7f6_79ed4440d868_g	replicationHealthErrors_s	eventType_s	description_s	healthErrors_s	logId_g	timeOfOccurence_t	recoveryNetworkId_s	failoverHealthErrors_s	lastRpoCalculatedTime_t	rpoInSeconds_d	agentVersion_s	recoveryRegion_s	multiVmGroupCreateOption_s	targetStorageAccountId_s	targetStorageAccountName_s	multiVmSyncStatus_s	replicationHealth_s	failoverHealth_s	name_s	primaryFabricName_s	recoveryFabricName_s	primaryFabricType_s	recoveryFabricType_s	primaryContainerName_s	recoveryContainerName_s	protectionState_s	activeLocation_s	policyName_s	replicationProviderName_s	osFamily_s	itemType_s	multiVmGroupId_g	multiVmGroupName_g	id_g	lastHeartbeat_t	initialReplicationProgressPercentage_d	affectedResourceId_s	affectedResourceName_s	affectedResourceType_s	version_s	affectedResourceId_g	logId_d	host_s	LogScheduleFreqInMins_s	LogRetentionType_s	LogRetentionDuration_s	resultDescription_ChildJobs_ContosoWeb2_Linux_7dfa0c0e_9b8d_491d_80b8_0c284e85e40c_g	publicIpAddress_s	port_d	totalDipCount_d	dipDownCount_d	healthPercentage_d	resultDescription_ChildJobs_ContosoWeb1_Dev_ca942d23_8d7b_4a08_a7b0_6df217fc60d9_g	PolicyUniqueId_s	resultDescription_ChildJobs_ContosoAzADDS1_ContosoRetail_com_69666b76_bf16_4b8a_bce6_00f683865c57_g	resultDescription_ChildJobs_ContosoADDS1_ContosoRetail_com_6fd68e14_f18a_45de_8d26_50f76855b1ce_g	resultDescription_ChildJobs_ContosoAzADDS2_e652ac47_3731_480d_8c45_06115a929da3_g	Region_s	GatewayId_g	hsmPoolPropertiesStatus_d	resultDescription_ChildJobs_Contosoweb_Linux_81422fdd_5aa4_49d9_aa86_5e78139aa8e7_g	resultDescription_ChildJobs_ContosoIT2_c4469c89_da43_40e1_b6d7_b5125f304da6_g	retryHistory_s	error_code_s	error_message_s	resultDescription_ChildJobs_ContosoWeb2_Linux_7a1f664e_7e77_413b_b5da_9d01970c781b_g	workflowId_s	_schema_s	status_s	resource_resourceGroupName_s	resource_workflowName_s	resource_runId_s	resource_originRunId_s	resource_location_s	correlation_clientTrackingId_s	resource_actionName_s	code_s	resource_triggerName_s	resource_subscriptionId_g	resource_workflowId_g	correlation_actionTrackingId_g	startTime_t	endTime_t	resultDescription_ChildJobs_Store001Web1_2306024f_1425_42f9_a7c3_e33bcdf13837_g	resultDescription_ChildJobs_ContosoWeb_0823cacc_acfc_4b50_9695_05f338c05061_g	resultDescription_ChildJobs_StoreSrv003_394e7211_f2c1_4851_a2ff_efb9b4656ab5_g	resultDescription_ChildJobs_ContosoWeb2_ebea0ce7_21cf_4689_8641_e7b2087f188d_g	AlertUniqueId_s	AlertType_s	AlertStatus_s	AlertOccurrenceDateTime_s	AlertSeverity_s	CloudStorageInBytes_s	ProtectedInstances_s	resultDescription_ChildJobs_ContosoWeb1_Linux_05d47a4f_8462_4dfc_adf3_f0be0f98f926_g	JobOperation_s	JobStatus_s	JobFailureCode_s	JobStartDateTime_s	BackupStorageDestination_s	JobDurationInSecs_s	DataTransferredInMB_s	JobUniqueId_g	PolicyName_s	BackupFrequency_s	BackupTimes_s	BackupDaysOfTheWeek_s	RetentionDuration_s	DailyRetentionDuration_s	DailyRetentionTimes_s	WeeklyRetentionDuration_s	WeeklyRetentionTimes_s	WeeklyRetentionDaysOfTheWeek_s	MonthlyRetentionDuration_s	MonthlyRetentionTimes_s	MonthlyRetentionFormat_s	MonthlyRetentionDaysOfTheWeek_s	MonthlyRetentionWeeksOfTheMonth_s	YearlyRetentionDuration_s	YearlyRetentionTimes_s	YearlyRetentionMonthsOfTheYear_s	YearlyRetentionFormat_s	YearlyRetentionDaysOfTheWeek_s	YearlyRetentionWeeksOfTheMonth_s	VaultName_s	AzureDataCenter_s	VaultTags_s	StorageReplicationType_s	RegisteredContainerId_s	ProtectedServerType_s	ProtectedServerFriendlyName_s	BackupManagementServerUniqueId_s	AzureBackupAgentVersion_s	EventName_s	BackupItemUniqueId_s	BackupItemId_s	BackupItemName_s	BackupItemFriendlyName_s	BackupItemType_s	ProtectedServerName_s	ProtectionState_s	SchemaVersion_s	State_s	BackupManagementType_s	Level	ProtectedServerUniqueId_s	VaultUniqueId_s	PolicyUniqueId_g	EventId_d	resultDescription_ChildJobs_ContosoWeb3_62ae68a6_a33c_4945_9324_dcb7c7c5c5f8_g	DscResourceId_s	DscResourceName_s	DscResourceStatus_s	DscModuleName_s	DscModuleVersion_s	DscConfigurationName_s	ErrorCode_s	ErrorMessage_s	DscResourceDuration_d	NodeName_s	NodeComplianceStatus_s	DscReportStatus_s	ConfigurationMode_s	HostName_s	IPAddress	NodeId_g	DscReportId_g	LastSeenTime_t	ReportStartTime_t	ReportEndTime_t	NumberOfResources_d	resultDescription_ChildJobs_ContosoWeb1_ContosoRetail_com_102df91d_ae46_40ec_a30d_cd690b612b89_g	resultDescription_value_s	resultDescription_PSComputerName_s	resultDescription_PSSourceJobInstanceId_g	resultDescription_PSShowComputerName_b	resultDescription_Summary_EndDateTimeUtc_t	resultDescription_Summary_DurationInMinutes_d	resultDescription_Summary_MachineId_s	resultDescription_Summary_MachineName_s	resultDescription_Summary_ScheduleName_s	resultDescription_Summary_EndDateTimeUtc_s	resultDescription_Summary_DurationInMinutes_s	resultDescription_Summary_Status_s	resultDescription_Summary_StatusDescription_s	resultDescription_Summary_StartDateTimeUtc_t	resultDescription_Summary_RebootRequired_b	resultDescription_Summary_InitialRequiredUpdatesCount_d	resultDescription_Summary_TotalUpdatesInstalled_d	resultDescription_Summary_TotalUpdatesFailed_d	resultDescription_Summary_InstallPercentage_d	identity_claim_http_schemas_microsoft_com_identity_claims_scope_s	identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s	CallerIPAddress	clientInfo_s	id_s	OperationVersion	ResultSignature	DurationMs	identity_claim_http_schemas_microsoft_com_identity_claims_objectidentifier_g	identity_claim_appid_g	httpStatusCode_d	clientIP_s	httpMethod_s	requestQuery_s	userAgent_s	httpVersion_s	sslEnabled_s	clientPort_d	httpStatus_d	receivedBytes_d	sentBytes_d	timeTaken_d	clientIp_s	clientPort_s	requestUri_s	ruleSetType_s	ruleSetVersion_s	ruleId_s	Message	action_s	site_s	details_message_s	details_data_s	details_file_s	details_line_s	conditions_protocols_s	conditions_destinationPortRange_s	conditions_sourcePortRange_s	conditions_sourceIP_s	conditions_destinationIP_s	conditions_None_s	priority_d	subnetPrefix_s	macAddress_s	primaryIPv4Address_s	ruleName_s	direction_s	type_s	systemId_g	vnetResourceGuid_g	matchedConnections_d	instanceId_s	healthyHostCount_d	unHealthyHostCount_d	requestCount_d	latency_d	failedRequestCount_d	throughput_d	StreamType_s	ResourceId	OperationName	ResultType	ResultDescription	RunbookName_s	Caller_s	Category	SubscriptionId	ResourceGroup	ResourceProvider	Resource	ResourceType	Tenant_g	CorrelationId	JobId_g	Type	_ResourceId
b438b4f6-912a-46d5-9cb1-b44069212abc	Azure			2019-11-21T14:15:32.843Z					null	null	null			null	null	null	null	null	null	null	null	null	null	null																																										null	null	null		null							null	null		null																														null																																							null				null	null	null	null	null	null	null	null							null	null	null												null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null		null	null	null	null	null	null											null	null	null	null	null	null	null	null	null											null			null	null																									null	null						null							null	null	null	null								null																						null	null																																																																		null										null									null	null	null	null					null	null	null								null	null	null	null	null	null								null			null							null	null	null	null	null																				null	10.6.0.0/24	00-0D-3A-70-8D-0A	10.6.0.6	DefaultRule_AllowVnetOutBound	Out	allow	a938471e-4080-49a9-9055-b66ec742b7f0	01b6eaff-e962-4c22-b5a3-4d54f26adbdc	18651		null	null	null	null	null	null		/SUBSCRIPTIONS/E4272367-5645-4C4E-9C67-3B74B59A6982/RESOURCEGROUPS/CONTOSOAZUREHQ/PROVIDERS/MICROSOFT.NETWORK/NETWORKSECURITYGROUPS/CONTOSOWEB1NSG	NetworkSecurityGroupCounters					NetworkSecurityGroupRuleCounter	e4272367-5645-4c4e-9c67-3b74b59a6982	CONTOSOAZUREHQ	MICROSOFT.NETWORK	CONTOSOWEB1NSG	NETWORKSECURITYGROUPS				AzureDiagnostics	/subscriptions/e4272367-5645-4c4e-9c67-3b74b59a6982/resourcegroups/contosoazurehq/providers/microsoft.network/networksecuritygroups/contosoweb1nsg

So you for example the data was in the OperationName column you could do, one of these?

AzureDiagnostics
| where OperatioName  ==  "drop"

AzureDiagnostics
| where OperatioName  contains  "drop"

or

AzureDiagnostics
| where OperatioName  ==  "drop" or OperatioName  == "Insert"

I don't have any MariaDB but you may want to add a check for that - something like this before the query: 

AzureDiagnostics
| where Resource == "MariaDB"
...

This article might also help: https://techcommunity.microsoft.com/t5/Azure-Sentinel/Tip-Easily-use-JSON-fields-in-Sentinel/ba-p/76...

and https://docs.microsoft.com/en-gb/azure/azure-monitor/log-query/get-started-portal 

[#3]

Hi @CliveWatson 

Thank you so much for answering my question. I ran the query you suggested to find the columns which are in the attachment. I also tried to run your operation name queries with no luck and the check for the resource of mariadb also seems not to work! Could this indicate that the mariadb logs are not reaching the log analytics?

Thanks,

Laura

[#4]

@laurakate_young 

You may not have the data or are looking at the wrong thing (I just guessed the Resource name)

Maybe try one of these, you need to find a column that identifies the data (if it exists). 

AzureDiagnostics
| summarize count() by Resource

AzureDiagnostics
| search "maria"

[#5]

@CliveWatson 

That was the problem! Rather than mariadb it needed to be the specific name of the resource. Within the mariadb resource the sql commands are given under "sql_text_s" so I have run a query on that for the commands drop and delete and counting the number seems to work. However when I set the alert condition to greater than 0 in the last 5 minutes (in my mind indicating that one of these commands has been used), I receive emails every 5 minutes following a test of one of the commands. If I change the condition to greater than 1 and run the same test, I don't receive any emails. Any ideas? Thanks

AzureDiagnostics

| where Resource == "name of database"

| where sql_text_s contains "drop" or sql_text_s contains "delete"

| count

[#6]

@laurakate_young 

I would have thought there would be one column that was unique to MariaDB, like there is for SQL - with ResourceProvider.  Anyway moving on...

Anyway you may want to assign a count to AggregatedValue 

AzureDiagnostics
| where Resource == "CONTOSOSQL1NSG"
| where sql_text_s contains "drop" or sql_text_s contains "delete"
| summarize AggregatedValue  = count() by bin(TimeGenerated, 5m)

or just

AzureDiagnostics
| where Resource == "CONTOSOSQL1NSG"
| where sql_text_s contains "drop" or sql_text_s contains "delete"
| summarize AggregatedValue  = count() 

There a great series of post on Alerts https://cloudadministrator.net/2019/08/15/azure-monitor-alerts-series-part-1/  especially part 7 : https://cloudadministrator.net/2019/10/07/azure-monitor-alert-series-part-7/

[#7 · Accepted solution]

Hi  @CliveWatson 

Some great news. Your last suggestion has fixed the problem and my alerts are all running as they should be! Thank you also for the blog series link - it was very helpful.

Thanks again,

Laura