Saturday, 21 November 2015

Exchange Mailbox migration using PowerShell


Introduction : This document is just about the exchange mailbox migration --- I am just covering how can we migrate a single & multiple user's mailbox - Moving mailbox from one server to another server in the same forest is very simple process which we can  achieve either through powershell scripts or from the exchange console...

First we will start with mailbox movement for single user

How to get user mailbox details ?

001
Get-mailbox -Identity viviana









How to move mailbox ?

Refer the below important parameters :

TargetDatabase : The TargetDatabase parameter specifies whether to return all mailboxes that are being moved to the specified target database.
SuspendWhenReadyToComplete : The SuspendWhenReadytoComplete parameter specifies whether to return mailboxes that have been moved with the New-MoveRequest command and its SuspendWhenReadyToComplete
BadItemLimit : The BadItemLimit parameter specifies the maximum number of bad items that are allowed before the request fails. A bad item is a corrupt item in the source mailbox that can't be copied to the target mailbox. Also included in the bad item limit are missing items. Missing items are items in the source mailbox that can't be found in the target mailbox when the request is ready to complete.

Before move request, you can validate if you want to know what is actually going to happen by using -whatif cmdlet -- & use the above important parameters which may required in your production environment.


001
New-MoveRequest –identity viviana –TargetDatabase MDB02 -whatif










I just used -whatif for validating what exactly going to happen --- Rerun the same command by removing -whatif  for mailbox movement

001
New-MoveRequest –identity viviana –TargetDatabase MDB02











We can suspend or remove the move request - If you suspend the move request, that can be resumed, but removed move request can't be resumed back.


001
002
Suspend-MoveRequest -Identity viviana
Remove-MoveRequest -Identity viviana

How to check the status of move request ?

001
002
003
004
Get-MoveRequest
#----- We can also select particular properties as showing below
Get-MoveRequest | select DisplayName,Alias,Status,TargetDatabase,percentcomplete
Get-MoveRequestStatistics -Identity viviana










If you used SuspendWhenReadytoComplete parameter, you need to resume the move request in order to complete the move request - See the below command

001
Resume-MoveRequest -Identity viviana

Mailbox movement for Multiple Users

As per the above examples, we are just migrating a single user - But in production we need to migrate thousands of users -
All we need to do is -  Create an Input file which contains users & database details (Add database column only if we are moving users to multiple database for load balancing purpose, otherwise you can directly mention the database name - like moving single user)

Batch Name : Batch name is one of the important parameter in new move request in case if we are moving multiple mailboxes. The Batchname parameter specifies a descriptive name for moving a batch of mailboxes

I am taking input from a .CSV File --- As I am moving mailboxes to different database, included Database column as well in my input file.
Below is my Input File and saved it under C:\Project\Input.csv










As per the below script, we are just directly moving mailboxes - We can use the below important parameters as well.
-SuspendWhenReadyToComplete
-BadItemLimit

001
002
003
004
005
$csvinput = Import-Csv -Path 'C:\Project\Input.csv'
Foreach ($csv in $csvinput)
{
New-MoveRequest –identity ($csv.alias) –TargetDatabase ($csv.database) -BatchName "TESTMove01"
}

If we are moving mailboxes to the same Database, mention only Alias in CSV file and use the below script -- You need to type the database name manually in this case.
Note : We can use notepad as well if we are importing only user details - Replace Import-Csv with Get-Content cmdlet

001
002
003
004
005
$csvinput = Import-Csv -Path 'C:\Project\Input.csv'
Foreach ($csv in $csvinput)
{
New-MoveRequest –identity ($csv.alias) –TargetDatabase "MDB01" -BatchName "TESTMove01"
}

Move request status


1
2
3
4
5
6
7
8
9
Get-MoveRequest -BatchName "MDB01"
#-- Grid View with selected properties
Get-MoveRequest -BatchName "MDB01" | select DisplayName,Alias,Status,TargetDatabase,percentcomplete | Out-GridView

# we can use the below cmdlet as well

Get-MoveRequestStatistics -BatchName "MDB01" | select DisplayName,Alias,Status,TargetDatabase,percentcomplete | Out-GridView
<# Get-MoveRequestStatistics ::: Some of the failure messages that are returned by this cmdlet are temporary and don't indicate that a request -
has actually failed. If the Status value is Queued or InProgress,then the request is proceeding normally #>

Move request status summary


001
Get-MoveRequest -BatchName "MDB01" | group status -noelement

Export your status report


001
002
Get-MoveRequest -BatchName "MDB01" |select DisplayName,Alias,Status,TargetDatabase,percentcomplete |
Export-Csv -Path "C:\Report\Outputt.csv" -NoTypeInformation


