How to get a list of all licensed users and split this list into groups (eg. per department)?

[Stefan Salzl]

Dear Atlassian Community,

I would need some help with a requirement. Let me explain the situation/propblem that I am trying to solve:

We have a Jira/Confluence Datacenter instance with about more than 2000 users. The users are all from different business units. The requirement is to split the users into the business units as each of them gets charged for its amount of users.

The user provisioning mainly happens via AD but also some local users.

Scriptrunner is installed, LDAP ressource to AD is connected.

My main idea was to first of all create a group for each business unit (the business unit is filled in the AD attribute 'department' of each user). Is there a possibility to get a distint list of all 'departmernts' from AD via the LDAP ressource in scriptrunner?

Next step I would like to get kind of a report for each group with all users and additional information (like active/inactive, when was the last login,...)

 I really have a hard time how to start this. Any hint/help appreciated. Looking forward to your answers.

Thanks in advance.

Best
Stefan

[Ram Kumar Aravindakshan _Adaptavist_ · Accepted solution]

Hi @Stefan Salzl

You could try to query the Database to get the result for your requirement.

Below is a sample working code for your reference:-

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.xml.MarkupBuilder

def query = """SELECT DISTINCT u.lower_user_name, u.active as "User Status", g.id as "Group Id", g.group_name as "Group Name"
 FROM cwd_user u
 JOIN cwd_membership m
 ON u.id = m.child_id
 AND u.directory_id = m.directory_id
 JOIN licenserolesgroup lrg
 ON Lower(m.parent_name) = Lower(lrg.group_id)
 JOIN cwd_directory d
 ON m.directory_id = d.id
 join cwd_directory ud on ud.id = u.directory_id
 left join cwd_group g on g.id = m.parent_id
 left join cwd_directory gd on gd.id = g.directory_id
 WHERE d.active = '1'
 AND u.active = '1'
 AND license_role_name = 'jira-software';
"""

def result = [[:]]

DatabaseUtil.withSql('local_db'){ sql ->
 sql.eachRow(query.toString()) {
 result.addAll([['Username':"${it['lower_user_name']}", 'User Status':"${it['User Status']}", 'Group Id': "${it['Group Id']}", 'Group Name': "${it['Group Name']}"]])
 }
}

def userUtil = ComponentAccessor.userUtil
def emailBody = new StringWriter()
def html = new MarkupBuilder(emailBody)

html.html {
 head {
    style(type:'text/css', """
      table { border-collapse: collapse; width: 100%; }
      th, td { text-align: left; padding: 8px; }
      tr:nth-child(even){background-color: #f2f2f2}
      th { background-color: #04AA6D; color: white; }""".toString() )
     }
 body {
   table {
    thead {
      tr {
       th 'Username'
       th 'User Status'
       th 'Group Id'
       th 'Group Name'
      }
      result.each {
        if (it['Username']) {
          def userName = it['Username']
          def userStatus = it['User Status']
          def groupId = it['Group Id']
          def groupName = it['Group Name']

          if (userUtil.getGroupsForUser(userName).size() > 0) {
              tr {
                 td(userName)
                 td(userStatus)
                 td(groupId)
                 td(groupName)
              }
          }
       }
     }
   }
  } 
 }
}

emailBody.toString()

Please note that the sample working code above is not 100% exact to your requirement. Hence, you will need to modify it accordingly. 

Below is a screenshot of the code executed on the ScriptRunner console:-

I hope this helps to solve your question. :-)

Thank you and Kind regards,

Ram