Script to get users instead of processing through input file help

Hi Experts,

I need some help with getting the script to grab all users with a mailbox or smtp address in the script below.  

Now, I have to first export all users separate, filter out those without mail, and use the file with samaccountname to run the script.

This also takes an enormous amount of time to run, so any suggestions or tips are apprecaited!

I first connect to MSOL, Exchange online in shell, then run:

$Date = Get-Date
$strDate = "{0}{1:d2}{2:d2}_{3:d2}{4:d2}" -f $date.year,$date.month,$,$date.hour,$date.minute

$strOutputFile = "C:\temp\licensereport.csv"

$strInputFile = "C:\temp\Licensing\input.txt"
$strSANs=Get-Content $strInputFile 

$strAccountHeader = "SamAccountName,DisplayName,UserPrincipalName,DistinguishedName,Enabled,LastLogonDate,HomeDirectory ,ScriptPath"

$strOrganizationHeader = "Company,Department,Title,Manager"

$strCommunicationsHeader = "TelephoneNumber,OfficePhone,EmailAddress,msExchRecipientTypeDetails,msRTCSIP-PrimaryUserAddress"

$strMSOL = "IsLicensed,BlockCredential,AccountSKUIds,LicenseReconciliationNeeded"

$strHeader = $strAccountHeader +","+ $strOrganizationHeader +","+ $strCommunicationsHeader +","+ $strMSOL

    Add-Content $strOutputFile $strHeader
    Write-output $strHeader

foreach ($strSAN in $strSANs)
$ADUser = $null
$ADUser = Get-ADUser -Identity $strSAN -properties *

$strAccount = $null
$strAccount = $strSAN +","+ '"'+$ADUser.DisplayName+'"' +","+ $ADUser.UserPrincipalName +","+'"' + $ADUser.DistinguishedName + '"' +","+ $ADUser.Enabled +","+ $ADUser.LastLogonDate +","+ $ADUser.HomeDirectory +","+ $ADUser.ScriptPath

$strOrganization = $null
$strOrganization = '"' + $ADUser.Company +'"' +","+ '"' + $ADUser.Department + '"' +","+ '"' + $ADUser.Title + '"' +","+ '"'+$ADUser.Manager+'"'

$strCommunications = $null
$strCommunications = $ADUser.TelephoneNumber +","+ $ADUser.OfficePhone +","+ $ADUser.EmailAddress +","+ $ADUser.msExchRecipientTypeDetails +","+ $ADUser.'msRTCSIP-PrimaryUserAddress'

$strADUser = $strAccount +","+ $strOrganization +","+ $strCommunications 

$strUPN = $null
$strUPN = $aduser.UserPrincipalName

if ($strUPN -eq $null) {
    Add-Content $strOutputFile $strADUser
    Write-output $strADUser
} else
$msolusers = Get-MsolUser -All
$msolusers = $null
$msolusers = Get-MsolUser -UserPrincipalName $strUPN
if ($msolusers -eq $null) {
    Add-Content $strOutputFile $strSAN
    Write-output $strSAN
foreach ($msoluser in $msolusers) {
	$strAccountSKUIds = $null
    $strAccountSKUIds = $msoluser.Licenses.AccountSkuId -join ","
	$strAccountSKUIds = '"' + $strAccountSKUIds + '"'

    $strout = $strADUser +","+ $msoluser.IsLicensed +","+ $msoluser.BlockCredential +","+ $strAccountSKUIds +","+ $msoluser.LicenseReconciliationNeeded
    Add-Content $strOutputFile $strout
    Write-output $strout



Open in new window

Ron ShortsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
you obviously have modified your script as you have to
Connect-msolservice before any call to get-msoluser
Ron ShortsAuthor Commented:
@David - yes, I make the connection before running this script.  Thanks
Éric MoreauSenior .Net ConsultantCommented:
Have a look at my article from

It shows the licence of each user.
Ron ShortsAuthor Commented:
@Eric - excellent write up and script.  I have upwards of 30k users though and this is more info than I need and would take a long time to run I believe.  My main goal is to straighten out licensing as we are doing licensing based on group membership in a hybrid environment and have multiple licenses assigned, some mailboxes without licenses that have a mailbox etc.,  Thanks!
A few minor suggestions based on what you have..

When you pull the users from AD (line 25), only pull the properties you need, instead of all the properties.. that should be a relatively substantial  time savings.

Every place that you don't need variable substitution, don't use double quotes (").. use single quotes.  When you use a double quote, powershell scans the statement looking for variables, and then proceeds.  If you use the single quotes it does not try to scan them.

Don't use the vbscript syntax for combining your strings.. since the strings are not mutable (can't be changed).. when you add them together, it has to create a new string each time, so adding them sequentially means creating new strings each time.  Instead, use the -f format operator which is significantly faster. Example (line 28):
$strCommunications = $ADUser.TelephoneNumber +","+ $ADUser.OfficePhone +","+ $ADUser.EmailAddress +","+ $ADUser.msExchRecipientTypeDetails +","+ $ADUser.'msRTCSIP-PrimaryUserAddress'

#Using the -f syntax also means you never have to worry about screwing up the quoting.. just use single quotes everywhere with the replaceable parameters.  you can also use the same replaceable parameter multiple times

$strCommunications = ('{0},{1},{2},{3},{4}' -f $ADUser.TelephoneNumber, $ADUser.OfficePhone, $ADUser.EmailAddress, $ADUser.msExchRecipientTypeDetails, $ADUser.'msRTCSIP-PrimaryUserAddress')

Open in new window

Every time you pull a property from an object, like the above syntax, it costs time, so if you are going to use a property more than once, always set it to a variable, and then use that variable.  

And i read several articles about checking against $null, and there were a lot of reasons (I can't remember most of them..), but the preferred syntax is if ($null -eq $xyz){}.. if you don't do this, then you can end up with incorrect results.  


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.