Hello,
I am trying to find all my projects that do not have a Project Lead specified. I am using the script in the following article - https://confluence.atlassian.com/jirakb/retrieve-project-information-from-jira-s-database-1044111930.html - however this does not return any of the projects without a project lead.
<span>with</span> rank_created <span>as</span> <span>(</span>
<span>select</span> project<span>,</span>created<span>,</span>rank<span>(</span><span>)</span> <span>over</span><span>(</span><span>partition</span> <span>by</span> I<span>.</span>project <span>order</span> <span>by</span> I<span>.</span>created <span>desc</span><span>)</span> <span>as</span> ranking
<span>from</span> jiraissue I
<span>)</span><span>,</span>
rank_updated <span>as</span> <span>(</span>
<span>select</span> project<span>,</span>updated<span>,</span>rank<span>(</span><span>)</span> <span>over</span><span>(</span><span>partition</span> <span>by</span> I<span>.</span>project <span>order</span> <span>by</span> I<span>.</span>updated <span>desc</span><span>)</span> <span>as</span> ranking
<span>from</span> jiraissue I
<span>)</span><span>,</span>
count_issues <span>as</span> <span>(</span>
<span>select</span> project<span>,</span> <span>count</span><span>(</span><span>*</span><span>)</span> <span>as</span> total
<span>from</span> jiraissue j <span>group</span> <span>by</span> project
<span>)</span><span>,</span>
users <span>as</span> <span>(</span>
<span>select</span> user_name<span>,</span> lower_user_name<span>,</span> email_address<span>,</span> U<span>.</span>active <span>,</span>rank<span>(</span><span>)</span> <span>over</span><span>(</span><span>partition</span> <span>by</span> U<span>.</span>user_name <span>order</span> <span>by</span> D<span>.</span>directory_position<span>)</span> <span>as</span> ranking
<span>from</span> cwd_user U
<span>join</span> cwd_directory D <span>on</span> U<span>.</span>directory_id <span>=</span> D<span>.</span>id <span>and</span> D<span>.</span>active <span>=</span> <span>1</span>
<span>)</span>
<span>select</span> P<span>.</span>pname<span>,</span> P<span>.</span>pkey<span>,</span> U<span>.</span>user_name<span>,</span> U<span>.</span>email_address<span>,</span> U<span>.</span>active<span>,</span> RC<span>.</span>created<span>,</span> RU<span>.</span>updated<span>,</span> CI<span>.</span>total<span>,</span> to_timestamp<span>(</span>cast<span>(</span>A<span>.</span><span>"ENTITY_TIMESTAMP"</span> <span>as</span> <span>bigint</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span>as</span> <span>"Project created"</span>
<span>from</span> project P
<span>join</span> <span>"AO_C77861_AUDIT_ENTITY"</span> A <span>on</span> <span>"ACTION"</span> <span>=</span> <span>'Project created'</span> <span>and</span> A<span>.</span><span>"PRIMARY_RESOURCE_ID"</span> <span>=</span> cast<span>(</span>P<span>.</span>id <span>as</span> <span>varchar</span><span>)</span>
<span>join</span> app_user AU <span>on</span> AU<span>.</span>user_key <span>=</span> P<span>.</span><span>"lead"</span>
<span>join</span> users U <span>on</span> U<span>.</span>lower_user_name <span>=</span> AU<span>.</span>lower_user_name <span>and</span> U<span>.</span>ranking <span>=</span> <span>1</span>
<span>left</span> <span>join</span> rank_created RC <span>on</span> RC<span>.</span>project <span>=</span> P<span>.</span>id <span>and</span> RC<span>.</span>ranking <span>=</span> <span>1</span>
<span>left</span> <span>join</span> rank_updated RU <span>on</span> RU<span>.</span>project <span>=</span> P<span>.</span>id <span>and</span> RU<span>.</span>ranking <span>=</span> <span>1</span>
<span>left</span> <span>join</span> count_issues CI <span>on</span> CI<span>.</span>project <span>=</span> P<span>.</span>id<span>;</span>
Can someone please advise what I need to change to pull down these projects?
Thanks.
