Categories
Data Science

Pandas and SQL similarity 2

This article is the next in the series about the topic of Pandas and SQL similarity which we discussed earlier

Pandas and SQL similarity (2)

Check the dtypes

Pandas –

data type in panda
Data types in pandas

To check data types of the data frame, type ‘df.dtypes’ command

SQL –

type sp_help table_name in the query editor. You may be wondering what is ‘nvarchar’

The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.

You can visit for more information on varchar and nvarchar.

data types in SQL
Data type in SQL
Check the data

Pandas –

Once you load the data in to the pandas you can view the data by running the ‘df.head()’ command. This command will give you output of first 5 rows and all columns by default. This output is not in the order.

pandas head function
pandas head function to get the first n rows.

You can specify the number of rows you want to see e.g. 10

pandas head function
pandas head function to get first 10 rows

To view last rows in the data frame, type ‘df.tail()’ command

pandas tail function
pandas tail function to get last n rows
pandas tail function
pandas tail function to get last 10 rows

We can sort the data frame by column name in the ascending order

pandas sort values
pandas sort function to sort value by column

by descending order

pandas sort values
pandas sort function to sort value by column

SQL –

In SQL we have to use ‘ORDER BY’ Column to view the data.

SQL Sort
SQL sorting by ‘ORDER BY’ Clause in Ascending order
SQL Sort1
SQL sorting by ‘ORDER BY’ Clause in Descending order
Check the NULL Value

Every data set will have some kind of NULL or missing values either in one column or multiple column. Command to find out NULL Value is

Pandas –

df.isnull().sum()

The ‘Product Base Margin’ column has 72 missing values.

pandas null value
pandas isnull function

SQL –

In SQL to get NULL Value we have to run below query for each column.

SELECT * from table where columnname IS NULL

SQL Null Value
SQL ISNULL function
Select the columns

What if we want to see the specific columns, how do we select them?

Pandas –

Note – Pandas will not display all rows in the output by default, to see all rows please run the command –> ‘pd.set_option(‘display.max_rows’, None)’

Pandas select column
Pandas select multiple column

SQL –

select [Row ID],[Order Priority] from table name.

SQL Select column
SQL select multiple column from the table
Filter the data

Every data scientist or data analyst have to filter the data to get some insight. How do we do that in both pandas and SQL?

Pandas –

Lets say, we need details of all the orders shipped through ‘Regular Air’ only

pandas filter
pandas filter function

and sent to California state.

pandas filter command
pandas advance filter function

SQL –

SELECT * from table where [Ship Mode] = ‘Regular Air’

sql filter
SQL filter command
SQL Filter command
SQL advance filter command

Hope you like the article about pandas and SQL command similarity part 2, Stay tuned for next update.

 

 

 

Categories
Data Science

Pandas and SQL

Pandas and SQL are the integral part of Data Science. Pandas is used for data manipulation and analysis while SQL or Structured Query Language is usually used to fetch data, update the contents of the table, or operate on the structure of the database or tables, using any type of database tools, which will have a user interface to apply the operations on the database.

I believe Pandas and SQL are both similar in terms of analyzing the datasets, freshers like me find it difficult to remember the command syntax of both tool. A small effort from my end to show the command similarity.

Let’s Start..

Pandas Installation –

To work on both the tool, first we need to install them. I am using Anaconda distribution on my laptop which comes with all the required library pre-installed. To download Anaconda, please go to their site here

Launch the Jupyter Notebook

Jupyter notebook
Jupyter notebook

Load the pandas module,

pandas import

Note – If you are not using Anaconda, then pandas installation procedure can be found here

SQL Installation –

I am using Microsoft SQL Express edition for the demo purpose, which is available here for the download and installation procedure here.

Note – The only expectation here is that you should know pandas syntax.

Importing the data –

Pandas –

load data in to pandas
pandas data frame

I am using ‘read_excel’ because my source file is Excel and added sheet_name parameter because i want to import only first sheet from the excel file.

SQL –

You can import entire excel in to the SQL database with the help of SQL Import and Export wizard.

SQL Import Export
SQL Import Export

You have to choose source data source as ‘Microsoft Excel’ as shown in below screenshot

SQL Import Export select source
SQL Import Export select source

Choose the Excel file which you want to import and the correct Excel version

SQL Import Export select destination
SQL Import Export destination

Select ‘SQL Server Native Client 11.0’ in the ‘Choose a Destination’ screen

SQL Import Export select destination
SQL Import Export select destination SQL Server

Pick correct server name, authentication type and the database

You can select first option to copy entire data from the excel sheet or you can write custom SQL  query to import only required data. I will go ahead and select first option.

Here please select the sheet you to import under first column, new table will be created in the database with the sheet name (second column).

Select sheet to import in SQL database
Select sheet to import in SQL database

It will import data and will create new table in to the database which we provided.

SQL Table
SQL Table created after import

Lets work on the data now –

How to view entire data

Pandas –
pandas data frame
pandas data frame

‘df’ is nothing but a variable to store the data.  Pandas can store or read different type of data which is called as DataFrame.

You can type ‘print(df)’ or ‘df’ to view the entire data.

SQL –

Connect to the SQL Express using the SQL Management Studio

Click on the ‘New Query’

type below commands in the query field

‘select * from the dbo.Orders

SQL Select query

Hope you enjoyed the article on pandas and SQL, please visit for next set of commands.

Categories
RP4VM

RP4VM consistency group creation API

This post will give you information about step to RP4VM consistency group creation API. I created on PowerCLI script to create consistency group with in build Power Shell  command Invoke-Restmethod.

This script assumes you have two different vCenters. One for Production and one for Disaster recovery. Feel free to customize it based on your requirement.

  1. Source Site is in different VC and
  2. Target site is in different VC.

Please refer below script.

Note – You need to create RP4VM Cluster on both sides, need to join them before running this script. I wrote OVA Deployment script and can be found here RP4VM OVA Deployment via PowerCLI

