Friday, 28 September 2018

Tableau Dashboard – No records Found!


There are multiple blogs available on internet who talks about the same topic – how to show ‘No Records Found’ message on a tableau sheet. BUT none of them actually helped me to implement this task.
So when I succeeded, I thought to pen down this blog to help several others who may struggle to find out this information like I did.

Here goes my blog with step by step process with screenshots


1. I have created a tableau sheet ‘applications’ which has Environment, Host ID and Count of Applications Installed as fields. There is a filter added on ‘Date’ field. Refer screenshot below.

2. As per our requirement, if records exists in database for given relative date, data should appear.


3.       If records does not exist in database for selected relative date, ‘No Records Found’ message should appear.


4.       For this functionality, a dashboard is required since this can not be achieved using single tableau sheet. Create an empty dashboard by clicking on ‘+’ sign at the bottom bar of tableau desktop. I have named the dashboard as ‘No Data Found Example’ for better understanding.

5.       On the Dashboard, drag and drop a ‘Horizontal’ container object in ‘Tiled’ mode.

6.       Then drag and drop a ‘Text’ object inside the horizontal container in ‘Floating’ mode. Add text to the object and change font as per requirement.

7.       Relocate/Adjust  text object to the required location in the container.

8.       Drag and drop the Applications sheet on the container in ‘Floating’ mode

9.       Relocate/Adjust ‘Relative Date’ filter on the container to the required location.

1.   Relocate/Adjust ‘Application’ sheet on the container to the required location.

1.   And wohoo, you are all set. Test the dashboard by selecting different ‘Relative Date’ filter.



Hope this blog helped you. Your valuable feedback is welcome.
Till then, Happy Scripting!

Friday, 2 June 2017

Load Balancer Template with multiple Health probes and Load Balancing rules

This template allows you to create an internal load balancer with multiple Health probes and Load balancing rules. Same logic can be applied to Inbound NAT rules if you wish to create them as well in Load Balancer.
To read more about Azure Load Balancer, refer this article
Below is a snapshot from JSON Outline window
Load Balancer mainly has below properties
  1. Frontend IP Configuration
  2. Backend Address Pools
  3. Health Probes
  4. Load Balancing Rules
  5. Inbound NAT rules (not covered in this post)
Health probes and Load Balancing rules are created under copy section as we need to create them based on the parameter array. To read more about copy function, read this article.
Below is expanded copy section for Health Probes. Note that the name of the section should be ‘probe’. This template defines below properties of a Health Probe
  1. Protocol
  2. Port
  3. intervalInSeconds (amount of time between probe attempts)
  4. numberOfProbes (Unhealthy Threshold)
  5. requestPath (the uri used for requesting health status from the backend endpoint)
Below is expanded copy section for Load Balancing rules. Note that the name of the section should be ‘loadBalancingRules’. This template defines below properties of a Load Balancing rule
  1. Frontend IP Configuration
  2. Backend Address Pool
  3. Probe (Health Probe)
  4. Protocol
  5. Frontend Port
  6. Backend Port
  7. idleTimeoutInMinutes (keep a TCP or HTTP connection open without relying on clients)
  8. enableFloatingIP (It can be enabled only if port and Backend port matches)
