Gathering Rocket.chat Statistics

Recently I had to gather the amount of monthly sent messages of our Rocket.chat instance because we wanted to find out how many Push-notifications we will be approximately sending. The reason for this is that the company behind Rocket.chat announced that they will be charging for the use of their Gateway: https://forums.rocket.chat/t/push-notification-gateway-pricing/3044

To do this I gathered data directly from the mongodb that stores all rocketchat data:

Rocket.chat statistics

As you can see, we send more than 100.000 messages each month in our instance. I sadly don’t know how many push-notifications this results in. I had to dig deeper.

Rocket.chat has a „rocketchat_statistics“ collection inside its mongodb that stores the data that can be seen in the info-menu in the admin panel.

While this data is interesting, it doesn’t really help me with my problem.

This data inside the collection however is stored every hour as a json-blob. This means I can gather historical data about our Rocket.chat instance! (Here I somewhat „forgot“ my initial problem and rather worked with this new-found dataset).

Get data from mongodb

To check what data I can extract from the collection I first used a simple find() on the collection:

However, this displayed only the oldest data with not much information, so I had to sort the results to display the latest data. For this there’s the sort-function. You have to pass the sort-function a key to sort and a value to define how to sort.

To sort in descending order, pass -1 to the function:

This gave me the results I wanted:

Looking at the data I found several keys that interested me:

• totalConnectedUsers
• totalRooms
• totalDirect
• totalChannelMessages
• totalPrivateGroupMessages
• totalDirectMessages
• totalMessages

Now I needed to get all historical data for these keys. Fortunately, each json-blob has a createdAt key with the current timestamp. I just had to get all values for these two single keys.
The syntax for this is a little obtuse in my opinion:

After this I had the data and just needed to get it into a usable format. Fortunately, mongoexport allows to export data into csv-format and you can even specify which fields you want to extract:

Using the data…

To get something nice out of this data, I tried to use the most prominent tool for this: Excel. Loading a csv with 100.000 rows into Excel worked fine but as soon as I created a graph, Excel became unbearingly slow.

Searching for another easy method to create something nice out of the data I looked for various online services. However, they had the same problem as Excel: they were too slow.

Then I remembered someone saying that the best way to work with datasets is to use Python! A quick internet search suggested to use the library pandas.

… with Python

I then messed around with pandas and plotly. Plotly is a great and easy to use tool that produces nice graphs from my csv-files.

Here’s the only code I needed to generate them from all my csv’s:

What does not yet work is the static image export in the wsl. It just hangs forever. But that’s for another day.

Now, the best part! The results:

As you can see, most of our users use Windows. No surprise here, since we must use Windows on our computers. What did surprise me is that 31% use Mac OS, only because I never knew that so many people use a Mac at our company, where Windows is mandatory.

Another surprise for me is that two of three people use the desktop app, which is only a wrapper around the website of Rocketchat. Why not just use the webpage in your browser? I seem to be in the minority there.

One can also see when Covid 19 started and we all had to work from home. Most charts have a steep increase after January 2020. We’ll see how this develops. We do also have some other communication channels like Skype, Mail and the occasional MS Teams – I’d really like to see some statistics about these tools. Well, one can wish.