Setting up a process to run JIRA user list SQL on a certain frequency

Question

Hello..

I'm trying to figure out if there is a way that one could run a script on a periodic basis that could run a select SQL that would produce a list of JIRA users that haven't logged on in say 60 days. One thing that is a concern about such a script is protecting the JIRA database password so that it isn't clear text within the script. I've got a bit of an idea on the SQL select but am a little unsure on the script workings.

Thanks..

Doug Swartz · Answer

Depending on what DBMS you are using, if the remote database is on the same DBMS as the local Jira database, you could try using what Microsoft calls "Linked Servers".  This allows you to run a query as if the tables are in the local server, but they really access the remote server.

Oracle calls this federation.

Postgres has dblink. It has significantly less functionality.

Equivalent functionality is available in MySQL

All of these require DBA involvement, but they should allow you to use the normal Jira authentication without embedding authentication information into your script.

Jimmy Hurst · Answer

Doug..

I found out after I posted my last reply that the script I was trying to run in Scriptrunner would only work on an internal database (guessing that means the database and application is residing on the same server). The link had another example that would use an external database (which is true for what I'm trying to do - database resides on a different server than the test JIRA application) and that script has clear text database ID/password. I don't want to use that. I'm exploring some other ways to run the script to see if one of those might work. Thanks for responding.

Doug Swartz · Answer

Jimmy,

The Scriptrunner static type checker is a helper. It's telling you that you didn't declare the object the pname property is used on as a type which has a pname property. Based on the java.lang.object type, you likely didn't declare a type at all. Since this is Groovy, types are optional, except that they will cause the static type checker to be confused.

Did you try ignoring the type checker error and running the script? This can often work since type declarations are optional in Groovy.