$global:DateTime = $((Get-Date).ToString('yyyy-MM-dd-hh-mm')) 
$logpath = "$HOME\Desktop\CGCreation-log-$global:DateTime.txt"
Start-Transcript -path $logpath -append 


& 'C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Scripts\Initialize-PowerCLIEnvironment.ps1'

Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -DefaultVIServerMode Multiple
$cred = Get-Credential
$RPCred = Get-Credential
Connect-VIServer Sourcevcenter1, Targetvcenter2 -Credential $cred

##### Collect vCenter Instance UID ################

$vc = $global:DefaultVIServers | sort Name

$sourcevc = $vc[0].InstanceUuid
$targetvc = $vc[1].InstanceUuid

###############  Change setting to ignore TLS/SSL Error ################################
if (-not ([System.Management.Automation.PSTypeName]'ServerCertificateValidationCallback').Type)
{
$certCallback = @"
    using System;
    using System.Net;
    using System.Net.Security;
    using System.Security.Cryptography.X509Certificates;
    public class ServerCertificateValidationCallback
    {
        public static void Ignore()
        {
            if(ServicePointManager.ServerCertificateValidationCallback ==null)
            {
                ServicePointManager.ServerCertificateValidationCallback += 
                    delegate
                    (
                        Object obj, 
                        X509Certificate certificate, 
                        X509Chain chain, 
                        SslPolicyErrors errors
                    )
                    {
                        return true;
                    };
            }
        }
    }
"@
    Add-Type $certCallback
 }
[ServerCertificateValidationCallback]::Ignore()

$dc = 'dc name'

