Build custom email security reporting with Microsoft Defender for Office 365 and PowerBI

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Security teams in both small and large organizations track key metrics to make critical security decisions, as well as identify meaningful trends in their organization over time. Microsoft Defender for Office 365 has rich, built-in reporting capabilities that provide insights into your security posture. However, sometimes security teams require custom reporting solutions to create dedicated views, combine multiple data sources, and get additional insights to meet their needs.


In this blog, we will showcase an example on how you can leverage Power BI and the Microsoft 365 Defender Advanced Hunting APIs to build a custom dashboard and share a template that you can customize and extend.


Power BI allows you to connect multiple data sources, prepare the data, and transform the data easily with the ability to analyze it using built-in and custom visualizations. You can also use Power BI to extend security datasets and correlate it with more information like Identity details, allowing you to create custom views such as targeted roles and departments in your organization. 


In this case we largely rely on the information streamed from Advanced hunting - a threat-hunting tool that uses specially constructed queries to examine the past 30 days of event data across all products in Microsoft 365 Defender and can be used to run queries allowing the API to automate workflows. In the example below, we will show how to connect Power BI to the Microsoft 365 Defender Advanced hunting API and how to automatically pull Microsoft Defender for Office 365 detection details from the related hunting tables.


The queries based on the Email tables exposed through Advanced Hunting include:


  • EmailEvents - contains information about all emails
  • EmailAttachmentInfo - contains information about attachments in an email
  • EmailUrlInfo - contains information about URLs in an email
  • EmailPostDeliveryEvents – contains information about ZAP or Manual remediation events
  • UrlClickEvents - contains information about Safe Links clicks from email messages, Microsoft Teams, and Office 365 apps in supported desktop, mobile, and web apps.
  • CloudAppEvents - CloudAppEventsis used to visualize user reported Phish emails and Admin submissions.


You can find a Power BI template file (.pbit) in the unified Microsoft Sentinel and Microsoft 365 Defender repository which uses the method outlined above to visualize Microsoft Defender for Office 365 detection details here.


The template uses a delegated access model, and you should be able to use it with an account that has permission to run Advanced Hunting queries (e.g., Security Reader or Security Administrator). No app registration is needed.


The template has a high-level overview with details regarding Spam, Phish, and Malware detections and can aid as a great starting point to create your own, custom reporting dashboards.



Figure 1: Top level view of key security metrics consolidated in one view.

With a quick view the security team can take actionable steps to improve the organization’s security posture.




Figure 2: Drill down view of Malware detections

Given the High impact of security breach Malware items can cause to an organization’s security posture, this view offers security teams a detailed view of attributes they can focus on to prevent, respond, and eliminate - such breaches in their organization.




Figure 3: Details of Business Email Compromise (BEC) and Sender Authentication (SPF, DKIM, DMARC)




Figure 4: Top level metrics of Authentication results




Figure 5: Insights about URL clicks in email messages, Microsoft Teams, and Office 365 Apps (Word, Excel, PowerPoint)




Figure 6: Drilled down metrics in user reports and Admin submission in the organization




Figure 7: Information about configurations which override Microsoft Defender for Office 365 detection actions

Frequent review of Override insights is key in maintaining a healthy security posture to ensure only required overrides are in place.   


Based on the email attributes in the Advanced Hunting schema, you can define more functions and visuals. For example, using the DetectionMethods field to analyze detections caught by capabilities like Spoof detections, Safe Attachment, and Detonations.  Using the PowerBI external functions, you can use details such as SenderIPv4 and IPv6 details to map them by geographical location and construct a sender heatmap.  


Additional things to keep in mind: 

  • Make sure that you are using the correct credentials and the correct base API URL ( as part of the configuration.  
  • Make sure you are also aware of the API limitation in regard to the number of results and calls that can retrieved. 
  • Make sure you are fine tuning your queries and customizing your query to get the desired output. 

This sample Power BI is a powerful showcase for how you can use the Microsoft 365 Defender APIs and Power BI to visualize email security detection insights. It enables organizations to easily create customized dashboards that can help them analyze their threat landscape and respond quickly, based on the unique requirements.


Do you have questions or feedback about Microsoft Defender for Office 365? Engage with the community and Microsoft experts in the Defender for Office 365 forum. 


More information

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.