SOLVED

Audit Logs

Microsoft

I am trying to write a query that will output results of audit logs for external users added to AAD outside of work hours. Below is the query I currently have but it isn't giving me the results I want. What am I missing?

tijan2018_0-1646788166102.png


AuditLogs
| where OperationName == "Invite external user"
| where TimeGenerated !between (datetime(06:00:00) .. datetime(23:00:00))

5 Replies
best response confirmed by tijan2018 (Microsoft)
Solution

@tijan2018 

When you specify just time in a datetime value it implicitly means "Today at hh:mm:ss", so your query searches for any log that is not between "Today 06:00 and Today 23:00".

 

Maybe you want something like this?

AuditLogs
| where OperationName == "Invite external user"
| where hourofday(TimeGenerated) !between (6 .. 22)
This is excellent. Makes sense now. It is now giving me the desired output of the audit activities outside of the time range indicated in the query. Thank you!
I am glad it helped!
You also want to make sure that the date/time you are looking at is not stored in UTC time but rather your local time.
That is indeed important.
1 best response

Accepted Solutions
best response confirmed by tijan2018 (Microsoft)
Solution

@tijan2018 

When you specify just time in a datetime value it implicitly means "Today at hh:mm:ss", so your query searches for any log that is not between "Today 06:00 and Today 23:00".

 

Maybe you want something like this?

AuditLogs
| where OperationName == "Invite external user"
| where hourofday(TimeGenerated) !between (6 .. 22)

View solution in original post