Watcher List for a Blog

[Thomas Renner]

Hey,

is it possible to see the watcher list for a confluence blog? I found the functionality for a a space, but not for a blog. We want to use the blog to avoid email distribution lists, but we have to ensure that everybody is a blog watcher. Or is there a way were you can define the watcherlist for a blog? Or do you have another good idea?

[Thomas Renner · Accepted solution]

Hey,

what is the best way to access the db?

check: https://confluence.atlassian.com/pages/viewpage.action?pageId=179439242

notifications: storage of page- and space-level watches.

               Table "notifications"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 notificationid | bigint                      | not null
 pageid         | bigint                      |
 spaceid        | bigint                      |
 username       | character varying(255)      | not null
 creator        | character varying(255)      |
 creationdate   | timestamp without time zone |
 lastmodifier   | character varying(255)      |
 lastmoddate    | timestamp without time zone |
Indexes:
    "notifications_pkey" PRIMARY KEY, btree (notificationid)
    "n_pageid_idx" btree (pageid)
    "n_spaceid_idx" btree (spaceid)
Foreign-key constraints:
    "fk594acc88c38fbea" FOREIGN KEY (pageid) REFERENCES content(contentid)
    "fk594acc8b2dc6081" FOREIGN KEY (spaceid) REFERENCES spaces(spaceid)

Maybe droping all notification table entries with your specific spaceId will help you. But you should not drop your mail-user :-)

[Radek Janata]

You can get list of blog watchers using this db query:

SELECT
  s.SPACEKEY AS 'Space',
  COUNT(u.username) AS 'Count',
  GROUP_CONCAT(u.username ORDER BY u.username) AS 'Watchers'
FROM NOTIFICATIONS AS n
LEFT JOIN SPACES AS s ON n.SPACEID = s.SPACEID
LEFT JOIN user_mapping AS u ON n.USERNAME = u.user_key
WHERE n.CONTENTTYPE = 'blogpost'
GROUP BY s.SPACEKEY;

[Nick Muldoon]

G'day @Thomas Renner, @Sorin Sbarnea (Citrix),

Better Blogs for Confluence is a paid add-on that allows Space Administrators to subscribe users and groups to email notifications of new blogs in a space.

At Twitter we needed this feature to ensure every member of our product, design and engineering organisation (>1,500 people) were notified of new blog posts in the Engineering space. The Engineering Blog enabled VP's to communicate one-to-many and teams to share details of the technology and features they were working on. It didn't make sense to ask every new employee to subscribe to the blog in that space, hence Better Blogs was born.

With Better Blogs you can keep everyone informed, and do so with ease. For those wishing to take advantage of early bird pricing use the promotion code HHYUWD before the end of October 2015.

If you have feedback on the Better Blogs for Confluence add-on contact me via nick@arijea.com or +1 415 568 7064.

Regards,
Nicholas Muldoon
Arijea

[Sorin Sbarnea (Citrix)]

Nice to find out this. Here is some open-sourced feedback: pricing, even after promotion seems to be way off for the added-value and the maturity of the product. Priced at $5000 for 10k users, is half the price of Atlassian Questions, which is clearly a plugin with a lot of added value. It would be helpful if the plugin would inject a manage watcher menu in the blog homepage, as now is quite hard to find the location of the addon. Also it seems that at least on Mac OS Safari 9 clicking on an auto-completed group name does nothing, only pressing enter worked. Just to give a an idea of added value: the Announcer for JIRA/Confluence costed us $400, and I would say that probably the decision makers would find it of more value than the manage watchers one. Obviously opinions could be different from company to company :)

[Nick Muldoon]

