question

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 asked VivekSingh-5734 answered

Azure SQL audit set for Log Analytics. Cannot find the SQLDBAUDITLOGS in Log Analytics Workspace under LOGS blade

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?

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT commented

Hi @VivekSingh-5734, thanks for your patience.

  1. 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

  2. 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
    

  3. 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'




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·
VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered AnuragSharma-MSFT commented

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?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.




0 Votes 0 ·

Hi @VivekSingh-5734, please let me know if you tried the link I mentioned in last reply.

0 Votes 0 ·
VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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....

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VivekSingh-5734 avatar image
0 Votes"
VivekSingh-5734 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.