Microsoft 365 Defender Data

[ShawnHarrison]

I am attempting to read data from Microsoft 365 Defender to use in a small dashboard I am working on. I found the tutorial that shows how to use the advanced hunting query here. When I copy and paste the first code example, it just gives me an empty table. No errors, just no data. So I tried the next example using the OData feed. When using the code as is with "MachineActions" as the query, again I get an empty table. I got curious and replaced "MachineActions" with "AdvancedQueries" and received a 404 error.

Just to play with it some more, I tried adding a new data source. I selected OData Feed and provided the URL of https://api.securitycenter.microsoft.com/api and a list of tables populates. However, previewing the tables gives me mixed results. Some tables I can preview, but others give different error messages.

AdvancedHunting and AdvancedQueries...

AdvancedFeatured...

Vulnerabilities...

Those are the errors I receive when selecting random tables. Some tables show data, like AadUsers, and some appear to be empty. Is there something I am missing that is not allowing me to see the data? I can login to the 365 Defender portal and see the dashboards there, so I would assume it wouldn't be a permission issue. So far, I haven't been able to find any info on the errors I am encountering. Any advice would be greatly appreciated!

[Anonymous · Accepted solution]

@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. 

[ShawnHarrison]

This morning I had another call with third party support. Now they are claiming that connecting to the Defender 365 API can only be done if you are in the global admin role. To test this, I walked our security admin through the process and he gets the same result that I get. When using the blank query method and adding the query taken from Microsoft documentation, he just gets an empty table. I also had a rep from the microsoft escalation team contact me this morning, so hopefully I will get some further information soon.

[Anonymous · Accepted solution]

@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. 

[ShawnHarrison · Accepted solution]

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]

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. 

[ShawnHarrison]

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. 

[ShawnHarrison]

Here's an update since it's been a while. I continue to go back and forth with Microsoft tech support. They are claiming it's a permission issue, but each permission or role they say I need, I already have. So I am still waiting on them. This is technically third party tech support, so today I am reaching out to my company's sales rep to find out if there is someone else that look at this issue for us. I'll post here with any progress I manage to make.

[lbendlin]

You may be overwhelming the API with too many requests as evidenced by the 429 you got as the second error.

[ShawnHarrison]

Oddly enough, that error message only shows up when I select the AdvancedFeatures table. Even if it's the first table I select when I connect, it's always the same error for only that table. 

[lbendlin]

Might be that this table has too many redirects/lookups behind it?

[ShawnHarrison]

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.

[v-henryk-mstf]

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,
Henry

[ShawnHarrison]

Actually, 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.

[v-henryk-mstf]

Hi @ShawnHarrison ,

Looking forward to your feedback.😊

Best Regards,
Henry

[ShawnHarrison]

Sorry for the delay. Network admins finally got a device onboarded this morning in Defender 365. I attempted to query the API again and no change. Still no data and errors. The tech I was speaking to at Microsoft has transferred my ticket to the Power BI team. Once I hear from them, I will be sure to post an update.

[v-henryk-mstf]

Hi @ShawnHarrison ,

How is it going so far, looking forward to your reply.😊

Best Regards,
Henry

[ShawnHarrison]

Still waiting on the Power BI support team. I have spoken to them twice already, but no resolution as of yet. 

[Anonymous]

@ShawnHarrison   Did you ever get a solution to this?  I'm having the same issue.

[ShawnHarrison]

@Anonymous  I haven't yet. I was out of the office last week, but they want to schedule another meeting with me sometime this week. I'll be sure to post the outcome here.