Below is the full template
{
“$schema”: “https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#”,
“contentVersion”: “1.0.0.0”,
“parameters”: {
“loadBalancerName”: {
 “type”: “string”
},
“subscriptionId”: {
“type”: “string”
},
“virtualNetworkName”: {
“type”: “string”
},
“subnetName”: {
“type”: “string”
},
“location”: {
“type”: “string”
},
“lbFrontendIPPoolName”: {
“type”: “string”,
“defaultValue”: “LoadBalancerFrontEnd”
},
“lbBackendPoolName”: {
“type”: “string”,
“defaultValue”: “LoadBalancerBackEnd”
},
“healthProbes”: {
“type”: “array”,
“defaultValue”: [
{
“name”: “HealthProbe-TCP-80”,
“protocol”: “TCP”,
“port”: “80”,
“intervalInSeconds”: “15”,
“numberOfProbes”: “2”,
“requestPath”: null
},
{
“name”: “HealthProbe-TCP-443”,
“protocol”: “TCP”,
“port”: “443”,
“intervalInSeconds”: “15”,
“numberOfProbes”: “2”,
“requestPath”: null
},
{
“name”: “HealthProbe-HTTP-443”,
“protocol”: “HTTP”,
“port”: “443”,
“intervalInSeconds”: “15”,
“numberOfProbes”: “2”,
“requestPath”: “TestPath”
}
]
},
“lbRules”: {
“type”: “array”,
“defaultValue”: [
{
“name”: “HTTP”,
“lbFrontendIPPoolName”: “LoadBalancerFrontEnd”,
“lbBackendPoolName”: “LoadBalancerBackEnd”,
“healthProbe”: “HealthProbe-TCP-80”,
“protocol”: “TCP”,
“frontendPort”: “80”,
“backendPort”: “80”,
“idleTimeoutInMinutes”: “15”,
“enableFloatingIP”: false
},
{
“name”: “HTTPS”,
“lbFrontendIPPoolName”: “LoadBalancerFrontEnd”,
“lbBackendPoolName”: “LoadBalancerBackEnd”,
“healthProbe”: “HealthProbe-TCP-443”,
“protocol”: “TCP”,
“frontendPort”: “443”,
“backendPort”: “443”,
“idleTimeoutInMinutes”: “15”,
“enableFloatingIP”: false
}
]
}
},
“variables”: {
“vnetID”: “[resourceId(‘Microsoft.Network/virtualNetworks’, parameters(‘virtualNetworkName’))]”,
“subnetRef”: “[concat(variables(‘vnetID’),’/subnets/’,variables(‘subnetName’))]”,
“lbID”: “[resourceId(‘Microsoft.Network/loadBalancers’, parameters(‘loadBalancerName’))]”
},
“resources”: [
{
“apiVersion”: “2017-03-01”,
“name”: “[parameters(‘loadBalancerName’)]”,
“type”: “Microsoft.Network/loadBalancers”,
“location”: “[parameters(‘location’)]”,
“properties”: {
“frontendIPConfigurations”: [
{
“name”: “[parameters(‘lbFrontendIPPoolName’)]”,
“properties”: {
“subnet”: {
“id”: “[variables(‘subnetRef’)]”
}
}
}
],
“backendAddressPools”: [
{
“name”: “[parameters(‘lbBackendPoolName’)]”
}
],
“copy”: [
{
“name”: “probes”,
“count”: “[length(parameters(‘healthProbes’))]”,
“input”: {
“name”: “[parameters(‘healthProbes’)[copyIndex(‘probes’)].name]”,
“properties”: {
“protocol”: “[parameters(‘healthProbes’)[copyIndex(‘probes’)].protocol]”,
“port”: “[parameters(‘healthProbes’)[copyIndex(‘probes’)].port]”,
“intervalInSeconds”: “[parameters(‘healthProbes’)[copyIndex(‘probes’)].intervalInSeconds]”,
“numberOfProbes”: “[parameters(‘healthProbes’)[copyIndex(‘probes’)].numberOfProbes]”,
“requestPath”: “[parameters(‘healthProbes’)[copyIndex(‘probes’)].requestPath]”
}
}
},
{
“name”: “loadBalancingRules”,
“count”: “[length(parameters(‘lbRules’))]”,
“input”: {
“name”: “[parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].name]”,
“properties”: {
“frontendIPConfiguration”: {
“id”: “[concat(resourceId(‘Microsoft.Network/loadBalancers’, parameters(‘loadBalancerName’)), ‘/frontendIpConfigurations/’,parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].lbFrontendIPPoolName)]”
},
“backendAddressPool”: {
“id”: “[concat(resourceId(‘Microsoft.Network/loadBalancers’, parameters(‘loadBalancerName’)), ‘/backendAddressPools/’,parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].lbBackendPoolName)]”
},
“probe”: {
“id”: “[concat(resourceId(‘Microsoft.Network/loadBalancers’, parameters(‘loadBalancerName’)), ‘/probes/’,parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].healthProbe)]”
},
“protocol”: “[parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].protocol]”,
“frontendPort”: “[parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].frontendPort]”,
“backendPort”: “[parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].backendPort]”,
“idleTimeoutInMinutes”: “[parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].idleTimeoutInMinutes]”,
“enableFloatingIP”: “[parameters(‘lbRules’)[copyIndex(‘loadBalancingRules’)].enableFloatingIP]”
}
}
}
]
}
}
],
“outputs”: {
“LB”: {
“type”: “object”,
“value”: “[reference(concat(‘Microsoft.Network/loadBalancers/’, parameters(‘loadBalancerName’)))]”
}
}
}