foreach ($storenumber in $dc){

######### Get source vRPACluster IP ######################################

$ucscluster = Get-Cluster $storenumber -Server $vc[0] | Get-VM | where {$_.Name -like "*vRPA1"} |Select Name, @{N="IPAddress";E={@($_.guest.IPAddress[0])}}

$split = $ucscluster.IPAddress

$IPbyte = $split.Split(".")
$newucsip = ($IPByte[0]+"."+$IPByte[1]+"."+$IPByte[2])

$startip = "102"

$clusipaddress = "$newucsip.$($startip+$_)"


############# Get vRPA Cluster Details ###########################


$uri = "https://$clusipaddress/fapi/rest/5_2/settings"

$jsonnew = Invoke-RestMethod -Uri $uri -Method Get -credential $RPCred -ContentType application/json

############## RPA Cluster Name ####################

$ucsRPAClustername = $jsonnew.systemSettings.clustersSettings.clusterName[1]
$targetvRPAClustername = $jsonnew.systemSettings.clustersSettings.clusterName[0]

############### source and Target RPA Cluster ID  ################

$ucsRPAClusterID = $jsonnew.systemSettings.clustersSettings.clusterUID[1].id
$targetRPAClusterID = $jsonnew.systemSettings.clustersSettings.ClusterUID[0].id


############### Get Array ID ########################

$sourcearrayUID = $jsonnew.systemSettings.clustersSettings.ampsSettings.managedArrays[1].arrayUID.id

$targetarrayUID = $jsonnew.systemSettings.clustersSettings.ampsSettings.managedArrays[0].arrayUID.id


##################Storage ResourcePoolID ################

$storageDSUID = $jsonnew.systemSettings.clustersSettings.ampsSettings.managedArrays[1].resourcePools.resourcePoolUID | sort StorageResourcePoolId

$sourceFirstDSID = $storageDSUID.StorageResourcePoolID[0]
$sourceSecondDSUID  = $storageDSUID.StorageResourcePoolID[1]

$DSUid = @()

 $DSUid += $sourceFirstDSID 
$DSUid += $sourceSecondDSUID  

$DS = 0

$targetDSUID = $jsonnew.systemSettings.clustersSettings.ampsSettings.managedArrays[0].resourcePools.resourcePoolUID.storageResourcePoolId

#$storage = (Get-Cluster $storenumber -Server $vc[1] | Get-VMHost | select -First 1 | Get-Datastore | where {$_.Name -like "VxRail*"}).ExtensionData.info.containerid


################### Pool ID ###############

$UCSPoolID = $jsonnew.systemSettings.clustersSettings.ampsSettings.managedArrays[1].resourcePools.resourcePoolUID | sort StorageResourcePoolId

$UCSPoolIDFirst = $UCSPoolID.uuid[0]
$UCSPoolIDSecond = $UCSPoolID.uuid[1]

$targetPoolID = $jsonnew.systemSettings.clustersSettings.ampsSettings.managedArrays[0].resourcePools.resourcePoolUID.uuid[0]
 

 ############### Target ESXi UID #####################

 $RestURL = Invoke-RestMethod https://$clusipaddress/fapi/rest/5_2/clusters/$targetRPAClusterID/virtual_infra_configuration -Method Get -credential $RPCred

 $targetESXiClusterID = $RestURL.virtualCentersConfiguration.datacentersConfiguration.esxClustersConfiguration.esxsConfiguration | sort Name

 $firstESX = $targetESXiClusterID.esxUID[0].uuid

 #$box = (Get-Cluster '04' -Server $vc[1] | Get-VMHost | select -First 1).extensiondata.hardware.systeminfo.uuid
 $secondESX = $targetESXiClusterID.esxUID[1].uuid
 $thirdESX = $targetESXiClusterID.esxUID[2].uuid
 $sample = @()

 $sample += $firstESX 
  $sample  += $secondESX
  $sample  += $thirdESX

 $targetDatacenterUID = $RestURL.virtualCentersConfiguration.datacentersConfiguration.datacenterUID.uuid

 $sourcecluster = Get-Cluster $storenumber -Server $vc[0]
 $i = 0
 foreach ($vm in $sourcecluster | Get-VM | where {$_.powerstate -ne "poweredoff" -and $_.Name -notlike "*Vs*" -and $_.Name -notlike "Prod*"-and  $_.Name -notlike "OST-$($sourcecluster)-W16C"} | sort name){

 if ($vm.Name -like "*Windows2016*"){

 $cgname = $sourcecluster.Name + "_" + $vm.Name + "_CG" }

 else{
 
 $cgname = $vm.Name + "_CG"}

 #$datastore  = $DSUid[$DS]

 ########## Collect VM UID ######################
 $vmuid = ($vm).extensiondata.config.instanceUuid

 $vmuid

 #### Calculate 30% of Total VM size ##########################

 $hdd = $vm | Get-HardDisk | Measure-Object -Sum CapacityGB
 $sourceHDD = [math]::Round($hdd.Sum * (20/100))

 $targetHDD = [math]::Round($hdd.Sum * (30/100))
 
 ##### Journal size in bytes #############

 $sourcejournalsize = [math]::Round($sourceHDD*1024*1024*1024)

 $targetjournalsize = [math]::Round($targetHDD*1024*1024*1024)

 $box = $sample[$i]
 $datastoreid = $DSUid[$DS]

  $json =  @"
  {
     "cgName" : "$cgname",
     "productionCopy" : {
          "clusterUID":{
               "id" : $ucsRPAClusterID
          },
          "copyUID": 0
     },
     "vmReplicationSets":
     [{
               "replicationSetVms":
               [{
                         "copyUID": {
                              "clusterUID": {
                                   "id": $targetRPAClusterID
                              },
                              "copyUID": 1
                         },
                         "vmParam": {
                              "JsonSubType": "CreateVMParam",
                              "targetVirtualCenterUID": {
                                   "uuid": "$targetvc"
                              },
                              "targetResourcePlacementParam": {
                                   "JsonSubType": "CreateTargetVMManualResourcePlacementParam",
                                   "targetEsxUID": {
                                        "uuid": "$box"
                                   }
                              },
                              "targetDatastoreUID": {
                                   "uuid": "$targetDSUID"
                              }
                         }
                    }, {
                         "copyUID": {
                              "clusterUID": {
                                   "id": $ucsRPAClusterID
                              },
                              "copyUID": 0
                         },
                         "vmParam": {
                              "JsonSubType": "ExistingVMParam",
                              "vmUID": {
                                   "uuid": "$vmuid",
                                   "virtualCenterUID": {
                                        "uuid": "$sourcevc"
                                   }
                              }
                         }
                    }
               ],
               "virtualHardwareReplicationPolicy": {
                    "provisionPolicy": "SAME_AS_SOURCE",
                    "hwChangesPolicy": "REPLICATE_HW_CHANGES"
               },
               "virtualDisksReplicationPolicy": {
                    "autoReplicateNewVirtualDisks": "true"
                   
               }
          }
     ],
     "links":
     [{
               "linkPolicy": {
                    "JsonSubType": "ConsistencyGroupLinkPolicy",
                    "protectionPolicy": {
                         "protectionType": "ASYNCHRONOUS",
                         "syncReplicationLatencyThresholds": {
                              "resumeSyncReplicationBelow": {
                                  "value": 3000,
                                   "type": "MICROSECONDS"
                              },
                              "startAsyncReplicationAbove": {
                                   "value": 5000,
                                   "type": "MICROSECONDS"
                              },
                              "thresholdEnabled": "true"
                         },
                         "syncReplicationThroughputThresholds": {
                              "resumeSyncReplicationBelow": {
                                   "value": 35000,
                                   "type": "KB"
                              },
                              "startAsyncReplicationAbove": {
                                   "value": 45000,
                                   "type": "KB"
                              },
                              "thresholdEnabled": "false"
                         },
                         "rpoPolicy": {
                              "allowRegulation": "false",
                              "maximumAllowedLag": {
                                   "value": 25,
                                   "type": "SECONDS"
                              },
                              "minimizationType": "IRRELEVANT"
                         },
                         "replicatingOverWAN": "true",
                         "compression": "LOW",
                         "bandwidthLimit": "0.0",
                         "measureLagToTargetRPA": "true",
                         "deduplication": "true",
                         "weight": 1
                    },
                    "advancedPolicy": {
                         "snapshotGranularity": "DYNAMIC",
                         "performLongInitialization": "true"
                    },
                    "snapshotShippingPolicy": null
               },
               "linkUID": {
                    "groupUID": {
                         "id": 0
                    },
                    "firstCopy": {
                         "clusterUID": {
                              "id": $ucsRPAClusterID
                         },
                         "copyUID": 0
                    },
                    "secondCopy": {
                         "clusterUID": {
                              "id": $targetRPAClusterID
                         },
                         "copyUID": 1
                    }
               }
          }
],
       "copies":
     [{
               "copyUID": {
                    "clusterUID": {
                         "id": $targetRPAClusterID
                    },
                    "copyUID": 1
               },
               "copyName": "copy1",
               "JsonSubType": "ConsistencyGroupCopyParam",
               "volumeCreationParams": {
                    "volumeParams":
                    [{
                              "JsonSubType": "VolumeCreationParams",
                              "volumeSize": {
                                   "sizeInBytes": $targetjournalsize
                              },
                              "arrayUid": {
                                   "id": $targetarrayUID,
                                   "clusterUID": {
                                        "id": $targetRPAClusterID
                                   }
                              },
                              "poolUid": {
                                   "uuid": $targetPoolID,
                                   "storageResourcePoolId": "$targetDSUID",
                                   "arrayUid": {
                                        "id": $targetarrayUID,
                                        "clusterUID": {
                                                      "id": $targetRPAClusterID
                                        }
                                   }
                              },
                              "tieringPolicy": null,
                              "resourcePoolType": "VC_DATASTORE"
                         }
                    ]
               }
          }, {
               "copyUID": {
                    "clusterUID": {
                         "id": $ucsRPAClusterID
                    },
                    "copyUID": 0
               },
               "copyName": "$vm",
               "JsonSubType": "ConsistencyGroupCopyParam",
               "volumeCreationParams": {
                    "volumeParams":
                    [{
                              "JsonSubType": "VolumeCreationParams",
                              "volumeSize": {
                                   "sizeInBytes": $sourcejournalsize
                              },
                              "arrayUid": {
                                   "id": $sourcearrayUID,
                                   "clusterUID": {
                                        "id": $ucsRPAClusterID
                                   }
                              },
                              "poolUid": {
                                   "uuid": $UCSPoolIDFirst,
                                   "storageResourcePoolId": "$datastoreid",
                                   "arrayUid": {
                                        "id": $sourcearrayUID,
                                       "clusterUID": {
                                                                        "id": $ucsRPAClusterID
                                        }
                                   }
                              },
                              "tieringPolicy": null,
                              "resourcePoolType": "VC_DATASTORE"
                         }
                    ]
               }
          }
    ], 
     "startTransfer" : false,
     "JsonSubType":  "ReplicateVmsParam"
     }
"@
     
     if($i -eq 2){
           $i = 0
		  }
     else{
     $i ++
	 }

    if($DS -eq 1){
    $DS = 0
	}
   else {
   $DS ++
   
   }

        #$body = $json | ConvertTo-Json;

        #$test = $body | ConvertTo-Json;
 
 $REST= "https://$clusipaddress/fapi/rest/5_2/groups/virtual_machines/replicate"

try{

$response = Invoke-RestMethod -Method Post -Uri $REST -Body $json -ContentType application/json -credential $RPCred -OutFile $HOME\Desktop\test_protection.txt



}
Catch [System.Net.WebException] { $exception = $_.Exception
 $respstream = $exception.Response.GetResponseStream()
$sr = new-object System.IO.StreamReader $respstream
$ErrorResult = $sr.ReadToEnd()
 write-host $ErrorResult 
}


catch {
    # Dig into the exception to get the Response details.
    # Note that value__ is not a typo.
    Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__ 
    Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription
}

sleep -Seconds 180
 }
 
 ##############  Start CG replication #########################

 $state = "https://${clusipaddress}/fapi/rest/5_2/groups/transfer_state"

$json = Invoke-RestMethod  -Uri $state  -Method Get -ContentType "application/json" -credential $RPCred | ConvertTo-Json

$object = ConvertFrom-Json -InputObject $json

$object.innerSet | select -Property sourceClusterName, groupName, transferState  > $HOME\desktop\$clus.txt
$CG = $object.innerSet | sort Name| select -First 5
foreach($_ in $CG){ if($_.transferState -eq "PAUSED" ){

$groupID = $_.consistencyGroupUID.trim("@{id=}")

$data = @{

id = "$groupID"
};

$body = $data | ConvertTo-Json;

$transfer = "https://${clusipaddress}/fapi/rest/5_2/groups/$groupID/start_transfer"

Invoke-RestMethod -Uri $transfer -Credential $RPCluster -Method PUT -ContentType "application/json" -Body $body -credential $RPCred
 
 }
 }
 }

 
