Double variable substitution inside step template

Legacy Uncategorised
1

Hi, I have a problem with variable substitution.

Here is my setup:

  • A variable set containing variables of form: “User.[INSTANCE_X].Name” = [Some user name] and their corresponding safe variables “User.[INSTANCE_X].Password” = [password]
  • A step template created to execute arbitrary SQL on a server (tested and working with direct user/password input) / This template is an edit from the community one.
  • A project which has an input variable “Instance”.

On the process of this project, I have a step using the template mentioned above with the following custom expression “#{User.#{Instance}.Name}” as input for the user name parameter.

This doesn’t work since the log of the step says “Login failed for user ‘#{User.#{Instance}.Name}’.”

Any suggestion is welcome.

2

Hi Arnaud,

Thanks for getting in touch! The way that you have described these variables can not be done within Octopus. However we do have a couple of suggestions for using variables for these tricky scenarios.
However we are not entirely sure if it will work for you unless you provide us wit a bit more information:

  • Where are you using these variables.
  • Do you know all of your instances at deployment time.
  • Could you provide screen shots of your variable sets and project variables.

Hopefully we will be able to figure something out for you.

Thanks,
Daniel

3

Hi, thanks for coming back.

  • For now I used this type of syntax only as input to step templates.
  • Yes the instance list is discrete, but quite huge and will evolve with the time. The reason I want to kind of abstract that away from my deployment scripts.
  • Here is a sample from my varibale list:

