This script is used to create M265 License Usage Analysis reports

M365LicenseUsageAnalysis.ps1

<#
Author: Neeraj Kumar
This script is used to create M265 License Usage Analysis reports. Please create required variables under shared resources.
Also register an app within Microsoft Entra Id with API permissions for Microsoft Graph usage.
CreatedDate : 24-Nov-2023 
#>


$global:tenantId = Get-AutomationVariable -Name 'TenantId'
$global:clientId = Get-AutomationVariable -Name 'ClientId'
$global:clientSecret = Get-AutomationVariable -Name 'ClientSecret'
$global:certThumbprint = Get-AutomationVariable -Name 'CERT_THUMBPRINT'
$global:spSiteUrl = Get-AutomationVariable -Name 'SharePointSiteUrl'
$global:spSiteName = Get-AutomationVariable -Name 'SharePointSiteName'
#$global:spLibraryName = Get-AutomationVariable -Name 'OneDriveDocumentLibraryName'
$global:spLibraryName ="Shared Documents/Reports"
$global:accessToken =$null
$global:DestinationURL ="$spSiteUrl/sites/$spSiteName/$spLibraryName"
$global:csvPriceList

  <# Connect to Graph API and get access tokens  #>
Function ConnectToGraph()
{
   
    try
    {
        Connect-MgGraph -ClientId $global:clientId -TenantID $global:TenantId -CertificateThumbprint $global:certThumbprint  -Nowelcome
        $graphtokenBody = @{
            Grant_Type    = "client_credentials"
            Client_Id     = $clientId
            Client_Secret = $clientSecret
            Scope         = "https://graph.microsoft.com/.default"
        }

        $jsonBody = $graphtokenBody #| ConvertTo-Json
        $oauth = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Body $jsonBody 
        $global:accessToken = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"}
            
   }
    catch {
            Write-Error $Error[0]
            Write-Output "Error in connecting to Microsoft Graph"
            $Error=$null
          }
}
<# 2. Connect to SharePoint Online #>

