Power BI for Azure ATP advanced Hunting, query for Failed Logon

[snteran]

We are running into a row limitation with Advanced Hunting, 10,000 limitation, and it is our understanding we can get up to 100,000 rows with Power BI.  Will we be able to connect, and how, to our Advanced Hunting workspace from Azure ATP?

[snteran · Accepted solution]

Well, I think I found the api for O365 security:

https://docs.microsoft.com/en-us/microsoft-365/security/mtp/api-advanced-hunting?view=o365-worldwide

Hope this helps others.

[v-yingjl]

Hi @snteran ,

Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[snteran]

well, I think I found the issue as far as connecting Power BI to the correct Advanced Hunting schema.

Looking at https://docs.microsoft.com/en-us/windows/security/threat-protection/microsoft-defender-atp/api-power...

it appears there is not an api created from Power BI to https://security.microsoft.com/advanced-hunting 

I guess we will need to see how to collect the data into a cluster someone and then connect to the cluster to run our query for Failed Logon.

Any advice/suggestions would be appreciated.

Cheers,

Serge

[v-yingjl]

Hi @snteran ,

Maybe you can refer this blog and sample queries:

1. Create custom reports using Microsoft Defender ATP APIs and Power BI 
2. Microsoft Defender ATP Advanced Hunting (AH) sample queries 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aaarmstee67]

the TVM_Export_API returns no rersults set after refreshing that table. it's empty. anybody has an idea why this is happeneing

[snteran]

Thanks for the suggestion.  I tried to add to my query but got syntax error.

Query > security.microsoft.com > advanced hunting:

IdentityLogonEvents

| where LogonType == "Failed logon" and isnotempty(AccountName)

| project LogonTime = Timestamp, LogonType, Application, FailureReason, AccountName, AccountUpn, DeviceName, DestinationDeviceName

Not sure there is a way to add the properties, I'll try a few other ways but I'm not an KQL guru.

Cheers,

Serge

[artemus]

Hrm... not too familar with ATP.

You could try changing:

[Query=[key=AdvancedHuntingQuery]]

to:

[Query=[key=AdvancedHuntingQuery, properties=[Options=[truncationmaxrecords=100000]]]]