Guide for migrating Keycloak Groups/Teams to CX Teams
This document provides a step-by-step guide for exporting teams from Keycloak, formatting the team data, and importing it into CX Teams that uses MongoDB as underlying Database.
The script must be run at time of deployment of release containing the implementation of CX Teams in unified-admin, make sure that no team exists in unified-admin after deployment. We should run the script to import all the existing teams from Keycloak before creating new teams in unified-admin.
An agent can only belong to one team. If an agent is in multiple teams, they will be removed from all but one.
In teams with multiple supervisors, one will be the primary supervisor, and the others will be secondary supervisors.
Team assignment is mandatory for agent and supervisor roles only. Users with other roles i.e (i.e admin, routing-manager, quality-manager etc) are not required to be assigned to a team.
A user shouldn’t have more than one custom CX roles assigned to it.
If user has an admin role then it shouldn’t have an agent/supervisor or any other role assigned to it. Same for all other roles, one users should only have one CX role assigned to it apart from uma_authorization, offline_access and default-roles-expertflow.
Make sure only users with agent or supervisor roles are part of team. Remove all user from teams with neither of role assigned to them, its mandatory
Any updation in user teams will only be reflected once the user re-logins the system.
Please make sure that proper roles (supervisor/agent) are assigned to the user.
Prerequisites
Access to a Linux machine.
Keycloak and MongoDB set up in your targeted machine.
jq
installed on the Linux machine. If not installed, follow the installation steps below.
Installation of Required Tools
To install jq on ubuntu environment, execute this command:
sudo apt-get install -y jq
To install jq on RHEL/CentOS 7/8 Server environment, execute this command:
curl -L https://github.com/jqlang/jq/releases/download/jq-1.7.1/jq-linux-amd64 > jq-linux-amd64
chmod +x jq-linux-amd64
mv jq-linux-amd64 /usr/local/bin/jq
Once the installation of Pre-requisites is done, we can start the process of Exporting of Keycloak teams, Formatting them and then Importing them to CX Teams Structure.
Export Teams from Keycloak:
Create a script file named export_teams_from_keycloak.sh
and copy the below given script to this file.
kubectl -n ef-external exec -ti ef-postgresql-0 -- /opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash -c "psql \"postgresql://sa:Expertflow123@localhost/keycloak_db\" -c \"COPY (
WITH UniqueUsernames AS (
SELECT
ue.username,
MAX(CASE WHEN ga.name = 'supervisor' THEN 1 ELSE 0 END) AS has_supervisor_attribute
FROM
keycloak_group kg
LEFT JOIN
user_group_membership um ON kg.id = um.group_id
LEFT JOIN
group_attribute ga ON kg.id = ga.group_id
LEFT JOIN
user_entity ue ON um.user_id = ue.id
JOIN
realm r ON r.id = kg.realm_id
WHERE
r.name = 'expertflow'
AND kg.name NOT IN ('senior_agents_permission', 'agents_permission')
GROUP BY
ue.username
),
AggregatedData AS (
SELECT
kg.id AS group_id,
kg.name AS group_name,
ue.id AS user_id,
ue.username,
ue.first_name,
ue.last_name,
MAX(CASE WHEN ga.name = 'supervisor' THEN ga.value ELSE NULL END) AS supervisor_names,
MAX(CASE WHEN ga.name = 'ciscoTeamId' THEN ga.value ELSE NULL END) AS ciscoTeamId_value
FROM
keycloak_group kg
LEFT JOIN
user_group_membership um ON kg.id = um.group_id
LEFT JOIN
user_entity ue ON um.user_id = ue.id
LEFT JOIN
group_attribute ga ON kg.id = ga.group_id
JOIN
realm r ON r.id = kg.realm_id
WHERE
r.name = 'expertflow'
AND kg.name NOT IN ('senior_agents_permission', 'agents_permission')
GROUP BY
kg.id, kg.name, ue.id, ue.username, ue.first_name, ue.last_name
),
SupervisorDetails AS (
SELECT
ad.group_id,
ad.group_name,
ad.user_id,
ad.username,
ad.first_name,
ad.last_name,
json_agg(json_build_object('username', ue_sup.username, 'user_id', ue_sup.id)) AS supervisor_details,
ad.ciscoTeamId_value
FROM
AggregatedData ad
LEFT JOIN LATERAL
unnest(string_to_array(ad.supervisor_names, ',')) AS sup(username) ON TRUE
LEFT JOIN
user_entity ue_sup ON trim(sup.username) = ue_sup.username
GROUP BY
ad.group_id, ad.group_name, ad.user_id, ad.username, ad.first_name, ad.last_name, ad.ciscoTeamId_value
)
SELECT
json_agg(json_build_object(
'group_id', group_id,
'group_name', group_name,
'user_id', user_id,
'username', username,
'first_name', first_name,
'last_name', last_name,
'attribute_name1', 'supervisor',
'attribute_value1', supervisor_details,
'attribute_name2', 'ciscoTeamId',
'attribute_value2', ciscoTeamId_value
)) AS json_data
FROM
SupervisorDetails
) TO STDOUT\"" | sed '1,6d' > teams.json
Once the script is copied to the file, run this script file using the following command
chmod +x export_teams_from_keycloak.sh
./export_teams_from_keycloak.sh
After running the script file, a new file will be created named teams.json . This file contains all the Teams, Agents and Supervisors of existing Keycloak instance.
Format the Exported Team Data
After exporting the Teams from Keycloak, we have to format the exported Teams to make them optimized to import to CX. For this process, we need to follow the below given steps:
Create a script file named formatting_teams.sh
and copy the below given script to this file. The below script takes new created file teams.json and format that file to optimize the import process.
#!/bin/bash
outputFile="teams.json"
jq 'map(
.supervisor = (
if .attribute_name1 == "supervisor" and (.attribute_value1 | type) == "array" then
.attribute_value1
else []
end
) |
.ciscoTeamId = (
if .attribute_name2 == "ciscoTeamId" then .attribute_value2 else null end
) |
{
group_id: .group_id,
group_name: .group_name,
user_id: (if .user_id == null then "" else .user_id end),
username: (if .username == null then "" else .username end),
first_name: (if .first_name == null then "" else .first_name end),
last_name: (if .last_name == null then "" else .last_name end),
supervisor: .supervisor,
ciscoTeamId: .ciscoTeamId
}
) |
group_by(.group_name) |
map({
(.[0].group_name): map({
group_id,
user_id,
username,
first_name,
last_name,
supervisor,
ciscoTeamId
})
}) | add' "$outputFile" > "${outputFile}.tmp" && mv "${outputFile}.tmp" "$outputFile"
Once the script is copied to the file, run this script file using the following command
chmod +x formatting_teams.sh
./formatting_teams.sh
After running this script file, teams.json will be formatted to a much structured and Optimized JSON.
Import Formatted Teams into MongoDB
Once the Teams are formatted, we are now ready to import the teams to CX. For this Process, we need to follow the below given steps:
Create a script file named import_teams_to_mongo.sh
and copy the below given script to this file. The below script takes formatted teams.json and iterate through each Team, then importing each Team to MongoDB against CX Teams.
If TLS + Auth is enabled:
#!/bin/bash
# Define the path to the JSON file
json_file="teams.json"
# Get the current timestamp in the desired format
current_timestamp=$(date -u +"%Y-%m-%dT%H:%M:%S.%3NZ")
# Process JSON and prepare it for MongoDB import
jq -c --arg timestamp "$current_timestamp" 'to_entries | map({
team_name: .key,
supervisor_Id: (if (.value[0].supervisor | length > 0) then .value[0].supervisor[0].user_id else null end),
source: (if .value[0].ciscoTeamId != null then "CISCO" else "CX" end),
created_by: "1",
team_Id: (if .value[0].ciscoTeamId != null then .value[0].ciscoTeamId else .value[0].group_id end),
v: 0,
createdAt: $timestamp,
updatedAt: $timestamp
})' $json_file > /tmp/teams_for_mongo.json
# Process JSON for team members and prepare it for MongoDB import
jq -r --arg timestamp "$current_timestamp" 'to_entries |
map({key, value: ( .value | map(select(.username != null)) ) }) |
map(select(.value | length > 0)) |
.[] |
.value[] |
{team_Id: (if .ciscoTeamId != null then .ciscoTeamId else .group_id end), userId: .user_id, username: .username, createdAt: $timestamp, updatedAt: $timestamp}' $json_file |
jq -s 'unique_by(.username) | map(select(.username != null and .username != "") | {team_Id: .team_Id, type: "agent", userId: .userId, username: .username, v: 0, createdAt: .createdAt, updatedAt: .updatedAt})' > /tmp/members_for_mongo.json
# Process JSON for secondary supervisors and prepare it for MongoDB import
jq -r --arg timestamp "$current_timestamp" '
to_entries
| map(select(.value[0].supervisor | length > 1))
| map(.key as $team_name | .value[0] as $first_entry | .value[0].supervisor[1:]
| map({
team_Id: ($first_entry.ciscoTeamId // $first_entry.group_id),
type: "secondary-supervisor",
userId: .user_id,
username: .username,
__v: 0,
createdAt: $timestamp,
updatedAt: $timestamp
})
)
| flatten
| map(select(.username != null))
| if length == 0 then empty else . end' $json_file > /tmp/secondary_supervisors_for_mongo.json
# Copy the prepared JSON files to the MongoDB pod
kubectl -n ef-external cp /tmp/teams_for_mongo.json mongo-mongodb-0:/tmp/teams_for_mongo.json
kubectl -n ef-external cp /tmp/members_for_mongo.json mongo-mongodb-0:/tmp/members_for_mongo.json
kubectl -n ef-external cp /tmp/secondary_supervisors_for_mongo.json mongo-mongodb-0:/tmp/secondary_supervisors_for_mongo.json
export MONGODB_ROOT_PASSWORD=$(kubectl get secret --namespace ef-external mongo-mongodb -o jsonpath="{.data.mongodb-root-password}" | base64 -d)
# Import into MongoDB
kubectl exec -n ef-external mongo-mongodb-0 -- mongoimport --authenticationDatabase admin -u root -p"${MONGODB_ROOT_PASSWORD}" --ssl --tlsInsecure --sslPEMKeyFile /certs/mongodb.pem --sslCAFile /certs/mongodb.pem --db adminPanel --collection teams --file /tmp/teams_for_mongo.json --jsonArray --mode merge --upsertFields team_Id
echo "Teams collection import exited with $?"
# Import team member data into MongoDB
kubectl exec -n ef-external mongo-mongodb-0 -- mongoimport --authenticationDatabase admin -u root -p"${MONGODB_ROOT_PASSWORD}" --ssl --tlsInsecure --sslPEMKeyFile /certs/mongodb.pem --sslCAFile /certs/mongodb.pem --db adminPanel --collection teammembers --file /tmp/members_for_mongo.json --jsonArray --mode merge --upsertFields username,type
echo "teammembers collection import existed with $?"
# Add a check before importing secondary supervisors
if [ -s /tmp/secondary_supervisors_for_mongo.json ]; then
# Import secondary supervisors data into MongoDB
kubectl exec -n ef-external mongo-mongodb-0 -- mongoimport --authenticationDatabase admin \
-u root -p"${MONGODB_ROOT_PASSWORD}" \
--ssl --tlsInsecure \
--sslPEMKeyFile /certs/mongodb.pem \
--sslCAFile /certs/mongodb.pem \
--db adminPanel \
--collection teammembers \
--file /tmp/secondary_supervisors_for_mongo.json \
--jsonArray \
--mode merge \
--upsertFields username,team_Id,type
echo "teammembers Supervisors collection import existed with $?"
else
echo "No secondary supervisors to import"
fi
# Feedback
echo "Teams and team members have been created/updated in MongoDB"
If TLS + Auth is disabled:
#!/bin/bash
# Define the path to the JSON file
json_file="teams.json"
# Get the current timestamp in the desired format
current_timestamp=$(date -u +"%Y-%m-%dT%H:%M:%S.%3NZ")
# Process JSON and prepare it for MongoDB import
jq -c --arg timestamp "$current_timestamp" 'to_entries | map({
team_name: .key,
supervisor_Id: (if (.value[0].supervisor | length > 0) then .value[0].supervisor[0].user_id else null end),
source: (if .value[0].ciscoTeamId != null then "CISCO" else "CX" end),
created_by: "1",
team_Id: (if .value[0].ciscoTeamId != null then .value[0].ciscoTeamId else .value[0].group_id end),
v: 0,
createdAt: $timestamp,
updatedAt: $timestamp
})' $json_file > /tmp/teams_for_mongo.json
# Process JSON for team members and prepare it for MongoDB import
jq -r --arg timestamp "$current_timestamp" 'to_entries |
map({key, value: ( .value | map(select(.username != null)) ) }) |
map(select(.value | length > 0)) |
.[] |
.value[] |
{team_Id: (if .ciscoTeamId != null then .ciscoTeamId else .group_id end), userId: .user_id, username: .username, createdAt: $timestamp, updatedAt: $timestamp}' $json_file |
jq -s 'unique_by(.username) | map(select(.username != null and .username != "") | {team_Id: .team_Id, type: "agent", userId: .userId, username: .username, v: 0, createdAt: .createdAt, updatedAt: .updatedAt})' > /tmp/members_for_mongo.json
# Process JSON for secondary supervisors and prepare it for MongoDB import
jq -r --arg timestamp "$current_timestamp" '
to_entries
| map(select(.value[0].supervisor | length > 1))
| map(.key as $team_name | .value[0] as $first_entry | .value[0].supervisor[1:]
| map({
team_Id: ($first_entry.ciscoTeamId // $first_entry.group_id),
type: "secondary-supervisor",
userId: .user_id,
username: .username,
__v: 0,
createdAt: $timestamp,
updatedAt: $timestamp
})
)
| flatten
| map(select(.username != null))
| if length == 0 then empty else . end' $json_file > /tmp/secondary_supervisors_for_mongo.json
# Copy the prepared JSON files to the MongoDB pod
kubectl -n ef-external cp /tmp/teams_for_mongo.json mongo-mongodb-0:/tmp/teams_for_mongo.json
kubectl -n ef-external cp /tmp/members_for_mongo.json mongo-mongodb-0:/tmp/members_for_mongo.json
kubectl -n ef-external cp /tmp/secondary_supervisors_for_mongo.json mongo-mongodb-0:/tmp/secondary_supervisors_for_mongo.json
# Import into MongoDB
kubectl exec -n ef-external mongo-mongodb-0 -- mongoimport --db adminPanel --collection teams --file /tmp/teams_for_mongo.json --jsonArray --mode merge --upsertFields team_Id
# Import team member data into MongoDB
kubectl exec -n ef-external mongo-mongodb-0 -- mongoimport --db adminPanel --collection teammembers --file /tmp/members_for_mongo.json --jsonArray --mode merge --upsertFields username,type
# Add a check before importing secondary supervisors
if [ -s /tmp/secondary_supervisors_for_mongo.json ]; then
# Import secondary supervisors data into MongoDB
kubectl exec -n ef-external mongo-mongodb-0 -- mongoimport --db adminPanel --collection teammembers --file /tmp/secondary_supervisors_for_mongo.json --jsonArray --mode merge --upsertFields username,team_Id,type
echo "teammembers Supervisors collection import existed with $?"
else
echo "No secondary supervisors to import"
fi
# Feedback
echo "Teams and team members have been created/updated in MongoDB"
Once the script is copied to the file, run this script file using the following command:
chmod +x import_teams_to_mongo.sh
./import_teams_to_mongo.sh
After running this script file, all the Teams in teams.json will be imported to CX Teams along with Agents and Supervisors of each team.
Only those Agents/Supervisors that are part of Team will be exported as Team-Members.