The report is blank

[ashmsport]

Hi Vikas, I've updated the script to include ServicePrincipalIDs and ServicePrincipalNames based on the steps given on Techwizard but it generates a blank file. Can you tell me what am I missing? The script is saved in a Temp Folder and I manually created a Report folder in it where the blank excel file is created.

[VikasSukhija]

Are you commenting about https://techwizard.cloud/2021/05/23/office-365-license-assignment-dates/ ? Have you installed the excel module as well ? Also script has built-in functions to create the folders if you are creating them manually then something is wrong .

[ashmsport]

Yes, I'm referring to that article you mentioned. And excel module is installed but it kept throwing a file path error. So I created the folder myself.. Going to install the module again and give it a shot. Get back to you shortly.

[ashmsport]

Tried again on a different server. It creates Log folder but not the Report folder. Throws this error: |01/14/2022 13:10:36| |exception occured Could not find a part of the path 'D:\Temp\report\tempcsv3.csv'.| |Error|

[VikasSukhija]

Ok, Create a report folder manually in the same location form which script is running , lets see if that fixes then I will upload the new version.

[VikasSukhija]

I have updated the script so it creates the report folder automatically, you can check the new version.

[ashmsport]

Thanks, it's creating the folder now but I'm back to the original problem. The report file is empty. Tested in two tenants, same behavior. In Prod it takes 10 minutes to fetch all AAD user data and generates the report. But the file is still empty.

[VikasSukhija]

Share the code as something might be wrong.

[ashmsport]

<#PSScriptInfo

.VERSION 1.3

.GUID 5e9b54d7-ba22-49df-b67d-b021c9ea5727

.AUTHOR Vikas Sukhija

.COMPANYNAME techwizard.cloud

.COPYRIGHT techwizard.cloud

.TAGS

.LICENSEURI https://techwizard.cloud/2021/05/23/office-365-license-assignment-dates/

.PROJECTURI https://techwizard.cloud/2021/05/23/office-365-license-assignment-dates/

.ICONURI

.EXTERNALMODULEDEPENDENCIES AzureAD,ImportExcel 

.REQUIREDSCRIPTS

.EXTERNALSCRIPTDEPENDENCIES

.RELEASENOTES
https://techwizard.cloud/2021/05/23/office-365-license-assignment-dates/

.PRIVATEDATA

Created with:   ISE
Created on:     5/11/2021   
Filename:       LicenseAssignmentDates.ps1 
updated on:     6/14/2021 (fixed bug with export excel showing incorrect dates)
updated on:     1/17/2022 (fixed bug as it was not creating report folder)

>

Requires -Module AzureAD,ImportExcel

<#

.DESCRIPTION 
License Assignment Dates 

>

Param( $run="run") function New-FolderCreation { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string]$foldername )

$logpath = (Get-Location).path + "\" + "$foldername" $testlogpath = Test-Path -Path $logpath if($testlogpath -eq $false) {

Start-ProgressBar -Title "Creating $foldername folder" -Timer 10

