Relatório de utilização de licenças do Jira

[Rodrigo]

Oi pessoal!

Aqui na Saraiva já tivemos que regular mais severamente as licenças do Jira (Software de Service Desk) e estamos de olho nelas novamente.

Pra viabilizar esse trabalho, tivemos que consultar o banco de dados diretamente e extrair dados como o último login efetuado, a última visualização de kanbans, dashboards, a última pesquisa de issues...

A query abaixo foi feita pra MS SQL Server, Jira 7 e ainda pode ser bastante otimizada:

select distinct u.directory_id, a.user_key, u.lower_user_name, u.display_name, sd.LICENSE_ROLE_NAME as SD, sw.LICENSE_ROLE_NAME as SW,
 (DATEADD(SECOND,cast (ua.attribute_value as bigint)/1000,'1970-1-1')) as 'Last Login',
 (DATEADD(SECOND,lastdash.lastviewed/1000,'1970-1-1')) as 'Viewed Dasboard',
 (DATEADD(SECOND,lastkanban.lastviewed/1000,'1970-1-1')) as 'Viewed Kanban',
 (DATEADD(SECOND,lastsearch.lastviewed/1000,'1970-1-1')) as 'Viewed Search',
 (DATEADD(SECOND,lastissue.lastviewed/1000,'1970-1-1')) as 'Viewed Issue',
 i.created as 'Created Issue'
from cwd_user (nolock) u
 join cwd_membership m (nolock) on u.lower_user_name = m.lower_child_name
 join cwd_group g (nolock) on g.id = m.parent_id
 join JIRA.jira.licenserolesgroup lic (nolock) on lic.GROUP_ID = g.lower_group_name and lic.LICENSE_ROLE_NAME in ('jira-servicedesk', 'jira-software')
 left join JIRA.jira.licenserolesgroup sd (nolock) on sd.GROUP_ID = g.lower_group_name and sd.LICENSE_ROLE_NAME = 'jira-servicedesk'
 left join JIRA.jira.licenserolesgroup sw (nolock) on sw.GROUP_ID = g.lower_group_name and sw.LICENSE_ROLE_NAME = 'jira-software'
 left join cwd_user_attributes (nolock) ua on u.id = ua.user_id and ua.attribute_name = 'login.lastLoginMillis'
 join jira.app_user (nolock) a on u.lower_user_name = a.lower_user_name
 left join userhistoryitem (nolock) lastdash on lastdash.id = (select top 1 id from userhistoryitem (nolock) uhi where uhi.username = a.user_key and uhi.entitytype = 'Dashboard' order by uhi.lastviewed desc)
 left join userhistoryitem (nolock) lastissue on lastissue.id = (select top 1 id from userhistoryitem (nolock) uhi where uhi.username = a.user_key and uhi.entitytype = 'Issue' order by uhi.lastviewed desc)
 left join userhistoryitem (nolock) lastkanban on lastkanban.id = (select top 1 id from userhistoryitem (nolock) uhi where uhi.username = a.user_key and uhi.entitytype = 'RapidView' order by uhi.lastviewed desc)
 left join userhistoryitem (nolock) lastsearch on lastsearch.id = (select top 1 id from userhistoryitem (nolock) uhi where uhi.username = a.user_key and uhi.entitytype = 'Searcher' order by uhi.lastviewed desc)
 left join jiraissue (nolock) i on i.id = (select top 1 id from jiraissue (nolock) ji where ji.reporter = a.user_key order by created desc)
where u.active = 1
order by 7 desc;

Exportamos esse resultado para uma planilha e lá filtramos e ordenamos conforme necessário.

Com isso conseguimos saber quem está consumindo licenças e nem tem feito login ou quem consome licenças e não precisaria devido ao perfil de utilização.

Antes de remover as licenças ainda faço umas consultas JQL pra ver quando foi a última vez que a pessoa alterou algum status ou se tem alguma issue recente no nome dela...

Compartilhem também como vocês fazem pra facilitar esse trabalho de administrar licenças! Sugestões e melhoria na query também são bem-vindas! :-)