Dec 22 2022 06:50 AM
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
Dec 22 2022 07:56 AM
SolutionJan 03 2023 06:30 AM
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
Jan 03 2023 07:15 AM
Jan 03 2023 07:40 AM
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
Jan 03 2023 11:49 PM
Jan 04 2023 01:41 AM
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.
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:
Am I missing something obvious here?
Thanks
Jan 04 2023 09:02 AM
Jan 05 2023 03:21 AM
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
Jan 05 2023 08:04 AM
Jan 05 2023 09:40 AM
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.
Dec 22 2022 07:56 AM
Solution