#################################################################

Stop-Transcript

Copy-Item $HOME\Desktop\CGCreation-log-$global:DateTime.txt "Log collection location path" -Confirm:$false -Force

Disconnect-VIServer * -Confirm:$false -Force

 

I have added replication step at the end of script.

Please do due diligence before running this RP4VM consistency group creation API script in the production environment.  I am working on it to improve it, if you have any suggestions feel free to drop me a message or send me an email.

Categories
VMware

OVA deployment ESXi local datastore

This article is about a script for OVA deployment from ESXi  local data store. This idea came to my mind after working on one project where i had to deploy OVAs on around 250 ESXi clusters.

Here i used VMware OVFTool and Selenium webdriver. You can Download  both software from the links mentioned below. Import-Vapp command from powercli doesn’t support HTTPS source for OVA deployment from ESXi local data store. You can check its help section for more information.

Import-v App script can be found here OVA Deployment via PowerCLI

OVFTOOL Download

Selenium

Explanation Line by Line-

1 – Path to OVFtool exe.

3-5 –  I have added Selenium binary or DLL using Add-type method.

7 – Path to chrome driver to open ESXi local data store webpage and fetch the HTTPS link

8 -9  – We would need ESXi root credentials here

11- Enter the v Center details

13 – Location of the OVA which needs to be uploaded to local data store

15 – Folder which we will create inside local data store to store OVA

17-30 – Code to upload OVA on local data store of first ESXi inside Cluster

30 -36 – v Switch and port group information to create VMKernel on ESXi hosts.

42-52 – Collecting IP setting for the OVAs

54 – 60 – Collect Names for the OVAs

63 – 68 – Port group and local data store information.

72-83 –  Use of Selenium and Chrome driver to access ESXi local datastore and fetch HTTPS source path

86 – 115 –  Commands to deploy OVA on ESXi hosts with OVFTOOL and VMKernel creation commands.

$ovftool =  "C:\Program Files (x86)\VMware\VMware OVF Tool\ovftool.exe"

Add-Type -Path 'C:\Users\TKMAMIL\Desktop\OVFTOOL\Vipal\selenium-dotnet-3.14.0\dist\WebDriver.dll'
Add-Type -Path 'C:\Users\TKMAMIL\Desktop\OVFTOOL\Vipal\selenium-dotnet-3.14.0\dist\WebDriver.Support.dll'
Add-Type -Path 'C:\Users\TKMAMIL\Desktop\OVFTOOL\Vipal\selenium-dotnet-3.14.0\dist\Selenium.WebDriverBackedSelenium.dll'

$chromedriver =  'C:\Users\vipal\Desktop\user\selenium-dotnet-3.14.0\dist\chromedriver.exe'
$esxpassword = Read-Host -Prompt 'Enter password'

$users = "root"
Connect-VIServer vcenter -User username -Password password

$clusters = Get-Cluster '0673'

