FIT Code Rush 2018 - Keynote

I was privileged to address at CodeRush hackathon organized by Faculty of IT - University of Moratuwa recently. Myself and my team at LB Finance PLC submitted few challenges for the hackathon from FinTech and cloud spaces.

We spent the same night at University premises and evaluated the competition. I was amazed and thrilled by the competence and dedication from young students

During my speech I presented opportunities available in FinTech and Cloud segments and stressed the importance of not forgetting basic concepts

    

Excel Services: Unable to refresh one or more data connections when using Excel 2016

Recently I used Microsoft Excel 2016 to publish a excel report to SharePoint, it gives following error when running the workbook. The workbook had a connection to BISM which is referring Analysis Services cube.

Error Message

ULS error log

The workbook '' attempted to access external data using the unsupported provider 'Provider=MSOLAP.8;

Following is the data connection I had

To resolve the issue, I had to add MSOLAP.8 to trusted data providers list.

Finally restart IIS to see the change

Change the size of Azure VM in a given schedule using Azure Runbook

In our previous article, I explained how to write a simple PowerShell script to expand the size of an Azure VM.

1. PowerShell script to expand and reduce the size of an Azure VM
2. Change the size of Azure VM in a given schedule using Azure Runbook

In this article I’ll explain how to execute PowerShell scripts in a given schedule using Azure Automation Account, Runbooks and Schedules.

What is an Azure Automation?

Azure Automation is a Software as a Service (SaaS) that can be used to automate processes. You can automate cloud automation tasks using Azure Automation. Some common tasks are,

• Build and deploy resources
• Configure VMs
• Monitor resources

What is a Runbook?

Azure Automation use Runbooks to automate processes. Runbooks are executed in automation Sandboxes, which means as a Platform as a Service (PaaS) VMs. You will get the isolation that you expect from Runbooks

Following are the steps I used to expand and reduce the size of VMs in my Azure tenant.

1. Create Azure Automation Account

Navigate to Variables section and create following variables

2. Create the Runbook

Create new Runbook as a PowerShell script to expand the size

Add following script

$connectionName = "AzureRunAsConnection"

try

{

# Get the connection "AzureRunAsConnection "

$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

"Logging in to Azure..."

Add-AzureRmAccount `

-ServicePrincipal `

-TenantId $servicePrincipalConnection.TenantId `

-ApplicationId $servicePrincipalConnection.ApplicationId `

-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

}

catch {

if (!$servicePrincipalConnection)

{

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Error -Message $_.Exception

throw $_.Exception

}

}

$vmName = Get-AutomationVariable -Name 'vmName'

$resourceGroup = Get-AutomationVariable -Name 'resourceGroup'

$lowHwProfile = Get-AutomationVariable -Name 'lowHwProfile'

"Updating VM size"

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName

$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName

$vm.HardwareProfile.VmSize = $lowHwProfile

Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

"VM size updated"

Same way create another Runbook to reduce the VM size

3. Create Schedules and apply to Runbooks

Click on Schedules in Automation Account

Navigate to specific Runbook to apply the schedule

That’s it. We have to create another schedule to reduce the size of the VM on Monday night and apply to the particular Runbook

Presentation - Automating Business Processes with SharePoint & Flow

Following is the presentation I did at Microsoft Student Champs Monthly Meeting- February 2018

PowerShell script to expand and reduce the size of an Azure VM

When we provision VMs from Azure, we select the best possible size template from the gallery. But sometimes our VMs can’t cater the demand for specific time periods or are underutilized in certain times. One of my SQL Server VMs is very busy on Mondays, but manageable on other days.

I had to use Azure PowerShell script to modify the size template. In this article I’ll share the script I’ve used to expand the VM size and later reduce its size.

I’ve split the article in to two

1. PowerShell script to expand and reduce the size of an Azure VM
2. Change the size of Azure VM in a given schedule using Azure Runbook

Expand VM size

$vmName = "Test-VM"
$resourceGroup = "Test-Resource"
$highHwProfile = "Standard_DS13_v2_Promo"
$subscriptionId = "<subscription_id>"

Login-AzureRmAccount
Select-AzureRMSubscription -SubscriptionId $subscriptionId

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName
$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName
$vm.HardwareProfile.VmSize = $highHwProfile
Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

Reduce VM size

$vmName = "Test-VM"
$resourceGroup = "Test-Resource"
$highHwProfile = "Standard_DS12_v2_Promo"
$subscriptionId = "<subscription_id>"

Login-AzureRmAccount
Select-AzureRMSubscription -SubscriptionId $subscriptionId

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName
$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName
$vm.HardwareProfile.VmSize = $highHwProfile
Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

Microsoft Azure : Resolve - You don’t have access to this data. Please contact your global administrator to get access

I have been using my Azure subscription for a long time. But I was surprised when I got following error message when I tried to play around with Azure Active Directory

I can’t even add ore remove users!

Then I realized that I have associated my subscription to someone else's directory when creating the tenant.

Following are the steps I used to bring my tenant back to normal, where I have access to all features.

1. Create a new Azure Active Directory

2. Change directory in Azure Subscriptions

3. Verify

As you can see all my assets (including VMs) are unharmed.

Presentation - Continuous Integration with SharePoint

I presented regarding ALM with SharePoint in SharePoint Sri Lanka Forum on 10th January 2018.

Following is the presentation I did

Automate build and release for SharePoint Framework solution

This is the third part of the article series on how to manage SharePoint Framework solution in a team environment

1. Configure SharePoint Framework solution with Git
2. Integrate SharePoint Framework solution in to TFS project
3. Automate build and release for SharePoint Framework solution

In this blog post I’ll explain how to automate build and release process for SharePoint Framework solutions. This uses ALM API which was released recently.

• Download sample SPFX solution : https://github.com/dinushak/SPFxTest 
• Download deployment script : https://github.com/dinushak/SPFxTest/blob/master/publishapp.ps1

Configure Solution

Add following script to the root of the solution. This script will retract if previous version of the solution already exists. Then it will add the solution to App Catalog site and then to the SharePoint site that you wish your customization to be published.

Param(

[string]$username,

[string]$password

)

$packagePath = ".\release\sharepoint\solution\sp-fx-test.sppkg"

$encpassword = convertto-securestring -String $password -AsPlainText -Force

$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $encpassword

Connect-PnPOnline -Url "https://dinushaonline.sharepoint.com/sites/dev" -Credentials $cred

$appExist = Get-PnPApp | ? { $_.Title -eq 'SPFxTest' }

if ($appExist -ne $null) { 

Uninstall-PnPApp -Identity $appExist.Id

Unpublish-PnPApp -Identity $appExist.Id

Remove-PnPApp -Identity $appExist.Id

}

Add-PnPApp -Path $packagePath -Publish -Overwrite

$appAfterAdd = Get-PnPApp | ? { $_.Title -eq 'SPFxTest' }

Publish-PnPApp -Identity $appAfterAdd.id

Install-PnPApp -Identity $appAfterAdd.id

Configure Build

Create a new build definition in your TFS Online environment to perform continuous integration

We have to perform npm install, gulp bundle, ship and archive steps in following steps

Configure Release

Create variables for SharePoint online username and password which will be used in publishapp.ps1 script

Enable Continuous Deployment

This step will be used to automatically trigger a release as soon as a build is succeeded