Graph API Teams User Activity Reporting

Creating a CSV file of Teams User activity

For tenancies with thousands of users, extracting usage data from Office 365 is no mean feat. This process will allow you to extract a CSV file that contains the usage data for thousands of users.

Make a note of the application ID:

App ID: 849232c6-f6dc-450e-892a-149982db63a6

Create a certificate to authenticate the app when it accesses user data in Azure:

You will need an Azure App Registration with the correct permission assigned:

Read All User Activity Data

# Your tenant name (can something more descriptive as well)

$TenantName        = "<tenancy_name>"

# Where to export the certificate without the private key

$CerOutputPath     = "C:\Temp\PowerShellGraphCert.cer"

# What cert store you want it to be in

$StoreLocation = "Cert:\CurrentUser\My"

# Expiration date of the new certificate$ExpirationDate    = (Get-Date).AddYears(2)

# Splat for readability

$CreateCertificateSplat = @{    

    FriendlyName      = "AzureApp"

    DnsName           = $TenantName

    CertStoreLocation = $StoreLocation

    NotAfter          = $ExpirationDate    

    KeyExportPolicy   = "Exportable"    

    KeySpec           = "Signature"    

    Provider          = "Microsoft Enhanced RSA and AES Cryptographic Provider"

    HashAlgorithm     = "SHA256"

                                }

# Create certificate$Certificate = New-SelfSignedCertificate @CreateCertificateSplat

# Get certificate path$CertificatePath = Join-Path -Path $StoreLocation -ChildPath $Certificate.Thumbprint

# Export certificate without private key

Export-Certificate -Cert $CertificatePath -FilePath $CerOutputPath | Out-Null

Upload the cert to Azure 

Ensure API permissions are granted in the app
Create an access token in Powershell

$TenantName = "<your tenant name>.onmicrosoft.com"
$AppId = "<your application id"
$Certificate = Get-Item Cert:\CurrentUser\My\<self signed and uploaded cert thumbprint>
$Scope = "https://graph.microsoft.com/.default"

# Create base64 hash of certificate
$CertificateBase64Hash = [System.Convert]::ToBase64String($Certificate.GetCertHash())

# Create JWT timestamp for expiration
$StartDate = (Get-Date "1970-01-01T00:00:00Z" ).ToUniversalTime()
$JWTExpirationTimeSpan = (New-TimeSpan -Start $StartDate -End (Get-Date).ToUniversalTime().AddMinutes(2)).TotalSeconds
$JWTExpiration = [math]::Round($JWTExpirationTimeSpan,0)

# Create JWT validity start timestamp
$NotBeforeExpirationTimeSpan = (New-TimeSpan -Start $StartDate -End ((Get-Date).ToUniversalTime())).TotalSeconds
$NotBefore = [math]::Round($NotBeforeExpirationTimeSpan,0)

# Create JWT header
$JWTHeader = @{
    alg = "RS256"
    typ = "JWT"
    # Use the CertificateBase64Hash and replace/strip to match web encoding of base64
    x5t = $CertificateBase64Hash -replace '\+','-' -replace '/','_' -replace '='
}

# Create JWT payload
$JWTPayLoad = @{
    # What endpoint is allowed to use this JWT
    aud = "https://login.microsoftonline.com/$TenantName/oauth2/token"

    # Expiration timestamp
    exp = $JWTExpiration

    # Issuer = your application
    iss = $AppId

    # JWT ID: random guid
    jti = [guid]::NewGuid()

    # Not to be used before
    nbf = $NotBefore

    # JWT Subject
    sub = $AppId
}

# Convert header and payload to base64
$JWTHeaderToByte = [System.Text.Encoding]::UTF8.GetBytes(($JWTHeader | ConvertTo-Json))
$EncodedHeader = [System.Convert]::ToBase64String($JWTHeaderToByte)

$JWTPayLoadToByte =  [System.Text.Encoding]::UTF8.GetBytes(($JWTPayload | ConvertTo-Json))
$EncodedPayload = [System.Convert]::ToBase64String($JWTPayLoadToByte)

# Join header and Payload with "." to create a valid (unsigned) JWT
$JWT = $EncodedHeader + "." + $EncodedPayload

# Get the private key object of your certificate
$PrivateKey = $Certificate.PrivateKey

# Define RSA signature and hashing algorithm
$RSAPadding = [Security.Cryptography.RSASignaturePadding]::Pkcs1
$HashAlgorithm = [Security.Cryptography.HashAlgorithmName]::SHA256

