Sql query for creator of bamboo plans

[Tim Finch]

Hi,

I have a sql query that details for me all new Bamboo plans created in the last 30 days.

select p.TITLE as 'Project',
b.TITLE as 'Plan',
b.FULL_KEY as 'Plan Key',
b.CREATED_DATE as 'Created Date'
from dbo.PROJECT p, dbo.BUILD b
where b.PROJECT_ID = p.PROJECT_ID
and BUILD_TYPE= 'CHAIN'
and b.CREATED_DATE >= CURRENT_TIMESTAMP -30

Is there any way this can be modified to include who the creator of the plan is?

I can;t see anything obvious linking the plans to a user in the database?

Thanks

[Tim Finch · Accepted solution]

I think I have manged to figure it out myself....

select p.TITLE as 'Project',
b.TITLE as 'Plan',
b.FULL_KEY as 'Plan Key',
b.CREATED_DATE as 'Created Date',
al.USER_NAME as 'Created By'
from dbo.PROJECT p, dbo.BUILD b, dbo.AUDIT_LOG al
where b.PROJECT_ID = p.PROJECT_ID
and BUILD_TYPE= 'CHAIN'
and b.CREATED_DATE >= CURRENT_TIMESTAMP -30
and b.FULL_KEY = al.ENTITY_ID
and al.MSG = 'Plan has been created.'