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.
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.
That's what I had tried at first. It gives me an empty table. Using the OData source was a way to play with it and find out what exactly I could see and possibly generate a useful error message.
This might be my last update. Unfortunately, tech support hasn't been very helpful. An escalation team is "monitoring" the case that I opened with support, but that's all they are doing. The last contact I had with support they stated that the person trying to query the API needs to be a global admin and has to have a license for Power BI Pro. My IT admin was on the call with us and just to prove that theory wrong, he made me a global admin. The issue was still there. So, the tech support reps took some diagnostic traces and passed it on to the Power BI product team. That was last week. Since then, I have discovered that my organization has a solution that scans our network and records all of the same data (and more) to a SQL Server database. I am giving up on the Defender API (and MS tech support) and I will get all the data from SQL Server instead.
If by some miracle that they contact me with a solution, I'll try it out and post the results here for anyone that is still having issues.
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.