# Create a signature of the JWT
$Signature = [Convert]::ToBase64String(
    $PrivateKey.SignData([System.Text.Encoding]::UTF8.GetBytes($JWT),$HashAlgorithm,$RSAPadding)
) -replace '\+','-' -replace '/','_' -replace '='

# Join the signature to the JWT with "."
$JWT = $JWT + "." + $Signature

# Create a hash with body parameters
$Body = @{
    client_id = $AppId
    client_assertion = $JWT
    client_assertion_type = "urn:ietf:params:oauth:client-assertion-type:jwt-bearer"
    scope = $Scope
    grant_type = "client_credentials"

}

$Url = "https://login.microsoftonline.com/$TenantName/oauth2/v2.0/token"

# Use the self-generated JWT as Authorization
$Header = @{
    Authorization = "Bearer $JWT"
}

# Splat the parameters for Invoke-Restmethod for cleaner code
$PostSplat = @{
    ContentType = 'application/x-www-form-urlencoded'
    Method = 'POST'
    Body = $Body
    Uri = $Url
    Headers = $Header
}

$Request = Invoke-RestMethod @PostSplat

Reference: https://adamtheautomator.com/microsoft-graph-api-powershell/

Search Unified Audit Log

Audit Stream Activities

When required to audit the creation of Teams meetings or live events videos, the activity of such events is logged to the Office 365 audit logs aka unified audit logs.

The following Powershell command, reliant upon the Exchange Online Powershell module, will search the audit logs over the last 90 days and output the relevant entries from the audit log:

ipmo exchangeonlinemanagement

connect-exchangeonline

$Logs = Search-UnifiedAuditLog -RecordType MicrosoftStream  -StartDate ((Get-date).AddDays(-90)) -EndDate (get-date) -Resultsize 5000

Sway reporting in Powershell

Measure Sway usage in Office 365

This procedure will allow you to extract the number of users who have accessed Sway within Office 365 in the last thirty days. Perfect for keeping the Business Transformation manager  happy.

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri '
https://ps.protection.outlook.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession -Session $Session -AllowClobber
$logs = Search-UnifiedAuditLog -RecordType Sway -StartDate ((get-date).AddDays(-30)) -EndDate (get-date) -ResultSize 5000 $logs.userids|sort -Unique|measure

Investigate License errors in Office 365

Investigate License errors in Office 365

An important part of any administrators job is the routine investigation of any errors encountered in the Office 365 environment. This short article will describe how to identify if there are any licensing errors in your Office 365 tenancy.

This assumes you already use dynamic groups for assigning Office 365 product licenses as per this Microsoft webpage: 

https://docs.microsoft.com/en-us/azure/active-directory/fundamentals/active-directory-licensing-whatis-azure-portal

Report on all users who have a license error attached to their MSOL identity

1. Install the MSOnline powershell module

install-module MSOnline

2. Connect to MSOnline powershell

connect-msservice

3. Assign all users to a variable 

$allusers = get-msoluser -all

4. Filter all users with a licensing error into a separate variable

$errorusers = $allusers|where{$_.IndirectLicenseErrors -ne $null}

This will give you a list of all accounts that have a license error attached to them and each instance will require investigation.

Identify suspended Office 365 licensed users

Identify suspended 365 licensed users

This command will identify if there are any users with suspended Office 365 licenses in your tenancy.
There is a requirement of the MSOnline powershell commandlets to be able to access this information.

Get-MsolSubscription|where{$_.status -eq "Suspended"}|fl skupartnumber,ObjectId

Report on Sway usage in Office 365

Report on Sway usage in Office 365

When asked to create a report of Sway usage for a group of users within Office 365, there is no dedicated Sway admin portal so we need some Powershell commands and some audit report data to achieve this.

1. Access https://protection.office.com
2. Sign in using your work or school account.
3. In the left pane of the Security & Compliance Center, click Search, and then click Audit log search.
   The Audit log search page is displayed.
4. Configure your search criteria as 'Viewed Sway'
5. Enter the start and finish dates of your search window
6. Click Search to begin the audit log search process
7. When the search is completed, the results will be displayed in the browser. We need to export these to CSV by clicking the Export Results drop down list.
8. Open the CSV file in Excel and highlight the data in the users column and copy this to the clipboard.
9. Open Powershell ISE and issue the following commands:

$users = get-clipboard

$users|where{$_ -like "user@domain.gov.uk"}|sort -unique

This will return a list of users from a particular domain who have accessed Sway