$filername = "C:\Users\TKMAMIL\Desktop\RP_for_VMs_5.1.SP1.P5_Installation_Kit_md5_1c01d803b1e6917d3f5e657057955c46\RP_for_VMs_5.1.SP1.P5_Installation_Kit_md5_\EMC_RecoverPoint_vRPA-RP4VMs_rel5.1.SP1.P5_d.246_md5_cf06f6f4b7d0166dc9add0d2926eaccc.ova"

$tgt = 'RP4VM'

foreach ($clus in (Get-Cluster $clusters| sort Name)){

######### Upload OVA to local datastore#######################

$ds = Get-Cluster $clus | Get-VMHost | sort Name | select -First 1 | Get-Datastore | where {$_.name -like "*local*"}

New-PSDrive -Location $ds -Name vg -PSProvider VimDatastore -Root "\" > $null

$position = 0

if(!(Test-Path -Path "vg:/$($tgt)")){

New-Item -ItemType Directory -Path "vg:/$($tgt)" > $null
}

$position ++ 

Write-Progress -Activity "Copying OVA to $ds" -Status "Copying File $filername" -PercentComplete (($position/$filername.count)*100)
 
Copy-DatastoreItem -Item $filername -Destination "vs:/$($tgt)" 

Remove-PSDrive -Name vg -Confirm:$false 
 #################################################################3

 $vswitch = 'vSwitch5'

$pg = 'RP4VM_VMKernel'
$port = 'vRPA_Data'
$subnet = 'subnet'
$subnetprefix = 'x.x.x'
$ipperserver = 1
$firstip = 40
#################################################

#########################################


## collect IP information #########
$IPS = @()
$ucsip = Get-Cluster $clus | Get-VMHost | select -First 1 | Get-VMHostNetwork | select DnsAddress

$split = $ucsip.dnsaddress

$IPbyte = $split[0].Split(".")
$newucsip = ($IPByte[0]+"."+$IPByte[1]+"."+$IPByte[2])

$startip = "103"
$endip = "104"
$gateway = "254"

$netmask = "subnet"

$ucsipaddress = "$newucsip.$($startip+$_)"
$ucsipaddress1 = "$newucsip.$($endip+$_)"
$ucsgateway = "$newucsip.$($gateway+$_)"

$IPS += $ucsipaddress
$IPS += $ucsipaddress1

$internet = 0
#########################################

###########Collect VM name##################

#$VMNAME = @()

#$vm = "Prod$($clus)_vRPA1"
#$vm1 = "Prod$($clus)_vRPA2"

#$VMNAME += $vm
#$VMNAME += $vm1

$i = 1

#################################


########## Select PortGroup #############

$portgoup = get-cluster $clus | Get-VMHost|select -First 1 | Get-VirtualPortGroup |where {$_.name -like "*VMN*"}

###########################
$esx = Get-cluster $clus|Get-VMHost|sort Name | select -First 1

$localdata = $esx | Get-Datastore | where {$_.name -like "*Local*"}

########collect  Source path#######


$options = New-Object -TypeName "OpenQA.Selenium.Chrome.ChromeOptions"
$options.AcceptInsecureCertificates = $true

$driver = New-Object -TypeName "OpenQA.Selenium.Chrome.ChromeDriver($options, $chromedriver)"

$driver.Url = 'https://' + $users + ':' + $esxpassword + '@' + $esx + '/folder'

sleep -Seconds 5

$driver.FindElementByLinkText('ha-datacenter').click()

sleep -Seconds 5

$driver.FindElementByLinkText($localdata).click()

sleep -Seconds 5

#$driver.FindElementByLinkText('iSCSI').click()


$driver.FindElementByLinkText('RP4VM/').click()

sleep -Seconds 5

$source = $driver.FindElementByXPath('/html/body/table[2]/tbody/tr[4]/td[1]/a')

$link = $source.GetAttribute('href')


###################################
    
foreach ($esxhost in (Get-cluster $clus | Get-VMHost | sort Name )){
$virtual = "Prod$($clus)_vRPA" + $i
$datastore = get-cluster $clus | Get-Datastore | where {$_.name -like "$($clus)0" + $i }
$i ++

$protocol = $IPS[$internet]
$internet ++

#$ucsdatastore = $Data[$datastoreloop]
#$datastoreloop ++

Start-Process $ovftool -ArgumentList --X:logFile=ovftool.log, --X:logLevel=trivia,  --acceptAllEulas,--noSSLVerify,--disableVerification,--allowExtraConfig, --poweron, --deploymentOption=Med+, --datastore=$datastore, --network=$portgoup, --prop:ip=$protocol, --prop:netmask=$netmask, --prop:gateway=$ucsgateway, --name=$virtual, $link, vi://tkmamil:abc12DEFghi@vcenter:443/Stores/$clus/host/$clus -NoNewWindow -Wait -RedirectStandardOutput C:\Users\TKMAM\Desktop\output.txt

##### Create Portgroup for vRPA ##############################

Get-VirtualSwitch -VMHost $esxhost -Name $vswitch | New-VirtualPortGroup -Name $port

Get-VirtualSwitch -VMHost $esxhost -Name $vswitch | New-VirtualPortGroup -Name $pg

###############################################################

0..$($ipperserver-1) | %{

$ipaddress = "$subnetprefix.$($firstip + $_)"
$firstip += $ipperserver
New-VMHostNetworkAdapter -VMHost $esxhost -VirtualSwitch $vswitch -IP $ipaddress -SubnetMask $subnet -PortGroup $pg
}
}
$driver.Close()
$driver.Dispose()
$driver.Quit()
}

Hope you liked the OVA deployment from ESXi local datastore script.  Please change the script based on your requirements and share your thoughts.

Categories
VxRail

VxRail – VSAN Software Component

I am starting new blog series on VxRail (Hyper Converged Product). To start with this, i thought about writing VxRail  – VSAN Software component which will help in better understanding of VxRail and VSAN.

VSAN Software Component

Cluster Level Object Manager (CLOM)

