We have a smaller issue when it comes to library variable sets; we simple have too many of them. Especially those sets where we keep the user accounts and passwords (one set for DEV-, one for VER- and one for PROD-accounts). So every time we create a project we have to make sure we tick all of these sets in variable sets for the OD project.
This we want to solve by only having a single library variable set for all accounts and passwords regardless if it’s a DEV, VER och PROD account.
The issue with this is the move… we have a few alternatives:
Someone has to do this manually… 100+ accounts and passwords stored in keepass to be added again in a new variable set. Not the most fun job and something could indeed go wrong…
We use a rest-api call and move the variables. However then we couldn’t move the sensitive values I was told. That idea didn’t work either.
Move it via a query… simple yet efficient, the only thing we have to double check is that we validate that we do it correctly, correctly formed json and that we don’t get any duplicated values (accounts).
The process for doing this would be rather simple (I think?), as below steps:
Create a new empty library variable set
Get the ID of that new variable set
Store the JSON for all other variablesets that we want to move, and remove the pre-fixing “{“Variables”: [” text and the last “]}” text from each library variable set.
Merge together all of the JSON code and re-add above text back
Execute the following (simplified JSON below) SQL code in the OD database.
Questions about this:
Is that it? Will it work? Is there another table that I need to update?
What happens if there are duplicated variable names once I execute the query?
In the table dbo.VariableSet there is a “RelatedDocumentIds” column which i guess just contains meta-data, however since I’m no expert on the DB model I can’t say for sure how OD uses this column. Will I have to get all environment-IDs and insert these here as well if the variables that we are to merge into a single set use other environments?
Simplified JSON used in the SQL code below:
update vs
-- The merge:d variable set with sensitived value-variables.
set vs.JSON = N'{
"Variables": [
{
"Id": "6CB6DFA3-744C-48D5-9523-74688F5CA47E",
"Name": "MyPassword",
"Description": null,
"Type": "String",
"Value": "Password",
"Scope": { "Environment": [ "Environments-1" ] }
},
{
"Id": "544D555A-7008-4EB2-82ED-B8A58D1D1898",
"Name": "MyPassword",
"Description": null,
"Type": "Sensitive",
"Value": "blahblahblahblahSomePasswordBlahBlahThatIsHashed",
"Scope": { "Environment": [ "Environments-41" ] }
}
]
}'
from dbo.VariableSet vs
where vs.Id = 'variableset-LibraryVariableSets-122' -- The new variable set.
and vs.IsFrozen = 0; -- Forgot to add this, also _very important_!
I have tried to answer each of your questions as best as I can below:
Will it work? It should work, but we don’t advise users to manually update the DB in this kind of way as the risk of something going wrong is very high. Our recommendation will always be to use the UI our Octopus SDK to interact with the Octopus API even if this means having to manually update sensitive variables. But, in this case as you’re creating a new library variable set the impact on existing configurations should be minimal and can easily be reverted by deleting the new library variable set. The biggest concern we have is getting the RelatedDocumentIds right, as this is used in various looksup within Octopus.
Is there another table that I need to update? If you wanted to automatically update projects etc that use your currently library sets you’d have to update the tables related to those entities. I’d recommend not doing this, I’d recommend setting up a test project and add the new library variable set to it to make sure it doesn’t break anything.
What happens if there are duplicated variable names once I execute the query? Variables with the same name will get grouped in the UI.
In the table dbo.VariableSet there is a “RelatedDocumentIds” column which i guess just contains meta-data, however since I’m no expert on the DB model I can’t say for sure how OD uses this column. Will I have to get all environment-IDs and insert these here as well if the variables that we are to merge into a single set use other environments? The RelatedDocumentIds has to contain all scope values (environments, roles, targets) as these are used in various lookups within Octopus, so you would somehow have to extract these from the library variables sets you want to merge and create a distinct list of pipe-separated IDs for the new library variable set.
If you have the ability to test this on a non-production Octopus instance (your Octopus license allow you to install up to 3 Octopus instances so you could take a backup of your prod database and restore that to a test server) that would be the best way to achieve this to minimize the risk of impacting something on your production instance.
We’ll go ahead and create a new variable-set and merging all the other variable sets into this newly created one, and of course extract all of the scopes for the variables and store this in a piped list (distinct values only as you said), I was thinking of the following code for extracting environments, roles and machines etc. into the RelatedDocumentIds column:
with cte_getRelDocs
as ( select distinct
[Key] = oj2.[Key],
RelatedDocumentIds = replace(replace(replace(oj2.Value, '["', ''), '"]', ''), '","', '|')
from OctopusDB.dbo.VariableSet vs with (nolock)
cross apply openjson(json_query(vs.JSON, '$.Variables')) oj
cross apply openjson(json_query(oj.Value, '$.Scope')) oj2
where vs.Id in ( 'variableset-LibraryVariableSets-121',
'variableset-LibraryVariableSets-122',
'variableset-LibraryVariableSets-123',
'variableset-LibraryVariableSets-141',
'variableset-LibraryVariableSets-3',
'variableset-LibraryVariableSets-4',
'variableset-LibraryVariableSets-5'
)
),
cte_distRelDocs
as ( select distinct
ss.value
from cte_getRelDocs cg
cross apply string_split(cg.RelatedDocumentIds, '|') ss
)
select RelatedDocumentIds = ( select '|' + cd.Value as [text()]
from cte_distRelDocs cd
order by cd.value
for xml path ('')
) + '|'
This would produce the following piped list of environments: |Environments-1|Environments-161|Environments-22|Environments-23|Environments-24|Environments-25|Environments-26|Environments-28|Environments-29|Environments-41|Environments-42|
I’ve tried it on a few different variablesets that contains target roles and/or machines and the code seems to pick up all of these into the piped result.
I assume that the order of the piped-list doesn’t matter… or does it matter?