script or sql query to find 400 users associated date like assigned issue, reported issues
1 answer
1 accepted
Hi @Dayanand
I am reusing the solution that I have provided in the Community Post and adding some additional queries for your requirements, i.e.:-
No of issue assigned for these 400 users?
No of issue reported by these 400 users?
Comments on JIRAs by these 400 users?
All project access these 400 users having in the whole Jira system?
Regarding your 4th requirement i.e.:-
All project access these 400 users having in the whole Jira system?
There is no straightforward approach to getting this. You can use a basic condition to check if or not the user has permission to, for example, Create an Issue in the project or Transition an Issue in the project.
In the sample code below, I have used this condition:-
def allPermission = (Projects.allProjects.collect { project ->
permissionManager.hasPermission(new ProjectPermissionKey('CREATE_ISSUES'), project, it)
} - false).size()
This will give the number of projects the user has permission to Create an Issue. So, if you are doing a count of 400 projects unless the user has permission to do all 400 projects, it will not return a total of 400.
Below is the updated working code for your reference:-
import com.adaptavist.hapi.jira.groups.Groups
import com.adaptavist.hapi.jira.issues.Issues
import com.adaptavist.hapi.jira.mail.Mail
import com.adaptavist.hapi.jira.projects.Projects
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.security.login.LoginManager
import com.atlassian.jira.security.plugin.ProjectPermissionKey
import groovy.xml.MarkupBuilder
import org.jsoup.Jsoup
import java.text.SimpleDateFormat
def loginManager = ComponentAccessor.getComponentOfType(LoginManager)
def userUtil = ComponentAccessor.userUtil
def permissionManager = ComponentAccessor.permissionManager
def issueManager = ComponentAccessor.issueManager
def adminGroup = Groups.getByName('jira-administrators').members
def softwareGroup = Groups.getByName('jira-software-users').members
def serviceDeskGroup = Groups.getByName('jira-servicedesk-users').members
def users = adminGroup + softwareGroup + serviceDeskGroup
users.unique()
final def filePath = '/tmp'
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 'User Name'
th 'Full Name'
th 'Email Address'
th 'Last Login'
th 'Is Active?'
th 'Total Issues Assigned'
th 'Total Issues Reported'
th 'Total Comments Added'
th 'Total Projects with Permission'
}
users.each {
def lastLoginTime = loginManager.getLoginInfo(it.username).lastLoginTime
def username = it.username
def displayName = it.displayName
def emailAddress = it.emailAddress
def active = it.active
def totalReported = Issues.search("assignee = ${username}").collect {it.key }.size()
def totalAssigned = Issues.search("reporter = ${username}").collect {it.key }.size()
def totalComment = Issues.search("issueFunction in commented('by ${username}')").size()
def allPermission = (Projects.allProjects.collect { project ->
permissionManager.hasPermission(new ProjectPermissionKey('CREATE_ISSUES'), project, it)
} - false).size()
if (userUtil.getGroupsForUser(it.name).size() > 0) {
tr {
if (!active) {
td ( username )
td ( displayName )
td ( emailAddress )
td ('Inactive User' )
td ( active )
td ( totalAssigned )
td ( totalReported )
td ( totalComment )
td ( allPermission)
} else if (!lastLoginTime) {
td ( username )
td ( displayName )
td ( emailAddress )
td ('Logon not found' )
td ( active )
td ( totalAssigned )
td ( totalReported )
td ( totalComment )
td ( allPermission )
} else {
def date = new Date(lastLoginTime)
def df2 = new SimpleDateFormat("dd/MM/yy hh:mm")
def dateText = df2.format(date)
td ( username )
td ( displayName )
td ( emailAddress )
td ( dateText )
td ( active )
td ( totalAssigned )
td ( totalReported )
td ( totalComment )
td ( allPermission )
}
}
}
}
}
}
}
}
def dest = new File("${filePath}/output.csv")
if (dest.exists()) {
dest.delete()
}
dest.createNewFile()
def subject = 'User data'
def emailAddr = 'sample@user.com'
def fileWriter = new FileWriter("${filePath}/output.csv")
fileWriter.write(generateCSV(emailBody.toString()))
fileWriter.close()
final static String generateCSV(String tableDetails) {
def stringWriter = new StringWriter()
def doc = Jsoup.parseBodyFragment(tableDetails)
def rows = doc.getElementsByTag('tr')
rows.each {
def header = it.getElementsByTag('th')
def cells = it.getElementsByTag('td')
header.each { headerCell ->
stringWriter.append(headerCell.text().concat(', '))
}
cells.each { cell ->
stringWriter.append(cell.text().concat(', '))
}
stringWriter.append('\n')
}
stringWriter.toString()
}
Mail.send {
setTo(emailAddr)
setHtml()
setSubject(subject)
setBody(emailBody.toString())
addAttachment(dest)
}
emailBody.toString()
Please modify the working code above according to your requirements and not just copy and paste it into your environment. Do not expect it to work as it is not 100% exact to your environment.
Below is a screenshot of the configuration for your reference:-
Since you are doing a check for 400 over users, the results will be truncated if you try to get them via the ScriptRunner console.
The best approach would be to use the email to add the result to the email body and as a CSV attachment.
I tested with 5 projects and 5 users in the test case I used. Of the 5 users, only one has access to all 5 projects, i.e. the Admin user. The others have access to only 4 projects.
Below is a screenshot of the example output that you should receive via email:-
I hope this helps to solve your issue.
Thank you and Kind regards,
Ram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.