Users who have not created any content

[Thor Henrik Nordvik]

Hi 

Is there a way to find users who have not created any content in a time periode ?

[Nicolai Sibler · Accepted solution]

Hi @Thor Henrik Nordvik 

try this SQL that I found in the Knowledge Base:

SELECT cu.user_name
FROM cwd_user cu
JOIN user_mapping um ON um.username = cu.user_name
WHERE um.user_key NOT IN
( SELECT CREATOR
FROM CONTENT
WHERE CONTENTTYPE IN ('PAGE','BLOGPOST','COMMENT')
AND CREATIONDATE > '2007-01-01' AND CREATOR IS NOT NULL)
AND cu.active = 'T';

Hope that helps!

Kind regards,

Nicolai

[Mykenna Cepek]

I can't think of an easy way to do this in the Confluence GUI (even in the admin area).

The Confluence Cloud REST API can return this information. Use a Search request with the appropriate CQL to locate the data you want.

Likely a little bit of scripting will be needed, if you want a repeatable and clean listing.

If you just need this once (or rarely), I'd figure out a command-line request to get a list of all users, and another one to return a list of who has created content in that timeframe.

Then I'd strip out anything but the users in the second output, sort both user lists (and remove duplicates from the second output), and then diff the result. I'm good at the unix command line, so things like sort -u and awk or sed would be handy here. If that's greek to you, maybe consult a nearby developer who is handy with scripting. Someone good at python or scripting would be a good choice.

Hope that helps!

[Kishan Sharma]

I agree, Confluence REST API is the way to go here!

[Thor Henrik Nordvik]

How do I find the users that have created content, the unix commands I can, its the SQL and how the database is modeles that I am unsure of.