•An instance of CLOM runs on all the vSAN nodes
•/etc/init.d/clomd [start | stop | restart | status]  (Need to check on VxRail Appliance)
•Checks if object can be created based on available resources
•Ensures policy compliance of an object
•Is responsible for initial placement, migration and replacement of components
•Distributes the load equally across all the hosts

Distributed Object Manager (DOM)

•An instance of DOM runs on all the vSAN nodes
•Creates local components of an object once CLOM agrees to create an object
•Instructs DOM on other nodes to create the remaining components
•Resyncs the objects during recovery
•Defines three roles: Owner, Client and Component Manager

DOM Owner 

•Provides distributed access path to the Object
•There is one owner assigned to each DOM Object
•Owner also determines who is allowed to send I/Os to the object

DOM Client

•DOM Client performs I/Osto the object on behalf of a VM
•It runs on every host that runs a VM
•Typically resides with DOM Owner

Local Log-Structured Object Manager (LSOM)

•Performs SSD log recovery when the host boots
•Interacts with the MD and SSD directly
•Provides persistence of storage for the components
•Provides read and write buffering
•Reports unhealthy drives
•Performs I/O retries to the failing drives

•Reliable Datagram Transport (RDT)

•Network protocol for the transmission of vSAN traffic

•Cluster Membership, Monitoring and Directory Services (CMMDS)

•DOM, CLOM may use CMMDS database to learn about the topology and object configuration
•Requires multicast network to be able to form a cluster (pre vSAN 6.6)
•CMMDS is used to elect owners for objects
•It maintains inventory of items such as hosts, devices and networks
•Stores object metadata info such as policies, distributed RAID etc. in             an in-memory database
•CMMDS discovers, maintains and establishes a cluster of networked node members
•Defines four cluster roles: Master, Backup, Agent and witness host(forstretched clusters)
•The roles have nothing to do with managing objects in the cluster or doingI/Os to the objects
•The backup speeds up the process of convergence if master fails
•The roles are decided at the time of cluster discovery
•Updates are exchanged with the help of RDT
•Master to other hosts – > 224.1.2.3:12345  (Every second)
•Other hosts to Master -> 224.2.3.4:23451 (Updates)

For more information about VSAN, please refer VMware VSAN



Categories
PTE

PTE – Speaking and writing – Part 1

PTE Introduction – Introduction.This section will help you with PTE Speaking and writing information.

To assess speaking your skills will be tested by your ability to produce spoken English in an academic environment and writing section will require you to write response in academic English using correct Grammar and spelling.

Speaking 1.1 – Personal Introduction

The personal introduction is an opportunity for you to give some information about yourself. You will get 25 second to read the prompt and prepare your response, and 30 second to give response. This section will not be scored. You can give introduction in below format

  • Your name, where are you working currently
  • Your hobbies, what is importance of English in your life
  • Why did you choose PTE

Speaking 1.2  – Read a loud

You have 30-40 seconds to prepare, depending on the length of the passage. You hear a short tone. After the tone, start speaking immediately. Do not start speaking before the microphone opens because your voice will not be recorded. You should speak clearly. There is no need to rush.

Finish speaking before the progress bar reaches the end. The word “Recording” changes to “Completed”.

Tip -You can record only once and if you pause for 3 second then recording will be stopped.

Scoring –  You will be scored based on Content, Pronunciation and oral fluency

Content:  5 ---- 0 points
Pronunciation:  5 --- 0 points
Oral Fluency:  5 --- 0 points.

Content is not about what you do, it is about what you should not do.

You should not  – 

1. Misread a word - you say a different word then mentioned in paragraph / text. e.g. When someone ---- and you say when somebody
2. Skip a word - means you leave / don't mention a word  e.g. when someone offers afree sample.   here you missed "a"
3. Add means - you add a word that does not belong their.  e.g. when "a" someone offer. You added "a"

Be careful with

  • Articles –   a / an / the
  • Preposition –  in / on / at / to / for / with
  • Plural – Nouns –> Its very IMP to correctly pronounce plural forms, don’t skip “s”

Top Score

5  Native like - Means native English speaker can easily understand you
4. Advanced - There were mistakes but i (native speaker) get you
3. Good - Interference with first language
2. Intermediate - What is that, i (native speaker) could not understand.
1. Low - Sorry ?
0. Non-English - What?

Top score “5” means – all “44” sounds of English are made correctly. Accent is fine but PTE focus on if you are capable of making all 44 sounds.

Understand what you are reading, if you understand what you are reading then you will read it naturally like pause at right place, correct pronunciation.

Read a loud means – Express the meaning as much as you can, as if you are giving speech or conveying a message.

Do’s (Read) –

  1. Loud a possible and with confidently
  2. Moderate pace, not quietly
  3. Meaning
  4. If you don’t know word, you can pretend.

Don’t –

  1. skip, misread or add a word
  2. Hesitate, repeat word
  3. Restart sentence
  4. “um” and “ahh”

For practice you can refer   E2Language  or  Tutorials  

Speaking 1.3 – Repeat Sentence

In this section,you need to repeat the sentence you hear.The audio begins to plays automatically. When the audio finishes, the microphone opens and the recording status box shows “Recording”. Speak into the microphone immediately (there is no short tone) and repeat exactly what you heard. You should speak clearly. There is no need to rush.

Finish speaking before the progress bar reaches the end. The word “Recording” changes to “Completed”. You are not able to replay the audio and only able to record your response once.

You will get 3 sec to prepare to listen, please take a deep breath. Question will have single sentence and repeated one time only so you must listen carefully. There are chances you might get 10-12 repeat a sentence question.

Scoring

Content – It has max 3 points per sentence. If you repeat every word in correct sequence, you will get 3 points. You get 2 points if you can repeat more than 50% of words in the correct sequence and 1 point if you repeat less than 50 % word in correct sequence.You should repeat the sentence with correct grammar.

Pronunciation and oral fluency –  You get 5 points for native like pronunciation and oral fluency.

