Querying your Dropbox Business Activity

Software Engineering 2268 views

Today I needed to find out which team members were utilizing our Dropbox Business account the most. It turns out this is easy. The Dropbox Help Center has an article, Monitor team sharing activity, which describes how to generate your activity report from the Admin Console. You can export that report as CSV, which you can then load into SQLite to query.

sqlite> .mode csv
sqlite> .import ~/Downloads/2018-06-01-full-log.csv dropbox_activity
sqlite> select email, count(*) from dropbox_activity group by email order by 2 desc;
user1@mail.com,9709
user2@mail.com,3940
user3@mail.com,11
user4@mail.com,9
user5@mail.com,6
user6@mail.com,5
user7@mail.com,4

After I thought about it, I realized I was only interested in certain event types. If a file was added, edited, deleted, or downloaded:

select
    email,
    count(*)
from
    dropbox_activity
where
    "Event type" in ("Added files", "Edited files", "Deleted files", "Downloaded files")
group by
    email
order by
    2 desc;