Forum Discussion
tijan2018
Microsoft
Mar 09, 2022Audit 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?
AuditLogs
| where OperationName == "Invite external user"
| where TimeGenerated !between (datetime(06:00:00) .. datetime(23:00:00))
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)
- GaryBusheyBronze ContributorYou also want to make sure that the date/time you are looking at is not stored in UTC time but rather your local time.
- JonhedSteel ContributorThat is indeed important.
- JonhedSteel Contributor
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)