Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
Best practices for leveraging Microsoft 365 Defender API's - Episode Two
Published Mar 10 2021 02:04 AM 17.6K Views

In the previous episode we provided recommendations about how to use the Microsoft 365 Defender API and, specifically, how to optimize the Advanced hunting query.

In this episode we will demonstrate use cases detailing how to access the API data and use this information in other products. 

One of the most common uses of the API is for visualization in PowerBIThis provides the capability to analyze, visualize, and share your data with others quickly and easily.

If you are not familiar with PowerBi, we suggest you visit the Microsoft PowerBi web site, and download PowerBI desktop. 

We already documented how to use PowerBI to create custom reports using  
Microsoft Defender for Endpoint APIs connection to Power BI - Windows security | Microsoft Docs. 


Today we would like to give you additional information on how to use Microsoft 365 APIs with PowerBi, specifically in the following use cases: 

  • Identifying onboarded devices and their health status 
  • Viewing the compliance status of the devices based on the security recommendations 
  • Aggregating alerts 
  • Using Aadvanced hunting queries to build a custom view 

And build some visualizations like the following dashboard 



Figure 1. Alert Status 


Before you begin 

Let’s start by connecting the Microsoft 365 APIs by opening the PowerBi desktop and add a new data source. 

From the menu select Get data and add an OData feed connector to access the Microsoft 365 Defender API. 



Figure 2. PowerBi connectors 


Insert as the URL of the API. 



Figure 3. OData feed connector 


Provide your credentials. 



Figure 4. Provide credentials 


After the authentication you will see all the different APIs you can access. 



Figure 5Available APIs 


NOTE: Be aware that not all APIs can be used to retrieve data as is with PowerBi, some will require additional parameters (e.g. the MachineAction API requires the DeviceID to properly retrieve data for the specific devices and can be used for example in scripts automation), or the AdvancedHunting and AdvancedQueries APIs require a KQL query as a parameter (we will provide an example later). In addition you should use only documented APIs, which means they are supported by the product. In this view you will get all APIs also ones that are still work in progress. 


Get started

Now that you’ve added the connection, we can proceed with loading data. 

Select the Alerts API. 



Figure 6. Alerts APIs 


You should see the data loaded in the Alerts table. 



Figure 7. Alerts APIs – Available fields 


Before moving on, let’s do some optimization to simplify and speed up the process of accessing additional data with other Microsoft 365 APIs 

From the top menu select Transform Data. 



Figure 8Transform data 


Then, select “New Source. 



Figure 9. New Source 


Then, select “Blank Query”. 



Figure 10. Blank Query 


Then select “Advanced Editor. 


Figure 11. Advanced Editor 


Once you have the Advanced Editor open, replace the default code with the following code and save. 



Figure 12. Advanced Editor - Content 





Rename the Query1 to customODataQueryas this will be the function name that we will use, passing different values as parameters it will allow us to retrieve data from different APIs. 

We can also organize the queries in different groups to easy understand the different meaning. 



Figure 13. Functions – customODataquery 


We will now create a query by starting from a blank query and replacing the name of the API we want to access; we need to pass two parameters: the name of the API and the expected results type (“table” or “record”) 



    Source = customODataQuery("alerts","table") 





Figure 14. Advanced Editor - ODataAlerts 


Follow the same steps to add additional APIs. 



Figure 15.  ODataAlerts 


When you select one of the tables, you can expand the data.



Figure 16ODataAlerts - Table 


After the expansion you will see all the available columns. 



Figure 17ODataAlerts - Columns 


Once you have loaded the tables you are ready to organize the data to visualize in a dashboard.  

Check out some of the examples we provided in the PowerBi template published here. 

The PowerBi template contains the following connection to the corresponding APIs: 



Figure 18. PowerBi Template connections 


Example dashboards and uses 

Let’s take a look at some examples of dashboards and visualizations in PowerBI. 


Microsoft Defender for Endpoint Onboarded Devices

This dashboard organizes the details of the onboarded devices. 



Figure 20. Microsoft Defender for Endpoint Onboarded Devices page 


To populate the data, we need the Machines API, so add another query as we did previously (use the customODataQuery PowerBi function we created previously) and specific the Machines API 



    Source = customODataQuery("Machines","table") 




We also would like to adjust the timestamp to date, in this way we can use this field to create data aggregations by day. 



Figure 21. Machines API - Query 


The built-in APIs provide a lot of information to conduct a thorough investigation. You can also aggregate the data to tailor fit your investigation needs.  

Should you find that additional information is needed to enhance the existing set of APIs, please let us know. 


Microsoft Defender for Endpoint Security Control 

This page aggregates the recommendations from Threat & Vulnerability Management. In this case we filtered for by Antivirus and Endpoint Detection and Response. 



Figure 22. Security Recommendation - Security Control Summary page 


To populate the data, we need the Recommendations API, so we will add another query as we did previously (use the customODataQuery PowerBi function we created previously) and specify the Recommendations API.  



    Source = customODataQuery("Recommendations","table") 




We also will need to expand the machineReferences field to join this information with the Machines table



Figure 23Recommendations API - Query 


This will quickly show you the overall compliance status of devices and give you an easy way to export a list of affected devices for remediation. 


Microsoft Defender for Endpoint Agent Health Status 

Should you wish to get some data based on a custom query, you can access the AdvancedHunting API. 

In the previous episode of this series, we encouraged you to optimize and adopt best practices when writing your own queries; this becomes even more important when you plan to use a custom query with APIs to load data into PowerBibecause write efficient optimized queries will execute faster and will consume less resources 

Let’s take this query Endpoint Agent Health Status Report  from GitHub as an example, in  PowerBI we defined 2 additional functions and we created a new query. 

We can start with this template and replace the text with the query we would like to use 



    Source = customquery("COPY YOUR QUERY HERE") 




In our case we added this additional statement to return results from the last 24 hours. 

  | where Timestamp >= ago(24h) 


Here you can see a way to represent the result in a visual format. 



Figure 24. Microsoft Defender for Endpoint Agent Health Status page 



In this post, we demonstrated how you can use the Microsoft 365 APIs and PowerBi to easlyeasily create a tailored dashboard that can help you create visualizations with key information or KPIWe covered the step-by-step instructions to access thMachinesRecommendations and Alerts APIs and how to build custom queries with Advanced hunting API , we used APIs to easily access the Microsoft 356 data and PowerBI to "translate” the date from tables in a visual format. We hope that this can be helpful for you and increase the creation of data visualizations. 



For more information about Microsoft 365 Defender APIs and the features discussed in this article, please read: 



As always, we’d love to know what you think. Leave us feedback directly on Microsoft 365 security center or start a discussion in Microsoft 365 Defender community

Version history
Last update:
‎Apr 28 2021 05:33 AM
Updated by: