Did you ever figure this out? I'm trying to sort out the Download reporting from my .dmp and it makes no sense. Users have Downloads for items they never Viewed and Download seems to fire 20-30 times in a row.
This may not be exactly what you are looking for since it only accounts for views and not downloads, but here is a query that shows who looked at what: content viewed in the last month by logged in users
You can probably massage that to include downloaded items (Activity Type 320) to get the report you need.
Thanks, Matt. I actually have a checkbox for View information in the query I use now. I appreciate your assistance and really like the code. Do you have others? I've been looking for a canned code resource for weeks.
Anyway, my problem is that I don't know why "Download" fires so much and I have an internal person trying to say that my reports are inaccurate because it is so out of whack. I just need to find an explanation for why it does that, so I can calm this person down.
HOPE you have a good SQL engine
I don't see any common keys between the documents table and any other jive table. How can I join the doc table to the activity table?
The following joins will do the job, just not most efficiently
,jdc.name as community_name
( SELECT DISTINCT ON (jd.internaldocid, jdv.versionid) jd.internaldocid, jd.documentid, jd.versionid AS current_version_id, jdv.minorversion, jdv.title AS doc_title, jdv.state AS doc_state, jdv.status AS doc_status, jdv.language AS doc_language, jdv.userid AS modifier_userid, timezone('UCT', to_timestamp(jd.creationdate / 1000)) AS doc_created_ts, timezone('UCT', to_timestamp(jdv.modificationdate / 1000)) AS doc_last_modified_ts, timezone('UCT', to_timestamp(jd.expirationdate / 1000)) AS doc_expires_ts, jd.userid AS doc_create_userid, jd.containertype, jd.containerid, jd.recommended, jd.meanrating, jd.readcount,
WHEN jd.commentstatus = 1 THEN 'disabled'::text
WHEN jd.commentstatus = 2 THEN 'open'::text
WHEN jd.commentstatus = 3 THEN 'closed'::text
END AS comment_status
FROM jivedocversion jdv
LEFT JOIN jivedocument jd ON jd.internaldocid = jdv.internaldocid AND jd.versionid = jdv.versionid) AS jdoclkup
LEFT JOIN jivedw_object jdo ON jdo.object_id = jdoclkup.internaldocid
LEFT JOIN jivedw_activity_fact jaf ON jaf.direct_dw_object_id = jdo.dw_object_id
LEFT JOIN jivedw_containter jdc ON jdc.dw_container_id = jaf.dw_container_id
WHERE jdo.object_type IN (102)
AND jaf.activity_type IN (10,320) --10 = views, 320 = downloads
AND jdc.container_type IN (14)
--AND jaf.user_id NOT IN (-1) --OPTIONAL: THIS WILL REMOVE ANONYMOUS USERS FROM ACTIVITY
In order to be able to see count of document downloads/views 'by community' and/or 'by userid' You would have to create the above as a table, and then create some aggregrate queries to count the instances within the dimensions you're looking for...