function ConnectToSharePoint
{
    $SiteUrl = "$global:spSiteUrl/sites/$global:spSiteName"
    Write-Output $SiteUrl
    try 
    {
        $SiteMemberCredential = Get-AutomationPSCredential -Name "SharePoint"
        Connect-PnPOnline -Url $SiteUrl -Credentials $SiteMemberCredential 
    }   
    catch{Write-Host "Unable to connect to SharePoint Online.."
            Write-Error $Error[0]
            $Error=$null
    }
}
Function GetServicePlans
{
    param ( $CSVFileName)
    $filePath=$null
    Write-Output "Get Service Plans"
    $serviceplans=$null
    $serviceplans=Get-MgSubscribedSku
    Write-Output $serviceplans.Count
    $ServicePlanReport = [System.Collections.Generic.List[Object]]::new()
    $subscribedUsers = [System.Collections.Generic.List[Object]]::new()
   
    for (($i=0);$i -lt $serviceplans.Count;$i++)
    {
       
      
      $obj = [pscustomObject][ordered] @{
      AccountName = $serviceplans[$i].AccountName
      SkuId=$serviceplans[$i].SkuId
      SkuPartNumber =$serviceplans[$i].SkuPartNumber       
      Enabled=$serviceplans[$i].PrepaidUnits.Enabled
      Consumed=$serviceplans[$i].Consumedunits
      Status=$serviceplans[$i].CapabilityStatus
      Warning =$serviceplans[$i].PrepaidUnits.Warning
      LockedOut =$serviceplans[$i].PrepaidUnits.LockedOut
      Suspended =$serviceplans[$i].PrepaidUnits.Suspended
      Services=$serviceplans[$i].ServicePlans.Count
      plans=$serviceplans[$i].ServicePlans.ServicePlanName -join ","               
     } #obj
     #Fetch users for this SKU
     Write-Output "Fetching users for SKU:"$serviceplans[$i].SkuId
     $RequiredProperties=@('Id','DisplayName','UserPrincipalName','CreatedDateTime','AccountEnabled','UsageLocation','Department','JobTitle')
     $users= Get-MgUser -Filter "assignedLicenses/any(x:x/skuId eq $($serviceplans[$i].SkuId))" -ConsistencyLevel Eventual -CountVariable skuusers -All -Property $RequiredProperties | select $RequiredProperties| Sort-Object DisplayName
        foreach ($user in $users)
        {
            $UPN=$user.UserPrincipalName
            $AccountEnabled =$user.AccountEnabled
            $rownum++
          
            
            if ($UPN -notmatch '#EXT#')
            {
            $Type = "Member"
            }
            else
            {
            $Type="External User"
            }
            
                     
            $userObj = [pscustomObject][ordered] @{
            No=$rownum
            Id =$user.id
            DisplayName = $user.DisplayName
            UserPrincipalName =$user.UserPrincipalName
            UserType=$Type
            UsageLocation=$user.UsageLocation
            Dept=$user.Department
            JobTitle =$user.JobTitle
            SkuId =$serviceplans[$i].SkuId
            SkuPartNumber =$serviceplans[$i].SkuPartNumber 
            CreatedDate=$user.CreatedDateTime.ToString("dd-MM-yyyy hh:mm:ss")
            
            }   
            $subscribedUsers.Add($userObj) 
   }
    $ServicePlanReport.Add($obj)

    } #end For
   
     $filePath =$env:Temp
    
     try {
   		    $ServicePlanReport | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = $CSVFileName}
    		# Add the file to the Reports folder
      	 		 WritetoSharePoint($CSVFileName)
            Write-Output "Writing User Licenses"
             $CSVFileName ="SubscribedUsers.csv"
             $subscribedUsers | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = $CSVFileName}
    		# Add the file to the Reports folder
      	 		 WritetoSharePoint($CSVFileName)
    	
    	}#try	
	catch {  Write-Error $Error[0]
            Write-Output "Error in generating report"
            $Error=$null
            } 
} #function
Function getUsersbyServicePlan 
{
param (
        [String[]] $Param
         
       )
$licenses=$null
$users=$null
$SkuPartNumber =$Param[0]

$CSVFileName = "4."+$SkuPartNumber + ".csv"
$report = [System.Collections.Generic.List[Object]]::new()
$rownum =0

$licenses=Get-MgSubscribedSku -All | Where SkuPartNumber -EQ $SkuPartNumber
if ($licenses -ne $null)
{
$RequiredProperties=@('Id','DisplayName','UserPrincipalName','CreatedDateTime','AccountEnabled','UsageLocation','Department','JobTitle','SigninActivity')
$users= Get-MgUser -Filter "assignedLicenses/any(x:x/skuId eq $($licenses.SkuId))" -ConsistencyLevel Eventual -CountVariable skuusers -All -Property $RequiredProperties | select $RequiredProperties| Sort-Object DisplayName
foreach ($user in $users)
{
    $UPN=$user.UserPrincipalName
    $AccountEnabled =$user.AccountEnabled
    $rownum++
    $logindate =$user.SignInActivity.LastSignInDateTime
    if ($logindate -eq $null) 
     {
         $LastInteractiveSignIn = $null
         $InactiveDays_InteractiveSignIn =$null
     }
     else
     {
      
      $InactiveDays_InteractiveSignIn = (New-TimeSpan -Start $logindate).Days
      $LastInteractiveSignIn =$logindate.ToString("dd-MM-yyyy hh:mm:ss")
     }
    
      $logindate =$user.SignInActivity.LastNonInteractiveSignInDateTime
       if ($logindate -eq $null)
        {
                 $LastNonInteractiveSignIn = $null
                 $InactiveDays_NonInteractiveSignIn = $null
        }
     else
      {
                $LastNonInteractiveSignIn =$logindate.ToString("dd-MM-yyyy hh:mm:ss")
                $InactiveDays_NonInteractiveSignIn = (New-TimeSpan -Start $logindate).Days
       }
     if ($UPN -notmatch '#EXT#')
     {
       $Type = "Member"
     }
     else
     {
     $Type="External User"
     }
    
     if($AccountEnabled -eq $true)
    {
        $AccountStatus='Enabled'
    }
    else
    {
        $AccountStatus='Disabled'
    }
    
     $obj = [pscustomObject][ordered] @{
     No=$rownum
     Id =$user.id
     DisplayName = $user.DisplayName
     UserPrincipalName =$user.UserPrincipalName
     UserType=$Type
     AccountEnabled =$AccountStatus
     UsageLocation=$user.UsageLocation
     Dept=$user.Department
     JobTitle =$user.JobTitle
     Licenses=$licenses.Count  
     LicensePlan=$licenses.SkuPartNumber -join ","
     CreatedDate=$user.CreatedDateTime.ToString("dd-MM-yyyy hh:mm:ss")
     LastInteractiveSignIn=$LastInteractiveSignIn  
     LastNonInteractiveSignIn= $LastNonInteractiveSignIn
     InactiveDays=$InactiveDays_InteractiveSignIn
     InactiveDays_NoInteractiveSignIn =$InactiveDays_NonInteractiveSignIn
    }   
    $report.Add($obj) 
   }
   
     try {
         $filePath = $env:Temp
   		    $report | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = $CSVFileName}
    		# Add the file to the Reports folder
    		
   		   WritetoSharePoint($CSVFileName)
    		
    	}#try	
	catch { 
         Write-Error $Error[0]
             $Error=$null
        Write-Output "Error creating report"
        } 

    $report = $null
   }
   else
   {
     $obj = [pscustomObject][ordered] @{
     No=$null
     Id =$null
     DisplayName = $null
     UserPrincipalName =$null
     UserType=$null
     AccountEnabled =$null
     UsageLocation=$null
     Dept=$null
     JobTitle =$null
     Licenses=0  
     LicensePlan=$null
     CreatedDate=$null
     LastInteractiveSignIn=$null 
     LastNonInteractiveSignIn= $null
     InactiveDays=$null
     InactiveDays_NoInteractiveSignIn =$null
    }   
    $report.Add($obj) 
     try {
         $filePath = $env:Temp
   		$report | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = $CSVFileName}
    		# Add the file to the Reports folder
    	
   		    WritetoSharePoint($CSVFileName)
    	  		
    	}#try	
	catch { 
         Write-Error $Error[0]
             $Error=$null
        Write-Output "Error creating report"
        } 
    Write-Output "No users found with $($SkuPartNumber) licenses"
    }

}

