Forum Discussion
Exchange Online email counting report
- 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
https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D30')?$format=application/json
I've run and got the JSON with this command:
$EmailActivityUrl = "https://graph.microsoft.com/v1.0/reports/getEmailActivityCounts(period='D30')?$format=application/json" $EmailActivityResponse = Invoke-WebRequest -Method Get -Uri $EmailActivityUrl -Headers $headers -ErrorAction Stop $EmailActivityResponse.RawContentWhen I look at the rawcontent I've got this:
???Report Refresh Date,Send,Receive,Read,Meeting Created,Meeting Interacted,Report Date,Report Period
2023-01-01,571,7449,12017,16,12,2023-01-01,30
2023-01-01,627,11372,4534,1,5,2022-12-31,30
2023-01-01,14941,70869,61941,143,266,2022-12-30,30
2023-01-01,22415,92833,88179,232,363,2022-12-29,30
2023-01-01,23598,92497,95913,296,424,2022-12-28,30
2023-01-01,23405,93177,99517,222,381,2022-12-27,30
2023-01-01,15204,69919,77528,132,255,2022-12-26,30
2023-01-01,314,6540,2537,3,8,2022-12-25,30
2023-01-01,464,9006,2888,2,12,2022-12-24,30
2023-01-01,15662,75377,66199,129,301,2022-12-23,30
2023-01-01,22882,93571,103051,331,808,2022-12-22,30
2023-01-01,25760,105291,120548,381,900,2022-12-21,30
2023-01-01,28259,121649,116950,381,1403,2022-12-20,30
2023-01-01,28443,118293,127388,351,850,2022-12-19,30
2023-01-01,866,11273,5826,30,51,2022-12-18,30
2023-01-01,896,10058,8320,9,20,2022-12-17,30
2023-01-01,26360,109431,110962,375,651,2022-12-16,30
2023-01-01,28692,113545,124791,368,728,2022-12-15,30
2023-01-01,28539,112163,120704,419,895,2022-12-14,30
2023-01-01,25249,103345,109477,403,874,2022-12-13,30
2023-01-01,29526,122038,144469,465,1068,2022-12-12,30
2023-01-01,845,9340,6146,18,51,2022-12-11,30
2023-01-01,779,10056,4401,13,38,2022-12-10,30
2023-01-01,23919,100824,96651,279,534,2022-12-09,30
2023-01-01,3554,26349,21331,59,122,2022-12-08,30
2023-01-01,28999,128720,127487,370,813,2022-12-07,30
2023-01-01,26428,105094,111070,361,745,2022-12-06,30
2023-01-01,30450,126926,150896,457,892,2022-12-05,30
2023-01-01,952,8883,8044,22,38,2022-12-04,30
2023-01-01,968,10464,4816,21,28,2022-12-03,30So now I'm on the same place, how can I count the contents?
Thanks- That's still CSV format, not the JSON. Use the /beta endpoint to get the JSON.
I did that, but something is not lining up correctly. I've changed the endpoint to /beta/ and the query is like this:
$EmailActivityUrl = "https://graph.microsoft.com/beta/reports/getEmailActivityCounts(period='D30')?$format=application/json" $EmailActivityResponse = Invoke-WebRequest -Method Get -Uri $EmailActivityUrl -Headers $headers -ErrorAction StopBut still the contents are not in JSON format.
But if I got to run the same URL in the "https://developer.microsoft.com/en-us/graph/graph-explorer", the output is JSON, as you can see here:
Am I missing something obvious here?
Thanks