Creating e-mail security dashboard

[Anonymous]

Hi everyone

I am trying to create a dashboard to show information regarding e-mail activity (e.g. number of e-mails received/sent, malware/spam e-mails blocked etc).  We currently do this using an Excel export from Office 365 (which is manually put together) and it looks as below:

This works fine but we now want to do this in a more automated fashion and I have been experimenting with connecting directly to Office 365 using the API, using these instructions.  This gives me audit log information though and doesn't seem to give me the information about e-mail that I need.

Has anyone else tried to do this or similar, and if so, which API did you use to get the correct data?  I'd be grateful for any pointers as at the moment I'm struggling to find the best way to get this information.

Thanks in advance for any help.

[andrewsommer]

The built in connector for Microsoft Exchange is pretty limited IMO. 

The best way to go about this is using the Microsoft Graph API. 

1. Register an Azure AD App

• Go to Azure Portal > "App Registrations" > "New Registration".
• Grant necessary permissions (Mail.Read, Calendars.Read, Contacts.Read).
• Generate a client secret.

1. Use Power Query (M) or Power Automate to Pull Data

• Call the Microsoft Graph API (https://graph.microsoft.com/v1.0/me/messages for emails).
• Parse JSON responses in Power BI.

Please mark this post as solution if it helps you. Appreciate Kudos.

[v-chuncz-msft]

@Anonymous,

You may try the built-in connector Microsoft Exchange.

[Anonymous]

@v-chuncz-msft

Thank you for your response.  I've just tried the built-in Exchange and Exchange Online connectors but it looks like I can only connect to a single mailbox?

I need to be able to connect to our Exchange environment as a whole as we want to report on oragnisation-wide e-mail numbers, malware etc.

[Anonymous]

Just checking if anyone knows if that I want to achieve is possible? I seem to have come up against a brick wall here and can't find a way of getting e-mail security information out of Office 365 to automatically feed into Power BI.

[jimbob1302]

Did you ever progress with this? It's the exact same thing I am trying to acheive... thanks