It should be in the analytics db. I can try to look at what the SQL would be tomorrow
Looks like group permission type is not in analytics so this query from the analytics tab pulls all the read activity (activity_type=10) for a member (see below where by username is mitchelj). The fields it is returning in order are member's numerical userid, username, the timestamp of the read, the day id, what was read, the id of what was read, what kind of object is it, the container id, the container type, and the container name.
jivedw_object.name as direct_object_name,
jivedw_object.object_id as direct_object_id,
jivedw_container.name as container_name,
jivedw_activity_fact.activity_type = 10 and
You should be able to marry that up with data on the private groups with this query from production:
select * from sgroup;
Group types are as follows:
0 = Public
1 = Members Only
2 = Private
3 = Secret
Then match your container_ids from the first query to the group_id in the second query. It's not perfect. This doesn't figure out for you if any of the projects or blogs are in secret groups as they have a separate container id, but I don't have the time to figure that out at the moment. It should give you some idea if it's going on & if you want to figure it the rest out in more detail.
We have an add-on framework that we built that provides this (and much more). In the simplest of terms, if Jive tracks the information in the database, we can report on it. The framework is built using QlikView's BI tool and it sits on top of Jive's Analytics database. (v4.5, 5, or 6). As QlikView runs everything in memory, it is incredibly fast....you can spin through millions of rows of activity data in seconds.
I am happy to have a chat and demo with you if you have any interest. (firstname.lastname@example.org)
I would be grateful for a chat and demo sometime in the near future - the possibilities of QlikView seem highly useful!
Would first like to look into the system a little and consolidate what information/analytics I would like to track.
If OK with you will send you a message to figure out a convenient time.