Tạo report user trên Ms365

Bước 1: chuẩn bị full data

Kết nối Ms365 để lấy data:

#Connect-MgGraph -Scopes "User.Read.All","Directory.Read.All","Reports.Read.All" 

Lấy report danh sách user theo GUID (mặc định) 

$mailReport = "$env:TEMP\mailUsage-guid.csv"
Get-MgReportMailboxUsageDetail -Period "D7" -OutFile $mailReport
$mailUsage = Import-Csv $mailReport

Lấy report danh sách user theo UPN: vào admin center -> settings -> Org settings -> reports -> bỏ tích Display concealed user, group, and site names in all reports

$mailReport = "$env:TEMP\mailUsage-upn.csv"
Get-MgReportMailboxUsageDetail -Period "D7" -OutFile $mailReport
$mailUsage = Import-Csv $mailReport 

Trả lại setting report như cũ 

Chạy mapping 2 file trên để lấy được danh sách tương ứng GUID với UPN/Email, lưu lại

$mapping = for ($i=0; $i -lt $mailGuid.Count; $i++) {
    [PSCustomObject]@{
        ObjectId = $mailGuid[$i].'User Principal Name'
        UPN      = $mailUpn[$i].'User Principal Name'
        Display  = $mailUpn[$i].'Display Name'
    }
}

$mapping | Export-Csv -Path "D:\OneDrive\Downloads\Documents\ReportM365\Mapping_Guid_Upn.csv" -NoTypeInformation -Encoding UTF8 

Xuất UPN + Description + homePhone từ AD on-premise, copy về máy mình

Get-ADUser -Filter * -Property UserPrincipalName,Description,homePhone |
    Select-Object UserPrincipalName,Description,homePhone |
    Export-Csv -Path "C:\Backup\AD_Report.csv" -NoTypeInformation -Encoding UTF8

Bước 2: chạy report

PowerShell admin: lưu ý path tới 2 file Mapping và AD report đã lấy ở trên:

#Connect-MgGraph -Scopes "User.Read.All","Directory.Read.All","Reports.Read.All"

$skus = Get-MgSubscribedSku | Select-Object SkuId, SkuPartNumber, SkuDisplayName

$mailReport = "$env:TEMP\mailUsage.csv"
Get-MgReportMailboxUsageDetail -Period "D7" -OutFile $mailReport
$mailUsage = Import-Csv $mailReport

$driveReport = "$env:TEMP\driveUsage.csv"
Get-MgReportOneDriveUsageAccountDetail -Period "D7" -OutFile $driveReport
$driveUsage = Import-Csv $driveReport

# Mapping file
$mapping = Import-Csv "D:\OneDrive\Downloads\Documents\ReportM365\Mapping_Guid_Upn.csv"

# AD Report file
$adReport = Import-Csv "D:\OneDrive\Downloads\Documents\ReportM365\AD_Report.csv"

$users = Get-MgUser -All -Property UserPrincipalName,DisplayName,SignInActivity,AssignedLicenses,AccountEnabled,OnPremisesDistinguishedName,JobTitle,Department

function Convert-ToGB($bytes) {
    if ([string]::IsNullOrWhiteSpace($bytes)) { return "" }
    try { 
        return [math]::Round([double]$bytes / 1GB, 2) 
    } catch { 
        return "" 
    }
}

$result = foreach ($u in $users) {

    $licenses = ($u.AssignedLicenses | ForEach-Object {
        $lic = $_
        $sku = $skus | Where-Object { $_.SkuId -eq $lic.SkuId }
        if ($sku) { "$($sku.SkuPartNumber) - $($sku.SkuDisplayName)" }
    } | Select-Object -Unique) -join '; '

    $map = $mapping | Where-Object { $_.UPN -eq $u.UserPrincipalName }

    $adInfo = $adReport | Where-Object { $_.UserPrincipalName -eq $u.UserPrincipalName }
    $description = if ($adInfo) { $adInfo.Description } else { "" }
    $homePhone   = if ($adInfo) { $adInfo.homePhone } else { "" }

    $mailStorage    = ""
    $mailQuota      = ""
    $mailLastActive = ""
    $driveStorage   = ""
    $driveAllocated = ""

    if ($map) {
        $objId = $map.ObjectId

        $mail = $mailUsage | Where-Object { $_.'User Principal Name' -eq $objId }
        if ($mail) {
            $mailStorage    = Convert-ToGB $mail.'Storage Used (Byte)'
            $mailQuota      = Convert-ToGB $mail.'Issue Warning Quota (Byte)'
            $mailLastActive = $mail.'Last Activity Date'
        }

        $drive = $driveUsage | Where-Object { $_.'Owner Principal Name' -eq $objId }
        if ($drive) {
            $driveStorage   = Convert-ToGB $drive.'Storage Used (Byte)'
            $driveAllocated = Convert-ToGB $drive.'Storage Allocated (Byte)'
        }
    }

    [PSCustomObject]@{
        AccountStatus         = if ($u.AccountEnabled) {"Enabled"} else {"Disabled"}
        UserPrincipalName     = $u.UserPrincipalName
        DisplayName           = $u.DisplayName        
        JobTitle              = $u.JobTitle
        Department            = $u.Department
        Description           = $description
        HomePhone             = $homePhone
        Licenses              = $licenses
        MailUsageGB           = $mailStorage
        MailQuotaGB           = $mailQuota
        MailLastActivity      = $mailLastActive
        OneDriveUsageGB       = $driveStorage
        OneDriveAllocatedGB   = $driveAllocated
        ADLastSignIn          = $u.SignInActivity.LastSignInDateTime
        OUPath                = $u.OnPremisesDistinguishedName
    }
}

$result | Export-Csv -Path "C:\UsersLastLogon.csv" -NoTypeInformation -Force -Encoding UTF8