How to list Calendars without Restrictions using a SQL query
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
This document provides information about which tables store the Restrictions values for the Team Calendars in our DB, so you can run an SQL query to filter them.
Solution
The tables that manage the Restrictions of the calendars are:
AO_950DC3_TC_SUBCALS_PRIV_USRwhen it is restricted by users.AO_950DC3_TC_SUBCALS_PRIV_GRPwhen it is restricted by a group.
So if a calendar has an entry in any of those tables, it is restricted and can only be seen/modified by the selected users/groups. Next are the details of the tables, and they relate to each other by the ID column:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
"AO_950DC3_TC_SUBCALS"
"COLOUR" varchar(255) NULL,
"CREATED" int8 NULL DEFAULT 0,
"CREATOR" varchar(255) NULL,
"DESCRIPTION" text NULL,
"ID" varchar(255) NOT NULL,
"LAST_MODIFIED" int8 NULL DEFAULT 0,
"NAME" text NOT NULL,
"PARENT_ID" varchar(255) NULL,
"SPACE_KEY" varchar(255) NULL,
"STORE_KEY" varchar(255) NOT NULL,
"SUBSCRIPTION_ID" varchar(255) NULL,
"TIME_ZONE_ID" varchar(255) NULL,
"USING_CUSTOM_EVENT_TYPE_ID" varchar(255) NULL,
CONSTRAINT "AO_950DC3_TC_SUBCALS_pkey" PRIMARY KEY ("ID"),
CONSTRAINT fk_ao_950dc3_tc_subcals_parent_id FOREIGN KEY ("PARENT_ID") REFERENCES public."AO_950DC3_TC_SUBCALS"("ID"),
CONSTRAINT fk_ao_950dc3_tc_subcals_subscription_id FOREIGN KEY ("SUBSCRIPTION_ID") REFERENCES public."AO_950DC3_TC_SUBCALS"("ID")
"AO_950DC3_TC_SUBCALS_PRIV_GRP"
"GROUP_NAME" varchar(255) NOT NULL,
"ID" serial4 NOT NULL,
"SUB_CALENDAR_ID" varchar(255) NOT NULL,
"TYPE" varchar(255) NOT NULL,
CONSTRAINT "AO_950DC3_TC_SUBCALS_PRIV_GRP_pkey" PRIMARY KEY ("ID")
"AO_950DC3_TC_SUBCALS_PRIV_USR"
"ID" serial4 NOT NULL,
"SUB_CALENDAR_ID" varchar(255) NOT NULL,
"TYPE" varchar(255) NOT NULL,
"USER_KEY" varchar(255) NOT NULL,
CONSTRAINT "AO_950DC3_TC_SUBCALS_PRIV_USR_pkey" PRIMARY KEY ("ID")
SQL Query
The following SQL query retrieves information about Team Calendars that are not private (visible to everyone) in a Confluence instance:
1
2
3
4
5
6
7
SELECT um.lower_username as creator, tc."NAME" as calendar_name, to_timestamp(CAST(tc."CREATED" AS bigint)/1000) as creation_date, s.spacename, s.spacekey
FROM "AO_950DC3_TC_SUBCALS" tc
JOIN user_mapping um ON um.user_key = tc."CREATOR"
JOIN SPACES s ON tc."SPACE_KEY"=s.spacekey
LEFT JOIN "AO_950DC3_TC_SUBCALS_PRIV_USR" pu ON tc."ID" = pu."SUB_CALENDAR_ID"
LEFT JOIN "AO_950DC3_TC_SUBCALS_PRIV_GRP" pg ON tc."ID" = pg."SUB_CALENDAR_ID"
WHERE tc."PARENT_ID" IS NULL AND pu."ID" IS NULL AND pg."ID" IS NULL;
In summary, the query retrieves information about the creator, calendar name, creation date, space name, and space key for all top-level Team Calendars in Confluence that are not marked as private for any individual user or user group.
Was this helpful?