How to find plans and environments that send notifications to webhooks
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
Get a list of build plans and deployment environments that are configured to send notifications to webhooks.
Environment
All supported versions of Bamboo connected to a supported external database.
Solution
This solution will require you to have the appropriate access to run the below SQL queries on your Bamboo database
General query
SELECT full_key,
de.environment_id AS environment_id,
recipient_type,
recipient,
condition_key,
condition_data
FROM notifications
JOIN notification_sets ns
ON notifications.notification_set = ns.notification_set_id
LEFT OUTER JOIN build b
ON ns.notification_set_id = b.notification_set
LEFT OUTER JOIN deployment_environment de
ON ns.notification_set_id = de.notification_set
WHERE recipient_type LIKE '%webhook%' Queries for filtered data
The queries below will retrieve filtered data in regards to webhook URL, webhook recipient type and conditions, as well as output URLs to access the retrieved plans/environments in the UI.
select full_key, '<BAMBOO-URL>/browse/'||full_key as BUILD_URL,
de.environment_id as environemnt_Id, '<BAMBOO-URL>/deploy/config/configureEnvironmentNotifications.action?environmentId='||environment_id as DEPLOYMENT_ENVIRONMENT_URL,
reverse(split_part(reverse(recipient_type),'.',1)) as recipient_type,
reverse(split_part(reverse(recipient), '|',1)) AS WEEBHOOK_URL,
reverse(split_part(reverse(condition_key),':',1)) AS condition_key,
condition_data
from notifications
join notification_sets ns on notifications.notification_set = ns.notification_set_id
left outer join build b on ns.notification_set_id = b.notification_set
left outer join deployment_environment de on ns.notification_set_id = de.notification_set
where recipient_type like '%webhook%'select full_key,
CONCAT('<BAMBOO-URL>/browse/',full_key) as BUILD_URL,
de.environment_id as environemnt_Id,
CONCAT('<BAMBOO-URL>/deploy/config/configureEnvironmentNotifications.action?environmentId=',environment_id) as DEPLOYMENT_ENVIRONMENT_URL,
substring_index(RECIPIENT_TYPE, '.',-1) as recipient_type,
substring_index(RECIPIENT, '|',-1) as weebhook_URL,
substring_index(CONDITION_KEY, ':',-1) as condition_key,
condition_data
from NOTIFICATIONS
join NOTIFICATION_SETS ns on NOTIFICATIONS.notification_set = ns.notification_set_id
left outer join BUILD b on ns.notification_set_id = b.notification_set
left outer join DEPLOYMENT_ENVIRONMENT de on ns.notification_set_id = de.notification_set
where recipient_type like '%webhook%'select FULL_KEY,
('<BAMBOO-URL>/browse/'+ b.FULL_KEY) as BUILD_URL,
de.ENVIRONMENT_ID as environemnt_Id,
('<BAMBOO-URL>/deploy/config/configureEnvironmentNotifications.action?environmentId='+ (CAST(ENVIRONMENT_ID AS varchar))) AS DEPLOYMENT_ENVIRONMENT_URL,
right(RECIPIENT_TYPE, charindex('.', reverse(RECIPIENT_TYPE)) -1) as recipient_type,
right(RECIPIENT, charindex('|',reverse(RECIPIENT)) -1) AS webhook_URL,
right(CONDITION_KEY, charindex(':', reverse(CONDITION_KEY)) -1) as condition_key,
CONDITION_DATA
from NOTIFICATIONS
join NOTIFICATION_SETS ns on NOTIFICATIONS.NOTIFICATION_SET = ns.NOTIFICATION_SET_ID
left outer join BUILD b on ns.NOTIFICATION_SET_ID = b.NOTIFICATION_SET
left outer join DEPLOYMENT_ENVIRONMENT de on ns.NOTIFICATION_SET_ID = de.NOTIFICATION_SET
where NOTIFICATIONS.RECIPIENT_TYPE like '%webhook%'You can also filter for specific webhooks. E.g., if you're looking for builds and environments that send notifications to Slack webhooks, you can add the following filter to the query above:
AND recipient like '%slack%'