Hi Bill, Thanks for the feedback - as Better Blogs is a brand new add-on this is exactly what I need to hear! The two items you call out - "inject a manage watcher menu in the blog homepage" and "Mac OS Safari 9 clicking on an auto-completed group name does nothing" - have been added to the backlog. Thank you. In regards to pricing I understand the concern, and thank you for bringing my attention to Announcer. Future plans for Better Blogs take it beyond the current functionality and do, I believe, justify the pricing (although I understand people are buying today, not the future). As it is a brand new add-on I'll keep a close eye on feedback around this and adjust as necessary. Thanks Bill, have a great week, Nicholas

[Tim Mohrbach]

Same here, great addon, exactly what I have been looking for... But unfortunately way out of our budget zone... price/value compared to the price of a confluence license is quite unreasonable. it's currently 1/8 of the confluence price with 250 users. We would start considering if the addon was 1/8 of it's current price. But obviously companies have different budgets for different things and some might not mind spending the money already.

[Nick Muldoon]

Thanks for the feedback @Tim Mohrbach.

[Sorin Sbarnea (Citrix)]

Shorly this is not possible but you should vote / comment on this https://jira.atlassian.com/browse/CONF-30644

[AlysonA]

Hi Thomas,

Assuming that I understood you correctly, you can see who's watching a blog post by navigating to Tools > Manage Watchers. Does that suits your needs?

Cheers

[Thomas Renner]

Thanks for you replay, but i don't need the watcher list for just one specific blog post.

You can just follow a blog (or all blog posts), if you go to the corresponding space, Tools -> View in Hierarchie, Advanced and click on "only watch space blogs. It would be nice to the watcher list for this point.

[Jean-François FORGET]

Janata query don't works for me.

I've update it a little bit like this:

SELECT
s.SPACEKEY AS 'Space',
CONTENT.TITLE,
COUNT(u.username) AS 'Count',
GROUP_CONCAT(u.username ORDER BY u.username) AS 'Watchers',
creator.username
FROM NOTIFICATIONS AS n
JOIN CONTENT ON CONTENT.CONTENTID = n.CONTENTID
LEFT JOIN SPACES AS s ON CONTENT.SPACEID = s.SPACEID
LEFT JOIN user_mapping AS u ON n.USERNAME = u.user_key
LEFT JOIN user_mapping AS creator ON CONTENT.CREATOR = creator.user_key
WHERE CONTENT.CONTENTTYPE = 'BLOGPOST'
AND CONTENT.CONTENT_STATUS = 'current'
GROUP BY s.SPACEKEY;

[Vera Henrichs]

How about this feature? https://jira.atlassian.com/browse/CONF-40582 Please vote!

[Sorin Sbarnea (Citrix)]

https://jira.atlassian.com/browse/CONF-30644

[sandro gehrig]

Thanks Thomas

Doing some tests (delete and recreate my watch) I now know the spaceID where our blog is in. With this info I can get a list of space watchers.

Since there aren't that many users watching, I'll just go straight forward and configure our "notification user" to use the mail distribution group. As soon as one of the watchers (mostly techs) gets angry because of doubled notifications he'll have to solve it himself by unwatching the space.

It was and is never a really good idea to directly change entrys inside the database, and since I am able to avoid it, I'll do so ;)

again thanks for the helpful link and help

Regards

[Thomas Renner]

You think about a sql query with the sql plugin for confluence? Or how do you want to access you internal jira db?

[sandro gehrig]

This is confluence related. I can access the database directly, basically no need for plugins doing this.

The question is how can I determine (once) who is space blog watcher of one specifig space/blog.
And, if it's just a boolean value per user, I would be able to remove them from watch list because I plan to create one local user which is linked to a mail group. Setting him as watcher then would result in the desired infomail to all dist-group members. In order to guard against dublicate mails (because one is inside the distribution group, but itself also a blog watcher) I have to remove existing watchers.

And all this to workaround the missing "manage space watchers" feature.

So, anyone knows those details about the way the "watch" feature get's saved into the db?

Thanks for your help

sandro

[sandro gehrig]

Isn't there any way to get a list of "only space blogs" watchers?

A mysql query would also be enough!