Connection Tool - A Tool to Programmatically Remove Unused Datasource Connections, and List All Connections

Managing connections within your Sisense server can become complex over time, if there are a large number of connections, and connections are often added, and replace earlier datasource connections. In some scenarios unused connections can accumulate, potentially cluttering the Connection Manager UI with no longer relevant connections. Although unused connections typically represent minimal direct security risk, it's considered best practice to maintain a clean, organized list of connections, and in some scenarios it can be desired to remove all unused connections.

Sisense prevents the deletion of connections actively used in datasources, safeguarding your dashboards and datasources from disruptions. However, inactive or "orphaned" connections remain after datasources are deleted or a connection is replaced, potentially contributing to unnecessary UI complexity in the connection manager UI.

Connections can be of any type Sisense supports, common types include various SQL connections, Excel files, and CSV files, as well as many data providers, such as Big Panda. This tool can also be used to list all connections, with no automatic deletion of unused connections.

Introducing the Sisense Connection Prune Tool

The Sisense Connection Prune Tool is a Python-based Sisense API based tool designed to programmatically identify and delete unused connections. It generates a CSV report listing all connections and their associated datasources, streamlining your connection management process. If desired, it can automatically remove all unused connections automatically from a Sisense server.

Using the Tool, sourcing the Virtual Environment, generating the Connection CSV

CSV Output of Used Connections and Associated Datasources

CSV opened visually to view as Table, Excel and other programs and text editors can open CSV files

Sisense Connection Prune Tool README

Here's the full README included with the tool:

# Sisense Connection Prune Tool

A command-line tool to list used data connections and prune unused Sisense connections via CSV. It allows you to generate a CSV file of all connections and their dependencies, then delete those connections if needed, after removing the connections to keep from the CSV.

## Features
- **Dry Run Mode**: Simulate deletions without making any changes.
- **CSV-Based Flow**: Easily inspect and list connections to remove before deletion.
- **Logging**: Extensive logs if needed.
- **Error Handling**: Clear and descriptive messages for issues encountered during execution.

## Usage

1. **Activate the Virtual Environment**  
   After downloading this project folder, activate the Python virtual environment bundled with it that includes all Python dependencies.  
   - **Windows**: `venv\Scripts\activate`  
   - **macOS/Linux**: `source venv/bin/activate`

2. **Configure the Tool**  
   Open the `config.yaml` file and set your Sisense server URL, bearer token, CSV file path, and log file path. For example:

   ```yaml
   server_url: "https://your.sisense.server"
   bearer_token: "your_bearer_token_here"
   dry_run: true
   csv_file_path: "connections.csv"
   log_file_path: "connection_tool.log"
   ```

   - **server_url**: The URL of your Sisense instance.
   - **bearer_token**: Your Sisense API token for authentication.
   - **dry_run**: If set to `true`, deletions will be simulated (no real deletions).
   - **csv_file_path**: Where the CSV file should be created and read from.
   - **log_file_path**: Where log file will be stored.

3. **Run the Tool**  
   ```bash
   python3 ConnectionPruneTool.py
   ```
   You will be prompted to choose an option:

   1. **Generate connection CSV**  
      - Fetches all Sisense connections.
      - Immediately removes (or simulates removing, if `dry_run` is `true`) any connection with no dependencies.
      - Writes all remaining connections and their dependencies to the CSV file.
      - **Important**: Inspect the CSV file and remove lines for any connections you want to **keep**.

   2. **Delete connections from CSV list**  
      - Reads the CSV file.
      - Removes or simulates removing each connection still listed.
      - Provides a summary report of which connections were deleted or bypassed.

4. **Review the Logs**  
   Check the file specified in `log_file_path` for a record of all actions taken or simulated if needed. This is helpful for understanding what happened during each run and diagnosing any issues.

## Example Workflow

1. **Generate CSV**  
   ```bash
   python3 ConnectionPruneTool.py
   # Choose option 1 when prompted
   ```
   After generation, open the CSV file and **delete rows** corresponding to any connections you want to **keep**.

2. **Delete Connections**  
   ```bash
   python3 ConnectionPruneTool.py
   # Choose option 2 when prompted
   ```
   The tool will read the CSV and delete the remaining listed connections (or simulate deletion, if `dry_run` is enabled).

## Notes
- Unused connections are removed automatically in step 1, without a CSV step
- To keep a connection, remove its line from the CSV before proceeding with deletion.
- If `dry_run` is set to `true`, no actual deletions will occur, only simulated logs and printed messages.
- The log file will be cleared at the start of each run, so be sure to review or archive logs (or change log file name in config), if needed.

This is a command-line tool to list used data connections and prune unused Sisense connections, in general and via a CSV list. It allows a user with a data admin or higher bearer token to generate a CSV file of all connections and their dependencies, then delete those connections if needed, from the remaining connections in the CSV.

Example Output:

Deleted unused connection: Old_DB_Connection (ID: 123abc)