Tip  –

  • Try to copy the rhythm and speed from the audio.
  • Sentence will generally have 10-15 words.
  • Take care of pronouncing “s” at correct place.
  • Sentence will last for 3-4 sec, you should be able to finish in same time with no errors.

Speaking 1.4 :  Describe image

You will get one graph / diagram or image in this question. 25 seconds will be provided to prepare and 40 seconds for answer.

Types of images –

  1.  Bar chart
  2. Double Bar chart
  3. Two bar chart
  4. Pie chart
  5. Double pie chart
  6. Diagram or process
  7. Table
  8. Map
  9. Multiple graph

You might get 5-6 images on test day. You will be judges or marked based on below 3 main points

  1.  Content  – You should describe the image and not label it.
  2. Pronunciation – How clearly describe image
  3. Oral Fluency – How smoothly you describe image

Use below method to describe the image –

 a. Introductory sentence ( in 5 Sec ) –

You should start like   ” This —– represents “the title” + x-axis.”

lets take above bar chart example –

“This bar chart represents the top oil reserves in various countries like Venezuela, Saudi, Canda,Iran, Russia, UAE, US and China in Million barrels.

We have to arrange the title, so it makes grammatical sense. First we have to identify the type of graph, then looks at the title and X and Y axis.

 b. Image Description (in 25 Sec)-

You should describe image in such a way that it cover all aspects of image.  We can describe above image in following way

“Venezuela has the most amount of oil at close to 300,000. This is followed closely bySaudi Arabia, Canada and then Iran. The US and China have the least amount ofoil – well below 1 million barrels each”

c. Conclusion (in 10 sec) –  

Actually you will get 10 Sec, but i would suggest to do it in 5 sec. You can start like  “Overall we can say that ……..” to show it as we are concluding the image description

We can conclude above image like  “Overall, this graph shows a steep and gradual decline in the amount of oil that each country has and Venezuela has far more oil than the US and China.”

Categories
PTE

How to Prepare Pearson Test of English (PTE)

This time i have decided to write a non-technical stuff. Recently i cleared my PTE and thought of writing what is PTE and how to prepare for it.

What is PTE?

PTE Academic is a computer-based academic English language test aimed at non-native English speakers wanting to study abroad and for immigration purpose. It tests reading, writing, Speaking and Listening.

Questions often test 2 skills together, such as listening and reading or reading and speaking. The whole test is done in a single session, lasting 3 hours and is taken sitting at a computer in a secure test environment. The speaking part of the exam is done at the computer. Your voice is recorded and sent for marking. One advantage of this test is that you get your results very quickly; in about 5 days.

More detailed information about PTE academic Here

Who accept PTE

PTE is accepted by number of university worldwide and countries like Australian and New Zealand for Visa application.

More information can be found Here

Test Format

There are twenty different question formats, ranging from multiple choice through to essay writing and interpreting information.PTE Academic assesses real-life, academic English, so you will hear excerpts from lectures and view graphs and charts. You will hear a range of accents in the test, from British and American to non-native speakers, so you will be exposed to the type of accents you will encounter in everyday life.

PTE Academic assesses real-life, academic English, so you will hear excerpts from lectures and view graphs and charts. You will hear a range of accents in the test, from British and American to non-native speakers, so you will be exposed to the type of accents you will encounter in everyday life.

Part 1: Speaking & Writing (77 – 93 minutes)

  • Personal introduction
  • Read aloud
  • Repeat sentence
  • Describe image
  • Re-tell lecture
  • Answer short question
  • Summarize written text
  • Essay (20 mins)

Part 2: Reading (32 – 41 minutes)

  • Multiple choice, choose single answer
  • Multiple choice, choose multiple answers
  • Re-order paragraphs
  • Reading: Fill in the blanks
  • Reading & writing: Fill in the blanks

Part 3: Listening (45 – 57 minutes)

  • Summarize spoken text
  • Multiple choice, choose multiple answer
  • Fill in the blanks
  • Highlight correct summary
  • Multiple choice, choose single answer
  • Select missing word
  • Highlight incorrect words
  • Write from dictation

Please check Part 1 Speaking and writing

Categories
VMware

How to use plink to run command from windows

Recently i had to run some commands on 250 linux servers in one of my project. So i decided to use plink.exe from windows machine. This section will help you how to use plink to run linux command from windows on multiple server

plink.exe without sudo privilege 

plink.exe -ssh -t -pw <password> -l <user>@<host>  -m <script> <IP Address of host>

syntax –
-m – create filename called “script.txt” or the name you want and place it under folder where plink.exe is located
-pw – password of remote host
-l – user id to login to remote host
ip address of host
-t – it will open pseudo shell

lets say script.txt include,
hostname
pwd
ls /tmp

Above command will show output on your local windows machines cmd.exe or powershell

if the host key was not cached in the registry yet, you would get an interactive prompt saying “Store key in cache? (y/n)” which would not be conducive to a batch script.
And once again, the way around this is to pipe a “y” to stdin, which makes the command:
cmd.exe /c echo y | plink.exe -ssh -t -pw myP4ss -l myuser@trusty1 -m script.txt <IP Address of host?


plink and sudo with password prompt

In the above example we dealt with simple commands that required no input and no privilege escalation with sudo (hostname, pwd).  But many times the commands we want to run in batch processes require sudo, which throws up an interactive prompt for a password.

Sudo password echoed and embedded inside plink script

Make sure you call plink with “-t” so that a tty is setup.  Then sudo to a shell and echo the password into stdin before running the command you want with sudo.

/bin/echo -e "myP4ss\n" | sudo -S /bin/bash
sudo apt-get update

Or you can echo the password directly to the command you want to run as sudo

/bin/echo -e "myP4ss\n" | sudo apt-get update

Run the command on number of hosts using powershell

First you need to create one text file with list of remote server IPS or FQDN (if DNS is configured properly) in the text file and place it under same folder where plink.exe is located