<# 3. Execute Graph API Reports for Exchange #>
function ExecuteReportAPI
{ 
    param([String[]] $Param )
    $CSVFileName = $Param[0]
    $graphApiUri = $Param[1]
      
    $filePath = $env:Temp
try{
                
        Write-Output "Fetching report output by executing Graph API"
      
         $Reports = Invoke-RestMethod -Method Get -Uri $graphApiUri -Headers $global:accessToken | ConvertFrom-Csv
         $Reports | Export-Csv -Path $filePath\$CSVFileName -NoTypeInformation -Append
         WritetoSharePoint($CSVFileName)
       
    }
    catch 
    { Write-Output "Exception while fetching Usage report"
      Write-Error $Error[0]
      $Error = $null
    }

}
# Output results to SharePoint folder
function WritetoSharePoint{
   param( $CSVFileName )
     # Export to CSV locally
    $filePath = $env:Temp
   Write-Output $global:DestinationURL
    # Upload to SharePoint
    $FolderObject = Get-PnPFolder -Url $global:DestinationURL
    $Upload= Add-PnPFile -Path $filePath\$CSVFileName  -Folder $FolderObject
    
    If ($Upload -ne $null)
    {
         Write-Output $CSVFileName " Report sucessfully uploaded"
    }
    # Clean up local file
   Remove-Item -Path $filePath\$CSVFileName -Force
}
function getPriceList 
{
    $filePath =$env:Temp
    $FolderObject = Get-PnPFolder -Url $global:DestinationURL
    $FileRelativeURL =  "/sites/CloudOpsAccelerators/Shared Documents/Reports/M365 License Pricebook.xlsx"
    Write-Output $FileRelativeURL
    Write-Output "Get PnPFile"
    $File = Get-PnPFile -Url $FileRelativeURL 
    If($File -ne $null)
    {
        Write-Output "Downloading file from SharePoint Online: "  + $FileRelativeURL
     
        #Download file from sharepoint online
        try
        {  
            $filename =$File.Name
            Get-PnPFile -Url $FileRelativeURL -AsFile -Path $filePath -Filename $File.Name  -Force
             Write-output "Importing file:"  $filePath\$File.Name
            $global:csvPriceList = Import-CSV -Path $filePath\$filename
            Write-Output "Price List imported"            
        }
        catch
        {
            Write-Error $Error[0]
            $Error[0]=$null
        }
       
    }
    Else
    {
        Write-output "Could not Find File at "$FileRelativeURL
    }
}
function Get180DaysActivity
{
 ExecuteReportAPI("1.OneDriveActivityUserDetail_180.csv","https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(period='D180')")
 ExecuteReportAPI("1.TeamsActivityUserDetail_30.csv","https://graph.microsoft.com/v1.0/reports/getTeamsUserActivityUserDetail(period='D180')")
 ExecuteReportAPI("1.M365AppsUsageDetail_180.csv","https://graph.microsoft.com/v1.0/reports/getM365AppUserDetail(period='D180')")
 ExecuteReportAPI("1.OutlookActivityUserDetail_180.csv","https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D180')")
 ExecuteReportAPI("1.YammerActivityUserDetail_180.csv","https://graph.microsoft.com/v1.0/reports/getYammerActivityUserDetail(period='D180')")
 ExecuteReportAPI("1.SharePointActivityUserDetail_180.csv","https://graph.microsoft.com/v1.0/reports/getSharePointActivityUserDetail(period='D180')")

}

