Audit Logs

[tijan2018]

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?

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

[#2 · Accepted 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)

[#3]

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!

[#4]

I am glad it helped!

[#5]

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.

[#6]

That is indeed important.