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

[Jimmy Hurst]

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]

Jimmy, Have you seen this page?:  https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-active-users-counting-towards-the-jira-application-license-278695452.html It doesn't include the #days inactive, but could move you toward your goal.

You don't say what sort of script you are talking about, and protecting the database password depends on what you have available. Are you running the script from within Jira in a tool like Scriptrunner? Running a script at the Operating System Command line, or another approach? 

[Jimmy Hurst]

Doug...

I've seen that page and other similar queries and have tested a query that shows me a list of inactive users in a period like 60 days. I was unaware of the JIRA tool Scriptrunner and might have to look at that tool. I was leaning toward running a shell script from the Linux server but am concerned about how to protect the database password in that script.

Thanks for the feedback.

[Jimmy Hurst]

We've been testing a trial of ScriptRunner and I recently ran across this link - 

https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html

I took the script mentioned in the above link that connects to the JIRA database and added some of my own SQL to it.. When I try to run it from the ScriptRunner Script Console I get an error like the following - 

[Static type checking] - No such property : pname for class: java.lang.Object  @ line 25, column 17 

Anyone have any ideas on how to correct that error? Or am I trying to do something with the JIRA DB that I shouldn't be doing?

Thanks..

[Doug Swartz]

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.

[Jimmy Hurst]

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]

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.