4 Replies Latest reply on Aug 3, 2010 6:52 AM by mark.schwanke

    Monitoring Private Messaging Activity?

      How do I monitor Private Messaging activity via a report?  Is there an SQL Query I can run or an analytics report?  I need to know who's messaging and how many messages they are sending.  Ideally, I'd also like to know who they are sending messages to.

       

      Thanks,

      Heather

        • Re: Monitoring Private Messaging Activity?

          Heather, I would recommend starting off by looking at an existing SQL query for analytics, for example the one posted here to gather activity: Example Analytics Queries

          (where activity type and object type are defined in the following document)

           

          So in your case, you could narrowly define which object type you're looking for (e.g. private messages, direct_object_type = 20)... so for example:

          SELECT jivedw_activity_agg_week.user_id, jivedw_user.username, jivedw_activity_agg_week.activity_type, jivedw_activity_agg_week.direct_object_type, jivedw_activity_agg_week.activity_count, jivedw_container.name, jivedw_week.begin_ts, jivedw_week.end_ts, jivedw_week.week_of_year, jivedw_week.year FROM  jivedw_activity_agg_week,jivedw_container,jivedw_user, jivedw_week WHERE jivedw_activity_agg_week.dw_container_id = jivedw_container.dw_container_id AND jivedw_week.week_of_year = 27 AND jivedw_activity_agg_week.user_id = jivedw_user.user_id AND jivedw_activity_agg_week.week_id = jivedw_week.week_id AND ((jivedw_activity_agg_week.activity_type=10) OR (jivedw_activity_agg_week.activity_type=20)) AND jivedw_activity_agg_week.direct_object_type = 20;
          

           

          For other tables you can query, please see the analytics database schema: http://www.jivesoftware.com/builds/docs/jive_sbs_employee/latest/developer/AnalyticsDatabaseSchema.html

          1 person found this helpful
            • Re: Monitoring Private Messaging Activity?

              Thank you, this was very helpful.

              • Re: Monitoring Private Messaging Activity?

                With the help of curtis, I was able to adjust this to expand the report to show all activity.  My next question is, can I add a column that will show me which document/discussion, etc, people are taking action on?

                 

                I like that I can see that they viewed a document, but is there a way to tell which document they viewed?

                 

                Thanks,

                Heather

                 

                Here's the query I'm using right now:

                 

                SELECT jivedw_activity_agg_week.user_id, jivedw_user.username, jivedw_activity_agg_week.activity_type,  jivedw_activity_agg_week.direct_dw_object_id, jivedw_activity_agg_week.activity_count, jivedw_container.name, jivedw_week.begin_ts, jivedw_week.end_ts, jivedw_week.week_of_year, jivedw_week.year
                FROM jivedw_activity_agg_week,jivedw_container,jivedw_user, jivedw_week
                WHERE jivedw_activity_agg_week.dw_container_id = jivedw_container.dw_container_id AND jivedw_week.week_of_year = '41'
                AND jivedw_activity_agg_week.user_id = jivedw_user.user_id AND jivedw_activity_agg_week.week_id = jivedw_week.week_id AND ((jivedw_activity_agg_week.activity_type='10') OR (jivedw_activity_agg_week.activity_type='20')) order by jivedw_container.name;

                  • Re: Monitoring Private Messaging Activity?
                    mark.schwanke

                    Great! I was just about to post a question about looking at private messages. I want to get a little more specific though. I have partial content of a message and want to look back to see who the exchange was between. So is it possible to see what messages contain the sentence/words I have and match it back to a sender and receiver on date?

                     

                    Mark