Reference Page - Technet
Download Scrips                                          

                                                     Happy Migration   :)


Tuesday, 26 May 2015

How to get MAC & IP address of a remote computer

Below function is to get the MAC and IP address of a local or remote machine.The output will be displayed on the screen (write-host). You will be able to get the IP & MAC details for a multiple machine at same time by giving the machine names.

The logic here is, first to get the active IP address of machine and match the IP with MAC address of the matching adapter.

How to get the IP address of a machine ?


001
002
$Inputmachine = "ComputerName"
$IPAddress = ([System.Net.Dns]::GetHostByName($Inputmachine).AddressList[0]).IpAddressToString











How to get network adapter details ?

You will be getting all the network adapter details using the below script, including IP and Mac address. 
Check what information is available in $IPMAC using the below command.

$IPMAC | select *


001
$IPMAC = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $Inputmachine

Below is the function to get IP and MAC address of machines : 


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
function Get-IPMAC
{
<#
        .Synopsis
        Function to retrieve IP & MAC Address of a Machine.
        .DESCRIPTION
        This Function will retrieve IP & MAC Address of local and remote machines.
        .EXAMPLE
        PS>Get-ipmac -ComputerName viveklap
        Getting IP And Mac details:
        --------------------------

        Machine Name : viveklap
        IP Address : 192.168.1.103
        MAC Address: 48:D2:24:9F:8F:92
        .INPUTS
        System.String[]
        .NOTES
        Author - Vivek RR
        blog - www.vivekrr.blogspot.in
        Adapted logic from the below blog post
        "http://blogs.technet.com/b/heyscriptingguy/archive/2009/02/26/how-do-i-query-and-retrieve-dns-information.aspx"
#>

Param
(
    #Specify the Device names
    [Parameter(Mandatory=$true,
            ValueFromPipeline=$true,
            Position=0)]
    [string[]]$ComputerName
)
    Write-Host "Getting IP And Mac details:`n--------------------------`n"
    foreach ($Inputmachine in $ComputerName )
    {
        if (!(test-Connection -Cn $Inputmachine -quiet))
            {
            Write-Host "$Inputmachine : Is offline`n" -BackgroundColor Red
            }
        else
            {

            $MACAddress = "N/A"
            $IPAddress = "N/A"
            $IPAddress = ([System.Net.Dns]::GetHostByName($Inputmachine).AddressList[0]).IpAddressToString
            #$IPMAC | select MACAddress
            $IPMAC = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $Inputmachine
            $MACAddress = ($IPMAC | where { $_.IpAddress -eq $IPAddress}).MACAddress
            Write-Host "Machine Name : $Inputmachine`nIP Address : $IPAddress`nMAC Address: $MACAddress`n"
      
            }
    }
}


Tested Result  :) 













Saturday, 21 March 2015

How to connect SQL database using PowerShell










Importing Machine details:  (Connecting to an SCCM database)

I am using an SCCM (System Center Configuration Manager) database to pull some information using powershell, the database contains a table called CM_VRR and the table hold many information which arecollected from local machines as well as the server details.

I know getting the data from a SQL server is not that easy if you have a sql management studio, but in few scenario we need to collect the data very frequently from sql server to provide an input for something else, or getting a small updated information.

If we can use powershell to get those information automatically, that will be great as it saves lot of our time, also we no need to have a sql admin studio installed on our machine.or we no need to open the console.

Below are my SQL server information.

SQL Server 2012 :
Server Name : ADDC-2012
Database : CM_VRR




















SCCM 2012 Console (Front End)

















SQL Query - Used to get the Computer Information (Device Collection Details)



















Now let's start this using powershell !!!

Note the below classes:

Reference : https://msdn.microsoft.com/en-us/library/System.Data.SqlClient


System.Data.SQLClient.SQLConnection : This Represents an open connection to a SQL Server database
System.Data.SqlClient.SqlCommand : Represent a Transact-SQL statement or stored procedure to execute against a SQL Server database
System.Data.SqlClient.SqlDataAdapter : Represents a set of data commands and a database connection that are used to fill the data-set and update a SQL Server database
System.Data.DataSet : Represents an in-memory cache of data.

1 - Opening SQL Connection using System.Data.SQLClient.SQLConnection
We should have the proper permission to the database to access the data using powershell

$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}
$SQLCommand.Connection = $SQLConnection 

2 - Crating stored procedure and adding SQL query using System.Data.SqlClient.SqlCommand.
In the below SQL Query, I have hard-coded the collection Name. If any portion of your sql query is dynamic, you just create a variable in the initial stage and include in your query.

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"

