is it possible to find all boards connected to an inactive user with groovy?

[Tomas Gustavsson]

Currently i have a script the gives me all inactive users, 

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.user.util.UserManager

// all inactive jira users
UserManager userManager = ComponentAccessor.getUserManager()
userManager.getUsers().findAll{user -> !user.isActive()}.each { user ->
//...
}

Now i would like to find out if the inactive user also has a board that i can remove, somewhat as below but with groovy. any ideas?

[Andrew · Accepted solution]

Hi @Tomas Gustavsson ,

You can find it direct from DB.

SELECT * FROM ao_60db71_rapidview where owner_user_name='andrewdvizhok';

How request to DB from scriptrunner I already write here https://community.atlassian.com/t5/Jira-questions/get-all-category-created-in-jira/qaq-p/1084797

But unfortunately I don't know how delete it through scritrunner. I prefer use selenium to delete boards/schemes/etc in UI, because delete direct from DB or scriptrunner may break system.

B.R.

[Tomas Gustavsson]

This is great, I will not delete then i just need for find what i can delete and then I will use jira for the deletion.

i did get an issue, maybe it is related to my jira version which is 7.6.4 i do get an error.
can you please help?

startup failed: Script44.groovy: 14: unable to resolve class Sql @ line 14, column 5. Sql sql = new Sql(conn) ^ Script44.groovy: 14: unable to resolve class Sql @ line 14, column 11. Sql sql = new Sql(conn) ^ 2 errors

[Tomas Gustavsson]

solved, my bad.

[Tomas Gustavsson]

Any idea why?

I have got your original Sql statment to work, the one in the link.

when adding the select statement in your answer here, i get the error message
ERROR: relation "ao_60db71_rapidview" does not exist Position: 16

[Andrew]

Yep, in some DB used only upcases. Try 'AO_60DB71_RAPIDVIEW'.

B.R.

[Tomas Gustavsson]

Is there a way to find out which columns a table as ao_60db71_rapidview holds?

[Tomas Gustavsson]

i did and that seams to work, now it complain about column-name

[Andrew]

SELECT ID, NAME FROM AO_60DB71_RAPIDVIEW where OWNER_USER_NAME='andrewdvizhok';

and in code use it.ID and it.NAME

B.R.

[Tomas Gustavsson]

Super now i got it, thanks for a really great help and support

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = """
SELECT * FROM "AO_60DB71_RAPIDVIEW";
"""
def result
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) {
sb << "${it.name} ${it.OWNER_USER_NAME}; \n"
}
result = sb.toString()
log.error sb.toString()
}
finally {
sql.close()
}

return result

[Andrew]

Good! But careful because your code return  list all boards, not only from inactive owners. 
B.R.