Microsoft Security Tech Accelerator
Dec 06 2023, 07:00 AM - 12:00 PM (PST)
Microsoft Tech Community

Audit Logs


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?


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

5 Replies
best response confirmed by tijan2018 (Microsoft)


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?

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