Export last log on more 90 days Office 365

%3CLINGO-SUB%20id%3D%22lingo-sub-2287437%22%20slang%3D%22en-US%22%3EExport%20last%20log%20on%20more%2090%20days%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287437%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20export%20csv%20file%2C%20with%20users%20that%20not%20logged%20on%20more%20then%2090%20days.%3C%2FP%3E%3CP%3EI%20tried%20to%20run%20this%20script%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGet-Mailbox%20-RecipientType%20'UserMailbox'%20%7C%20Get-MailboxStatistics%20%7C%20Sort-Object%20LastLogonTime%20%7C%20Where%20%7B%24_.LastLogonTime%20%E2%80%93lt%20(%5BSystem.DateTime%5D%3A%3ANow).AddDays(-90)%20%7D%20%7C%20Export-Csv%20%22D%3A%5CLogs%5CO365MAILBOXSTATS_REPORT.CSV%22%20-NoTypeInformation%3C%2FP%3E%3CP%3EI%20didn't%20received%20any%20csv%20file%20after%20the%20script%20finish.%3C%2FP%3E%3CP%20class%3D%22%22%3EAlso%2C%20I%20got%20this%20errors%20%3A%3C%2FP%3E%3CP%3EGet-MailboxStatistics%3A%20The%20specified%20mailbox%20%22User%20name%22%26nbsp%3B%20isn't%20unique.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EMy%20goal%20is%20to%20get%20users%20that%20not%20connected%20to%20O365%20more%20then%2090%20days.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2287437%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2290600%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20last%20log%20on%20more%2090%20days%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2290600%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035868%22%20target%3D%22_blank%22%3E%40dannytveria%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EYour%20error%20means%20that%20PowerShell%20is%20trying%20to%20use%20not%20unique%20value%20for%20%3CSTRONG%3E-Identity%3C%2FSTRONG%3E%20parameter%20of%20%3CSTRONG%3EGet-MailboxStatistics%3C%2FSTRONG%3E%20cmdlet.%3CBR%20%2F%3EFor%20Identity%20parameter%20you%20can%20use%20any%20value%20that%20uniquely%20identifies%20the%20mailbox.%20For%20example%3A%3C%2FP%3E%3CUL%3E%3CLI%3EName%3C%2FLI%3E%3CLI%3EAlias%3C%2FLI%3E%3CLI%3EDistinguished%20name%20(DN)%3C%2FLI%3E%3CLI%3ECanonical%20DN%3C%2FLI%3E%3CLI%3EDomain%5CUsername%3C%2FLI%3E%3CLI%3EEmail%20address%3C%2FLI%3E%3CLI%3EGUID%3C%2FLI%3E%3CLI%3ELegacyExchangeDN%3C%2FLI%3E%3CLI%3ESamAccountName%3C%2FLI%3E%3CLI%3EUser%20ID%20or%20user%20principal%20name%20(UPN)%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20case%20one%20of%20the%20options%20would%20be%20to%20use%20%3CSTRONG%3EUPN%3C%2FSTRONG%3E%20as%20it%20is%20unique%20within%20your%20organization.%3C%2FP%3E%3CP%3EYou%20need%20to%20modify%20your%20script%20to%20explicitly%20pass%20%3CSTRONG%3EUserPrincipalName%3C%2FSTRONG%3E%20to%20%3CSTRONG%3EGet-MailboxStatistics%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2290737%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20last%20log%20on%20more%2090%20days%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2290737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F672105%22%20target%3D%22_blank%22%3E%40AndySvints%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Andy%2C%3C%2FP%3E%3CP%3Ethanks%20for%20your%20response.%3C%2FP%3E%3CP%3EI%20didn%60t%20understand%20where%20exactly%20I%20need%20to%20change%20value.%3C%2FP%3E%3CP%3Ecan%20you%20please%20help%20me%20with%20my%20example%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2291350%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20last%20log%20on%20more%2090%20days%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291350%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035868%22%20target%3D%22_blank%22%3E%40dannytveria%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EGet-Mailbox%20-RecipientType%20'UserMailbox'%20%7C%25%7B%20Get-MailboxStatistics%20%24_.UserPrincipalName%20%7C%20Sort-Object%20LastLogonTime%20%7C%20Where%20%7B%24_.LastLogonTime%20-lt%20(%5BSystem.DateTime%5D%3A%3ANow).AddDays(-90)%20%7D%20%7C%20Export-Csv%20%22D%3A%5CLogs%5CO365MAILBOXSTATS_REPORT.CSV%22%20-NoTypeInformation%20-Append%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I'm trying to export csv file, with users that not logged on more then 90 days.

I tried to run this script:

 

Get-Mailbox -RecipientType 'UserMailbox' | Get-MailboxStatistics | Sort-Object LastLogonTime | Where {$_.LastLogonTime –lt ([System.DateTime]::Now).AddDays(-90) } | Export-Csv "D:\Logs\O365MAILBOXSTATS_REPORT.CSV" -NoTypeInformation

I didn't received any csv file after the script finish.

Also, I got this errors :

Get-MailboxStatistics: The specified mailbox "User name"  isn't unique.

 

My goal is to get users that not connected to O365 more then 90 days.

8 Replies

Hello @dannytveria,

Your error means that PowerShell is trying to use not unique value for -Identity parameter of Get-MailboxStatistics cmdlet.
For Identity parameter you can use any value that uniquely identifies the mailbox. For example:

  • Name
  • Alias
  • Distinguished name (DN)
  • Canonical DN
  • Domain\Username
  • Email address
  • GUID
  • LegacyExchangeDN
  • SamAccountName
  • User ID or user principal name (UPN)

 

In your case one of the options would be to use UPN as it is unique within your organization.

You need to modify your script to explicitly pass UserPrincipalName to Get-MailboxStatistics.

Hope that helps.

@AndySvints 

Hi Andy,

thanks for your response.

I didn`t understand where exactly I need to change value.

can you please help me with my example?

Hello @dannytveria,

Try this:

Get-Mailbox -RecipientType 'UserMailbox' |%{ Get-MailboxStatistics $_.UserPrincipalName | Sort-Object LastLogonTime | Where {$_.LastLogonTime -lt ([System.DateTime]::Now).AddDays(-90) } | Export-Csv "D:\Logs\O365MAILBOXSTATS_REPORT.CSV" -NoTypeInformation -Append}

Hope that helps.

Hi Andy,
thanks for the help.
I getting a warning:
By default, only the first 1000 items are returned. Use the ResultSize parameter to specify the number of items returned.

Also, no CSV file not created maybe because of the warning?

Hello @dannytveria,

Add -ResultSize Unlimited to Get-Mailbox:

Get-Mailbox -RecipientType 'UserMailbox' -ResultSize Unlimited | ...

Also try to remove filter with LastLogonDate  just to test of that it works.

It might be that there are no users who accessed their mailbox more that 90 days ago.

Hope that helps.

It`s worked with the LastLogonDate.
the LastLogonDate field is when the user last login to his mailbox on 365?

Or which field actually shows the last use mailbox?

@dannytveria,

 

The research was done on many real-time users, and we have concluded that getting Office 365 user’s inactive time from LastUserActionTime is the best.

 

Reference: https://o365reports.com/2019/06/18/office-365-users-last-logon-time-incorrect/

 

Hope that helps.

 

Hi Andy,
thanks for your gr8 help.
U was very helpfull