Office 365 reports

[timbo343]

I am powershell newbie and I have a powershell script that i am able to run on office365 to tell me which students accessed their email accounts last. The data shown on this report is the date/time and name of student.

 

Now, this is where i need a bit more information. I need to know what year the student is in which will save me a few days manually adding/looking up the student in SIMS. I was wondering if there was a way to extract the email address from this script so i am able to tell what year group these students are in. At the moment the script is sorting the results by DisplayName then Piped with FT -autosize Displayname, LastLogonTime.

 

Have been trying to see what other switches i can use to get the email address from this report but it looks like im not having much luck.

 

Thanks

[Marshall_IT]

can you post the script so i can see which command you are calling?

 

I would guess that you will probably see a -DisplayName somewhere and that you should be able to tag on after this -UserPrincipalName which should give you the login name of the student, which is normally the same as the email address.

[timbo343]

Yeah sure but i have just found a way to get what i want but here are the scripts:

 

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $Cred -Authentication Basic -AllowRedirection

Import-PSSession $Session

Get-Mailbox | Get-MailboxStatistics | Sort DisplayName | FT -AutoSize DisplayName, LastLogonTime > “c:\users\*username*\desktop\test2.txt”
Get-Mailbox | Sort DisplayName  | ft -autosize displayname, EmailAddresses > "c:\users\*username*\desktop\test1.txt"

-ResultSize Unlimited

 

With this script i am able to get the email address and marry up the display name.

 

Import the text files into excel, copy the email address from test1 over to test2. From here, im able to run =left(*cell*,8) then =right(*cell*,2). This gives the intake year the student is in. Then i am able to copy the information over to different sheets named within 30 days, 30-60 days. 61-90 days, 90+ days, never.

 

Then i run a =countif(*cells*,*intakeyear*) to give me the results i need.

 

A long way round but it works and i don't think i will be running it every day, more like once a week or once every 2 weeks.

[rpwillis]

Give this a go after importing the session.

 

Write-Host "DisplayName,Email,LastLogon,Year"

$mailboxes = get-mailbox

$mailboxes | foreach-object {

$stats = get-mailboxstatistics $_.Identity

$_.DisplayName + "," + $_.PrimarySMTPAddress + "," + $stats.LastLogonTime + "," + $_.PrimarySMTPAddress.Substring(7,2)

}

 

This assumes that the intake year value is at position 8 & 9 in the email address.

[timbo343]

Thanks. What does it actually do as im not sure about the "write host" bit?

[rpwillis]

It will write out csv format data.

Write-Host just outputs to the console.

[timbo343]

ah ok, so to extract to CSV what would i need to add on the end of the script?

[Marshall_IT]

Add

 

> c:\filepath\filename.csv

 

To the end of the command!

Edited March 5, 2014 by Marshall_IT