Forum Discussion

JMSHW0420's avatar
JMSHW0420
Iron Contributor
Mar 27, 2025

Scheduled Analytics Rule not triggering...

Hello, I am trying to trigger the following KQL query in a custom scheduled Analytics Rule...

It is to identify ANY Global Administrator and verify if they have committed any activity (Sign-in) over the last 24 hours.

Simple testing is to get a Global Administrator to sign in within the last 24 hours.

Now the query triggers and returns records when run in the Logs pane...

What I have noticed is that when activated in a custom-scheduled Analytics Rule, it fails to return records!

Now the time range set for the analytics rule (query frequency & lookback duration) aligns properly with the query logic or any log ingestion delay.

Query scheduling

  • Run query every: 1 day 
  • Lookup data from the last: 1 day

The funny thing is, when testing the KQL query in the Analytics Rule and Set rule logic/View query results, if the FIRST ATTEMPT returns no results (in the simulation), after repeatedly testing (clicking the test link), it DOES return records!

Why is there a time-lag? How can I ensure the query triggers correctly, returning records accordingly, and related Incidents?

This is the KQL query...  

let PrivilgedRoles = dynamic(["Global Administrator"]);
let PrivilegedIdentities =
    IdentityInfo
    | summarize arg_max(TimeGenerated, *) by AccountObjectId
    | mv-expand AssignedRoles
    | where AssignedRoles in~ (PrivilgedRoles)
    | extend lc_AccountUPN = tolower(AccountUPN)
    | summarize AssignedRoles=make_set(AssignedRoles)
        by
        AccountObjectId,
        AccountSID,
        lc_AccountUPN,
        AccountDisplayName,
        JobTitle,
        Department;
SigninLogs
| where TimeGenerated > ago (1d)
| extend lc_UserPrincipalName = tolower(UserPrincipalName)
| join kind=inner PrivilegedIdentities on $left.lc_UserPrincipalName == $right.lc_AccountUPN
| project
    TimeGenerated,
    AccountDisplayName,
    AccountObjectId,
    lc_AccountUPN,
    lc_UserPrincipalName,
    AppDisplayName,
    ResultType,
    ResultDescription,
    IPAddress,
    LocationDetails
alerts
analytics
KQL
kusto
Log Data
  • duliprb's avatar
    duliprb
    Brass Contributor
    Apr 01, 2025

    Hi JMSHW0420 Greetings, what I believe is start from simple query and see whether incidents are creating and then gradually improve the quality, secondly check you have selected right table and right colums, that way you can identify the exact problem. Also make sure there is actual data visible in result simulation.

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Mar 31, 2025

    I'd look to remove the ago(1d) line from the SigninLogs part as the portal applies those, and retest

    You can also avoid the lowercase conversion and use the ID's

    let PrivilgedRoles = dynamic(["Global Administrator"]);
let PrivilegedIdentities =
    IdentityInfo
    | summarize arg_max(TimeGenerated, *) by AccountObjectId
    | mv-expand AssignedRoles
    | where AssignedRoles in~ (PrivilgedRoles)
    | summarize AssignedRoles=make_set(AssignedRoles)
        by
        AccountObjectId,
        AccountSID,
        AccountUPN,
        AccountDisplayName,
        JobTitle,
        Department;
SigninLogs
| join kind=inner PrivilegedIdentities on $left.UserId == $right.AccountObjectId
| project
    TimeGenerated,
    AccountDisplayName,
    AccountObjectId,
    AccountUPN,
    UserPrincipalName,
    AppDisplayName,
    ResultType,
    ResultDescription,
    IPAddress,
    LocationDetails

     

  • AndrewBlumhardt's avatar
    AndrewBlumhardt
    Icon for Microsoft rankMicrosoft
    Mar 31, 2025

    The query is valid. I am not sure why you are having issues. I recommend revising your intent and target table. Verify that there are not templated rules that meet your needs that are included with these connectors. Your query would alert anytime someone assigned GA logs on. This could be very noisy and the security context is unclear. You might do better to monitor the AuditLog for PIM elevation and GA usage. It may even be better as a workbook to make GA usage easier to review.

    • JMSHW0420's avatar
      JMSHW0420
      Iron Contributor
      Mar 31, 2025

      Hello Andrew.

      Thank you for your response.

      As I explained, I know the query works as I developed it.

      What is unclear is why there appears to be a delay in retrieving the log data when triggered from an analytics rule.

      As for your response, I am unsure what you mean by intent and target table. Could you expand on that, please?

      Also, I assume when you mean other templated rules, you mean similar type rules performing to the one I am running?

      Luckily, the query is not noisy as we are dealing with an SME client. As for the security context, the client ONLY wants to know when GAs log in; not so much the operations they perform.

      Again, I am grateful for your response, Andrew. 

      Jason

Resources