Welcome to My WeBlog.

Hello there,

I am glad that you are here! I am really excited to start this blog and is all about PowerShell along with other technical stuff. The posts I blog here are truly from my personal experience and for my future reference. So I strongly recommend before you try anything from this blog directly on your production environment, please do it on your test environment first. You’re always welcome to leave your comments, suggestions, and question about my blog posts on my contact page.

Thank you & Keep visiting my blog!

PowerShell – How to use splatting to pass parameters to commands

Many a times we might have come across the situation where we need to execute the lengthy CmdLets/functions having bunch of parameters and exceeds screen width and wrapped down to the next line or need to scroll towards end of the command or need to ignore the new line using the escape character (`).

Splatting, pass the parameter values as a collection in the form of name and value pair, as a hash table or array of values. It makes the command shorter, easy to read and can be re-used. It’s a hash table/array variable though, to pass the paramater values to the command @ sysbol will be used before the variable name instead of $.


$paramtable = @{
Name1 = 'Value1'
Name2 = 'Value2'
Name3 = 'Value3'
C:\> Sample-Command @paramtable
C:\> Sample-Command <optional parameters> @paramtable <optional paramaters>

To provide the named parameter values hash table can be used and to provide the positional parameters array can be used. When splatting, it is not necessary to use either hash table or an array only to pass the parameters, positional parameters and/or named parameters can also be used along with.

EXAMPLE: Splatting with hash table

Create a new file using New-Item CmdLet by passing necessary parameters…

EXAMPLE: Splatting with array

Copy a file from one location to other using Copy-Item CmdLet by passing necessary parameters…

An another example…

Just a tip #5 – Select-Object with calculated properties in PowerShell

Sometimes the value from the output might not be in the readable format, the header might be not appropriate for the value or the value is not straightforward, and sometimes there is a need of calculated field altogether…

# Get the total memory in GB from the local computer using the calculated property with Select-Object
Get-CimInstance -ClassName Win32_OperatingSystem | Select-Object -Property PSComputerName, `
@{Name = 'Memory in GB'; Expression = {[Math]::Round($_.TotalVisibleMemorySize/1MB)}}

# Get the services where the names are starting with App, and display IsRunning with Yes/No using the calculated property
$IsRunning = @{
Label = "IsRunning"
Expression = {
if($_.Status -eq 'Running') { "Yes" }
else { "No" }
Get-Service -Name App* | Select-Object -Property Name, DisplayName, $IsRunning



Excel reports using ImportExcel module from PowerShell Gallery.

You know why Excel is still one of the popular reporting tools because it is very easy to use, customizable as per your needs and moreover it is interactive. There are many ways that you can generate excel reports programmatically, but especially when you are using a scripting language like PowerShell there are tons of modules are already available with sophisticated features in the public repositories to reuse in your code, and they are very easy and simple to use.

Please be careful when you are installing the modules from the public repositories, because people around the world they publish their code into the repositories which work for them as per their environment and settings, but it may harmful for your environment. So please ensure the scripts work fine for you in your test environment first and then use it in your production environment.

ImportExcel PowerShell Module by Doug Finke from PowerShellGallery is a very popular and much helpful module, it works even without Excel installed on your computer, and again it’s an open source project in GitHub. This module is very rich in features and compact to use in your code, and I find this module is very useful and helpful.

Now let’s get started with ImportModule module in PowerShell Core

SincePowerShellGalleryis a default repository in PowerShell, you don’t need to set the repository again, just ensure that you havePSGalleryas a default repository…


Now it’s time to get the module installed and imported to the session…

# Find the module
Find-Module -Name ImportModule

# Install the modules
Find-Module -Name ImportModule | Install-Module 
Install-Module -Name Install-Module

# Update the module
Update-Module -Name ImportExcel

# Verify the module is installed
Get-Module -Name ImportExcel -ListAvailable

# Import the module
Import-Module -Name ImportExcel


To check the CmdLets available in the ImportExcel module, run the CmdLet below…

Get-Command -Module ImportExcel

Now let’s see how to export the data to excel and various options…

Export-Excel CmdLet will do all the magic with various parameters; to simply export the data to excel, just pipe the output to Export-Excel, this will export the data to excel, apply filters, auto size the columns and pop up the excel window, but this will not save the file to disk.

Get-Service | Select Name, DisplayName, Status | Export-Excel


… just to export the data to excel and save the file to disk, use -Path flag with Export-Excel …

Get-Service | Select Name, DisplayName, Status | Export-Excel -Path C:\Test.xlsx


Observe the data in the excel opened after the file was created, where the columns are compact, not readable and no filters applied. By default without any parameters Export-ExcelCmdLet will not save the file to disk, show the window, apply filters, auto size the columns, but if we use -Path and want to pop up the window, apply filters and auto size the columns we need to use the -Show, -AutoSize, -AutoFilter flags…

# To Show the window after the file save to disk
Get-Service | Select Name, DisplayName, Status | Export-Excel -Path .\Test.xlsx -Show

# To apply filters, and allow autosize the columns
Get-Service | Select Name, DisplayName, Status | Export-Excel -Path .\Test.xlsx -Show -AutoSize -AutoFilter

Now let’s see formating the text in the excel reports…

# Get the services exported to excel and highlight the services state seperately for services running and services are stopped.
$ConTxt1 = New-ConditionalText -Text 'Stopped' -ConditionalTextColor Red -BackgroundColor Yellow
$ConTxt2 = New-ConditionalText -Text 'Running' -ConditionalTextColor Yellow -BackgroundColor Green
Get-Service | Select Status, Name, DisplayName | Export-Excel -Path .\Test.xlsx -AutoSize -Show -ConditionalFormat $ConTxt1, $ConTxt2
# '-ConditionalFormat' parameter accepts arrays

import-excel-6Setting the icons to the values to represent the changes with in the given range…

# Get the processes, and represent the changes in the memory with the icons
$ConFmt = New-ConditionalFormattingIconSet -Range "C:C" -ConditionalFormat FiveIconSet -IconType Arrows
Get-Process | Select Company, Name, PM, Handles | Export-Excel -Path .\Process.xlsx -Show -AutoSize -AutoFilter -ConditionalFormat $ConFmt

# Also club it with the conditional text
$ConTxt = New-ConditionalText -Text 'Microsoft' -ConditionalTextColor Yellow -BackgroundColor Green
Get-Process | Select Company, Name, PM, Handles | Export-Excel -Path .\Process.xlsx -Show -AutoSize -AutoFilter -ConditionalFormat $ConFmt, $ConTxt


Now let’s see some creating pivot tables and charts…

# Get the services and identify the number of service are running & stopped and the services count per start type
$Data = Get-Service | Select-Object Status, Name, DisplayName, StartType | Sort-Object StartType

# Parmaters in a hashtable
$Param = @{
Show = $true
AutoSize = $true
IncludePivotTable = $true
PivotRows = 'StartType' 
PivotData = 'StartType' 
PivotColumns = 'Status'

# Create the pivot table
$Data | Export-Excel -Path C:\GitRepo\Test.xlsx @Param


# Get the services and identify the number of service are running & stopped and the services count per start type
$Data = Get-Service | Select-Object Status, Name, DisplayName, StartType | Sort-Object StartType

# Parmaters in a hashtable
$Param = @{
Show = $true
AutoSize = $true
PivotRows = 'StartType' 
PivotData = 'StartType'
IncludePivotChart = $true
ChartType = 'PieExploded3D'

# Create the pivot charts
$Data | Export-Excel -Path C:\GitRepo\Test.xlsx @Param


There are plenty of options are available, so please explore the all the features in the ImportExcel and make the best use of this module. You can also achive the same by writting your own code, but this is very compact and easy to use.

Many thanks to Doug Finke! #ImportExcel


‘Clear-RecycleBin’ is not recognized as the name of a cmdlet in PowerShell Core

Recently I have upgraded to PowerShell Core and slowly switching from Windows PowerShell to PowerShell Core. I have noticed quite a few CmdLets are missing in the PowerShell Core, since it became an open source and supports on cross-platform most of the platform dependent CmdLets won’t work on the other platforms. I usually clear my temp folders and recyclebin in all my computers frequently, and noticed Clear-RecycleBin CmdLet is not a valid CmdLet in PowerShell Core…

Clear-RecycleBin : The term 'Clear-RecycleBin' is not recognized as the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Clear-RecycleBin -Force
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Clear-RecycleBin:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

So I have decided to create a PowerShell function to achieve almost the the same functionality of Clear-RecycleBin using .NET class, and here is the function…



Note: I have not added the -DriveLetter flag, since I want to clear the recyclebin from all the drives, if you want clear the recyclebin from a specific drive, you need add the driveLetter argument to the Empty method in the C# code and add the -DriveLetter parameter to the PowerShell function.

Thank you.

Just a tip #4 – Download a zip file from the internet and extract using PowerShell.

The code below will download the .zip file from the internet, then extracts the files from the zip and opens the extracted folder…

$Url = 'https://download.sysinternals.com/files/BGInfo.zip'
$ZipFile = 'C:\ZipFolder\' + $(Split-Path -Path $Url -Leaf)
$Destination= 'C:\Extracted\'

Invoke-WebRequest -Uri $Url -OutFile $ZipFile

$ExtractShell = New-Object -ComObject Shell.Application
$Files = $ExtractShell.Namespace($ZipFile).Items()
Start-Process $Destination

Create new Azure VM using PowerShell (Az)

Deploying the Virtual Machines in the Azure cloud using the templates is the best way to create the VM to satisfy the attributes like quick, consistent, reusable and handles the dependency among the resources. However PowerShell has the flexibility to deploy the VMs with ease and allows the user to choose the required parameters necessary for the particular deployment alone without even touching the code. Ofcourse this approach is also quick and reusable, but the user has to ensure the consistency and dependency among the resources if required while creating the resources in the Azure cloud.

Since the new Azure PowerShell Module Az 1.0.1 is released, I have written the scripts using the Az module CmdLets. So please install Az module on Windows PowerShell or PowerShell Core, import the module and connect to Azure account using ‘Connect-AzAccount’.

Add required parameters to the script…

#requires -Modules Az
[string] $ResourceGroupName, # Resource Group
[string] $VMName, # VM Name
[string] $Location, # Location
[string] $OSType, # OS Type (Windows/Linux)
[string] $VirtualNetworkName, # VNet
[string] $SubnetName, # Subnet
[string] $SecurityGroupName, # NSG
[string] $VMSize, # VM Size
[switch] $AssignPublicIP, # Assign PIP
[pscredential]$VMCredential, # VM login credential
[Int[]] $AllowedPorts # NSG rules

Ensure you are connected to Azure subscription, if the script exits then connect to Azure subscription using Connect-AzAccount CmdLet, and this is a browser-based authentication.

# Verify Login
if( -not $(Get-AzContext) ) { return }

Ensure that there is no existing vm with the same name in the resurce group. If there is a VM already exists then exit the script.

# Verify VM doesn't exist
[Microsoft.Azure.Commands.Compute.Models.PSVirtualMachine] `
$VM = Get-AzVM -ResourceGroupName $ResourceGroupName -Name $VMName -ErrorAction SilentlyContinue
if($null -ne $VM) { return }

Create VM login credentials, if not provided along with the script…

# Create user object
if (-not $PSBoundParameters.ContainsKey('VMCredential'))
[pscredential] $VMCredential = Get-Credential -Message 'Please enter the vm credentials'

# Verify credential
if ($VMCredential.GetType().Name -ne "PSCredential") { return }

The script identifies the existing resources with the names provided, if exist then they will be used and if they don’t exist then will be created with different names.

Two things that you need to choose based on your requirements, one is the VM Size and the other is OS Image (Sku)…

# Lists all the VM Sizes available in South India region
PS C:\> Get-AzVMSize -Location southindia

To retrieve the OS Skus, I have written an another post ‘List of available Azure VM Image skus using new Azure PowerShell module Az.‘, please refer…

Now the main block starts from here…

Github Link: https://github.com/kpatnayakuni/PowerShell/blob/master/Create-AzVM.ps1

# Verify/Create a resource group
[Microsoft.Azure.Commands.ResourceManager.Cmdlets.SdkModels.PSResourceGroup] $ResourceGroup = Get-AzResourceGroup -Name $ResourceGroupName -ErrorAction SilentlyContinue
if ($null -eq $ResourceGroup)
$ResourceGroup = New-AzResourceGroup -Name $ResourceGroupName -Location $Location

# Verify the virtual network
[Microsoft.Azure.Commands.Network.Models.PSVirtualNetwork] $VNet = Get-AzVirtualNetwork -Name $VirtualNetworkName -ResourceGroupName $ResourceGroup.ResourceGroupName -ErrorAction SilentlyContinue
if ($null -eq $VNet)
[Microsoft.Azure.Commands.Network.Models.PSSubnet] $SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix
$VNet = New-AzVirtualNetwork -ResourceGroupName $ResourceGroup.ResourceGroupName -Location $Location -Name $VirtualNetworkName -AddressPrefix -Subnet $SubnetConfig
[Microsoft.Azure.Commands.Network.Models.PSSubnet[]] $Subnets = Get-AzVirtualNetworkSubnetConfig -VirtualNetwork $VNet
$SubnetConfig = $Subnets | Where-Object -FilterScript {$_.Name -eq $SubnetName}
if ($null -eq $SubnetConfig)
$VNetAddressPrefixes = $VNet.AddressSpace.AddressPrefixes
$AddressPrefix = @($VNetAddressPrefixes.Split('.'))
$AddressPrefix[2] = [int]($Subnets.AddressPrefix|Measure-Object -Maximum).Maximum.ToString().Split('.')[2] + 1
$AddressPrefix = $AddressPrefix -join '.'
$VNet | Add-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix $AddressPrefix | Set-AzVirtualNetwork

[Microsoft.Azure.Commands.Network.Models.PSSubnet] $Subnet = Get-AzVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $VNet

# Create a public IP address and specify a DNS name
if ($PSBoundParameters.ContainsKey('AssignPublicIP'))
[string] $PipName = $VMName + '-pip'
[Microsoft.Azure.Commands.Network.Models.PSPublicIpAddress] $VerifyPip = Get-AzPublicIpAddress -Name $PipName -ResourceGroupName $ResourceGroup.ResourceGroupName -ErrorAction SilentlyContinue
if ($null -ne $VerifyPip) 
$PipName = $VMName + '-pip-' + $(Get-Random).ToString()
[Microsoft.Azure.Commands.Network.Models.PSPublicIpAddress] $PublicIP = New-AzPublicIpAddress -ResourceGroupName $ResourceGroup.ResourceGroupName -Location $Location -Name $PipName -AllocationMethod Static -IdleTimeoutInMinutes 4

# Create/Select a network security group
[Microsoft.Azure.Commands.Network.Models.PSNetworkSecurityGroup] $NSG = Get-AzNetworkSecurityGroup -Name $SecurityGroupName -ResourceGroupName $ResourceGroup.ResourceGroupName -ErrorAction SilentlyContinue
if ($null -eq $NSG)
# Create an inbound network security group rules
if ($PSBoundParameters.ContainsKey('AllowedPorts'))
[System.Array] $NsgRules = @()
[int] $Priority = 1000
foreach ($Port in $AllowedPorts)
[Microsoft.Azure.Commands.Network.Models.PSSecurityRule] $Rule = New-AzNetworkSecurityRuleConfig -Name "Allow_$Port" -Protocol Tcp -Direction Inbound -Priority $Priority -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange $Port -Access Allow
$NsgRules += $Rule
[Microsoft.Azure.Commands.Network.Models.PSNetworkSecurityGroup] $NSG = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroup.ResourceGroupName -Location $Location -Name $SecurityGroupName -SecurityRules $NsgRules
$NSG = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroup.ResourceGroupName -Location $Location -Name $SecurityGroupName
# Add an inbound network security group rules, if missing any
if ($PSBoundParameters.ContainsKey('AllowedPorts'))
[int[]] $NSGAllowedPorts = $NSG.SecurityRules | Where-Object -FilterScript {$_.Access -eq "Allow"} | Select-Object -ExpandProperty DestinationPortRange
[int[]] $PortsToAllow = $AllowedPorts | Where-Object -FilterScript {$_ -notin $NSGAllowedPorts}
[int] $Priority = ($NSG.SecurityRules.Priority|Measure-Object -Maximum).Maximum + 100
if ($PortsToAllow.Count -gt 0)
foreach($Port in $PortsToAllow)
$NSG | Add-AzNetworkSecurityRuleConfig -Name "Allow_$Port" -Protocol Tcp -Direction Inbound -Priority $Priority -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange $Port -Access Allow | Set-AzNetworkSecurityGroup

# Create a virtual network card and associate with public IP address and NSG
[string] $NICName = "$VMName-nic"
[Microsoft.Azure.Commands.Network.Models.PSNetworkInterface] $NIC = Get-AzNetworkInterface -Name $NICName -ResourceGroupName $ResourceGroup.ResourceGroupName -ErrorAction SilentlyContinue
if ($null -ne $NIC)
$NICName = $VMName + "-nic-" + $(Get-Random).ToString()
[Microsoft.Azure.Commands.Network.Models.PSNetworkInterface] $NIC = New-AzNetworkInterface -Name $NICName -ResourceGroupName $ResourceGroup.ResourceGroupName -Location $Location -SubnetId $Subnet.Id -NetworkSecurityGroupId $NSG.Id 
if ($PSBoundParameters.ContainsKey('AssignPublicIP'))
$NIC | Set-AzNetworkInterfaceIpConfig -Name $NIC.IpConfigurations[0].Name -PublicIpAddressId $PublicIP.Id -SubnetId $Subnet.Id | Set-AzNetworkInterface | Out-Null

# VM Size
if($PSBoundParameters.ContainsKey('VMSize') -eq $false )
$VMSize = 'Standard_A1'

# OS Type
[hashtable] $VMSourceImage = @{PublisherName='';Offer='';Sku=''}
switch ($OSType) {
'Windows' { $VMSourceImage.PublisherName = 'MicrosoftWindowsServer'
$VMSourceImage.Offer = 'WindowsServer'
$VMSourceImage.Sku = '2016-Datacenter'
$VMSourceImage.PublisherName = 'Canonical'
$VMSourceImage.Offer = 'UbuntuServer'
$VMSourceImage.Sku = '18.10-DAILY'

# Create a virtual machine configuration
[Microsoft.Azure.Commands.Compute.Models.PSVirtualMachine] $VMConfig = New-AzVMConfig -VMName $VMName -VMSize $VMSize 
if ($OSType -eq 'Windows')
$VMConfig | Set-AzVMOperatingSystem -Windows -ComputerName $VMName -Credential $VMCredential | Out-Null
$VMConfig | Set-AzVMOperatingSystem -Linux -ComputerName $VMName -Credential $VMCredential | Out-Null
$VMConfig | Set-AzVMSourceImage -PublisherName $VMSourceImage.PublisherName -Offer $VMSourceImage.Offer -Skus $VMSourceImage.Sku -Version latest | Out-Null
$VMConfig | Add-AzVMNetworkInterface -Id $NIC.Id | Out-Null
$VMConfig | Set-AzVMBootDiagnostics -Disable | Out-Null

# Create a virtual machine
New-AzVM -ResourceGroupName $ResourceGroup.ResourceGroupName -Location $Location -VM $VMConfig

To create a Windows VM…

.\Create-AzVM.ps1 -ResourceGroupName test-rg `
-VMName testvm -Location southindia `
-OSType Windows `
-VirtualNetworkName test-vnet `
-SubnetName testnet `
-SecurityGroupName test-nsg `
-AssignPublicIP `
-AllowedPorts 3389 `
-VMCredential $cred `

To create a Linux VM…

.\Create-AzVM.ps1 -ResourceGroupName test-rg `
-VMName testvm -Location southindia `
-OSType Linux `
-VirtualNetworkName test-vnet `
-SubnetName testnet `
-SecurityGroupName test-nsg `
-AssignPublicIP `
-AllowedPorts 22 `
-VMCredential $cred `


Tagging Microsoft Azure Resources using PowerShell (Az)

In Azure Cloud, Tags play a major role to manage resources in an easy way, in an other words Tags are an additional meta data associated with the Azure resources. We can assign the tags to the individual resources like VM, Storage Account, VNet and etc., and we can also assign the tags to the Resource Groups as well. Resource groups allow us to organize the related resources together and facilitate the management, but tags are used to group the resources beyond the resource groups including the resource groups, and at the same time resources inside the resource group do not inherit the tags associated with the resource group. Tags are Key and Value combination that can be assigned to the resources in the Azure cloud, for example…

Tag Key Tag Value
ResourceType VM
Project MyProject
Department Marketing
Environment Production
CostCenterCode 123456

Do bear in mind that each individual resource can have up to 15 tags max (Microsoft keeps updating the numbers time to time, so please refer the Microsoft Docs for the exact number), and ensure the tags are unique and consistent naming convention among Azure resources.

Tags are used to organize the deployed resources in the Azure cloud, we could search the resources by tag key/value, for example search the resources with the tags associated {Key:Value} Type:VM and Environment:Production, then the search results all the production VMs across the resource groups within a subscription. Tags are also used to view the related resources, like all the resources tagged to a specific project or a specific cost center and to facilitate the billing and cost management.

Tags can be created at the time of creating resources or at the later time by using the Azure portal or any command line tools like PowerShell or Azure CLI.

Let’s see how we can create and manage the tags using PowerShell…

#requires -Module Az

# Connect-AzAccount

### Add new tags to an existing resource

# Get the resource
[Microsoft.Azure.Commands.ResourceManager.Cmdlets.SdkModels.PSResource] $Resource = Get-AzResource -Name testvm -ResourceGroupName test-rg

# Resource tags
[hashtable] $Tags = $Resource.Tags

# Ensure not to overwrite the tags
if ($null -eq $Tags) {
[hashtable] $Tags = @{Type="VM"; Environment="Test"} }
else {
$Tags += @{Type="VM"; Environment="Test"} }

# Add new tags to the resource (-Force to override the confirmation if there are any existing tags)
Set-AzResource -ResourceId $Resource.Id -Tag $Tags -Force

### Remove an existing tag / remove all tags from a resource

# Get the resource
[Microsoft.Azure.Commands.ResourceManager.Cmdlets.SdkModels.PSResource] $Resource = Get-AzResource -Name testvm -ResourceGroupName test-rg

# Resource tags
[hashtable] $Tags = $Resource.Tags

# Remove the specific tag

# Overwrite the remaing tags to the resource (-Force to override the confirmation if there are any existing tags)
Set-AzResource -ResourceId $Resource.Id -Tag $Tags -Force

## Remove all tags
Set-AzResource -ResourceId $Resource.Id -Tag @{} -Force

### List all the resources with a specific tag key
Get-AzResource -TagName "Environment"

### List all the resources with a specific tag value
Get-AzResource -TagValue "Test"

### List all the resources with a specific tag key and value
Get-AzResource -Tag @{Environment="Test"}

### List all the tags and number of resources associated in a subscription.

Github link: https://github.com/kpatnayakuni/PowerShell/blob/master/Demo-Tags.ps1



Create and assign a public ip to an Azure Virtual Machine using Azure PowerShell (Az).

Sometimes deliberately we don’t create and assign a public ip to an Azure Virtual Machine to not to expose to the internet as a safety measure, but later at some point of time we may require the VM to be accessed via internet and we definitely need a public ip to access the VM, the script below will help to create and assign a public ip address to an Azure VM…

Note: If no Network Security Group is associated with Virtual Machine, by default all ports are open to the internet, and please be careful.

#requires -Module Az

# Function to create and assign a public ip address 
# to an Azure Virtual Machine using Az PowerShell module.
Function Assign-AzVMPublicIP2
# Resource Group Name
[string] $ResourceGroupName,
# Virtual Machine Name
[string] $VMName
# Retrieve the Virtual Machine details
$VM = Get-AzVM -ResourceGroupName $ResourceGroupName -Name $VMName -ErrorAction SilentlyContinue

# Checking the VM existance
if($null -eq $VM)
Write-Error "Please enter a valid and existing Resource Group Name and Virtual Machine Name"

$Location = $VM.Location # Location to create a public ip
$NICId = $VM.NetworkProfile.NetworkInterfaces.Id # Network Interface resource id
$NICResource = Get-AzResource -ResourceId $NICId # Retrieve the NIC resource details

# Retrive the NIC Object
$NIC = Get-AzNetworkInterface -Name $NICResource.Name -ResourceGroupName $NICResource.ResourceGroupName
$NICIPConfigName = $NIC.ipConfigurations[0].Name # IP Config Name to be used with Set-AzNetworkInterfaceIpConfig CmdLet
$NICSubnetId = $NIC.ipConfigurations[0].subnet.id # Subnet id to be used with Set-AzNetworkInterfaceIpConfig CmdLet

# Create a public ip
$PublicIP = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -Location $Location -Name "$VMName-pip" -AllocationMethod Static -IdleTimeoutInMinutes 4

# Warn the user if no NSG is associated with this VM
if ($null -eq $NIC.NetworkSecurityGroup)
Write-Warning "Since no Network Security Group is associated with this Virtual Machine, by default all ports are open to the internet."

# Assign the public ip to the VM NIC
$NIC | Set-AzNetworkInterfaceIpConfig -Name $NICIPConfigName -SubnetId $NICSubnetId -PublicIpAddressId $PublicIP.Id | Set-AzNetworkInterface

Assign-AzVMPublicIP2 -ResourceGroupName test-rg -VMName test-vm

List of available Azure VM Image skus using new Azure PowerShell module Az.

As you might already know, Microsoft has released a new Azure PowerShell module Az to replace with AzureRM module in future. As of now both the versions are available for Windows PowerShell and PowerShellCore. But no further developments for AzureRM module except for bg fixes and all the updates and feature enchantments come along with the new modules Az itself.

Just to start with the Az module, lets retrieve the list of Azure VM Images (skus) available in a given location from the mentioned publisher with the offerings…


Just a tip #3 – $args in PowerShell

In scripting, there are many things to experience in many ways, the traditional way is always the best practice though, the formal way is always an option…

Traditional way

Function Add
<span></span>[int] $Number1,
[int] $Number2
[int] $Sum = 0
$Sum = $Number1 + $Number2
return, $Sum

Add -Number1 4 -Number2 5

Add tip 1

Formal way

<span></span>$Add = {$args[0] + $args[1]}
. $Add 4 5

Add tip 2

SQL Server PSObject – Working with SQL Server using PowerShell

SQL Server loves PowerShell, it makes SQL Server DBA life easy and simple. I have seen SQL Server automated with PowerShell to an extent where I stopped using SQL Server Management Studio (SSMS) ever since I started using PowerShell. Database Administrator doesn’t require SSMS all the time to connect to SQL Server if you are accompanying with PowerShell. There are quite a few tools are already available in the internet from dbatools.io, idera PowerShell scripts and etc., but every approach is unique.

SQL Server PSObject, SQL Server functionalities within a single PowerShell object. PSObject, I love the most in PowerShell, you can customise the object of your own choice of properties & methods, and the usage is also as simple as just initiate the object and call the methods of your choice.

I have created a new PSObject with ConnectSQL and ExecuteSQL methods, they are the very basic and predominant functionalities to work with SQL Server.

# Create an object
$SQLServerObject = New-Object -TypeName psobject

And added few essential properties, mainly used to establish the connection to Sql Server…

# Basic properties
$SQLServerObject | Add-Member -MemberType NoteProperty -Name ServerName -Value 'SQLServer' # Server Name
$SQLServerObject | Add-Member -MemberType NoteProperty -Name DefaultPort -Value 1433 # Port
$SQLServerObject | Add-Member -MemberType NoteProperty -Name Database -Value 'master' # Database
$SQLServerObject | Add-Member -MemberType NoteProperty -Name ConnectionTimeOut -Value 15 # Connection Timeout
$SQLServerObject | Add-Member -MemberType NoteProperty -Name QueryTimeOut -Value 15 # Query Timeout
$SQLServerObject | Add-Member -MemberType NoteProperty -Name SQLQuery -Value '' # SQL Query
$SQLServerObject | Add-Member -MemberType NoteProperty -Name SQLConnection -Value '' # SQL Connection

The properties like ServerName, Port, Database and ConnectionTimeout are must to define before you call either connect method or execute method, SQLConnection property holds the sql server connection object to execute the sql queries with execute method. SQLQuery property holds the query text to execute the query against the sql server mentioned in the ServerName property, you can also enter the query while calling the execute method. Ensure the Server is ping-able usign the TestConnection method…

# Method to ensure the server is pingable
$SQLServerObject | Add-Member -MemberType ScriptMethod -Name TestConnection -Value {
Test-Connection -ComputerName $this.ServerName -ErrorAction SilentlyContinue

Establish the connection and store the connection object in the SQLConnection property of the object.

# Method to establish the connection to SQL Server and holds the connection object for further use
$SQLServerObject | Add-Member -MemberType ScriptMethod -Name ConnectSQL -Value {

[string] $ServerName= $this.ServerName
[int] $Port = $this.DefaultPort
[string] $Database = $this.Database
[int] $TimeOut = $this.ConnectionTimeOut

$SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server = $ServerName,$Port; Database = $Database; Integrated Security = True;Connection Timeout=$TimeOut;"

$this.SQLConnection = $SQLConnection

ExecuteSQL method to execute the queries using the connection established using the ConnectSQL method…

# Execute SQL method to execute queries using the connection established with ConnectSQL
$SQLServerObject | Add-Member -MemberType ScriptMethod -Name ExecuteSQL -Value {

[string] $QueryText

# Select runtime query / predefined query
[string] $SQLQuery = $this.SQLQuery
if ([string]::IsNullOrEmpty($QueryText) -eq $false)
$SQLQuery = $QueryText

# Verify the query is not null and empty, then execute
if ([string]::IsNullOrEmpty($SQLQuery))
Write-Host "Please add query to this object or enter the query." -ForegroundColor Red
if ($this.SQLConnection.State -eq 'Open')
# SQL Command
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery
$SQLCommand.CommandTimeout = $this.QueryTimeOut
$SQLCommand.Connection = $this.SQLConnection
# SQL Adapter
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
# Dataset
$DataSet = New-Object System.Data.Dataset
$SQLAdapter.Fill($DataSet) | Out-Null
return $DataSet.Tables[0]
Write-Host "No open connection found." -ForegroundColor Red

And finally return the object…

# Return the object
return, $SQLServerObject

Now, lets see how we can connect to sql server and execute the sql queries…

First, create an object…

PS C:\GitRepo> $SQL = .\Create-SQLServerObject.ps1
PS C:\GitRepo> $SQL


By default it takes the localhost name as servername, default sql server port and master database a default database to establish the connection. Assign a server name and test the connectivity…

PS C:\GitRepo> $SQL.ServerName = 'SQLServer'
PS C:\GitRepo> $SQL.TestConnection()


If the server is accessible, then establish the connection to the SQL Server, if the sql server port is other than default port, then assign the port to the object…

PS C:\GitRepo> $SQL.DefaultPort = 2866 # Just an example

Establish the connection…

PS C:\GitRepo> $SQL.ConnectSQL()
PS C:\GitRepo> $SQL.SQLConnection


Add query text to the object and call ExecuteSQL method…

PS C:\GitRepo> $SQL.SQLQuery = "Select database_id,name from sys.databases"
PS C:\GitRepo> $SQL.ExecuteSQL()


You can also enter the query while calling the method itself…

PS C:\GitRepo> $SQL.ExecuteSQL("Select @@Version as Version")


You can add any number of methods of your choice and customise as per your requirements, you can also execute the *.sql files as well…


The complete code is available in my git repository https://github.com/kpatnayakuni/PowerShell/blob/master/Create-SQLServerObject.ps1

Thank you.