$null = New-Item -Path (Get-Location).path -Name $foldername -Type directory

} } function Write-Log { [CmdletBinding()] param ( [Parameter(Mandatory = $true,ParameterSetName = 'Create')] [array]$Name, [Parameter(Mandatory = $true,ParameterSetName = 'Create')] [string]$Ext, [Parameter(Mandatory = $true,ParameterSetName = 'Create')] [string]$folder,

[Parameter(ParameterSetName = 'Create',Position = 0)][switch]$Create,

[Parameter(Mandatory = $true,ParameterSetName = 'Message')]
[String]$message,
[Parameter(Mandatory = $true,ParameterSetName = 'Message')]
[String]$path,
[Parameter(Mandatory = $false,ParameterSetName = 'Message')]
[ValidateSet('Information','Warning','Error')]
[string]$Severity = 'Information',

[Parameter(ParameterSetName = 'Message',Position = 0)][Switch]$MSG

) switch ($PsCmdlet.ParameterSetName) { "Create" { $log = @() $date1 = Get-Date -Format d $date1 = $date1.ToString().Replace("/", "-") $time = Get-Date -Format t

  $time = $time.ToString().Replace(":", "-")
  $time = $time.ToString().Replace(" ", "")
  New-FolderCreation -foldername $folder
  foreach ($n in $Name)
  {$log += (Get-Location).Path + "\" + $folder + "\" + $n + "_" + $date1 + "_" + $time + "_.$Ext"}
  return $log
}
"Message"
{
  $date = Get-Date
  $concatmessage = "|$date" + "|   |" + $message +"|  |" + "$Severity|"
  switch($Severity){
    "Information"{Write-Host -Object $concatmessage -ForegroundColor Green}
    "Warning"{Write-Host -Object $concatmessage -ForegroundColor Yellow}
    "Error"{Write-Host -Object $concatmessage -ForegroundColor Red}
  }

  Add-Content -Path $path -Value $concatmessage
}

} } #Function Write-Log ####################Load variables and log####################################### $log = Write-Log -Name "LicenseAssignmentDates-Log" -folder "logs" -Ext "log" New-FolderCreation -foldername report $report1 = (Get-Location).path + "\report\LicenseAssignmentDates-Report.xlsx" $csv = (Get-Location).path + "\report\tempcsv3.csv" ################################################################################## try{ Write-Log -Message "Start ................Script" -path $log Write-Log -Message "Connect to AZUREAD" -path $log Connect-AzureAD import-module ImportExcel } catch{ $exception = $.Exception.Message Write-Log -Message "exception $exception has occured Connecting to AzureAD" -path $log -Severity Error Exit } #######################Get all Azure AD Users################################################### Write-Log -Message "Get all AzureAD Users" -path $log try{ [System.Collections.ArrayList]$collection = @() $getallazureadusers = Get-AzureADUser -Filter "UserType eq 'Member'" -All $true Write-Log -Message "Fetched all AzureAD Users" -path $log $getallazureadusers | ForEach-Object{ $upn = $.UserPrincipalName $coll = "" | Select UserPrincipalName, EXCHANGE_S_FOUNDATION, Microsoft_Stream, DYN365_CDS_VIRAL, FLOW_P2_VIRAL, FLOW_CCI_BOTS, CCIBOTS_PRIVPREV_VIRAL, DYN365_CDS_CCI_BOTS, FORMS_PRO, FORMS_PLAN_E5, FLOW_FORMS_PRO, DYN365_CDS_FORMS_PRO, FLOW_P2_VIRAL_REAL, POWERAPPS_P2_VIRAL, VIVA_LEARNING_SEEDED, WINDOWSUPDATEFORBUSINESS_DEPLOYMENTSERVICE, UNIVERSAL_PRINT_01, MIP_S_Exchange, MICROSOFT_COMMUNICATION_COMPLIANCE, GRAPH_CONNECTORS_SEARCH_INDEX, POWER_VIRTUAL_AGENTS_O365_P3, CDS_O365_P3, PROJECT_O365_P3, MICROSOFTENDPOINTDLP, INSIDER_RISK, EXCEL_PREMIUM, MTP, DYN365_CDS_O365_P3, MICROSOFTBOOKINGS, RECORDS_MANAGEMENT, ML_CLASSIFICATION, INSIDER_RISK_MANAGEMENT, INFO_GOVERNANCE, DATA_INVESTIGATIONS, CUSTOMER_KEY, COMMUNICATIONS_DLP, COMMUNICATIONS_COMPLIANCE, SAFEDOCS, M365_ADVANCED_AUDITING, INFORMATION_BARRIERS, KAIZALA_STANDALONE, PREMIUM_ENCRYPTION, WHITEBOARD_PLAN3, MIP_S_CLP2, MIP_S_CLP1, MYANALYTICS_P2, PAM_ENTERPRISE, ATA, BPOS_S_TODO_3, FLOW_O365_P3, POWERAPPS_O365_P3, ADALLOM_S_STANDALONE, STREAM_O365_E5, Deskless, THREAT_INTELLIGENCE, TEAMS1, WINDEFATP, WIN10_PRO_ENT_SUB, RMS_S_PREMIUM2, AAD_PREMIUM_P2, RMS_S_PREMIUM, RMS_S_ENTERPRISE, MFA_PREMIUM, INTUNE_A, AAD_PREMIUM, YAMMER_ENTERPRISE, SWAY, SHAREPOINTWAC, SHAREPOINTENTERPRISE, PROJECTWORKMANAGEMENT, OFFICESUBSCRIPTION, MCOSTANDARD, MCOMEETADV, MCOEV, LOCKBOX_ENTERPRISE, EXCHANGE_S_ENTERPRISE, EXCHANGE_ANALYTICS, EQUIVIO_ANALYTICS, BI_AZURE_P2, ADALLOM_S_O365, MDE_LITE, POWER_VIRTUAL_AGENTS_O365_P2, CDS_O365_P2, PROJECT_O365_P2, DYN365_CDS_O365_P2, KAIZALA_O365_P3, WHITEBOARD_PLAN2, ADALLOM_S_DISCOVERY, BPOS_S_TODO_2, FLOW_O365_P2, POWERAPPS_O365_P2, FORMS_PLAN_E3, STREAM_O365E3 # for reporting add the names $coll.UserPrincipalName = $upn $getazureaduserServiceid = $.assignedplans $getazureaduserServiceid | where{$.CapabilityStatus -eq "Enabled"}| foreach-object{ $serviceplanid = $.ServicePlanId $AssignedTimestamp = get-date $($_.AssignedTimestamp) -Format MM/dd/yy ############updated serviceplanid here####################### switch($serviceplanid){ "113feb6c-3fe4-4440-bddc-54d774bf0318" {$coll.EXCHANGE_S_FOUNDATION = $AssignedTimestamp} "acffdce6-c30f-4dc2-81c0-372e33c515ec" {$coll.Microsoft_Stream = $AssignedTimestamp} "17ab22cd-a0b3-4536-910a-cb6eb12696c0" {$coll.DYN365_CDS_VIRAL = $AssignedTimestamp} "50e68c76-46c6-4674-81f9-75456511b170" {$coll.FLOW_P2_VIRAL = $AssignedTimestamp} "5d798708-6473-48ad-9776-3acc301c40af" {$coll.FLOW_CCI_BOTS = $AssignedTimestamp} "ce312d15-8fdf-44c0-9974-a25a177125ee" {$coll.CCIBOTS_PRIVPREV_VIRAL = $AssignedTimestamp} "cf7034ed-348f-42eb-8bbd-dddeea43ee81" {$coll.DYN365_CDS_CCI_BOTS = $AssignedTimestamp} "17efdd9f-c22c-4ad8-b48e-3b1f3ee1dc9a" {$coll.FORMS_PRO = $AssignedTimestamp} "e212cbc7-0961-4c40-9825-01117710dcb1" {$coll.FORMS_PLAN_E5 = $AssignedTimestamp} "57a0746c-87b8-4405-9397-df365a9db793" {$coll.FLOW_FORMS_PRO = $AssignedTimestamp} "363430d1-e3f7-43bc-b07b-767b6bb95e4b" {$coll.DYN365_CDS_FORMS_PRO = $AssignedTimestamp} "d20bfa21-e9ae-43fc-93c2-20783f0840c3" {$coll.FLOW_P2_VIRAL_REAL = $AssignedTimestamp} "d5368ca3-357e-4acb-9c21-8495fb025d1f" {$coll.POWERAPPS_P2_VIRAL = $AssignedTimestamp} "b76fb638-6ba6-402a-b9f9-83d28acb3d86" {$coll.VIVA_LEARNING_SEEDED = $AssignedTimestamp} "7bf960f6-2cd9-443a-8046-5dbff9558365" {$coll.WINDOWSUPDATEFORBUSINESS_DEPLOYMENTSERVICE = $AssignedTimestamp} "795f6fe0-cc4d-4773-b050-5dde4dc704c9" {$coll.UNIVERSAL_PRINT_01 = $AssignedTimestamp} "cd31b152-6326-4d1b-ae1b-997b625182e6" {$coll.MIP_S_Exchange = $AssignedTimestamp} "a413a9ff-720c-4822-98ef-2f37c2a21f4c" {$coll.MICROSOFT_COMMUNICATION_COMPLIANCE = $AssignedTimestamp} "a6520331-d7d4-4276-95f5-15c0933bc757" {$coll.GRAPH_CONNECTORS_SEARCH_INDEX = $AssignedTimestamp} "ded3d325-1bdc-453e-8432-5bac26d7a014" {$coll.POWER_VIRTUAL_AGENTS_O365_P3 = $AssignedTimestamp} "afa73018-811e-46e9-988f-f75d2b1b8430" {$coll.CDS_O365_P3 = $AssignedTimestamp} "b21a6b06-1988-436e-a07b-51ec6d9f52ad" {$coll.PROJECT_O365_P3 = $AssignedTimestamp} "64bfac92-2b17-4482-b5e5-a0304429de3e" {$coll.MICROSOFTENDPOINTDLP = $AssignedTimestamp} "d587c7a3-bda9-4f99-8776-9bcf59c84f75" {$coll.INSIDER_RISK = $AssignedTimestamp} "531ee2f8-b1cb-453b-9c21-d2180d014ca5" {$coll.EXCEL_PREMIUM = $AssignedTimestamp} "bf28f719-7844-4079-9c78-c1307898e192" {$coll.MTP = $AssignedTimestamp} "28b0fa46-c39a-4188-89e2-58e979a6b014" {$coll.DYN365_CDS_O365_P3 = $AssignedTimestamp} "199a5c09-e0ca-4e37-8f7c-b05d533e1ea2" {$coll.MICROSOFTBOOKINGS = $AssignedTimestamp} "65cc641f-cccd-4643-97e0-a17e3045e541" {$coll.RECORDS_MANAGEMENT = $AssignedTimestamp} "d2d51368-76c9-4317-ada2-a12c004c432f" {$coll.ML_CLASSIFICATION = $AssignedTimestamp} "9d0c4ee5-e4a1-4625-ab39-d82b619b1a34" {$coll.INSIDER_RISK_MANAGEMENT = $AssignedTimestamp} "e26c2fcc-ab91-4a61-b35c-03cdc8dddf66" {$coll.INFO_GOVERNANCE = $AssignedTimestamp} "46129a58-a698-46f0-aa5b-17f6586297d9" {$coll.DATA_INVESTIGATIONS = $AssignedTimestamp} "6db1f1db-2b46-403f-be40-e39395f08dbb" {$coll.CUSTOMER_KEY = $AssignedTimestamp} "6dc145d6-95dd-4191-b9c3-185575ee6f6b" {$coll.COMMUNICATIONS_DLP = $AssignedTimestamp} "41fcdd7d-4733-4863-9cf4-c65b83ce2df4" {$coll.COMMUNICATIONS_COMPLIANCE = $AssignedTimestamp} "bf6f5520-59e3-4f82-974b-7dbbc4fd27c7" {$coll.SAFEDOCS = $AssignedTimestamp} "2f442157-a11c-46b9-ae5b-6e39ff4e5849" {$coll.M365_ADVANCED_AUDITING = $AssignedTimestamp} "c4801e8a-cb58-4c35-aca6-f2dcc106f287" {$coll.INFORMATION_BARRIERS = $AssignedTimestamp} "0898bdbb-73b0-471a-81e5-20f1fe4dd66e" {$coll.KAIZALA_STANDALONE = $AssignedTimestamp} "617b097b-4b93-4ede-83de-5f075bb5fb2f" {$coll.PREMIUM_ENCRYPTION = $AssignedTimestamp} "4a51bca5-1eff-43f5-878c-177680f191af" {$coll.WHITEBOARD_PLAN3 = $AssignedTimestamp} "efb0351d-3b08-4503-993d-383af8de41e3" {$coll.MIP_S_CLP2 = $AssignedTimestamp} "5136a095-5cf0-4aff-bec3-e84448b38ea5" {$coll.MIP_S_CLP1 = $AssignedTimestamp} "33c4f319-9bdd-48d6-9c4d-410b750a4a5a" {$coll.MYANALYTICS_P2 = $AssignedTimestamp} "b1188c4c-1b36-4018-b48b-ee07604f6feb" {$coll.PAM_ENTERPRISE = $AssignedTimestamp} "14ab5db5-e6c4-4b20-b4bc-13e36fd2227f" {$coll.ATA = $AssignedTimestamp} "3fb82609-8c27-4f7b-bd51-30634711ee67" {$coll.BPOS_S_TODO_3 = $AssignedTimestamp} "07699545-9485-468e-95b6-2fca3738be01" {$coll.FLOW_O365_P3 = $AssignedTimestamp} "9c0dab89-a30c-4117-86e7-97bda240acd2" {$coll.POWERAPPS_O365_P3 = $AssignedTimestamp} "2e2ddb96-6af9-4b1d-a3f0-d6ecfd22edb2" {$coll.ADALLOM_S_STANDALONE = $AssignedTimestamp} "6c6042f5-6f01-4d67-b8c1-eb99d36eed3e" {$coll.STREAM_O365_E5 = $AssignedTimestamp} "8c7d2df8-86f0-4902-b2ed-a0458298f3b3" {$coll.Deskless = $AssignedTimestamp} "8e0c0a52-6a6c-4d40-8370-dd62790dcd70" {$coll.THREAT_INTELLIGENCE = $AssignedTimestamp} "57ff2da0-773e-42df-b2af-ffb7a2317929" {$coll.TEAMS1 = $AssignedTimestamp} "871d91ec-ec1a-452b-a83f-bd76c7d770ef" {$coll.WINDEFATP = $AssignedTimestamp} "21b439ba-a0ca-424f-a6cc-52f954a5b111" {$coll.WIN10_PRO_ENT_SUB = $AssignedTimestamp} "5689bec4-755d-4753-8b61-40975025187c" {$coll.RMS_S_PREMIUM2 = $AssignedTimestamp} "eec0eb4f-6444-4f95-aba0-50c24d67f998" {$coll.AAD_PREMIUM_P2 = $AssignedTimestamp} "6c57d4b6-3b23-47a5-9bc9-69f17b4947b3" {$coll.RMS_S_PREMIUM = $AssignedTimestamp} "bea4c11e-220a-4e6d-8eb8-8ea15d019f90" {$coll.RMS_S_ENTERPRISE = $AssignedTimestamp} "8a256a2b-b617-496d-b51b-e76466e88db0" {$coll.MFA_PREMIUM = $AssignedTimestamp} "c1ec4a95-1f05-45b3-a911-aa3fa01094f5" {$coll.INTUNE_A = $AssignedTimestamp} "41781fb2-bc02-4b7c-bd55-b576c07bb09d" {$coll.AAD_PREMIUM = $AssignedTimestamp} "7547a3fe-08ee-4ccb-b430-5077c5041653" {$coll.YAMMER_ENTERPRISE = $AssignedTimestamp} "a23b959c-7ce8-4e57-9140-b90eb88a9e97" {$coll.SWAY = $AssignedTimestamp} "e95bec33-7c88-4a70-8e19-b10bd9d0c014" {$coll.SHAREPOINTWAC = $AssignedTimestamp} "5dbe027f-2339-4123-9542-606e4d348a72" {$coll.SHAREPOINTENTERPRISE = $AssignedTimestamp} "b737dad2-2f6c-4c65-90e3-ca563267e8b9" {$coll.PROJECTWORKMANAGEMENT = $AssignedTimestamp} "43de0ff5-c92c-492b-9116-175376d08c38" {$coll.OFFICESUBSCRIPTION = $AssignedTimestamp} "0feaeb32-d00e-4d66-bd5a-43b5b83db82c" {$coll.MCOSTANDARD = $AssignedTimestamp} "3e26ee1f-8a5f-4d52-aee2-b81ce45c8f40" {$coll.MCOMEETADV = $AssignedTimestamp} "4828c8ec-dc2e-4779-b502-87ac9ce28ab7" {$coll.MCOEV = $AssignedTimestamp} "9f431833-0334-42de-a7dc-70aa40db46db" {$coll.LOCKBOX_ENTERPRISE = $AssignedTimestamp} "efb87545-963c-4e0d-99df-69c6916d9eb0" {$coll.EXCHANGE_S_ENTERPRISE = $AssignedTimestamp} "34c0d7a0-a70f-4668-9238-47f9fc208882" {$coll.EXCHANGE_ANALYTICS = $AssignedTimestamp} "4de31727-a228-4ec3-a5bf-8e45b5ca48cc" {$coll.EQUIVIO_ANALYTICS = $AssignedTimestamp} "70d33638-9c74-4d01-bfd3-562de28bd4ba" {$coll.BI_AZURE_P2 = $AssignedTimestamp} "8c098270-9dd4-4350-9b30-ba4703f3b36b" {$coll.ADALLOM_S_O365 = $AssignedTimestamp} "292cc034-7b7c-4950-aaf5-943befd3f1d4" {$coll.MDE_LITE = $AssignedTimestamp} "041fe683-03e4-45b6-b1af-c0cdc516daee" {$coll.POWER_VIRTUAL_AGENTS_O365_P2 = $AssignedTimestamp} "95b76021-6a53-4741-ab8b-1d1f3d66a95a" {$coll.CDS_O365_P2 = $AssignedTimestamp} "31b4e2fc-4cd6-4e7d-9c1b-41407303bd66" {$coll.PROJECT_O365_P2 = $AssignedTimestamp} "4ff01e01-1ba7-4d71-8cf8-ce96c3bbcf14" {$coll.DYN365_CDS_O365_P2 = $AssignedTimestamp} "aebd3021-9f8f-4bf8-bbe3-0ed2f4f047a1" {$coll.KAIZALA_O365_P3 = $AssignedTimestamp} "94a54592-cd8b-425e-87c6-97868b000b91" {$coll.WHITEBOARD_PLAN2 = $AssignedTimestamp} "932ad362-64a8-4783-9106-97849a1a30b9" {$coll.ADALLOM_S_DISCOVERY = $AssignedTimestamp} "c87f142c-d1e9-4363-8630-aaea9c4d9ae5" {$coll.BPOS_S_TODO_2 = $AssignedTimestamp} "76846ad7-7776-4c40-a281-a386362dd1b9" {$coll.FLOW_O365_P2 = $AssignedTimestamp} "c68f8d98-5534-41c8-bf36-22fa496fa792" {$coll.POWERAPPS_O365_P2 = $AssignedTimestamp} "2789c901-c14e-48ab-a76a-be334d9d793a" {$coll.FORMS_PLAN_E3 = $AssignedTimestamp} "9e700747-8b1d-45e5-ab8d-ef187ceec156" {$coll.STREAM_O365_E3 = $AssignedTimestamp} default{} } ##################################################################################### } if($coll.DomesticCalling -or ($coll.DomesticCallingPrepaid) -or ($coll.CommunicationCredits)){ $collection.add($coll) | Out-Null }

} Write-Log -Message "Exporting the data to Report" -path $log $collection | Export-Csv $csv import-csv $csv | Export-Excel $Report1 } catch{ $exception = $_.Exception.Message Write-Log -Message "exception occured $exception" -path $log -Severity Error exit } Disconnect-AzureAD Write-Log -Message "Script Finished" -path $log #################################################################################################

[ashmsport]

Tried with just one ServicePrincipalID and Name: "e95bec33-7c88-4a70-8e19-b10bd9d0c014" {$coll.SHAREPOINTWAC = $AssignedTimestamp} same thing

[VikasSukhija]

you need to remove the if condition or update the if condition as I was just giving example of or situation. if($coll.DomesticCalling -or ($coll.DomesticCallingPrepaid) -or ($coll.CommunicationCredits)){ $collection.add($coll) | Out-Null } remove above and just add $collection.add($coll) | Out-Null

[ashmsport]

Awesome, that worked. Thanks a lot. Owe you one 🍺