Reporting number of status updates?

[ITops123]

How can I create a report on the number of status updates created by users since a certain date? We have been pushing this feature a lot and would love to see if our number of status updates has gone up.

[Remo Siegwart · Accepted solution]

If you want to solve this with the SQL plugin, the query would probably look something like this:

SELECT creator AS 'Username', COUNT(*) AS 'Number of status updates'
FROM content
WHERE contenttype = 'USERSTATUS'
AND creationdate >= '2011-11-22' -- insert your date filter here
GROUP BY creator
ORDER BY creator

Find more information about the Confluence Data Model here.

Hope this helps

[Gert-Jan van de Streek]

One option is to write a custom supplier for the reporting plugin. When you have that supplier you can report on all your users or specific groups or whatever selection you require.

A custom supplier is easy to write, it would look somewhat like:

public class UserInfoSupplier implements Supplier {

private UserStatusManager userStatusManager;

public Object getValue(Object context, String key) throws UnsupportedContextException, SupplierException {

User user = (User) context;

return userStatusManager.getUserStatusCount(user);

}

}

[Stefan Kohler]

There is no function available for doing something like that. You could create your own plugin or macro to get some report like this. Plan B could be using the SQL plugin and query the database, but I would not recommend it)

## Macro title: User Status Count
## Macro has a body: N
## Body processing: No macro body
## Output: HTML
##
## Developed by: Stefan Kohler
## Date created: 29/06/2011

## @param Username:title=username|type=string|required=true|desc=Username of the user

#set($containerManagerClass=$content.class.forName('com.atlassian.spring.container.ContainerManager'))
#set($getInstanceMethod=$containerManagerClass.getDeclaredMethod('getInstance',null))
#set($containerManager=$getInstanceMethod.invoke(null,null))
#set($containerContext=$containerManager.containerContext)
#set($userStatusManager=$containerContext.getComponent('userStatusManager'))

$userStatusManager.getUserStatusCount($paramUsername)

This user-macro would do the trick, only downside is that you have to add it for every user in your instance

{userstatuscount:Username=stefan}

[ITops123]

I know you said you don't recommend doing a SQL query, but I'm using the SQL plugin to run other queries. If it is possible, I'd like to use this approach. Also, we have about 400 users, so I can't use a by-user macro.

Can you tell me what tables I need to query to count the status updates?

Additionally, where can I get a list of tables I can query using postgre with Confluence? I can only find examples of SQL queries but this doesn't help me get a comprehensive look at what's possible.