Sunday, October 17, 2021

Powershell to update Azure DevOps pipeline variables automatically during the execution


Sometimes we have a scenario to update the devops pipeline variables dynamically during the task execution without having to update manually and create new release. you can use the below approach by adding Powershell task to the pipeline.

# Write your PowerShell commands here.

$cosmosconnstr = "$(cosmosconnstring)"

if("$(cosmosconnstring)".Chars("$(cosmosconnstring)".Length - 1) -eq ';')


  $cosmosconnstr = "$(cosmosconnstring)".TrimEnd(';')


Write-Output("##vso[task.setvariable variable=ApplicationSettings.CacheConnection;]$cosmosconnstr")


#Run the below script in another stage to make sure you get the updated value

Write-host "CacheConnection Variable in previous task is: $(ApplicationSettings.CacheConnection)"

Azure Monitoring Agent extension for VMSS- Updating through ARM template

 You can add multiple extensions to Azure VMSS  through the extensionProfile of ARM template. The below script shows the extension configuration and mapping workspaceId to collect custom logs from VMSS instances.

                "properties": {
                  "protectedSettings": {
                    "workspaceKey""[listKeys(resourceId(parameters('RGName'),'Microsoft.OperationalInsights/workspaces/', parameters('workspacename')),'2015-11-01-preview').primarySharedKey]"
                  "settings": {
                    "workspaceId""[reference(resourceId(parameters('RGName'),'Microsoft.OperationalInsights/workspaces/', parameters('workspacename')), '2015-11-01-preview').customerId]"

Wednesday, October 13, 2021

Custom dimensions and measurements - Azure Application Insights Query

 n App Analytics you can slice and dice on your App Insights custom dimensions and measurements just as easily as any of the so-called “standard” properties.

The only thing that’s a little bit tricky is extracting them first.

It’s tricky because of 2 things:

  1. You have to explicitly set the type of the measurement/dimension after you extract it.
  2. Extracting properties that contain spaces and special characters is a little bit of a hassle.
Bellow is an example

| where timestamp > ago(3h)
| where name == "Query"
| extend query_time = todouble(customMeasurements.['Query Time'])
| extend query_name = tostring(customDimensions.['Query Name'])
| project query_time, query_name
| summarize avg(query_time) by query_name 
| render barchart

User Enagagement metrix example

union *
| where timestamp > ago(90d)
| where client_Browser startswith "chrome" 
| evaluate activity_metrics(user_Id , timestamp, 7d, client_Browser   )
| where dcount_values > 3
| project timestamp , retention_rate, client_Browser 
| where retention_rate > 0 and 
  timestamp < ago(7d) and timestamp > ago(83d) // remove partial data in tail and head
| render timechart

Monday, September 20, 2021

TFS Build failure: folder cannot be deleted because it is not empty

When we run the build pipeline in Azure Devops, if we encounter below error then you can make the change recommended here.


D:\DevOps\Build\33\s\PT\<foldername>cannot be deleted because it is not empty. 

D:\DevOps\Build\33\s\PT\<foldername> cannot be deleted because it is not empty.

---- Summary: 0 conflicts, 1 warnings, 0 errors ---- 

Sleeping for 200 ms 

Retrying. Attempt $2/$3 

##[error]_proc should be null. (Parameter '_proc')


You can perform different kinds of cleaning of the working directory of your private agent before the build is run. If the Clean is set to false, it does not  get a fresh pull before the build is run.


Change the clean dropdown to true and the error goes of in the next run.

Saturday, September 18, 2021

Update Azure SQL Database table using Service Principal Context

The script below checks whether a key exists in the DB and if yes it updates and if not it adds the key and value to the table. The context used here is the Service Principal Name (SPN) which is the client ID and secret key. Also the SQL authentication user ID and password is required. Make sure your machine ip is added in the firewall rules to run the query.

$dbuser = "username"

$password = "dbpswd"

$tenid = "tenantid"

$clientid = "client ID"

$secretkey = "Secret Key"

$Servername ="dbservername"


Write-Output "Starting"

#$clientid = Get-AzureRMAutomationVariable -Name $varclientid

#$secretkey = Get-AzureRMAutomationVariable -Name $varsecretkey

#$dbuser = Get-AzureRMAutomationVariable -Name $vardbuser

#$password = Get-AzureRMAutomationVariable -Name $vardbpass

#$sbpk = "test"

Add-SqlAzureAuthenticationContext -ClientID $clientid -Secret $secretkey -Tenant $tenid

$sqlConn = New-Object System.Data.SqlClient.SqlConnection

$sqlConn.ConnectionString = "Server=$; User ID = $dbuser ; Password = $password ; Database = $database; Column Encryption Setting=enabled;"


Write-Output "sql conn opened"

function updateparamMaster($sqlconn,$paraID,$ParaGrp,$ParaValue)


#Check if the paramid and ParamGroup exists

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$query = "select * from parametermaster where paramid= $paraID and ParamGroup= '"+$ParaGrp+"'"

$sqlcmd.CommandText = $query

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

$data = New-Object System.Data.DataSet


$paramcount = $data.Tables[0].Rows.count

Write-Host "Row count-" $paramcount

if($paramcount -eq 0)


Write-Output $ParaGrp "ParamGroup does not exist- creating new entry"

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "INSERT into parametermaster(paramid,ParamGroup,Value) VALUES (@paramid, @ParamGroup, @Val)"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@paramid",[Data.SQLDBType]::Int)))

$sqlcmd.Parameters["@paramid"].Value = $paraID

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ParamGroup",[Data.SQLDBType]::VarChar, 50)))

$sqlcmd.Parameters["@ParamGroup"].Value = $ParaGrp

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@val",[Data.SQLDBType]::NVarChar, 500)))

$sqlcmd.Parameters["@val"].Value =$ParaValue





Write-Output $ParaGrp "ParamGroup exist- updating entry"

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "UPDATE parametermaster SET [Value] = @Val WHERE paramid = @paramid AND ParamGroup = @ParamGroup"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@paramid",[Data.SQLDBType]::Int)))

$sqlcmd.Parameters["@paramid"].Value = $paraID

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ParamGroup",[Data.SQLDBType]::VarChar, 50)))

$sqlcmd.Parameters["@ParamGroup"].Value = $ParaGrp

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@val",[Data.SQLDBType]::NVarChar, 500)))

$sqlcmd.Parameters["@val"].Value =$ParaValue




updateparamMaster -sqlconn $sqlConn -paraID 118 -ParaGrp "SharedDb" -ParaValue

Tuesday, September 14, 2021

Add Virtual Machine Scale Set (VMSS) Managed Identity to Azure Key Vault Access Policy- Powershell

 The below Powershell script will help you to add VMSS managed identity to Azure Key vault access policy.

#First, Get the identity Object ID and application Id based on the managed identity name

$identity = Get-AzUserAssignedIdentity -ResourceGroupName "RGName" -Name "sample-vmssid"

# $identity.ClienId is the Application ID and $identity.PrincipalID is the Object ID. You can run the below command by passing parameters and permissions required to be set to the Key vault

Set-AzKeyVaultAccessPolicy -ResourceGroupName "RGName" -VaultName "VaultName-kvt" -ObjectId $identity.PrincipalId -ApplicationId $identity.ClientId -PermissionsToKeys get,list,unwrapKey,wrapKey -PermissionsToSecrets get -PermissionsToCertificates get,list,delete,create

#Thats it and you can see the vmss managed identity in the access policy for key vault.

#You can do the above using Azure CLI as well like below

spObjectID=$(az resource list -n vmss_name --query [*].identity.principalId --out tsv)

az keyvault set-policy -n vaultname-kvt --key-permissions get list wrapKey unwrapKey --secret-permissions backup restore --certificate-permissions get list delete create --object-id spObjectID

**Always share your knowledge**

Sunday, September 12, 2021

Set the compilation debug flag in IIS web.config

 One of the first things I check when troubleshooting ASP.NET applications is the debug flag in the web.config.

For this, I’ve written the following function that use the [xml] accelerator to cast the web.config file contents as an xmlDocument and use PowerShell’s dot notation to get the debug flag value, or use the .NET methods of the dom document to set the debug flag value:

function SetWebConfigDebugFlag {


        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]



        [ValidateSet(‘True’, ‘False’)]

        $DebugFlag = $false


$mersite= Get-Website -Name $IISsitename

$appPath = $mersite.physicalPath + "\web.config"

$config = [xml](Get-Content -Path $appPath)

 try {

if (($config.SelectSingleNode(‘configuration/system.web/compilation’)) -eq $null) {

$compilation = $config.CreateElement(‘compilation’)

$sw = $config.SelectSingleNode(‘configuration/system.web’)

[void] $sw.AppendChild($compilation)


if ($config.SelectSingleNode(‘configuration/system.web/compilation/debug’) -eq $null) {

$compilation = $config.SelectSingleNode(‘configuration/system.web/compilation’)

$compilation.SetAttribute(‘debug’, ($DebugFlag.ToString()).ToLower())

} else {

$config.configuration.‘system.web’.compilation.debug = $DebugFlag



if($?) {

$Result = ‘Success’

$ThisDebugFlag = $DebugFlag

} else {

$Result = ‘Error saving the config file’

$ThisDebugFlag = ""



catch {

$Result = $appPath.Exception.Message

if ($Result -eq ‘You cannot call a method on a null-valued expression.’) {

$Result = "Configuration does not contain a ‘system.web’ section"

$ThisDebugFlag = ""



New-Object -TypeName PSObject -Property @{‘Path’=$appPath; ‘DebugFlag’=$ThisDebugFlag; ‘Result’=$Result }


#Calling the above function by passing iissitename and Debugflag params.

SetWebConfigDebugFlag -IISsitename "iissitename" -DebugFlag 'True'