Exporting data from Countly through DB Viewer

DB Viewer is a plugin that provides a UI to browse databases. But it is also a great option to access Database data through REST API, for example, to export data. In this article, we will explain how to navigate the data scheme and find all the needed information to export events and their granular data from Countly.

Let's say you have some other database, and you want to populate it with data from Countly. Or you just want to prepare some kind of report through a third-party application. Or write some scripts for getting data out and processing it. These examples should help you in all those cases.

1. Getting information about all apps

This is a standard API call available in the core and will provide you with info about apps that exist and are accessible for users with provided api_key.

It is useful for getting list of app_ids if you are writing a script to iterate through all apps, for example.

Example call (replacing domain.com and {api_key} for user)

https://domain.com/o/apps/all?api_key={api_key}

‍

More info about the API call: https://api.count.ly/reference#oappsall

2. Getting user information about a specific app

This API call involves DB Viewer API and will list all documents of all users and their properties that are available for this specific app.

Example (replacing domain.com and {app_id} for the app you want to get info and {api_key} for user):

http://domain.com/o/db?dbs=countly&collection=app_users{app_id}&limit=1000000000000&api_key={api_key}

In the response object, the collections array should have all the user objects with their properties.

If there are too many users to read in a single request, you can paginate results by using limit and skip parameters. As example:

http://domain.com/o/db?dbs=countly&collection=app_users{app_id}&limit=100&skip=200&api_key={api_key}

‍

More information on pagination and filtering: https://api.count.ly/reference#odbdbsdbcollectioncollection

More information on possible user properties that you might get: https://support.count.ly/hc/en-us/articles/360037681532-countly-app-users-appID-

‍

If you want to monitor only new users, then you can adjust the filter part like this.

http://domain.com/o/db?dbs=countly&collection=app_users{app_id}&limit=1000000000000&query={"fs":{"$gt":1470009600}}&api_key={api_key}

Replacing 1470009600 with a timestamp of the last data sync. Of course you can do other filtering options or perform any kind of MongoDB query using the filter clause.

3. Getting all event data

Getting all events data would consist of 2 steps. Basically, what you need to do, is to get all event keys for a specific app, iterate through those event keys and create sha1 hashes, to be able to request data for each event specifically through DB Viewer API.

First step:

The first step would be getting the list of all event keys that are available for provided app.

Example call (replacing domain.com and {app_id} for the app you want to get info and {api_key} for user)

http://domain.com/o?api_key={api_key}&app_id={app_id}&method=get_events

‍

More information on this API call: https://api.count.ly/reference#omethodget_events

‍

There is a property named “list” on the response object, which is an array of all event keys.

So you got all the event keys that you had used in custom events in your app using Countly SDK.

Additionally there are some “built-in Countly events” that are stored as events but are internal events, as:

• [CLY]_session to get session data
• [CLY]_crash to get crash data
• [CLY]_push_open to get push opened data
• [CLY]_push_action to get push action data
• [CLY]_view to get view data

If you need any information on that data from Drill, then include them in your script’s even key array (basically adding them to the array of event keys you retrieved from the API).

Second step:

So now we need to create sha1 hashes for each event and use them in REST API calls to get information about specific events.

Hash is formed using the sha1 hash for the event key together with app ID.

So, for example, for the event with key “Buy” and for app ID “542e95d747f0be510c000004” the hash would be sha1(“Buy542e95d747f0be510c000004”) and could look like: “57c2b7d4eeae912495088c3754d0c6cfe9949f06”

And here is the request to get data about a specific event (replacing domain.com and {hash} for the event hash you want to get info and {api_key} for the user)

http://domain.com/o/db?dbs=countly_drill&collection=drill_events{hash}&limit=1000000000000&api_key={api_key}

Again, if there are too many events for a single request, you can paginate using limit and skip parameters as discussed when getting users. Because it is the same DB Viewer API call with same parameters only for another collection.

And you can then recreate the event timeline using ts property, which is the timestamp when the event occurred.

Another example is if you want to monitor only the latest data, then you can adjust the filter with a timestamp in milliseconds like this (replacing 1470009600000 with a timestamp in milliseconds of the last data sync)

http://domain.com/o/db?dbs=countly_drill&collection=drill_events{hash}&limit=1000000000000&queryr={"ts":{"$gt":1470009600000}}&api_key={api_key}

Similar way again, you can modify the filter parameter to perform any kind of MongoDB queries through filter parameter to filter out event data.

‍

And you can get more info about possible data and properties that you can retrieve using this request here: https://support.count.ly/hc/en-us/articles/360038263211-countly-drill-drill-events-ID-

Conclusion

There are many different ways to export data from Countly. For example, we provide some exporting and data-syncing scripts that you can use for your data export needs. But if you just want to export a small amount of data or understand how data looks like or is liked, DB Viewer and its REST API are exactly what you need.

Let us know if you have any suggestions or examples/use cases for DB Viewer on our  community discord: https://discord.gg/countly

‍

Arturs Sosins is the CTO at Countly, whose main goal is making Countly as robust and extendable as possible. In the spare time, when not spending time with his big family, Arturs enjoys game development, game design, and music-related projects.