Forum Discussion
Solved
Exchange Online email counting report
Hi, I'm trying to work on some monthly statistics for our company, where I could set a schedule task with powershell to get this data monthly. First of all, I would like to extract the total number ...
- There's a built-in report that gives you this data: https://admin.microsoft.com/#/reportsUsage/EmailActivity
If you want to access it programmatically, use the Graph API: https://learn.microsoft.com/en-us/graph/api/reportroot-getemailactivityuserdetail?view=graph-rest-1.0
Hi,
Thanks. After some issues I was able to get the script running, and now I can get the resulting csv file, with this code:
$EmailActivityUrl = "https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D30')"
$EmailActivityResponse = Invoke-WebRequest -Method Get -Uri $EmailActivityUrl -Headers $headers -ErrorAction Stop
$EmailActivityData = $EmailActivityResponse.BaseResponse.ResponseUri.AbsoluteUri
Invoke-WebRequest -Uri $EmailActivityData -OutFile "C:\Temp\$(get-date -f dd-MM-yyyy)-Microsoft-365-Get-All-Emails.csv"
To automate it, now I need to parse the CSV and count the values in a specific column.
Any quick way to do it?
Thanks
Get the JSON output instead of CSV:
https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D30')?$format=application/json
https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D30')?$format=application/json