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

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 Join to answer
Community showcase
Teodora [Botron]
Published Thursday in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

377 views 1 13
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot