How to query directly to my Jira Database?

I have a rather big database and it's needs cleaning. Therefore I want to filter out which users that haven't been online for x amount of time. I want to query my Jira database directly to retrieve this information.

How do I do this?

1 answer

This widget could not be displayed.

Look in cwd_user for the last login date.

You can use any database management tool you like to read the database, as long as it supports the database you've chosen for JIRA.  I tend to use the command line tools, because I rarely look at the raw database.

Remember to only read your database, never write to it with SQL.

Ok thx byt how do I actually query the Jira database?
I've tried to setup my own mysql server and then tried to import the database with no success.

You need a database management tool (I think you typed your comment while I was adding a missing bit to my answer)

For mysql, there are loads of options.  The basic one is the mysql command line tool of course - it's generally installed by default on the server, but you can load it up on any client and connect to the database over your network.

There are plenty of other tools you could use too.  A search for "MySQL Client" should give you a long list of options.

Maybe I'm totally of track but basically what I'm trying to do is..
I installed postgres and then I tried to load this ~/saab-ldap-sync-service-TRUNK/target/jira/home/database/h2db.mv.db into my newly created postgres database which fails.

I still dont get it how do I query this h2db.mv.db file?

You've now talked about three different databases, and although there are some migration tools around that might be able to convert one to another, we've had problems with them, so we don't recommend that at all.

The correct approach is to run your h2 based JIRA, go to admin -> backup and restore, and run the xml exporter.

Then install a new JIRA backed with postgres or mysql, and import the xml into it.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published yesterday in Marketplace Apps

The 7 hacks of highly successful automation

...there's anything I've learnt from working, it's that people are lazy! No offense to anyone reading this, but it's true and we can all admit it. The easier you make something for someone, the more...

157 views 0 12
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you