Exchange Database and User Login Report Marriage

Texkonc

How can I marry these two scripts?

Get-Mailbox -ResultSize Unlimited -ErrorAction SilentlyContinue | Select DisplayName,Database,WindowsEmailAddress,@{n="Size(MB)";e={[int]$(Get-MailboxStatistics $_.Alias).TotalItemSize.Value.ToMB()}} | Sort-Object "Size(MB)" -Descending | Export-Csv -Path C:\Scripts\MailboxStatistics-05042020.csv -NoTypeInformation

#Define a work folder for the report
$WorkFolder="C:\Scripts"
#Define # of days to search for users that have not logged in
$Days=90
#Report file
$ExportedReport = "$WorkFolder\$DaysDayEnabledUsers.csv"
#Search for users
Get-ADUser -Filter {Enabled -eq $TRUE} -SearchBase $OU -Properties EmailAddress,GivenName,Surname,DisplayName,Description,Title,Department,Office,LastLogonDate,homeMDB | Where {($_.LastLogonDate -lt (Get-Date).AddDays(-$Days)) -and ($_.LastLogonDate -ne $NULL)} | Sort | Select EmailAddress,GivenName,Surname,DisplayName,Description,Title,Department,Office,LastLogonDate | export-csv $ExportedReport -nti

Goal is get User login status, Mailbox DB, and Mailbox size to give to management and to use for baseline to start cleaning up exchange accounts. I am a google powersheller not the one that builds one.
I can cleanup the columns once in Excel.

dbeato

@Texkonc So you basically want to know which users have not logged in on their Domain login for the last 90 days and match their mailbox and provide the usage of that said mailbox or mailboxes ?

Texkonc

@dbeato said in Exchange Database and User Login Report Marriage:

@Texkonc So you basically want to know which users have not logged in on their Domain login for the last 90 days and match their mailbox and provide the usage of that said mailbox or mailboxes ?

Bingo

stacksofplates

@Texkonc said in Exchange Database and User Login Report Marriage:

How can I marry these two scripts?

You need to be ordained and then have them exchange rings.

Texkonc

@stacksofplates said in Exchange Database and User Login Report Marriage:

@Texkonc said in Exchange Database and User Login Report Marriage:

How can I marry these two scripts?

You need to be ordained and then have them exchange rings.

Someone....had to go there...

dbeato

I will get you something tomorrow, dealing with something important this week.

Texkonc

@dbeato said in Exchange Database and User Login Report Marriage:

I will get you something tomorrow, dealing with something important this week.

If it works, Beer is on me!
Second thought, I will let SAM pay....

dbeato

@Texkonc said in Exchange Database and User Login Report Marriage:

@dbeato said in Exchange Database and User Login Report Marriage:

I will get you something tomorrow, dealing with something important this week.

If it works, Beer is on me!
Second thought, I will let SAM pay....

Lol Ship it

dbeato

Now Back to the task, this was a mess whatever those two scripts you had did not take into account that this are not computers, this are users so the LastLogonDate is not a User Attribute and so forth. See below my script

#Import AD  and  Exchange Module (The Server Must have AD PowerShell Exchange PowerSHell  Modules)
Import-Module ActiveDirectory
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
#OU Iformation (if needed) ( I am not using it on my example)
$OU="dc=domain,dc=com"
#Get Today's Date
$today = Get-Date
#Date Format for File Name
$date = Get-Date -Format "MMddyyyy"
#Define a work folder for the report
$WorkFolder="C:\Scripts"
#Define the number of days to search for users that have not logged in
$Days=$today.AddDays(-90)
#Search for users
Get-ADUser -Filter {LastLogonDate -lt $Days -And Enabled -eq $TRUE }} | Select SAMAccountname | ConvertTo-Csv -NoTypeInformation | % { $_ -replace '"', ""}  | out-file "$WorkFolder\$date-Users.csv" -fo -en ascii
#Get all the Users from AD
$users= @(Get-Content $WorkFolder\$date-Users.csv)
#Loop for all the users to Report of All Mailboxes That are Inactive
foreach ($user in $users) {
Get-Mailbox -Identity $user | Select DisplayName,Database,WindowsEmailAddress,@{n="Size(MB)";e={[int]$(Get-MailboxStatistics $_.Alias).TotalItemSize.Value.ToMB()}} | Sort-Object "Size(MB)" -Descending | Export-Csv -Path $WorkFolder\MailboxStatistics-$date.csv -NoTypeInformation -Append
}

This will only give you the Reports of the Users That are Enabled and Have not logged in in the last 90 days. Now, give it a try and check it. If that is not what you are looking for then something else can be reviewed.

Texkonc

I ran it, but not a chance to review it yet. I should have time tomorrow.

Texkonc

OK, so Management wants these AD Attributes in the report.
In the List below
Attribute Management wants=What I think I need to call in the script

Name=Displayname
Company=Company
Description=Description
Department=Department
Title=Title
employeeID=Our only Custom Attribute.
Mail=EmailAddress
phyiscalDeliveryOfficeName=Office

Line 15, I added this string.
SAMaccountname,EmailAddress,GivenName,Surname,DisplayName,Description,Title,Department,Office,Company,employeeID

It made the Database file blank. I cant seem to see what it is that is making it barf.

Dashrender

Assuming linen 20 is what's outputting what you really care about, wouldn't you add your new items to it?
I'm guessing you don't care about that output at the userlist level, but instead at the finaly output level.

dbeato

@Texkonc So you don't need the Mailbox status and all that? I just don't understand that requirement?

Texkonc

@dbeato said in Exchange Database and User Login Report Marriage:

@Texkonc So you don't need the Mailbox status and all that? I just don't understand that requirement?

Yes I do, but I also need these other attributes.