The report is blank

[vikassukhija]

The report is blank

[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

[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_O365_E3 # 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

[ashmsport]

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