Hope this was helpful. Happy Coding!

Delete Empty Load Balancers with No Virtual Machines

Hi All,
Below script will help you to find and delete empty load balancers with no virtual machines from Azure environment.

Find Empty Load Balancers

You can find an empty load balancer by checking its BackEndIPConfigurations. Example:-
  1. $myLoadBalancer = Get-AzureRmLoadBalancer -ResourceGroupName ‘MyTempResourceGroup’ -Name ‘MyTempLoadBalancer’
  2. if($myLoadBalancer.BackendAddressPools.Backendipconfigurations -eq $null)
  3. {
  4.     Write-Host “Load Balancer is empty”
  5. }

Delete Empty Load Balancer

Remove-AzureRmLoadBalancer command deletes a load balancer. You will be required to pass name of the load balancer and resource group name. Example:-
  1. Remove-AzureRmLoadBalancer -Name ‘MyTempLoadBalancer’ -ResourceGroupName ‘MyTempResourceGroup’ -Force 

Full Script

Below script will find and delete empty load balancers in a given Subscription in Azure
  1. #1. Declare Variables
  2. $subscriptionName = ‘MyTestSubscription’
  3. #2. Selecting Subscription
  4. $ignoreOutput = Select-AzureRmSubscription -SubscriptionName $subscriptionName
  5. #3. Searching ResourceGroups in Subscription
  6. $resourceGroups = Get-AzureRmResourceGroup
  7. #4. Iterate through all resource groups to find empty Load Balancers
  8. foreach($currentResourceGroup in $resourceGroups)
  9. {
  10.     #5. Searching LoadBalancers
  11.     $loadBalancers = Get-AzureRmLoadBalancer -ResourceGroupName $currentResourceGroup.ResourceGroupName
  12.     foreach($currentLoadBalancer in $loadBalancers)
  13.     { 
  14.         #6. Validating if LoadBalancer is empty
  15.         if($currentLoadBalancer.BackendAddressPools.Backendipconfigurations -eq $null)
  16.         {
  17.             #7. Deleting empty LoadBalancer in Azure
  18.             Remove-AzureRmLoadBalancer -Name $currentLoadBalancer.Name -ResourceGroupName $currentResourceGroup.ResourceGroupName -Force
  19.         }           
  20.     }
  21. }    

Happy Coding!!!

Run PowerShell using Local System Account

You will very frequently come into a scenario where you will need to run some commands using local system account.
In my case, I was working on a windows service running using local system account. The windows service internally was calling PowerShell scripts.
I developed PowerShell scripts independently and they were absolutely working fine(running using my account which has elevated privileges), but when tested along with Windows Service, it failed due to various reasons like access and permission issues (as Windows service run scripts using local system account).
So to resolve this issue, I launched PowerShell with local system account, did required changes to code, performed complete testing and whoa I was all setup to go.
I thought to share all this information to you and save some of your time in troubleshooting.

Initial Setup

  1. Download PsExec from this link.
  2. It will ask you to download PSTools folder on your machine. Save it on your C:\ drive.

Open PowerShell with Local System Account

  1. Open Command Prompt
  2. Run below commands :
  3. Change directory to PSTools folder.
cd C:\PsTools
  1. Invoke PowerShell using PsExec.exe
.\PsExec.exe –i –s PowerShell.exe
  1. It will launch PowerShell. Validate current user
whoami


Open PowerShell ISE with Local System Account

  1. Open Command Prompt
  2. Run below commands :
  3. Change directory to PSTools folder.
cd C:\PsTools
  1. Invoke PowerShell using PsExec.exe
.\PsExec.exe –i –s PowerShell_Ise.exe
  1. It will launch PowerShell ISE. Validate current user