function GetDailyActivity
{ 
    param([String[]] $Param )
    $CSVFileName = $Param[0]
    $graphApiUri = $Param[1]

    $filePath = $env:Temp
  
    $var = 1
    $Reports = [System.Collections.Generic.List[Object]]::new()
 $date = Get-Date  
 $datecalc= $date
  $date = $date.ToString("yyyy-MM-dd") 
     
 for ($var = 1; $var -le 31; $var++)
    {      
        Write-Output "Execute Daily Activity Report for: " $date
        #ExecuteReportAPI("1.OneDriveActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(date=$date)")
        $CSVFileName ="1.OneDriveActivityUserDetail_"+ $date +".csv"
        Write-Output $CSVFileName
        $Reports = Invoke-RestMethod -Method Get -Uri $graphApiUri -Headers $global:accessToken | ConvertFrom-Csv
            
         $datecalc = $datecalc.AddDays(-1)
         $date = $datecalc.ToString("yyyy-MM-dd") 
         Write-Output "Exporting CSV File"
         $Reports | Export-Csv -Path $filePath\$CSVFileName -NoTypeInformation
         WritetoSharePoint($CSVFileName)
         $graphApiUri ="https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(date=$date)"
    }   
     
    #ExecuteReportAPI("1.TeamsActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getTeamsUserActivityUserDetail(date=$date)")
    #ExecuteReportAPI("1.M365AppsUsageDetail.csv","https://graph.microsoft.com/v1.0/reports/getM365AppUserDetail(date=$date)")
    #ExecuteReportAPI("1.OutlookActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(date=$date)")
    #ExecuteReportAPI("1.YammerActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getYammerActivityUserDetail(date=$date)")
    #ExecuteReportAPI("1.SharePointActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getSharePointActivityUserDetail(date=$date)")   
 
}
<#3. Fetch all users who have been assigned at least one license with their Last activity details using Get-MgBetaUser #>
Function GetLicensedUsers
{
    param ( $CSVFileName)

    $RequiredProperties=@('Id','DisplayName','UserPrincipalName','CreatedDateTime','AccountEnabled','UsageLocation','Department','JobTitle','RefreshTokensValidFromDateTime','SigninActivity')

    $users=Get-MgBetaUser -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel Eventual -CountVariable usercount -All -Property $RequiredProperties | select $RequiredProperties| Sort-Object DisplayName
    $report = [System.Collections.Generic.List[Object]]::new()
    $rownum =0

    foreach ($user in $users)
    {
    Write-Host "Retrieving license information for $($user.DisplayName)"
    $licenses = Get-MgUserLicenseDetail -UserId $user.id
    $UPN=$user.UserPrincipalName
    $AccountEnabled =$user.AccountEnabled
   $rownum++
   if ($licenses.Count -ne 0) 
   { 
     $productplan= $licenses.SkuPartNumber -join ","
     if ($productplan -match "SPE_F1" )
     {
         $F1 = "Y"
     }
     else
     {
         $F1="N"
     }
     if ($productplan -match "SPE_F3" )
     {
         $F3 = "Y"
     }
     else
     {
         $F3="N"
     }
     if ($productplan -match "SPE_E3" )
     {
         $E3 = "Y"
     }
     else
     {
         $E3="N"
     }
      if ($productplan -match "SPE_E5" )
     {
         $E5 = "Y"
     }
     else
     {
         $E5="N"
     }
     if ($productplan -match "PBI_PREMIUM_PER_USER" )
     {
         $PowerBIPrem = "Y"
     }
     else
     {
         $PowerBIPrem="N"
     }
     if (($E5 -eq "Y") -or ($E5 -eq "Y") )
     {
         if ( $licenses.Count -gt 1 )
          {
              $AddOn ="Y"
          }
          else
          {
              $AddOn="N"
          }
         
     }
    
     $logindate =$user.SignInActivity.LastSignInDateTime
     if ($logindate -eq $null) 
     {
         $LastInteractiveSignIn = $null
         $InactiveDays_InteractiveSignIn =$null
     }
     else
     {
      
      $InactiveDays_InteractiveSignIn = (New-TimeSpan -Start $logindate).Days
      $LastInteractiveSignIn =$logindate.ToString("dd-MM-yyyy hh:mm:ss")
     }
    
      $logindate =$user.SignInActivity.LastNonInteractiveSignInDateTime
       if ($logindate -eq $null)
        {
                 $LastNonInteractiveSignIn = $null
                 $InactiveDays_NonInteractiveSignIn = $null
        }
     else
      {
                $LastNonInteractiveSignIn =$logindate.ToString("dd-MM-yyyy hh:mm:ss")
                $InactiveDays_NonInteractiveSignIn = (New-TimeSpan -Start $logindate).Days
       }
     if ($UPN -notmatch '#EXT#')
     {
       $Type = "Member"
     }
     else
     {
     $Type="External User"
     }
    
     if($AccountEnabled -eq $true)
    {
        $AccountStatus='Enabled'
    }
    else
    {
        $AccountStatus='Disabled'
    }
 
     
      $obj = [pscustomObject][ordered] @{
     No=$rownum
     Id =$user.id
     DisplayName = $user.DisplayName
     UserPrincipalName =$user.UserPrincipalName
     UserType=$Type
     AccountEnabled =$AccountStatus
     UsageLocation=$user.UsageLocation
     Dept=$user.Department
     JobTitle =$user.JobTitle
     Licenses=$licenses.Count  
     LicensePlan=$licenses.SkuPartNumber -join ","
     CreatedDate=$user.CreatedDateTime.ToString("dd-MM-yyyy hh:mm:ss")
     LastInteractiveSignIn=$LastInteractiveSignIn  
     LastNonInteractiveSignIn= $LastNonInteractiveSignIn
     InactiveDays=$InactiveDays_InteractiveSignIn
     InactiveDays_NoInteractiveSignIn =$InactiveDays_NonInteractiveSignIn
     RefreshTokenValidFrom=$user.RefreshTokensValidFromDateTime
     F1=$F1
     F3=$F3
     E3=$E3
     E5=$E5
     PowerBiPremium=$PowerBIPrem
     AddOns=$AddOn
    } #obj  
    $report.Add($obj) 
   }#if
    }#for
 try
 {
   $filePath = $env:Temp
   $report | Export-Csv -Path $filePath\$CSVFileName -NoTypeInformation
   $Values = @{"Title" = $CSVFileName}
    # Add the file to the Reports folder
 
    WritetoSharePoint($CSVFileName)
    
  } #try

  catch {   Write-Error $Error[0]
             $Error=$null
              Write-Output "Error creating report"}
  $report = $null
} #function


function main
{
 ConnectToGraph
 ConnectToSharePoint
 getPriceList
 $Results = 	$global:csvPriceList | Select "Price per User" where { $_.SkuPartNumber -eq "SPE_E3" }
 Write-Output "Prices = " $Results

 #GetServicePlans("ServicePlans.csv")
 #GetLicensedUsers("LicensedUsers.csv")
 #Get180DaysActivity
 $date = Get-Date  
 $date =$date.AddDays(-2)
 $date = $date.ToString("yyyy-MM-dd") 
    
 #GetDailyActivity("1.OneDriveActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(date=$date)")
 #ExecuteReportAPI("1.OneDriveActivityUserDetail.csv","https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(date=$date)")
 
}
main