…
{“Id”:“f965a23e-7fa5-4035-8445-296b2feb3ad2”,“Name”:“User.INSTANCE01.SA.Userid”,“Value”:“sa”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“27190b1b-5367-4762-bac8-b8c51ec6cfe5”,“Name”:“User.INSTANCE01.SA.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“f0ffd991-2bcd-43fe-bf95-66bb2c0aa331”,“Name”:“User.INSTANCE01.PERF.Userid”,“Value”:“INSTANCE01-PERF”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“a9a850f2-3567-4bcb-a7c6-a028e28fd438”,“Name”:“User.INSTANCE01.PERF.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“bb8fecc8-d548-4851-bd71-461f2235df8a”,“Name”:“User.INSTANCE02.SA.Userid”,“Value”:“sa”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“36b53e2a-3b54-42eb-b459-cc9e65741e37”,“Name”:“User.INSTANCE02.SA.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“eaf01c00-f5b1-41c3-8e61-7746e4f37588”,“Name”:“User.INSTANCE02.PERF.Userid”,“Value”:“INSTANCE02-PERF”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“10810a90-8473-477b-8ad2-292c581f3362”,“Name”:“User.INSTANCE02.PERF.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“c3c07b4c-4f20-4e8f-a4b3-991007cff909”,“Name”:“User.INSTANCE03.SA.Userid”,“Value”:“sa”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“20f3110c-11ef-4409-8e62-370e9bb131a7”,“Name”:“User.INSTANCE03.SA.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“0f3dc6ad-b45a-492d-97e8-cef4fe84acc5”,“Name”:“User.INSTANCE03.PERF.Userid”,“Value”:“INSTANCE03-PERF”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“5da759af-7727-4532-8310-3e6e97ee8d58”,“Name”:“User.INSTANCE03.PERF.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“d47798e0-4e90-41ff-9408-cfcd788f5f28”,“Name”:“User.INSTANCE04.SA.Userid”,“Value”:“sa”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“9bf4d8ec-67be-4ec7-9952-ec999ea3438e”,“Name”:“User.INSTANCE04.SA.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“ac28fe06-1acf-42b1-a213-59a27a07f020”,“Name”:“User.INSTANCE04.PERF.Userid”,“Value”:“INSTANCE04-PERF”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“ea8aec85-177d-4495-8031-38a4b8d1d77b”,“Name”:“User.INSTANCE04.PERF.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“7e152979-3e3a-42aa-a94b-3f69aef7bad3”,“Name”:“User.INSTANCE05.SA.Userid”,“Value”:“sa”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“6eb40268-b777-49bc-9eeb-e0e447bc7a76”,“Name”:“User.INSTANCE05.SA.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
{“Id”:“f0a8bba4-5ff5-4cc8-b297-777f135f63bd”,“Name”:“User.INSTANCE05.PERF.Userid”,“Value”:“INSTANCE05-PERF”,“Scope”:{},“IsSensitive”:false,“IsEditable”:true,“Prompt”:null},
{“Id”:“f1731ba8-bc97-4cdf-9db7-1bc7e3c59bea”,“Name”:“User.INSTANCE05.PERF.Password”,“Value”:null,“Scope”:{},“IsSensitive”:true,“IsEditable”:true,“Prompt”:null},
…

and here is an extract of one of my actions step:

  "Actions": [
    {
      "Id": "0a30e963-514f-40f7-9e61-eb48cbff8a46",
      "Name": "Create DBA User",
      "ActionType": "Octopus.Script",
      "Environments": [],
      "Channels": [],
      "Properties": {
        "Octopus.Action.Script.Syntax": "PowerShell",
        "Octopus.Action.Script.ScriptBody": "$varDBInstance = $OctopusParameters['SQLInstance']\n$varSQLDatabase = $OctopusParameters['SQLDatabase']\n$varDBUser = $OctopusParameters['SQLUser']\n$varDBPassword = $OctopusParameters['SQLPassword']\n$varSqlScript = $OctopusParameters['SQLScript']\n\n$varConnectionString = \"Server=$varDBInstance;uid=$varDBUser;password=$varDBPassword;Database=$varSQLDatabase;Integrated Security=False;\"\n\n$varConnection = New-Object System.Data.SqlClient.SqlConnection;\n$varConnection.ConnectionString = $varConnectionString;\n$varConnection.Open();\n\n$queries = [System.Text.RegularExpressions.Regex]::Split($varSqlScript, \"^\\s*GO\\s*`$\", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)\n$queries | ForEach-Object {\n  $q = $_\n  if ((-not [String]::IsNullOrEmpty($q)) -and ($q.Trim().ToLowerInvariant() -ne \"go\")) {\n    Write-Host \"Executing: $q\"\n    #if($q.TrimStart().ToLowerInvariant().StartsWith(\"select\")) {\n      #$ds = $varConnection.ExecuteWithResults($q)\n      #Foreach ($t in $ds.Tables) {\n      #  Foreach ($r in $t.Rows) {\n      #    Foreach ($c in $t.Columns) {\n      #      Write-Host $c.ColumnName \"=\" $r.Item($c)\n      #    }\n      #  }\n      #}\n    #}\n    #else {\n      $command = $varConnection.CreateCommand();\n      $command.CommandText = $q;\n      $command.ExecuteNonQuery();\n    #}\n  }\n}",
        "Octopus.Action.Template.Id": "ActionTemplates-3",
        "Octopus.Action.Template.Version": "27",
        "SQLDatabase": "master",
        "SQLInstance": "#{SQLInstance}",
        "SQLUser": "#{User.#{SQLInstance}.SA.Userid}",
        "SQLPassword": "#{User.#{SQLInstance}.SA.Password}",
        "SQLScript": "CREATE LOGIN [#{User.#{ClientName}.DBA.Userid}] WITH PASSWORD=N'#{User.#{ClientName}.DBA.Password}', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF"
      },
      "SensitiveProperties": {}
    },

I was considering writing my own function inside a script module to perform these replacements inside my code, but it makes my step templates less re-usable and I am afraid that octopus will not mask the sensitive variables at all anymore in this scenario.

Can we create sensible output variables?

What are my other options?

4

Hi, any feedback?

5

Hi Arnaud,

Sorry fro the delay in getting back to you here I have been trying to get my head around a solution. (I also had this typed out and forgot to hit send this afternoon).
I think the best solution here would be to define your variables as arrays and then you can use a variable and find what you need with a small script.
As you do have a PowerShell script running the step it should work as you can iterate through the values then until you find your match.

So for example if you variable names were: User.[INSTANCE01].SA.Userid
You would be able to iterate through the array and as you are already using PowerShell this would give you a few options.

Vanessa

6

Hi, if the only solution is relying on code, then I can even keep the structure as it is. I can generate a variable name and query it.

My issue with this is that I will need to change my step templates to have logic which doesn’t belong to them in order to support this. (Normally the double variable substitution is mostly used in step template input variables)

Thanks for checking anyway.

7

Hi Arnaud,

I also can’t help feel this is complicated but could be redone with bound variables? I just feel like I am missing something here. You have your variables, and you know your instance. Is it maybe the use of the Step Template that is throwing everything off?
The Step Template is really the placeholder, and when it is inserted into the project it can have exact variables or project variables that are bound and scoped to specific environments to be replaced at deployment time.

http://docs.octopus.com/display/OD/Binding+syntax
But again I feel I am missing something and I just can’t wrap my brain around it.

Vanessa

8

Hi, I finally figured out a solution which still allows me to have perfectly independent step templates while keeping my double variable name resolution in place.

So the first step of my project is now a script execution (actually a step template too) called INFER which takes as input a newline separated list of “A=B” string where A is the name of an output variable this step should create and B is the string with double variable replacements.

The script uses powershell to parse the input, get values from octopus variables and replace in string. This one doesn’t support any of the subtlety of the octopus engine, but it’s not needed since:

Then on my project level, I have a set of variables with values like this: VAR = #{Octopus.Action[INFER].Output.A} (Surprisingly enough this works perfectly)

And finally further steps use the VAR variable. Since on the step itself we’re back to plain octopus replacement, if there are transformations needed they can be executed here.

Hi have no interest in working with GitHub, but here is a copy of the step template.

Octopus_-Variable-_Infer.json (2 KB)