SOLVED

Exchange Online email counting report

Frequent Contributor

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 of emails (received and sent) over the last 30 days.

I've been looking at the ExchangeOnlineManagement Module, and from what I can see, I would need to use the "Start-HistoricalSearch" to be able to retrieve emails. But I don't want to create a CSV of all emails, I just want as a result the total number of emails, but I can't seem to be able to do something like.

Has anyone had the same need or can help me understand how can I reach this goal?
Thanks

10 Replies
best response confirmed by dmarquesgn (Frequent Contributor)
Solution
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....

@Vasil Michev 

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

@Vasil Michev 

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.RawContent

When 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,30

 

So 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.

@Vasil Michev 

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 Stop

But still the contents are not in JSON format.

Screenshot_1.png 

But if I got to run the same URL in the "Graph Explorer | Try Microsoft Graph APIs - Microsoft Graph", the output is JSON, as you can see here:

Screenshot_2.png

Am I missing something obvious here?

 

Thanks

Yup, $ is a special character in PowerShell, so you have to escape it (add a ` in front):

"https://graph.microsoft.com/beta/reports/getEmailActivityCounts(period='D30')?`$format=application/json"

@Vasil Michev 

Hi, thanks. This worked like a charm. Now I've got the values on the JSON format.

How can I access the values on a specific field and count them all for example?

Thanks

The counts are already included, just reference them? I.e. to get the latest values:

($EmailActivityResponse.Content | ConvertFrom-Json).value[0].receive

@Vasil Michev 

Yes, I got the idea, so created this and solved it.

$TotalEmailCount = ($EmailActivityResponse.Content | ConvertFrom-Json).value.receive
foreach ($receivedemailday in $TotalEmailCount) {
       $TotalEmails += $receivedemailday
}

Thanks for the guidance.