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 PowerBI. This 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 https://api.security.microsoft.com/api 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 5. Available 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 8. Transform 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 customODataQuery, as 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”)
let
Source = customODataQuery("alerts","table")
in
Source
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 16. ODataAlerts - Table
After the expansion you will see all the available columns.
Figure 17. ODataAlerts - 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.
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.
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.
let
Source = customODataQuery("Recommendations","table")
in
Source
We also will need to expand the machineReferences field to join this information with the Machines table
Figure 23. Recommendations 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 PowerBi, because 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.
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 KPI. We covered the step-by-step instructions to access the Machines, Recommendations 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:
- Overview of management and APIs - Windows security | Microsoft Docs
- Microsoft Defender for Endpoint APIs connection to Power BI - Windows security | Microsoft Docs
- OData queries with Microsoft Defender for Endpoint - Windows security | Microsoft Docs
- Access the Microsoft Defender for Endpoint APIs - Windows security | Microsoft Docs
- Advanced hunting best practices
- Advanced hunting APIs - Microsoft 365 security | Microsoft Docs
- Microsoft-365-Defender-Hunting-Queries
- M365-PowerBi Dashboard
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