unable to query some of the schemas (EmailEvents, SecurityEvent) using Advanced Hunting

[arearawerbaw3br]

Describe the Bug with repro steps

I unified the MDE and Microsoft Sentinel Platform.

I can query all the tables in MDE portal.

In Logic apps, I can query tables like DeviceEvents, DeviceNetworkEvents.

However, I am unable to query some of the tables, like EmailEvents, SecurityEvent

"Failed to retrieve dynamic outputs. As a result, this operation's outputs might not be visible in subsequent actions. Error details: 'take' operator: Failed to resolve table or column expression named 'EmailEvents'. Fix semantic errors in your query."

suspect the backend URL are different

What type of Logic App Is this happening in?

Consumption (Portal)

Which operating system are you using?

Windows

Are you using new designer or old designer

tried Both

Did you refer to the TSG before filing this issue? https://aka.ms/lauxtsg

Yes

Workflow JSON

{
  "type": "ApiConnection",
  "inputs": {
    "host": {
      "connection": {
        "referenceName": "wdatp"
      }
    },
    "method": "post",
    "body": {
      "Query": "EmailEvents\n| take 10"
    },
    "path": "/api/advancedqueries/run"
  },
  "runAfter": {}
}

Screenshots or Videos

Browser

Chrome, Edge

Additional context

suspect the backend URLs are different

I faced similar problem in PowerBI which there are two different APIs

https://api.securitycenter.microsoft.com/api/advancedqueries (For DeviceEvents…)

https://api.security.microsoft.com/api/advancedhunting (For EmailEvents…)

[preetriti1]

Did you try without \n in your query?

[arearawerbaw3br]

same error

  "type": "ApiConnection",
  "inputs": {
    "host": {
      "connection": {
        "referenceName": "wdatp"
      }
    },
    "method": "post",
    "body": {
      "Query": "EmailEvents"
    },
    "path": "/api/advancedqueries/run"
  },
  "runAfter": {}
}

I assume there are two or more backend endpoints that caused the error.

https://api.securitycenter.microsoft.com/api/advancedqueries https://api.security.microsoft.com/api/advancedhunting

[Elaina-Lee]

Hi @arearawerbaw3br, I'm unable to reprod the working case, which makes it difficult to see where the problem is coming from. Could you provide us with your workflow ID, resource group etc? If you would not like to share over Github you can email me at [email protected]

[arearawerbaw3br]

Hi @Elaina-Lee,

Thanks for your respond. I think it requires Microsoft Defender for Endpoint license for you to reproduce it.

I can run the query in the Defender portal

However in the logic apps designer, it cannot query the Schema.

[preetriti1]

@arearawerbaw3br - Did you try keeping your query single line, there is \n being added when you go to next line, the backend endpoint is not handling it correctly hence the error...

@Elaina-Lee - The error is coming from backend service, the only fix we can try making is not adding \n but it wont be straight forward and we might have to create a new editor for this one.

[arearawerbaw3br]

Hi @preetriti1, I tried to query in single line but the error shown it cannot resolve the table "EmailEvents".

for your reference, https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-emailevents-table

[github-actions[bot]]

This issue is stale because it has been open for 45 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 14 days since being marked as stale.

[jeffreylauck]

I am not sure if this has been addressed yet but I am experiencing the same issue.

If I query DeviceInfo | take 10 using Advanced Hunting in a Logic App, it works fine.

But when I try to query EmailEvents | take 10, it is giving me the same error.

[daten-krake]

is there any update on this ? I run in the same error with EmailEvents and EmailAttachmentInfo!

could it be that this connector https://learn.microsoft.com/de-de/connectors/wdatp/#advanced-hunting has some hard coded values or is using the old API ?

cause when i run it via graph explorer and graph everything is fine. https://graph.microsoft.com/v1.0/security/runHuntingQuery

[ThreatMatrix]

I'm experiencing this issue in Power Automate, and I believe it's due to the query being directed to "apis/wdatp." with a connection reference of "shared_wdatp". Meaning only device specific (WDATP) queries will succeed. In the same way the Defender ATP trigger in Power Automate only works for AV related alerts, rather than all the more modern unified alerts that defender 365 correlates into incidents.

In my case IdentityLogonEvents and AADSignInEventsBeta were the tables I was looking to access. As above I get the error: "code": "BadRequest", "message": "'where' operator: Failed to resolve table or column expression named 'IdentityLogonEvents'. Fix semantic errors in your query."

[jeffreylauck]

@ThreatMatrix Thanks and I got the same answer from Microsoft Support. Basically they said the Advanced Hunting connector in Logic App only works for MDE, so Device* logs are available. However, since EmailEvents log table is part of Defender for M365, it is not available through this connector.

[dewardvide]

Thank you all for raising and commenting on this issue - I just wanted to mention that the issue still exists and affects tables such as the CloudAppEvents table as well. I hope the connector is updated soon : )