Forum Discussion

tijan2018's avatar
tijan2018
Icon for Microsoft rankMicrosoft
Mar 09, 2022

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?


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

  • 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)
  • GaryBushey's avatar
    GaryBushey
    Bronze Contributor
    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.
  • Jonhed's avatar
    Jonhed
    Steel Contributor

    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)
    • tijan2018's avatar
      tijan2018
      Icon for Microsoft rankMicrosoft
      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!

Resources