Skip to main content
Skip table of contents

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:

POWERSHELL
sudo apt-get install -y jq

To install jq on RHEL/CentOS 7/8 Server environment, execute this command:

POWERSHELL
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.

CODE
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

CODE
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.

CODE
#!/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

CODE
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:

CODE
#!/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:

CODE
#!/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:

CODE
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.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.