[QUESTION] Get-MDEInsights Module Issue

[AlexBilLogi]

I've been testing out STAT over the past few days and I'm having an issue with the Get-MDEInsights module.

Scope Accounts and Scope Host is working but Scope IP is failing and terminating the Logic App. The IP addresses are selected, then the IP Query is composes.

When the query is passed into the API via POST request it's erroring out with a 400 status code.

{ "error": { "code": "BadRequest", "message": "'join' operator: Failed to resolve column named 'IPAddress'. Fix semantic errors in your query.", "target": "|<removed>." } }

I removed the target that was part of the error.

Has anybody seen this one before? I ran the permissions script without any issues and the other modules I've got working. It just seems to be this one I'm struggling to troubleshoot.

[piaudonn]

Thanks @AlexBilLogi I'm trying to repro but I can't. That's weird cause as long as the column exist in the DeviceNetworkInfo table, the join should work (with or without data). @briandelmsft , reminds me of https://github.com/briandelmsft/STAT-Function/issues/16 :) Maybe it is a permission issue... I know you said the script doesn't yield any error. And AFAIK, the new delegation model around MDE doesn't apply to API permissions... But to be on the safe side, could you confirm that you can run the query with a user that has permissions maybe?

let ipData = print ipData = todynamic(url_decode("%5B%7B%22Address%22%3A%22x.x.x.x%22%7D%5D"));
let ipAddresses = ipData
| mv-expand ipData
| project Address = tostring(ipData.Address);
DeviceNetworkInfo
| where Timestamp > ago(30d)
| summarize arg_max(Timestamp,*) by DeviceId
| extend IPs = todynamic(IPAddresses)
| mv-expand IPs
| evaluate bag_unpack(IPs)
| join kind=inner (ipAddresses) on $left.IPAddress == $right.Address
| summarize ListIds=make_set(DeviceId) by IPAddress

where x.x.x.x is the IPAddress?

[AlexBilLogi]

Thanks @piaudonn

I've run the above query with the IP address substituted in from the Advanced Hunting blade within security.microsoft.com. There's no entries in the table but the query runs without error.

I've also re-ran the permissions script to confirm nothing was changed during my testing and all permissions and roles were already assigned correctly. I've ran the playbook again on a different incident and it's producing the same error as before. I'll keep testing/troubleshooting today and update here if I find what my problem it.

[AlexBilLogi]

I've also checked the permissions assigned to the Managed Identity and everything seems to be in order there.

[AlexBilLogi]

Here's something interesting.

If I run the query with looking back over the last 30 days from the Advanced Hunting blade it returns No results found in the specified time frame.

But, if I put down to 14 days it returns the error 'join' operator: Failed to resolve column named 'IPAddress'

The reason for this would be that this is built in my dev tenant, so there's no logs ingested to that table in the last 14 days. But I would expect this to be the other way around, erroring on actual data instead of due to the lack of.

I switched the loopback period for the Get-MDEInsights. To 30 days and it allows STAT to run all the way through without any issues. I think we can close this issue off because it seems to be caused by lack of data in MDE. In a real deployment scenario this wouldn't be the case. Thanks for looking into this.

[briandelmsft]

The reason for this would be that this is built in my dev tenant, so there's no logs ingested to that table in the last 14 days. But I would expect this to be the other way around, erroring on actual data instead of due to the lack of.

So what is happening is that the IPAddress column isn't part of the native schema for that table, it is added dynamically by the | evaluate bag_unpack(IPs). Since there is no data in your case to unpack the column doesn't get added and then the join fails as if there was a syntax error. If the join was based on a column that was part of the actual table schema this wouldn't matter.

I agree that it probably doesn't need to be fixed since it's such a corner case, but to fix it we would simply union in an empty table that does have that IPAddress column as part of its schema.

Can you try this in your dev tenant?

let ipData = print ipData = todynamic(url_decode("%5B%7B%22Address%22%3A%22x.x.x.x%22%7D%5D"));
let ipAddresses = ipData
| mv-expand ipData
| project Address = tostring(ipData.Address);
let tableSchema = datatable (IPAddress:string)[];
union isfuzzy=true(tableSchema),(
DeviceNetworkInfo
| where Timestamp > ago(14d)
| summarize arg_max(Timestamp,*) by DeviceId
| extend IPs = todynamic(IPAddresses)
| mv-expand IPs
| evaluate bag_unpack(IPs))
| join kind=inner (ipAddresses) on $left.IPAddress == $right.Address
| summarize ListIds=make_set(DeviceId) by IPAddress

[piaudonn]

Note that the logic has changed in v2 https://github.com/briandelmsft/STAT-Function/blob/main/modules/mde.py.

[AlexBilLogi]

That query returns the no results found in specified time frame as opposed to erroring so seems to be resolved. Thanks for all your help on this one.

[briandelmsft]

Note that the logic has changed in v2 https://github.com/briandelmsft/STAT-Function/blob/main/modules/mde.py.

@piaudonn The new KQL is susceptible to the same error, however equally unlikely:

DeviceNetworkInfo
| where Timestamp > ago(1s) //Changed to 1 second so that no results would be returned
| summarize arg_max(Timestamp,*) by DeviceId
| extend IPs = todynamic(IPAddresses)
| mv-expand IPs
| evaluate bag_unpack(IPs)
| where IPAddress == "10.0.0.4"
| distinct IPAddress, DeviceId
| top 30 by DeviceId

[piaudonn]

But the join would not fail ;) the where clause would. Total different issue :D

Joke aside, I guess we could workaround it with | extend IPAddress = column_ifexists("IPAddress","") just to be on the safe side. Opinion?

[briandelmsft]

Fixed in latest PR, will be released in the next build