CSV file generated at connections.csv. It contains 25 row(s) of active connections.

Please review and remove lines for connections you want to keep before running deletion step by running tool again. Remaining lines will be deleted in deletion mode.

Summary Report:
Total lines in CSV (active used connections): 25

Deleted Unused Connections:
 - Old_DB_Connection

No connections were bypassed.

API Endpoints Used

Retrieve connections: GET /api/v2/connections
Retrieve dependencies: GET /api/v2/connections/{connection_id}/getAllDependencies
Delete connection: DELETE /api/v2/connections/{connection_id}

Full Code

connections.py - Uses Sisense API endpoints to:

Fetch all connections (GET /api/v2/connections).
Retrieve datasource dependencies for a specific connection (GET /api/v2/connections/{connection_id}/getAllDependencies).
Delete a specific connection (DELETE /api/v2/connections/{connection_id}).

from helperFunctions import load_config, api_get, api_delete

config = load_config()
headers = {"Authorization": f"Bearer {config['bearer_token']}"}
base_url = config["server_url"]


# Retrieve all connections from Sisense
def get_all_connections():
    endpoint = f"{base_url}/api/v2/connections"
    return api_get(endpoint, headers)


# Retrieve dependencies of a specific connection
def get_connection_dependencies(connection_id):
    endpoint = f"{base_url}/api/v2/connections/{connection_id}/getAllDependencies"
    return api_get(endpoint, headers)


# Delete a specific connection
def delete_connection(connection_id):
    endpoint = f"{base_url}/api/v2/connections/{connection_id}"
    return api_delete(endpoint, headers)

helperFunctions.py -

Uses the Requests library to handle API requests (GET/DELETE).
Catches and logs API errors.
Uses PyYAML to read the config.yaml file for configuration.

import yaml
import requests
import logging


# Load configuration from YAML file
def load_config():
    with open("config.yaml", "r") as file:
        return yaml.safe_load(file)


# Configure logging settings
def setup_logging(log_file):
    logging.basicConfig(
        filename=log_file,
        level=logging.INFO,
        format="%(asctime)s - %(levelname)s - %(message)s",
    )


# Perform a GET request with error handling
def api_get(endpoint, headers):
    try:
        response = requests.get(endpoint, headers=headers, verify=False)
        response.raise_for_status()
        return response.json()
    except requests.RequestException as e:
        logging.error(f"GET request failed: {e}")
        print(f"Error during GET request: {e}")
        return None


# Perform a DELETE request with error handling
def api_delete(endpoint, headers):
    try:
        response = requests.delete(endpoint, headers=headers, verify=False)
        response.raise_for_status()
        return response
    except requests.RequestException as e:
        logging.error(f"DELETE request failed: {e}")
        print(f"Error during DELETE request: {e}")
        return None

ConnectionPruneTool.py -

Serves as the entry point for the entire tool.
Implements a CLI for user interaction.
Invokes connections.py and helperFunctions.py to retrieve, analyze, and optionally delete Sisense connections.
Uses Pandas to build a DataFrame of connection details and writes the results to a CSV file.
Reads back the CSV file to remove selected connections after manual edits.
Manages logs and prints summary reports to the terminal.

import pandas as pd
import logging
import os

from connections import (
    get_all_connections,
    get_connection_dependencies,
    delete_connection,
)
from helperFunctions import load_config, setup_logging

# Load config yaml
config = load_config()
setup_logging(config["log_file_path"])

# Clear log file at start
open(config["log_file_path"], "w").close()

dry_run = config["dry_run"]
csv_path = config["csv_file_path"]


