Best practices for leveraging Microsoft 365 Defender API's - Episode Two
Published Mar 10 2021 02:04 AM 6,761 Views
Microsoft

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 

 

msftdario_0-1615365951040.png

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. 

 

msftdario_1-1615365951021.png

Figure 2. PowerBi connectors 

 

Insert https://api.security.microsoft.com/api as the URL of the API. 

 

msftdario_2-1615365951023.png

Figure 3. OData feed connector 

 

Provide your credentials. 

 

msftdario_3-1615365951025.png

Figure 4. Provide credentials 

 

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

 

msftdario_23-1615366111099.png

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. 

 

msftdario_24-1615366111101.png

Figure 6. Alerts APIs 

 

You should see the data loaded in the Alerts table. 

 

msftdario_25-1615366111103.png

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. 

 

msftdario_26-1615366111103.png

Figure 8Transform data 

 

Then, select “New Source. 

 

msftdario_27-1615366111104.png

Figure 9. New Source 

 

Then, select “Blank Query”. 

 

msftdario_28-1615366141103.png

Figure 10. Blank Query 

 

Then select “Advanced Editor. 

msftdario_29-1615366141106.png

Figure 11. Advanced Editor 

 

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

 

msftdario_30-1615366141108.png

Figure 12. Advanced Editor - Content 

 

msftdario_0-1619422228784.png

 

 

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. 

 

msftdario_31-1615366141109.png

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”) 

 

let 

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

in 

    Source 

 

msftdario_32-1615366141110.png

Figure 14. Advanced Editor - ODataAlerts 

 

Follow the same steps to add additional APIs. 

 

msftdario_33-1615366141112.png

Figure 15.  ODataAlerts 

 

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

 

msftdario_34-1615366141113.png

Figure 16ODataAlerts - Table 

 

After the expansion you will see all the available columns. 

 

msftdario_35-1615366141114.png

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: 

 

msftdario_36-1615366141115.png

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. 

 

msftdario_37-1615366141125.png

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 

 

let 

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

in 

    Source 

 

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

 

msftdario_38-1615366141117.png

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. 

 

msftdario_39-1615366141128.png

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.  

 

let 

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

in 

    Source 

 

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

 

msftdario_40-1615366141119.png

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 

 

let 

    Source = customquery("COPY YOUR QUERY HERE") 

in 

    Source 

 

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. 

 

msftdario_41-1615366141131.png

Figure 24. Microsoft Defender for Endpoint Agent Health Status page 

 

Conclusion 

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. 

 

Appendix  

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

 
10 Comments
Co-Authors
Version history
Last update:
‎Apr 28 2021 05:33 AM
Updated by: