How to query Jenkins URLs associated with Bitbucket projects and repositories (Oracle DB)

Problem description

This article provides a solution for retrieving the Jenkins server URLs configured for Bitbucket projects and repositories. The approach is based on querying the relevant tables in an Oracle database, as used by the "Webhook to Jenkins for Bitbucket" app. This is particularly useful for organizations needing to audit or update Jenkins integrations, such as when upgrading the app or updating credentials.

Use Case:
Requested by a large enterprise customer to cross-reference Jenkins servers with Bitbucket projects and repositories that have Jenkins webhooks enabled.

Background

When using the "Webhook to Jenkins for Bitbucket" app, the Jenkins server URL is stored in the jenkinsBase property within the sta_shared_lob table. This table is linked to repositories and projects via the sta_repo_hook table, using the lob_id field.

The goal is to generate a list of all Bitbucket repositories and projects that have Jenkins webhooks enabled, along with their associated Jenkins server URLs.

Solution

SQL query for Oracle DB

Below is an example SQL query that retrieves:

The names of repositories and projects with Jenkins webhooks enabled
The associated Jenkins server URL (jenkinsBase)

-- Query to get repositories using enabled webhooks to Jenkins along with the Jenkins URL
SELECT 
    repo.name AS repository_name,
    hook.repository_id,
    hook.is_enabled,
    hook.hook_key,
    lob.jenkinsBase AS jenkins_url
FROM 
    sta_repo_hook hook
JOIN 
    repository repo ON repo.id = hook.repository_id
JOIN 
    sta_shared_lob lob ON lob.id = hook.lob_id
WHERE 
    hook.hook_key = 'com.nerdwin15.stash-stash-webhook-jenkins:jenkinsPostReceiveHook'
    AND hook.is_enabled = 't'
UNION
-- Query to get projects using enabled webhooks to Jenkins along with the Jenkins URL
SELECT 
    project.name AS project_name,
    hook.project_id,
    hook.is_enabled,
    hook.hook_key,
    lob.jenkinsBase AS jenkins_url
FROM 
    sta_repo_hook hook
JOIN 
    project ON project.id = hook.project_id
JOIN 
    sta_shared_lob lob ON lob.id = hook.lob_id
WHERE 
    hook.hook_key = 'com.nerdwin15.stash-stash-webhook-jenkins:jenkinsPostReceiveHook'
    AND hook.is_enabled = 't';

Note:

The above query assumes Oracle SQL syntax. Adjustments may be needed for other database systems, especially for JSON parsing or data type handling.
The jenkinsBase property may contain multiple URLs if multiple Jenkins servers are configured for a single hook.

Additional notes

If you need to parse JSON properties (e.g., for other plugins like YACC), the JSON parsing syntax will differ between Oracle and other databases (such as MS SQL).

Troubleshooting

If you do not see expected results, verify that the webhooks are enabled (is_enabled = 't') and that the hook_key matches the Jenkins webhook key.
Ensure your database user has sufficient permissions to query the relevant tables.