Reporting number of status updates?

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.

3 answers

1 accepted

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

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);

}

}

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}

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.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Apr 13, 2018 in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

357 views 20 10
Join discussion

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