+ Post New Thread
Results 1 to 8 of 8
Cloud Services Thread, Office 365 reports in Technical; I am powershell newbie and I have a powershell script that i am able to run on office365 to tell ...
  1. #1
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    3,031
    Thank Post
    305
    Thanked 292 Times in 202 Posts
    Rep Power
    120

    Office 365 reports

    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

  2. #2
    Marshall_IT's Avatar
    Join Date
    Jul 2011
    Location
    Leeds
    Posts
    507
    Thank Post
    76
    Thanked 63 Times in 54 Posts
    Blog Entries
    1
    Rep Power
    19
    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.

  3. #3
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    3,031
    Thank Post
    305
    Thanked 292 Times in 202 Posts
    Rep Power
    120
    Yeah sure but i have just found a way to get what i want but here are the scripts:

    Code:
    $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.

  4. #4
    rpwillis's Avatar
    Join Date
    Aug 2007
    Location
    SalamanderSoft Ltd
    Posts
    178
    Thank Post
    43
    Thanked 61 Times in 49 Posts
    Rep Power
    26
    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.

  5. Thanks to rpwillis from:

    timbo343 (5th March 2014)

  6. #5
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    3,031
    Thank Post
    305
    Thanked 292 Times in 202 Posts
    Rep Power
    120
    Thanks. What does it actually do as im not sure about the "write host" bit?

  7. #6
    rpwillis's Avatar
    Join Date
    Aug 2007
    Location
    SalamanderSoft Ltd
    Posts
    178
    Thank Post
    43
    Thanked 61 Times in 49 Posts
    Rep Power
    26
    It will write out csv format data.
    Write-Host just outputs to the console.

  8. #7
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    3,031
    Thank Post
    305
    Thanked 292 Times in 202 Posts
    Rep Power
    120
    ah ok, so to extract to CSV what would i need to add on the end of the script?

  9. #8
    Marshall_IT's Avatar
    Join Date
    Jul 2011
    Location
    Leeds
    Posts
    507
    Thank Post
    76
    Thanked 63 Times in 54 Posts
    Blog Entries
    1
    Rep Power
    19
    Add

    > c:\filepath\filename.csv

    To the end of the command!
    Last edited by Marshall_IT; 5th March 2014 at 01:45 PM.

  10. Thanks to Marshall_IT from:

    timbo343 (5th March 2014)

SHARE:
+ Post New Thread

Similar Threads

  1. Free Office 365 ebook
    By steve in forum Cloud Services
    Replies: 0
    Last Post: 18th August 2011, 07:35 PM
  2. Office 365 Charges
    By klop in forum General Chat
    Replies: 7
    Last Post: 9th May 2011, 03:54 PM
  3. Microsoft Office 365 Public BETA
    By EduTech in forum General Chat
    Replies: 4
    Last Post: 21st April 2011, 09:40 AM
  4. Replies: 3
    Last Post: 20th October 2010, 09:35 AM
  5. Office Error Report on Clipart Search
    By jcook117 in forum Windows
    Replies: 0
    Last Post: 19th January 2007, 10:03 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •