Forum Discussion

dmarquesgn's avatar
dmarquesgn
Iron Contributor
Dec 22, 2022
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 ...
Windows PowerShell
  • dmarquesgn's avatar
    dmarquesgn
    Iron Contributor
    Jan 03, 2023

    VasilMichev 

    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

      • dmarquesgn's avatar
        dmarquesgn
        Iron Contributor
        Jan 03, 2023

        VasilMichev 

        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

Resources