Can I extend the JIRA database schema?

Greg Warner

For my company reporting I need to generate reports on JIRA usage. Information such as the number of unique users who used the system in the past 24 hours.

The User Browser indicates this information is stored in the JIRA database so I'm guessing I can create a SQL query from the CWD_USER_ATTRIBUTES and CWD_USER table, Then store these values in tables I've created in the JIRA database for this purpose. Alternatively, is historical information maintained in the JIRA database already?

Is there any problem extending the existing JIRA database schema by adding additional tables to store this login history?

Jim Birch

I would be careful about updating the schema - it will complicate upgrades and could result in a serious performance clanger.

You should be able to get any Jira updaters from Jira's activity log information which is obviously in the database.

If you want lurkers, you need to do something different.

The Jira lastlogin field is problematic because users won't be logged in unless their cookie has expired (though the cookie timeout can be configured.)

You can turn on Tomcat access logging and then use standard access log tools to get standard stats like unique users and so on with nice graphics. You could also use the logged page actions to get an idea of what people were up to. However, the tomcat level logging only has IP addresses, not usernames.

There is alternate access loggin from the Jira level that includes usernames, see

http://confluence.atlassian.com/display/JIRA/User+access+logging

I think you'd need to write your own stuff to digest this log but it will tell you who did what.