3 - Creating SQL Dataadapter and Dataset. Using System.Data.SqlClient.SqlDataAdapter and System.Data.DataSet


$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset) | out-null

4 - Now your query result is available in $SQLDataset 

$SQLDataset.tables[0













As per the above result, we got two column with System Name and Collection Name based on our query. You may get many columns based on what query you are going to use. So we have to filter the data based on our required column.

Filtering the table data based on column. Below for-each loop will filter the first column ( $data[0] ) of our dataset.
Increase the array value from [0] to get the next column values $data[1

$tablevalue = @()
foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close() 

Putting all together : You have to modify the server details.

$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"
$SQLCommand.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand  
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset) | out-null

$tablevalue = @()
foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close()





Download Script


Monday, 16 March 2015

Powershell output handling using CSV

How to export powershell output to CSV - Using hash table ?


           We have many ways to export a PowerShell output to CSV format, and the simple one is just using Expoer-CSV which is familiar to all... Below is the method exporting a powershell output to CSV format by using hash table. Here we are updating (Appending) hash table from a For-each loop and exporting the result to CSV format. At the end using Expoer-CSV to export the hash table values.Benefit is to make out result as per our customized way.

That's all I am trying here...

Scenario : Just pulling few details of a grop of machines

Technet - Using export-csv

001
Get-Process | export-csv C:\vivek\process.csv

Passing the result to an hash table and exporting to CSV

If we want to export a customized powershell output to CSV file, we can use a hash table to do that. Here I am giving a scenario based example for creating a csv output using hash table.

Scenario : Pinging a group of system and exporting the result to csv file.And also checking the Operating system name and RAM size of the system,

1. Creating an empty hash table to store the result and Getting Machine Name from a .txt file located on our desktop.
You can use your own way to input the system details.

001
002
003
004
$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt
foreach ($sys in $pc)
{

2. Ping checking (Offline) -  This script portion will start based on ping result.If the ping result is false, it just create an hash table with predefined content.And if the result is true, there are some wmi query to check the system details.

Forming hash table and adding the values to PSObject based on ping result
Using [ordered]@ table to keep the order as we given

001
002
003
004
005
006
007
008
009
010
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{
$Result = [ordered]@{
MACHINE_NAME     = "$sys"
PING_STATUS      = "MACHINE OFFLINE"
OS_NAME = "N/A"     
TOTAL_PHYSICALMEMORY = "N/A"
                          }
    $Details += New-Object PSObject -Property $Result
}

We have to create a PSObject to store and append the hash table values. After the for-each loop, we will be exporting this psobject to comma separate value (csv) 

001
$Details += New-Object PSObject -Property $Result

3. Ping Checking (Online) -  As I mentioned previously, this is the true portion of ping result. If the ping result is true, we are checking the OS_NAME and TOTAL PHYSICALMEMORY of the system. 

001
002
003
004
005
006
$osname = "N/A"
$physicalmemory = "N/A"
$sysdetails = "N/A"
$sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory
$osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption
$physicalmemory = ($sysdetails)/1mb -as [int]

Forming hash table and adding the values to PSObject based on ping result

001
002
003
004
005
006
007
$Result = [ordered]@{ 
MACHINE_NAME     = "$sys"
PING_STATUS      = "MACHINE ONLINE"
OS_NAME = "$OSNAME"
TOTAL_PHYSICALMEMORY = "$physicalmemory"
              }
$Details += New-Object PSObject -Property $Result

At the end exporting all the result to .CSV file. I just created a location path with a customized name. You can give anything which you like

001
002
003
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv"
$Details | export-csv -Path $pathofcsv -NoTypeInformation

Output will looks like below  :)












Now putting all the script together :

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
#Machine availability check
#Handling Output using CSV

$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt
foreach ($sys in $pc)
{
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{
    $Result = [ordered]@{
    MACHINE_NAME     = "$sys"
    PING_STATUS      = "MACHINE OFFLINE"
    OS_NAME = "N/A"     
    TOTAL_PHYSICALMEMORY = "N/A"
                          }
    $Details += New-Object PSObject -Property $Result
}
else
{
$osname = "N/A"
$physicalmemory = "N/A"
$sysdetails = ""
$sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory
$osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption
$physicalmemory = ($sysdetails)/1mb -as [int]

    $Result = [ordered]@{ 
    MACHINE_NAME     = "$sys"
    PING_STATUS      = "MACHINE ONLINE"
    OS_NAME = "$OSNAME"
    TOTAL_PHYSICALMEMORY = "$physicalmemory"
              }
    $Details += New-Object PSObject -Property $Result
}
}
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv"
$Details | export-csv -Path $pathofcsv -NoTypeInformation


Download Script