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?

[dmitry dementiev]

you can use JIRA PropertySet mechanism. I did the feauture almost like yours via propertysets. http://confluence.atlassian.com/display/JIRA/JIRA+Architectural+Overview#JIRAArchitecturalOverview-UserpropertiesinPropertySets.

Regards, Dmitry.

[Jobin Kuruvilla [Adaptavist]]

Have a look at Active Objects as well! It provides a per plugin storage and will be imported/exported with the backup XML.

[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.

[Jim Birch]

On consideration, I think most tools could handle the Jira user access log.

[Radu Dumitriu]

Independently of the final solution you may choose, it is safe (IMHO) to extend Jira database schema by adding tables on the schema (not altering JIRA tables), but it will have some implications at backup / restore. At restore time, tables should already be created in the database.

[dmitry dementiev]

I myself didn`t extend db scheme, only used propertysets. Why? Have this http://bit.ly/ovKRUm answer from one of Atlassian guys :) Also you can have problems if you`d like to upgrade your JIRA to newer version.

[JamieA]

I don't think there's a problem with restore - just make sure they're defined in entitymodel.xml and then they'll be backed up and restored just fine. Just try to choose table names sufficiently specific such that atlassian won't add them in the future.