def generate_connections_csv():
    """
    Retrieves all connections from Sisense, deletes any that have no dependencies (unless in dry_run mode),
    and writes the remaining used connections and their dependent data models to a CSV file.
    """
    # Create or clear existing CSV
    open(csv_path, "w").close()

    connections = get_all_connections()
    if connections is None:
        logging.error("Failed to retrieve connections.")
        print("Failed to retrieve connections.")
        return

    data = []
    deleted = []
    bypassed = []

    # Go through each connection returned from the API
    for conn in connections:
        dependencies = get_connection_dependencies(conn["oid"])
        if dependencies is None:
            logging.warning(
                f"Failed to retrieve dependencies for: {conn['name']} ({conn['oid']})"
            )
            print(
                f"Failed to retrieve dependencies for: {conn['name']} ({conn['oid']})"
            )
            bypassed.append(conn["name"])
            continue

        # If no dependencies, optionally delete the connection (not in dry-run)
        if not dependencies:
            action_msg = "[Dry Run] Would delete" if dry_run else "Deleted"
            log_str = (
                f"{action_msg} unused connection: {conn['name']} (ID: {conn['oid']})"
            )
            print(log_str)
            logging.info(log_str)

            if not dry_run:
                try:
                    response = delete_connection(conn["oid"])
                    # If response is None, treat it as a failed deletion
                    if response is None:
                        logging.error(
                            f"Failed to delete unused connection: {conn['name']} ({conn['oid']})."
                        )
                        print(
                            f"Error deleting unused connection: {conn['name']} ({conn['oid']})"
                        )
                        bypassed.append(conn["name"])
                    else:
                        deleted.append(conn["name"])
                except Exception as e:
                    logging.error(
                        f"Exception while deleting unused connection: {conn['name']} ({conn['oid']}) - {e}"
                    )
                    print(
                        f"Error deleting unused connection: {conn['name']} ({conn['oid']})"
                    )
                    bypassed.append(conn["name"])
        else:
            # If the connection is used, add each dependency row to CSV.
            # Use a fallback value if 'title' or 'oid' is missing.
            for dep in dependencies:
                dep_title = dep.get(
                    "title",
                    "NULL TITLE Share Datasource with associated Bearer Token user to include in CSV",
                )
                dep_oid = dep.get("oid", "NULL OID")
                data.append(
                    {
                        "Connection Name": conn["name"],
                        "Connection ID": conn["oid"],
                        "Elasticube/Data Model Name": dep_title,
                        "Elasticube/Data Model ID": dep_oid,
                    }
                )

    # Create a DataFrame of only the used connections (with datasource dependencies)
    df = pd.DataFrame(data)
    df.to_csv(csv_path, index=False)

    row_count = len(df)

    logging.info(f"Generated CSV at {csv_path}")
    print(
        f"CSV file generated at {csv_path}. "
        f"It contains {row_count} row(s) of used connections.\n"
        "Please review and remove lines for connections you want to keep "
        "before running deletion step by running tool again. Remaining lines will be deleted in deletion mode."
    )

    # Summaries for auto-deleted (unused) connections
    print("\nSummary Report:")
    print(f"Total lines in CSV (active used connections): {row_count}")

    if deleted:
        print("\nDeleted Unused Connections:")
        for d in deleted:
            print(f" - {d}")
    else:
        print("\nNo connections were deleted in this step.")

    if bypassed:
        print("\nBypassed Connections:")
        for b in bypassed:
            print(f" - {b}")
    else:
        print("\nNo connections were bypassed in this step.")


def delete_connections_from_csv():
    """
    Reads the CSV (remaining lines after user review, removing lines for connections to keep),
    then deletes each connection listed. If a delete fails or returns None,
    the connection is logged and added to 'bypassed'.
    """

    if not os.path.exists(csv_path):
        print(
            "CSV file does not exist. Please generate it first or fix the config path."
        )
        return

    df = pd.read_csv(csv_path)

    deleted = []
    bypassed = []

    for _, row in df.iterrows():
        conn_id = row["Connection ID"]
        conn_name = row["Connection Name"]

        action_msg = "[Dry Run] Would delete" if dry_run else "Deleted"
        log_str = f"{action_msg} connection: {conn_name} ({conn_id})"
        print(log_str)
        logging.info(log_str)

        if not dry_run:
            try:
                response = delete_connection(conn_id)
                if response is None:
                    logging.error(
                        f"Failed to delete connection: {conn_name} ({conn_id})"
                    )
                    print(f"Error deleting connection: {conn_name} ({conn_id})")
                    bypassed.append(conn_name)
                else:
                    deleted.append(conn_name)
            except Exception as e:
                logging.error(
                    f"Exception while deleting connection: {conn_name} ({conn_id}) - {e}"
                )
                print(f"Error deleting connection: {conn_name} ({conn_id})")
                bypassed.append(conn_name)

    # Print summary report
    print("\nSummary Report:")
    if deleted:
        print("Deleted Connections:")
        for d in deleted:
            print(f" - {d}")
    else:
        print("No connections were deleted.")

    if bypassed:
        print("\nBypassed:")
        for b in bypassed:
            print(f" - {b}")
    else:
        print("No connections were bypassed.")


if __name__ == "__main__":
    # If there is no CSV file found, select step 1 automatically
    if not os.path.exists(csv_path):
        print(
            "No CSV file found; defaulting to generating connections CSV. "
            "Correct config if CSV file name has changed."
        )
        generate_connections_csv()
    else:
        choice = input(
            "Choose an option:\n"
            "1 - Generate connection CSV\n"
            "2 - Delete connections from CSV list\n"
            "Enter your choice (1 or 2): "
        )
        if choice == "1":
            generate_connections_csv()
        elif choice == "2":
            delete_connections_from_csv()
        else:
            print("Invalid option. Please enter '1' or '2'.")

Conclusion

By automating the detection of inactive connections and simplifying their removal, the Sisense Connection Prune Tool reduces clutter in the Sisense server Connection Manager UI while minimizing the risk of unintentionally impacting active datasources. Whether you opt for a dry-run mode to review potential deletions in a generated CSV file, or to simply list connections, or proceed with the full removal of unused connections, this tool offers a clear, flexible, and reliable approach to keeping your connections organized. A full copy of the tool, is attached below.