はじめに
本記事の内容は #p_UG 東京：新コネクタLT大会で、デモ形式で発表した内容です。
スライドは公開していますが、スライドでは伝わらない（何ならデモでもすっ飛ばした可能性がある）内容をこの記事で解説します。
今回のテーマ
(1) 棚卸
クラウドサービスの棚卸、してますか？してますよね？？（念押し）
誰かがクラウドサービスを使っているならば、それは誰が管理者で、誰が使っているのか、正確に把握しておく必要があるでしょう。把握できていない状況はセキュリティリスクにつながります。
棚卸は必要。でも時間はかけたくない。そんなせめぎあいを誰しも体験しているかと思うところです。
(2) TROCCO Backlog コネクタ
primeNumber 社のETL製品 TROCCO で立ち上がったコネクタ大幅拡充プロジェクト「CONNECT 100+」。
この中で、4月に追加されていたのが転送元 Backlog でした。
プロジェクト管理ツールとして利用している方は多数おられるのではないでしょうか。もちろん私もユーザーの一人です。
プロジェクト管理ツールという性質上、Backlog のユーザーはかなり大規模になり、参加するプロジェクトの制御のためのチームも増えていくと思います。
ユーザーが多いとつらくなるのが、ユーザーの棚卸です。
今回の記事では、できるだけサッサとユーザー棚卸を終わらせるために、正統派な方法を使ったり、TROCCO を使ったりしてみます。
前提
フォーマット
こんな感じのフォーマットでユーザー管理していると想定します。
| ユーザーID | 氏名 | メールアドレス | 所属チーム | ロール | 最終ログイン | アクティブ | 備考 |
|---|---|---|---|---|---|---|---|
| user001 | 山田 太郎 | yamada@example.com | 営業チーム | ADMIN | 2025-06-20 | 〇 | |
| user002 | 佐藤 花子 | sato@example.com | 開発チーム | MEMBER | 2025-06-21 | 〇 | 退職予定 |
| user003 | 鈴木 次郎 | suzuki@example.com | 管理チーム | GUEST | 2025-06-22 | ✕ | アカウント停止中 |
というわけで、何らかの手段で、できるだけ速く、できるだけ楽をしてこのフォーマットでユーザー一覧を作成することを考えます。
環境
棚卸の方法を試す Backlog 環境を用意します。
CSV で一括作成しましょう。
ユーザー追加csvのテンプレ
"Email","Name","Role","Teams"
"user1@apps.nulab.com","User 1","Admin",""
"user2@apps.nulab.com","User 2","Member",""
"user3@apps.nulab.com","User 3","Guest","Cacoo Team;Typetalk Team"
一気に100人まで追加できるみたいなので、お言葉に甘えて100人追加しようとしています。が、フリープランのユーザー数上限は10ユーザーです。。。
ヌーラボさん、変な動きをするアカウントですみません。。。。
招待し、追加してからユーザー数が圧倒的に超過していることに気づいたので、30ほどユーザーを削除。
こうして、10人のユーザー、2つのチームを用意しました。この環境で棚卸を行っていきます。
方法(1) Backlog 自身のユーザー一覧 CSV を利用する
ユーザー一覧csv
当然ながら、Backlog 自身にもユーザー一覧を出力する機能は存在します。
フォーマットはこんな感じです。
<!--
"Email","Name","Unique ID","Role","Inviting","Managed Account","Teams","Teams where you're admin","External Organizations","Active"
"■■■■■■+user10@gmail.com","User 10","■■■■■■user10","GUEST","false","false","""TEST""","","","true"
"■■■■■■+user13@gmail.com","","","MEMBER","true","false","""TEST""","","","true"
"■■■■■■+user14@gmail.com","","","GUEST","true","false","""TEST""","","","true"
"■■■■■■+user16@gmail.com","User 16","■■■■■■user16","MEMBER","false","false","""TEST""","","","true"
"■■■■■■+user18@gmail.com","","","GUEST","true","false","""TEST""","","","true"
"■■■■■■+user1@gmail.com","User 1","■■■■■■user1","MEMBER","false","false","""TEST""","","","true"
"■■■■■■+user20@gmail.com","","","MEMBER","true","false","""TEST""","","","true"
"■■■■■■+user23@gmail.com","","","MEMBER","true","false","""TEST""","","","true"
：中略
"■■■■■■@gmail.com","梶谷美帆","■■■■■■","ADMIN (Plan admin)","false","false","""MIHOKAJIYA全体"";""TEST"";""TEST2""","""MIHOKAJIYA全体"";""TEST"";""TEST2""","","true"
-->
メールアドレス、ユーザー名、招待中か、参加チーム（; 区切り）、ロール、Adminかどうか、...おおむね必要な情報はありそうですね。 [1]
というわけで、これを使ってユーザー棚卸しする方法を考えます。
csv 加工
構造化データなので SQL で処理するのが手っ取り早いでしょう。ではここに DuckDB を召喚します。
Docker コンテナのソースコード
Dockerfile
FROM datacatering/duckdb:v1.3.1
WORKDIR /app
COPY init.sql /app/
EXPOSE 3000
CMD ["duckdb", "-c", ".read init.sql"]
docker-compose.yml
version: '3.8'
services:
duckdb:
build: .
container_name: duckdb-container
ports:
- "3000:3000"
volumes:
- ./data:/app/data
stdin_open: true
tty: true
init.sql
-- Initialize DuckDB and load CSV file
CREATE TEMPORARY TABLE members AS SELECT * FROM read_csv_auto('data/input.csv');
COPY (
SELECT
"Unique ID" AS "ユーザーID",
"Name" AS "氏名",
"Email" AS "メールアドレス",
REPLACE("Teams", ';', ' | ') AS "所属チーム",
"Role" AS "ロール",
'' AS "最終ログイン",
CASE
WHEN "Active" = true THEN '〇'
ELSE '✕'
END AS "アクティブ",
'' AS "備考"
FROM
members
) TO 'data/output.csv' (
HEADER,
DELIMITER ','
)
;
結果
最終ログインは、このCSVだけでは取れなさそうなので一旦空欄。
アクティブか否かは、招待中かどうか（Inviting）からとったほうが良いかも？
方法(2) TROCCO Backlog コネクタを使う
準備編
Backlog API キー作成
Backlog 側でAPIキーを作成しておきます。作成の際は管理者ユーザーを使います。
TROCCO
Backlog 接続情報作成
先ほど取得した API キーを指定して、Backlog 接続情報を作成します。ホスト名は対象の Backlog の URL から取得。
転送設定作成
ここまで来たら、転送設定は選ぶだけです。
「ユーザー一覧」「チーム一覧」それぞれの転送設定を作成。
転送します。継続的に取得するならワークフロー化するのがいいですね。
Snowflake
ビューを作ります。
クエリ
create or replace view backlog_user_list as
with teams as (
SELECT
t."name" as "所属チーム",
-- tmp_f.*,
f.value:lastLoginTime as "最終ログイン",
f.value:mailAddress as "メールアドレス",
f.value:name as "氏名",
f.value:userId as "ユーザーID",
FROM
BACKLOG_TEAMS AS t
, LATERAL FLATTEN(input => parse_json(t."members"), outer => true) AS f
),
users as (
SELECT
t."userId" as "ユーザーID", -- ほんとは uniqueIｄ のほうが
t."name" as "氏名",
case
when t."roleType" = 1 then 'Admin'
when t."roleType" = 2 then 'Member, Guest'
when t."roleType" = 3 then 'Member, Guest (Add Issues Only)'
when t."roleType" = 4 then 'Member, Guest (View Issues Only)'
else ''
end as "ロール",
t."mailAddress" as "メールアドレス",
t."lastLoginTime" as "最終ログイン",
-- tmp_f.*,
FROM
BACKLOG_USERS AS t
),
final as (
select
bu."ユーザーID",
bu."氏名",
bu."メールアドレス",
listagg(bt."所属チーム", ' | ') as "所属チーム",
bu."ロール",
max(bu."最終ログイン") as "最終ログイン",
iff(max(bu."最終ログイン") is not null, '〇', '✕') as "アクティブ",
'' as "備考"
from
users bu
inner join
teams bt
on
bu."ユーザーID" = bt."ユーザーID"
group by
bu."ユーザーID"
, bu."氏名"
, bu."メールアドレス"
, bu."ロール"
)
select * from final;
結果
だいたいの情報をとれているので、これでよさそうです！
おわりに（小ネタ）
ユーザー作成時のcsvの内容を都度管理していくのも最適解な気がしますが、、、
クラウドサービス管理にはクラウドサービス管理用の製品を使うのが一番ですが、TROCCO があるならこんな方法もあるよという話でした。
-
欲深い人間なので、最終ログイン日や MFA 有効化済みか否かも欲しい気がしています ↩︎
Discussion