MYSQL Queries
12/03/2017
Connect to office 365 powershell
Connect with basic authentication:
$Cred = Get-Credential youradminemail
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $Cred -Authentication Basic –AllowRedirection
Import-PSSession $Session
Connect-MsolService -Credential $cred
Connect to office 365 with Multi Factor Authentication:
Change the youradminemail
Import-Module $((Get-ChildItem -Path $($env:LOCALAPPDATA+"\Apps\2.0\") `
-Filter CreateExoPSSession.ps1 -Recurse ).FullName | Select-Object -Last 1)
Connect-EXOPSSession -UserPrincipalName youradminemail
$global:UserPrincipalName="youradminemail"
MailBox Info
User Mailbox (Test) Info:
All fields:
Get-User -Identity “test” | Format-List
Only email address :
Get-User -Identity “test” | select WindowsEmailAddress| Format-List
Some fields:
Get-User -Identity “test” | select firstname,lastname,Phone,department,DisplayName,office| Format-List
Change office field to X in user “Test”:
Set-User -Identity test -office “X”
Set PASSWORD to user:
Set-MsolUserPassword -UserPrincipalName “xx@xx.com” -NewPassword “p@ss-WorD%”
Display primary SMTP Address:
Get-Mailbox -ResultSize Unlimited |Select-Object DisplayName,PrimarySmtpAddress
Get-Mailbox -ResultSize Unlimited |Select-Object Alias,PrimarySmtpAddress
Show mailbox quota:
option #1: Get-Mailbox | select IssueWarningQuota, displayname, totalitemsize | Format-Table -AutoSize
option #2: Get-Mailbox | select IssueWarningQuota, displayname, totalitemsize,PrimarySmtpAddress | Format-Table -AutoSize
option #3:
Get-Mailbox| select displayname,IssueWarningQuota,ProhibitSendReceiveQuota,`
@{n='TotalItemSize';e={(Get-MailboxStatistics -Identity $_.displayname).totalitemsize}}
Show block users:
Get-MsolUser -EnabledFilter DisabledOnly
Block user “Test”:
Set-MsolUser -UserPrincipalName test@xxx.com -BlockCredential $true
Unblock user “Test”:
Set-MsolUser -UserPrincipalName test@xxx.com -BlockCredential $true
Mailbox with forward field and export to CSV file:
Get-Mailbox | select UserPrincipalName,ForwardingSmtpAddress,DeliverToMailboxAndForward| export-csv C:\temp\”Mailboxes with a Forward.csv” -Encoding UTF8
View password never expires:
Get-MSOLUser | Select UserPrincipalName, PasswordNeverExpires
Get-MSOLUser -UserPrincipalName x@x.com | Select PasswordNeverExpires
SET mailbox forwarding for user TEST and save a local copy:
Set-Mailbox TEST -ForwardingsmtpAddress x@x.com -DeliverToMailboxAndForward $true
Disable forwarding for user TEST:
Set-Mailbox test@xxx.xom -ForwardingSmtpAddress $Null
Uploading user x@x.com photo:
Set-UserPhoto -Identity x@x.com -PictureData ([System.IO.File]::ReadAllBytes("c:\temp\userimage.jpg")) -Confirm:$false
Change status for ALL USERS to PasswordNeverExpires To True:
Get-MSOLUser | Select UserPrincipalName, PasswordNeverExpires | Where-Object {$_.PasswordNeverExpires -eq ""} | Set-MsolUser -PasswordNeverExpires $true
Block all users with the word “Testing” in the department field:
Get-MsolUser -All | Where {$_.Department -eq "Testing"} | Set-MsolUser -BlockCredential $true
View mailbox list that office and phone fields is empty:
Get-User | select firstname,Phone,department,office | Sort-Object firstname | where {$_.office -eq "" -and $_.Phone -eq ""}
View mailbox list with the “support” in the department field:
Get-User | select firstname,Phone,department,office | Sort-Object firstname | where {$_.Department -eq "support"}
View mailbox list with “Last Logon Time” and export to CSV file:
(Get-Mailbox) | Foreach {Get-MailboxStatistics $_.Identity | Select DisplayName, LastLogonTime} | export-csv "C:\temp\mailbox last login.csv" -Encoding UTF8
View mailbox with the office 365 license:
Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match ""}| select UserPrincipalName,FirstName,LastName,DisplayName | Format-Table -AutoSize
View mailbox with the office 365 “Exchange online” license:
Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "EXCHANGESTANDARD"}| select UserPrincipalName,FirstName,LastName,DisplayName| Format-Table -AutoSize
View mailbox with the office 365 “Exchange PREMIUM” license:
Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "O365_BUSINESS_PREMIUM"}| select UserPrincipalName,FirstName,LastName,DisplayName| Format-Table -AutoSize
Export mailbox users to CSV File:
Get-User | select firstname,lastname,Phone,department,DisplayName,office,@{n='PrimarySmtpAddress';e={(Get-Mailbox -Identity $_.displayname).PrimarySmtpAddress}}|`
export-csv C:\temp\"All Users MailBoxs.csv" -Encoding UTF8
Address Book
Get hidden addresses from address book:
Get-Mailbox | select name, Hiddenfromaddresslistsenabled | WHERE {$_.HiddenFromAddressListsEnabled -eq $true }
Get mailbox that are not hidden in your address book:
Get-Mailbox | select name, Hiddenfromaddresslistsenabled | WHERE {$_.HiddenFromAddressListsEnabled -eq $false }
Hide mailboxes from address Lists:
Set-Mailbox -Identity UserName -HiddenFromAddressListsEnabled $true
Distribution Group
View all distribution groups:
Get-DistributionGroup
Create distribution group “Support Team”:
New-DistributionGroup -Name “Support Team” -DisplayName “Support Team” -Alias “SupportTeam” -PrimarySmtpAddress SupportTeam@xxx.xxx
Get distribution groups “Support Team” info:
Get-DistributionGroup “Support Team” | Format-List
Hide this group from address lists – “Support Team”:
Set-DistributionGroup “Support Team” -HiddenFromAddressListsEnabled $True
Enable senders inside and outside of my organization to send emails to “Support Team” group:
Set-DistributionGroup “Support Team” -RequireSenderAuthenticationEnabled $False
Show this group in address lists – “Support Team”:
Set-DistributionGroup “Support Team” -HiddenFromAddressListsEnabled $False
Add members to “Support Team” group:
Update-DistributionGroupMember -Identity “Support Team” -Members x1@x.com,x2@x.com
Remove members to “Support Team” group:
Remove-DistributionGroupMember -Identity “Support Team” -Member “x2@x.com”
View all members in the distribution group “Support Team”:
Get-DistributionGroupMember -Identity “Support Team”
Export to CSV file all members in the distribution group “Support Team”:
Get-DistributionGroupMember -Identity "Support Team" `
| Select-Object Displayname,Name,PrimarySMTPAddress `
| Export-CSV -Path "C:\temp\Support Team.csv" -Encoding UTF8
Add all users with the word “ABC” in the office field to distribution group “Support Team”:
$users = Get-User | Where {$_.office -like “ABC*”}
foreach ($User in $users)
{
Add-DistributionGroupMember -Identity "Support Team" -Member $User.name
}
Count all members in the “Support Team” group:
(Get-DistributionGroupMember “Support Team”).Count
DELETE “Support Team” group:
Remove-DistributionGroup “Support Team”
View all distribution group with TRUE or FALSE in the HiddenFromAddressListsEnabled and export to CSV file:
Get-distributiongroup | select name, Hiddenfromaddresslistsenabled | Export-CSV -Path “C:\temp\Distribution Group list.csv” -Encoding UTF8
View all hidden distribution group:
get-distributiongroup | select name, Hiddenfromaddresslistsenabled | WHERE {$_.HiddenFromAddressListsEnabled -eq $true }
View all distribution group that show in the address book:
get-distributiongroup | select name, Hiddenfromaddresslistsenabled | WHERE {$_.HiddenFromAddressListsEnabled -eq $false }
Mail Contact
View all contacts:
Get-MailContact | Format-Table -AutoSize
Hide contact from address book:
Set-MailContact -Identity “contactname” -HiddenFromAddressListsEnabled $true
HIDE ALL CONTACTS from address book:
Get-MailContact | Where-Object {$_.Hiddenfromaddresslistsenabled -eq ''} | Set-MailContact -HiddenFromAddressListsEnabled $True
General Commands
Check available licenses:
Get-MsolAccountSku
Close power shell connection:
Get-PSSession
Get-PSSession | Remove-PSSession
