Forum Discussion
Microsoft 365 Defender Data
ShawnHarrison Thanks so much for replying. It is greatly appreciated. I did find one forum where you can use a blank query and connect to all of the tables in the Advanced Hunting Schema. In Power BI , go to new data connection, choose blank query. You have to use the advanced hunting format and you have to use the following URL: https://api.security.microsoft.com/api/advancedhunting
However, I really wish there was a way to connect via web or Odata and Power BI would prompt you with all the tables in the schema and you can pick which onese are needed for your reporting like you are showing in your orginal post.
I spoke with a tech from MS yesterday that was able to explain the issue in about 5 minutes. The problem was related to the first query. The MS documentation isn't the greatest and what it didn't mention in the example I was using is that the first part of that query contains a parameter. The first line, AdvancedHuntingQuery = "DeviceEvents | where ActionType contains 'Anti' | limit 20", is the table and filters. Honestly, I really should have noticed this. I wasn't getting any data because we don't have any rows matching those filters. As mentioned in this thread by Anonymous, I had to use the advanced hunting format as outlined on the advanced hunting page on the Defender 365 site. Changing that first line, I can define what table I need and filter the data that is needed. All you need is reader permissions, contrary to what support originally told me. Also, keep in mind that the query that is placed between those qoutes is written in KQL syntax.
One other thing to know about this is that the url that is used to query this data doesn't work for all the tables mentioned on the advanced hunting page. The second line of that query that contains the URL https://api.securitycenter.microsoft.com/api/advancedqueries may have to be changed to https://api.security.microsoft.com/api/advancedhunting when querying certain tables. If one URL doesn't work, then just try the other.
I'm glad that I eventually got it figured out, but I really wish they had better documentation on that.
Might be that this table has too many redirects/lookups behind it?
Could be. It looks like the table that I really need is AdvancedQueries, but I can't seem to find out how to connect properly. It's using an API but not asking for a token which I am beginning to think is my issue. I may have to give the app permission in Azure. That's what I am starting to think. I'll give that a try and see what happens.
Hi ShawnHarrison ,
Did everything go well in your follow-up operation. Please be specific if there is confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.
Looking forward to your feedback.
Best Regards,
HenryActually, I spoke with a Microsoft technician just yesterday. According to him, our organization hasn't assigned any licenses for Defender for Endpoint yet and we need to have at least one device onboarded first so that the connection to the API will be usable. Our network tech is going to do that today so I can test this. I will be sure to post my results here later today.
