# Exporting Connections

## Overview

This document describes one method of exporting connection data along with connection parameters to a JSON file using a supplied Python script. The resulting file can then be used import connections to another Keeper Connection Manager instance (as [documented here](/keeper-connection-manager/using-keeper-connection-manager/creating-connections/batch-import-and-api.md#importing-connections-with-csv-json-or-yaml)) or for migration to the Keeper Connection Manager (Cloud) version.

The example provided in this document is based on the standard [Auto Docker Install](/keeper-connection-manager/installation/auto-docker-install.md) method of Keeper Connection Manager, using a MySQL database.

### Step 1: Open MySQL port to local machine

In order for the python file to query the database from within the Docker container, edit the `/etc/kcm-setup/docker-compose.yml` file and add the "ports" section to the "db" container as displayed below:

```yaml
db:
        image: keeper/guacamole-db-mysql :2
        restart: unless-stopped 
        environment:
            ACCEPT EULA: "Y"
            GUACAMOLE_DATABASE: "guacamole_db"
            GUACAMOLE_USERNAME: "guacamole_user"
            GUACAMOLE_PASSWORD: "XXXXXXXXXXXXXXXXXXXXXXXXX"
            GUACAMOLE_ADMIN_PASSWORD: "XXXXXXXXXXXXXXXXXXXXXXXXX"
            MYSQL_ROOT_PASSWORD: "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
        ports:
            - "3306:3306"            
```

To apply the Docker Compose update, follow these steps:

1. If using the auto-docker install, navigate to the location where the `kcm-setup.run` script is stored and execute:

   ```bash
   sudo ./kcm-setup.run apply
   ```
2. For Docker Compose installation, navigate to the folder `/etc/kcm-setup` and run:

   ```bash
   docker compose up -d
   ```

### Step 2: Install Python modules

Assuming your instance already has Python3, install the necessary Python modules:

{% tabs %}
{% tab title="MYSQL" %}

```bash
pip3 install mysql-connector
pip3 install pyYAML
```

{% endtab %}

{% tab title="POSTGRES" %}

```bash
pip3 install psycopg2-binary
pip3 install pyYAML
```

{% endtab %}
{% endtabs %}

To install `python3-pip` on your system, use the appropriate command for your operating system:

**CentOS/RHEL 8 and newer:**

```bash
sudo dnf install python3-pip
```

**CentOS/RHEL 7 and earlier:**

```bash
sudo yum install python3-pip
```

**Ubuntu:**

```bash
sudo apt install python3-pip
```

### Step 3: Create the export script

Copy and paste the below code into a file called `export.py` and place it on the Keeper Connection Manager instance, perhaps in the same location as your `kcm-setup.run` file. This Python script performs the following:

* Locates the `docker-compose.yml` file in the standard `/etc/kcm-setup/` folder
* Pulls the MySQL/POSTGRES credentials and connects to the database
* Exports the connection information and creates a file called export.json in the same folder

Depending on your environment, you may need to edit the file.

{% tabs %}
{% tab title="MYSQL EXPORT" %}
{% code title="export.py" lineNumbers="true" %}

```python
import mysql.connector
import json
import yaml

# Path to the docker-compose.yml file
docker_compose_file = '/etc/kcm-setup/docker-compose.yml'

# Function to extract database credentials from docker-compose.yml
def get_db_config_from_compose():
    with open(docker_compose_file, 'r') as file:
        # Load the docker-compose YAML file
        compose_data = yaml.safe_load(file)
        
        # Extracting the necessary information from the 'db' service
        db_service = compose_data['services']['db']
        environment = db_service['environment']
        
        db_name = environment.get('GUACAMOLE_DATABASE', 'guacamole_db')
        db_user = environment.get('GUACAMOLE_USERNAME', 'guacamole_user')
        db_password = environment.get('GUACAMOLE_PASSWORD', 'password')  # Default in case it's not present
        
        return {
            'host': 'localhost',  # Assuming the database is local since it's inside Docker
            'user': db_user,
            'password': db_password,
            'database': db_name,
            'port': 3306  # Default MySQL port
        }

def build_connection_group_paths(cursor):
    """
    Build a dictionary mapping group IDs to their full paths by resolving parent-child relationships.
    """
    cursor.execute("SELECT connection_group_id, parent_id, connection_group_name FROM guacamole_connection_group")
    groups = cursor.fetchall()

    group_paths = {}

    def resolve_path(group_id):
        if group_id is None:
            return "ROOT"
        if group_id in group_paths:
            return group_paths[group_id]
        # Find the group details
        group = next(g for g in groups if g['connection_group_id'] == group_id)
        parent_path = resolve_path(group['parent_id'])
        full_path = f"{parent_path}/{group['connection_group_name']}"
        group_paths[group_id] = full_path
        return full_path

    # Resolve paths for all groups
    for group in groups:
        resolve_path(group['connection_group_id'])

    return group_paths

# SQL query to retrieve all connections, users, groups, and attributes
query = """
SELECT
    c.connection_id,
    c.connection_name AS name,
    c.protocol,
    cp.parameter_name,
    cp.parameter_value,
    e.name AS entity_name,
    e.type AS entity_type,
    g.connection_group_id,
    g.parent_id,
    g.connection_group_name AS group_name,
    ca.attribute_name,
    ca.attribute_value
FROM
    guacamole_connection c
LEFT JOIN
    guacamole_connection_parameter cp ON c.connection_id = cp.connection_id
LEFT JOIN
    guacamole_connection_attribute ca ON c.connection_id = ca.connection_id
LEFT JOIN
    guacamole_connection_group g ON c.parent_id = g.connection_group_id
LEFT JOIN
    guacamole_connection_permission p ON c.connection_id = p.connection_id
LEFT JOIN
    guacamole_entity e ON p.entity_id = e.entity_id;
"""

def export_to_json(db_config):
    try:
        # Connect to the database
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)  # Dictionary cursor for better handling

        # Build connection group paths
        connection_group_paths = build_connection_group_paths(cursor) 

        # Execute the query
        cursor.execute(query)
        rows = cursor.fetchall()

        # Organize the data into the expected format
        connections = {}
        for row in rows:
            conn_id = row['connection_id']
            if conn_id not in connections:
                # Resolve connection group path
                group_path = connection_group_paths.get(row['connection_group_id'], "ROOT")
                connections[conn_id] = {
                    'name': row['name'],
                    'protocol': row['protocol'],
                    'parameters': {},
                    'users': [],
                    'groups': [],  # User groups go here
                    'group': group_path,  # Connection group path 
                    'attributes': {}
                }
            # Handle parameters
            if row['parameter_name']:
                connections[conn_id]['parameters'][row['parameter_name']] = row['parameter_value']
            # Handle users
            if row['entity_type'] == 'USER' and row['entity_name'] not in connections[conn_id]['users']:
                connections[conn_id]['users'].append(row['entity_name'])
            # Handle user groups
            if row['entity_type'] == 'USER_GROUP' and row['entity_name'] not in connections[conn_id]['groups']:
                connections[conn_id]['groups'].append(row['entity_name'])
            # Handle attributes
            if row['attribute_name']:
                connections[conn_id]['attributes'][row['attribute_name']] = row['attribute_value']

        # Convert to list format
        connection_list = [conn for conn in connections.values()]

        # Output the data to a JSON file
        with open('export.json', 'w') as json_file:
            json.dump(connection_list, json_file, indent=4)

        print("Export successful! Data written to export.json")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        # Close the cursor and the connection
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    # Get the database configuration from docker-compose.yml
    db_config = get_db_config_from_compose()
    export_to_json(db_config)

```

{% endcode %}
{% endtab %}

{% tab title="POSTGRES EXPORT" %}

```python
import psycopg2
import psycopg2.extras
import json
import yaml

# Path to the docker-compose.yml file
docker_compose_file = '/etc/kcm-setup/docker-compose.yml'

# Function to extract database credentials from docker-compose.yml
def get_db_config_from_compose():
    with open(docker_compose_file, 'r') as file:
        compose_data = yaml.safe_load(file)

        db_service = compose_data['services']['db']
        env = db_service['environment']

        return {
            'host': 'localhost',
            'user': env.get('GUACAMOLE_USERNAME', 'guacamole_user'),
            'password': env.get('GUACAMOLE_PASSWORD', 'password'),
            'database': env.get('GUACAMOLE_DATABASE', 'guacamole_db'),
            'port': 5432  # Default PostgreSQL port
        }

def build_connection_group_paths(cursor):
    cursor.execute("SELECT connection_group_id, parent_id, connection_group_name FROM guacamole_connection_group")
    groups = cursor.fetchall()

    group_paths = {}

    def resolve_path(group_id):
        if group_id is None:
            return "ROOT"
        if group_id in group_paths:
            return group_paths[group_id]
        group = next(g for g in groups if g['connection_group_id'] == group_id)
        parent_path = resolve_path(group['parent_id'])
        full_path = f"{parent_path}/{group['connection_group_name']}"
        group_paths[group_id] = full_path
        return full_path

    for group in groups:
        resolve_path(group['connection_group_id'])

    return group_paths

# SQL query remains the same (PostgreSQL-compatible syntax)
query = """
SELECT
    c.connection_id,
    c.connection_name AS name,
    c.protocol,
    cp.parameter_name,
    cp.parameter_value,
    e.name AS entity_name,
    e.type AS entity_type,
    g.connection_group_id,
    g.parent_id,
    g.connection_group_name AS group_name,
    ca.attribute_name,
    ca.attribute_value
FROM
    guacamole_connection c
LEFT JOIN
    guacamole_connection_parameter cp ON c.connection_id = cp.connection_id
LEFT JOIN
    guacamole_connection_attribute ca ON c.connection_id = ca.connection_id
LEFT JOIN
    guacamole_connection_group g ON c.parent_id = g.connection_group_id
LEFT JOIN
    guacamole_connection_permission p ON c.connection_id = p.connection_id
LEFT JOIN
    guacamole_entity e ON p.entity_id = e.entity_id;
"""

def export_to_json(db_config):
    try:
        conn = psycopg2.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            dbname=db_config['database'],
            port=db_config['port']
        )
        cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        connection_group_paths = build_connection_group_paths(cursor)

        cursor.execute(query)
        real_dict_rows = cursor.fetchall()
        # Convert RealDictRow objects to native dicts
        rows = [dict(row) for row in real_dict_rows]

        # Organize the data into the expected format
        connections = {}
        for row in rows:
            conn_id = row['connection_id']
            if conn_id not in connections:
                group_path = connection_group_paths.get(row['connection_group_id'], "ROOT")
                connections[conn_id] = {
                    'name': row['name'],
                    'protocol': row['protocol'],
                    'parameters': {},
                    'users': [],
                    'groups': [],
                    'group': group_path,
                    'attributes': {}
                }
            if row['parameter_name']:
                connections[conn_id]['parameters'][row['parameter_name']] = row['parameter_value']
            if row['entity_type'] == 'USER' and row['entity_name'] not in connections[conn_id]['users']:
                connections[conn_id]['users'].append(row['entity_name'])
            if row['entity_type'] == 'USER_GROUP' and row['entity_name'] not in connections[conn_id]['groups']:
                connections[conn_id]['groups'].append(row['entity_name'])
            if row['attribute_name']:
                connections[conn_id]['attributes'][row['attribute_name']] = row['attribute_value']

        with open('export.json', 'w') as json_file:
            json.dump(list(connections.values()), json_file, indent=4)

        print("Export successful! Data written to export.json")

    except psycopg2.Error as err:
        print(f"Database Error: {err}")
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    db_config = get_db_config_from_compose()
    export_to_json(db_config)

```

{% endtab %}
{% endtabs %}

To execute the script, type:

```
sudo python3 export.py
```

This will produce a file called `export.json` in the local folder.

{% hint style="warning" %}
This export.json file will likely contain connection secrets, depending on how the connections were created. Protect this file by saving it to your Keeper vault.
{% endhint %}

### Notes

* The "groups" object refers to User Groups
* The "group" object refers to the Connection Group location
* When importing this into another KCM instance, the connections will only import successfully if the Connection Group exists in the target


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.keeper.io/keeper-connection-manager/exporting-connections.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.