Recently I had to gather the amount of monthly sent messages of our 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 announced that they will be charging for the use of their Gateway:

To do this I gathered data directly from the mongodb that stores all rocketchat data: 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. 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 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:

previous arrow
next arrow

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.

previous arrow
next arrow

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.

Effektives Arbeiten und mehr Erfolg durch DevOps-Lösungen und Automatisierung

Welche Potenziale können durch die Implementierung von DevOps und Cloud ausgeschöpft werden? Welche Herausforderungen durch DevSecOps müssen hinsichtlich des Datenschutzes berücksichtigt werden und wie ändert sich die Unternehmenskultur durch das agile Arbeiten?

➥ Zum Whitepaper „DevOps & Cloud: Synergieeffekte nutzen“