We have set the Azure SQL audit log for log Analytics and saved the configuration.
Burt from the Log Analytics Workspace & under LOGS blade cannot find the AUDIT logs for query purpose?
We have set the Azure SQL audit log for log Analytics and saved the configuration.
Burt from the Log Analytics Workspace & under LOGS blade cannot find the AUDIT logs for query purpose?
Hi @VivekSingh-5734, thanks for your patience.
In log analytics workspace, we can create a new rule alert and write custom query which would check if logins are failing and will send an alert based on that. Please refer to below article that covers the same:
Identifying who accessed Azure SQL using audit logs
Please use below query to know the count of user logins. Just add the date conditions into it:
AzureDiagnostics
| where action_id_s == 'DBAF'
| project server_principal_name_s, action_name_s
| summarize count() by server_principal_name_s, action_name_s
We can send the blob logs to Log analytics. Please check the below article:
Send logs to Azure Log Analytics
Please let me know if this helps or we can discuss further on the same.
If answer helps, please mark it 'Accept Answer'
Thanks for your time and valuable information. I will go through them and assess each of them. Just a question plz, is there a time limit on when a question put on this forum gets closed. Btw I had come across the link mentioned in point 1.
Also I do some azure security related questions, can i seek your help or do i setup another question.
Thanks again.
Thanks for replying back. We can keep this question opened as long as we get queries sorted out.
Also, as long as the other queries are related to the main question posted we can add here, but if you think it would be good to answer them separately we can raise a new question then.
Hi Anurag
On point 3 --> If earlier SQLDBAUDITLOGS are in the blob (log analytics was not setup), can we now access those OLD XEL files from LOG Analytics to run the queries
What I meant was that earlier audit was configured for ONLY Blob storage and later we changed the audit configuration to Log Analytics ONLY. But we would like to investigate the XEL files from the blob storage using Log Analytics queries.
One option of investigating XEL files is to download and merge in SSMS and run queries there But we would like to investigate the XEL files in the blob storage using Log Analytics queries like the one which you gave. Question is how do we access the XEL audit files in Blob storage and run queries on them like shown below
AzureDiagnostics
| where action_id_s == 'DBAF'
| project server_principal_name_s, action_name_s
| summarize count() by server_principal_name_s, action_name_s
I thought "Advanced Settings --> Data would provide the option of connecting the Log Analytics Workspace to a Blob storage where we have old audit files (XEL) and then investigate them .
We have audit data stored on BLob from previous configuration how do we access them using new Log Analytics config queries. Currently the Log Analytics queries only the audit data AFTER the config changes were made. Example If change was made on MARCH 20th Log Analytics queries show data after March 20th.... what about prior to it?
Hi @VivekSingh-5734, sorry for the delayed response. I have not tried it myself but if you refer to article, section 'Accessing logs in a Log Analytics workspace' talks about accessing blobs using Log analytics. Can you please check if this helps.
Hi @VivekSingh-5734, please let me know if you tried the link I mentioned in last reply.
One more query- Failed to resolve table or column expression named 'SecurityEvent' <--- why don't we have this table. Only THREE tables (AzureMetrics/Diagnostics/Activity) are there which help with queries. Is there a link on how to configure for SECURITYEVENT table....
Hi Anurag
Re - please let me know if you tried the link I mentioned in last reply.
Sorry it didn't help. I'm now wondering if we switch the audit location from storage to log analytics, the XEL audit files are no longer available for Kusto queries in Log Analytics & the only means of querying those files is through SSMS. Have searched on Google but nothing so far. But do appreciate your help throughout. Thanks again.
By the way another question which you could help is once the diagnostic logs are setup for Log Analytics why only three tables have the data shown below.
Why do we get errors like --> failed to resolve table or column expression named 'SecurityEvent' <--- why don't we have this table. Only THREE tables (AzureMetrics/Diagnostics/Activity) are there which help with queries. Is there a link on how to configure for SECURITYEVENT table....
AzureActivity
AzureDiagnostics
AzureMetrics
9 people are following this question.