Exporting data from Countly through DB Viewer

Previously we mentioned DB Viewer plugin and that, additionally to providing UI to browse database, it was also a great option to access Database data through REST API and export data.

And we have been t swamped with followup questions on how to get the data customers need. So we decided to sum up some common cases that in the end satisfied all user needs.

So for example, you have some other database and you want to populate it with data from Countly. Or you just want to prepare some kind of reports through 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 user with provided api_key.

This is useful for getting list of app_ids if you are writing 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: http://resources.count.ly/docs/oappsall

2. Getting user information about 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&filter={"_id":{"$ne":"uid-sequence"}}&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 single request, you can paginate result by using limit and skip parameters. As example:

http://domain.com/o/db?dbs=countly&collection=app_users{app_id}&limit=100&skip=200&filter={"_id":{"$ne":"uid-sequence"}}&api_key={api_key}

More information on pagination and filtering: http://resources.count.ly/docs/odbdbsdbcollectioncollection)

More information on possible user properties that you might get: http://resources.count.ly/docs/countlyapp_usersappid

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

http://domain.com/o/db?dbs=countly&collection=app_users{app_id}&limit=1000000000000&filter={"_id":{"$ne":"uid-sequence"},"fs":{"$gt":1470009600}}&api_key={api_key}

replacing 1470009600 with 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 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 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: http://resources.count.ly/docs/omethodget_events

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

So you got all event keys, that you had use 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 event.

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

So for example for 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 specific event is (replacing domain.com and {hash} for the event hash you want to get info and {api_key} for user)

http://domain.com/o/db?dbs=countly_drill&collection=drill_events{hash}&limit=1000000000000&filter={"_id":{"$ne":"meta"}}&api_key={api_key}

Again, if there are too many events for 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 event timeline using ts property, which is timestamp when event occurred.

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

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

Similar way again you can modify 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:
http://resources.count.ly/docs/countly_drilldrill_eventsid

Hope this covers most common cases for all other users, but if it still does not drop us a line for more information at engineering@count.ly

Arturs Sosins is one of the lead engineers at Countly, who’s 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.