whoami






Whoa! You are good to go. Do all your work and testing required to be done using Local System Account.

Happy Coding!!!

Update tags on Azure Virtual Machines

Hi All,
There is an excellent blog available on adding new tags on Azure Virtual Machines. Adding new tags is simple and direct, but updating existing tags requires little more logic.
So here is my blog solving this problem.

Scenario

In below screenshot, I have one virtual machine with existing tag ‘Builder’. I will be updating its value to ‘Sonam2’ from ‘Sonam’
1

Steps


  1. Declare Variables

#declare variables
$vmName = “MyVmName”
$tagName = “Builder”
$tagValue = “Sonam2”
$resourceGroupName = “myResourceGroupName”
$subscriptionName = “mySubscriptionName”
$tagExists = $false
$toAddHashTable = @{Name=”$tagName”;Value=”$tagValue”}
  1. Login to Azure Account

#login to azure account
$addAzAccount = Login-AzureRmAccount
Select-AzureRmSubscription -SubscriptionName $subscriptionName

  1. Fetch Azure VM tag list

#Fetch azure vm tags 
$vmTagsOriginal = (Get-AzureRmResource -ResourceGroupName $resourceGroupName -Name $vmName).Tags

  1. Logic to remove tag with old value from vm tag list

#Logic to remove tag with old value from vm tag list
if($vmTagsOriginal)
{
#validate if tag already exist
[System.Collections.ArrayList]$vmTagsArrayList = $vmTagsOriginal
foreach($tag in $vmTagsArrayList)

if($tag.Name -eq $toAddHashTable.Name)
{
$tagExists = $true
break;
}
}
#remove existing tag with old value from tag list
if($tagExists -eq $true)

$ignoreOutput = $vmTagsArrayList.Remove($tag)
}
}
else
{
#initiate tag list of no tag exists on vm
$vmTagsArrayList = @{}
}
  1. Add tag with new value in vm tag list

#add tag with updated value in vm tag list
$ignoreOutput = $vmTagsArrayList.Add($toAddHashTable)

  1. Update azure vm with updated tag list

#update azure vm with new tag list
$ignoreOutput= Set-AzureRmResource -ResourceGroupName $resourceGroupName -ResourceName $vmName -ResourceType “Microsoft.Compute/VirtualMachines” -Tag $vmTagsArrayList -Force

Output

In below screenshot, you can see the value of tag ‘Builder’ has been updated to ‘Sonam2’ from ‘Sonam’
2

Full script


#declare variables
$vmName = “MyVmName”
$tagName = “Builder”
$tagValue = “Sonam2”
$resourceGroupName = “myResourceGroupName”
$subscriptionName = “mySubscriptionName”
$tagExists = $false             
$toAddHashTable = @{Name=”$tagName”;Value=”$tagValue”}

#login to azure account
$addAzAccount = Login-AzureRmAccount
Select-AzureRmSubscription -SubscriptionName $subscriptionName

#Fetch azure vm tags   
$vmTagsOriginal = (Get-AzureRmResource -ResourceGroupName $resourceGroupName -Name $vmName).Tags

#Logic to remove tag with old value from vm tag list
if($vmTagsOriginal)
{
    #validate if tag already exist
    [System.Collections.ArrayList]$vmTagsArrayList = $vmTagsOriginal
    foreach($tag in $vmTagsArrayList)
    {           
        if($tag.Name -eq $toAddHashTable.Name)
        {
            $tagExists = $true
            break;
        }
    }
    #remove existing tag with old value from tag list
    if($tagExists -eq $true)
    {
        $ignoreOutput = $vmTagsArrayList.Remove($tag)
    }
}
else
{
    #initiate tag list of no tag exists on vm
    $vmTagsArrayList = @{}
}

#add tag with updated value in vm tag list
$ignoreOutput = $vmTagsArrayList.Add($toAddHashTable)

#update azure vm with new tag list
$ignoreOutput= Set-AzureRmResource -ResourceGroupName $resourceGroupName -ResourceName $vmName -ResourceType “Microsoft.Compute/VirtualMachines” -Tag $vmTagsArrayList -Force

Hope this was helpful. Happy Coding!