get-content .\linux_Server.txt | foreach {invoke-command -command {& echo y | .\plink.exe $_ -l <username> -pw <password> -m <file with list of command>; write-host -fore “$_ host finished”}}

This command will ssh in to each host under .\linux_Server.txt and execute set of commands defined in <file with list of command>

Hope this helps… feel free to comment with  suggestion,

Categories
VMware

PowerCLI Script to assign IP on each host in ESX Cluster

Recently i came across a situation where i had to create and assign IP to vmkernel portgroup (for vmotion) for 3300 ESX host. So i wrote one basic powercli script to assign IP on each host in ESX Cluster.

============================================================

PowerCLI Script to create VMKernel portgroup and assign IP on each host in cluster 

#  Note – Replace respective name wherever applicable.  Below sample is for one cluster, you can customize for all cluster in vcenter.

$vmhost = get-cluster 0000 | get-vmhost | sort Name

$pg = $vmhost[0] | get-virtualportgroup | where {$_.name -like ‘vSphere*’}

$subnetprefic=’xxxx’     # e.g. ‘192.168.10’
$ipperserver = 1
$startip = 4

foreach ($esx in $vmhost){

0..$($ipperserver-1)|%{
$ipaddress = “$subnetprefix.$($startip + $_)”
$startip += $ipperserver

new-vmhostnetworkadapter -vmhost $esx -virtualswitch $vdswitch -ip $ipaddress -subnet $subnet -portgroup $pg
}
}

Hope this short script helps.. Happy reading!!

Categories
VMware

OVA Deployment via PowerCLI

Recently i was working on project where i had to deploy RP4VM OVA on more than 400 ESXi hosts. So i decided to write article for  OVA deployment via PowerCLI

Initially i faced a lot of issue like  Deployment was stuck during ‘validation’, i couldn’t run simultaneous deployment in vcenter because of existing vcenter issue etc.

We used naming convention like VxRAIL(DC name)_vRPA1. You can change it if required.

We had to create VMKernel port on esxi hosts for other use, this is also included in last section of the script.

Here is the powercli code which i wrote, explained line by line

1 –  It will load all PowerCLI module if you want to run script from PowerShell

3- Enter the vCenter name and username, password

5 – Please mention path to ova

 7 – Please mention cluster name where you want to deploy OVA

11 – 19 – We used naming convention e.g . VxRail(Clustername)_vRPA1.

You can change as per your need.

23-26 – IP Settings for VMKernel port on ESXi hosts

29-51 – IP setting for OVA

54-81 –  Loop for OVA deployment on ESXi host in cluster

85-99 – Port group and switch info  for VMkernel assignment on ESXi.

Newer version of script available here

& 'C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Scripts\Initialize-PowerCLIEnvironment.ps1'

Connect-VIServer vcenter -User username -Password password

$filername = "path to OVA"

$storenumber = 'number / name'

#####################################################

$VMNAME = @()

$vm = "VxRail$($storenumber)_vRPA1"
$vm1 = "VxRail$($storenumber)_vRPA2"

$VMNAME += $vm
$VMNAME += $vm1

$i = 0

######################################################3

$subnet = 'subnet'
$subnetprefix = 'x.x.x'
$ipperserver = 1
$firstip = 40
#################################################

## collect IP information #########
$IPS = @()
$vxrailip = Get-Cluster $storenumber | Get-VM | where {$_.name -like "VxRail*"} | Select Name, @{N="IPAddress";E={@($_.guest.IPAddress[0])}}

$split = $vxrailip.IPAddress

$IPbyte = $split.Split(".")
$newucsip = ($IPByte[0]+"."+$IPByte[1]+"."+$IPByte[2])

$startip = "111"
$endip = "112"
$gateway = "126"

$netmask = "subnet"

$ucsipaddress = "$newucsip.$($startip+$_)"
$ucsipaddress1 = "$newucsip.$($endip+$_)"
$ucsgateway = "$newucsip.$($gateway+$_)"

$IPS += $ucsipaddress
$IPS += $ucsipaddress1

$internet = 0

###########################################################3
foreach ($esxhost in (Get-cluster $storenumber | Get-VMHost | sort Name )) {

$portgoup = $esxhost | Get-VirtualPortGroup |where {$_.name -like "Management*"}

$datastore = $esxhost | get-datastore | where {$_.name -like "VxRail*"}


###########################################

$virtual = $VMNAME[$i]
$i ++

$protocol = $IPS[$internet]
$internet ++

#####################################################

$ovfconfig = Get-OvfConfiguration -Ovf $filername

$ovfconfig.Common.ip.Value = $protocol
$ovfconfig.Common.netmask.Value = $netmask
$ovfconfig.Common.gateway.Value = $ucsgateway
$ovfconfig.DeploymentOption.Value = 'Med'
$ovfconfig.IpAssignment.IpProtocol.Value = 'IPv4'
$ovfconfig.NetworkMapping.RecoverPoint_Management_Network.Value = $portgoup
#################################################

$esxhost | Import-VApp -Source $filername -datastore $datastore -OvfConfiguration $ovfconfig -Name $virtual -DiskStorageFormat Thin -Force | Start-VM  -ErrorAction SilentlyContinue


###########################################################
$vswitch = Get-VMHost $esxhost | Get-VDSwitch


$pg = Get-VDswitch -VMHost $esxhost | get-VirtualPortGroup | where {$_.name	-like "vSphere*"}
###############################################################

0..$($ipperserver-1) | %{

$ipaddress = "$subnetprefix.$($firstip + $_)"
$firstip += $ipperserver
New-VMHostNetworkAdapter -VMHost $esxhost -VirtualSwitch $vswitch -IP $ipaddress -SubnetMask $subnet -PortGroup $pg  -ErrorAction SilentlyContinue
}

}
New-DrsRule -KeepTogether:$false -Name vRPAAntiafffinity -Enabled:$true -Cluster $storenumber -VM VxRail$($storenumber)* -Confirm:$false  -ErrorAction SilentlyContinue

Hope this helps… feel free to comment !!