Surprisingly helpful to have this, thank you.
I know this is an old thread, but I was wondering if you happened to be able to filter out or exclude groups that have been deleted from this query? We were hoping to use something like this to assist us in a "group clean up". I ran this and it worked great, but a lot of the older ones on the list were groups that we "cleaned up" our last go around, and we were hoping to start from what was exisiting now! Thanks for posting this, it's great!
To get a list of current groups you can run the following from the application database:
select * from jivesgroup;
This would give you a list to cross reference it also provides a grouptype field and the userid of who created the group.
Jive Group Types
1 - Members Only
2 - Private
3 - Secret
This is the query I usually run for our admins when we do cleanup's:
juser.username as "AdminNT",
to_timestamp(jgroup.creationdate/1000) as "creationdate",
to_timestamp(jgroup.modificationdate/1000) as "modificationdate",
WHEN jgroup.grouptype = 0 THEN 'Open'
WHEN jgroup.grouptype = 1 THEN 'Members Only'
WHEN jgroup.grouptype = 2 THEN 'Private'
WHEN jgroup.grouptype = 3 THEN 'Secret'
END as "Group Type"
from jivesgroup jgroup
join jiveuser juser on juser.userid = jgroup.userid
where jgroup.name is not null
order by jgroup.name asc
I hope this helps and let me know if you have any questions.
This works great, thank you! I also learned downloading the System Database instead of the Analytics one gives me better results. This query is exactly what i was looking for!
IDK if I would say "better' the different databases serve different purposes.
- Application is usually the best for snapshot information.
- Analytics keeps all of the activity data and is usually the best for any trending information or to look at what happened historically.
Just a word of caution from someone who has pulled info from the wrong place in the past that caused confusion.
Thanks for that clarification! I make it a point to download all three databases, but really have only used the Analytics one before today.
Here is how I use the different DBs:
- count of correct/helpful answers and who supplied them (there are some issues with the Analytics DB on those)
- to see how anything is currently set/configured (current users, current places, etc)
- for e-mail notifications
- see who interacted with what, how they interacted, and when
This is very helpful Matt!! I'm just learning my way around these databases, so this definitely steers me in the right direction as to when to use which one, thank you very much!