Difference between "SQLSecurityAuditEvents" and "ExecRequests"

Copper Contributor

Hi, I've been working on KQL for some weeks now and recently I've been doing a task in which I've to get the statement type (like SELECT, INSERT,UPDATE etc) for the respective SQL queries that are in the database, I've found that we can get that by using the following command for example

AzureDiagnostics 

| where Resource =="ABCXYZ"

| where StartTime_t > todatetime("2020-01-10T23:30") | where StartTime_t < todatetime("2020-02-11T06:30")

| where Category=="ExecRequests"

| where StatementType_s =="Select"

| extend Length=strlen(Command_s)

| extend Execution_in_Seconds= datetime_diff('second',EndTime_t,StartTime_t)

| where Status_s == "Completed"

| where datetime_diff('second',EndTime_t,StartTime_t)> 100

 

But the problem arises when I've to relate this to the username or the table name as I'm not able to find any columns in it which displays the username or table name, so I want to know is there any other way by which i can get Statement type along with the username or table ?

One more thing I wanted to ask is what is the difference between "SQLSecurityAuditEvents" and "ExecRequests" as I use the SQLSecurityAuditEvents generally to get the username along with the respective sql query , a simple query for example is

AzureDiagnostics

| where Category == "SQLSecurityAuditEvents"

| where ResourceProvider == "MICROSOFT.SQL"

| where database_name_s == "ABCXYZ" 

| where TimeGenerated > ago(24h)

 

Is there is any way that we can relate the above two queries and get the Statement type along with username or table name?

Thank you.

0 Replies