Crashlytics + Big Query: Creating a More Immediate and Convenient Crash Tracking Tool
Integrating Crashlytics and Big Query to automatically forward crash logs to a Slack Channel
ℹ️ℹ️ℹ️ The following content is translated by OpenAI.
Click here to view the original Chinese version. | 點此查看本文中文版
Crashlytics + Big Query: Creating a More Immediate and Convenient Crash Tracking Tool
Integrating Crashlytics and Big Query to automatically forward crash logs to a Slack Channel
Results
Pinkoi iOS Team Actual Photo
First, here’s the result image: we query Crashlytics crash logs weekly; filter out the top 10 issues with the most crashes; and send the messages to the Slack Channel, making it easier for all iOS team members to quickly understand the current stability.
Issues
For app developers, the Crash-Free Rate can be considered the most important metric; the data represents the proportion of app users who did not encounter a crash. I believe that every app should aim for a Crash-Free Rate of approximately 99.9%; however, this is not realistically achievable. As long as there is code, there can be bugs, and some crash issues may be caused by underlying (Apple) or third-party SDKs. Additionally, the varying Daily Active Users (DAU) can also impact the Crash-Free Rate; the higher the DAU, the more likely it is to encounter sporadic crash issues.
Since a 100% crash-free app does not exist, tracking and addressing crashes becomes very important. Besides the most common Google Firebase Crashlytics (formerly Fabric), there are other options like Bugsnag and Bugfender… I haven’t compared these tools in practice, so interested friends can research them on their own; if you use other tools, you won’t be able to utilize the content this article aims to introduce.
Crashlytics
The benefits of choosing Crashlytics include:
- Stability, backed by Google
- Free, easy and quick installation
- Besides crashes, it can also log error events (e.g., Decode Error)
- A complete Firebase suite: other services include Google Analytics, Realtime Database, Remote Config, Authentication, Cloud Messaging, Cloud Storage…
As a side note: I do not recommend building a fully-fledged service using Firebase, as the costs can become very high once traffic increases… it’s a classic bait-and-switch scenario.
However, Crashlytics also has many drawbacks:
- Crashlytics does not provide an API for querying crash data
- Crashlytics only retains crash logs for the past 90 days
- Crashlytics’ integration support and flexibility are extremely poor
The most painful aspect is the poor integration support and flexibility, combined with the lack of an API to write scripts for querying crash data; we can only rely on manual checks in Crashlytics from time to time to view crash logs and track crash issues.
Integrations Supported by Crashlytics:
- [Email Notification] — Trending stability issues (crash issues encountered by an increasing number of users)
- [Slack, Email Notification] — New Fatal Issue (crash issues)
- [Slack, Email Notification] — New Non-Fatal Issue (non-crash issues)
- [Slack, Email Notification] — Velocity Alert (sudden increase in crash issues)
- [Slack, Email Notification] — Regression Alert (issues that were solved but reoccurred)
- Crashlytics to Jira issue
The content and rules of the above integrations cannot be customized.
Initially, we directly used 2. New Fatal Issue to Slack or Email; if sent to Email, we would trigger subsequent processing scripts via Google Apps Script; however, this notification would flood the notification channel, as it would notify us regardless of whether the issue was significant or just a minor problem caused by user devices or iOS itself. As DAU grew, we were bombarded with these notifications daily, and only about 10% of them were truly valuable, related to errors in our code.
Thus, it did not solve the problem of Crashlytics being difficult to track automatically, and we still had to spend a lot of time reviewing whether the issues were important.
Crashlytics + Big Query
After searching around, I found this method, which is the only one provided by the official documentation; this is the trap under the guise of free offerings. I suspect that neither Crashlytics nor Analytics Events will ever provide an API for users to query data, as the official recommendation is to import data into Big Query for use, and exceeding the free storage and query limits in Big Query incurs charges.
Storage: The first 10 GB per month is free.
Query: The first 1 TB per month is free. (The query limit refers to the amount of data processed when executing a SELECT statement.)
For more details, please refer to the Big Query pricing documentation.
The setup details for Crashlytics to Big Query can be found in the official documentation, which requires enabling GCP services, binding a credit card, etc.
Getting Started with Big Query to Query Crashlytics Logs
Once the import cycle for Crashlytics logs to Big Query is set up and the first import has data, we can start querying the data.
First, go to the Firebase project -> Crashlytics -> click on the “•••” in the upper right corner -> select “BigQuery dataset.”
After going to GCP -> Big Query, you can select “firebase_crashlytics” in the left “Explorer” -> choose your Table name -> “Detail” -> on the right, you can view Table information, including the latest modification time, used capacity, storage duration, etc.
Confirm that there is imported data available for querying.
The upper tab can be switched to “SCHEMA” to view the field information of the Table or refer to the official documentation.
Clicking the “Query” button in the upper right corner opens an interface with an SQL Builder for assistance (if you’re not familiar with SQL, it’s recommended to use this):
Or you can directly click “COMPOSE NEW QUERY” to open a blank Query Editor:
Regardless of the method, it’s the same text editor; after entering the SQL, you can automatically check the SQL syntax and the estimated query cost in the upper right corner (This query will process XXX when run.):
Once you confirm the query, click “RUN” in the upper left to execute the query, and the results will be displayed in the Query results section below.
⚠️ After clicking “RUN” to execute the query, it will accumulate towards the query limit, and charges will apply; so please be careful not to run unnecessary queries.
If you’re not familiar with SQL, you can first understand the basic usage and then refer to the Crashlytics official examples to modify:
1. Count the number of crashes per day for the last 30 days:
1
2
3
4
5
6
7
8
9
10
SELECT
COUNT(DISTINCT event_id) AS number_of_crashes,
FORMAT_TIMESTAMP("%F", event_timestamp) AS date_of_crashes
FROM
`yourProjectID.firebase_crashlytics.yourTableName`
GROUP BY
date_of_crashes
ORDER BY
date_of_crashes DESC
LIMIT 30;
2. Query the top 10 most frequent crashes in the last 7 days:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
DISTINCT issue_id,
COUNT(DISTINCT event_id) AS number_of_crashes,
COUNT(DISTINCT installation_uuid) AS number_of_impacted_user,
blame_frame.file,
blame_frame.line
FROM
`yourProjectID.firebase_crashlytics.yourTableName`
WHERE
event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL 168 HOUR)
AND event_timestamp < CURRENT_TIMESTAMP()
GROUP BY
issue_id,
blame_frame.file,
blame_frame.line
ORDER BY
number_of_crashes DESC
LIMIT 10;
However, the data retrieved using this method may not match the order seen in Crashlytics, likely due to grouping by blame_frame.file (nullable) and blame_frame.line (nullable).
3. Query the 10 devices with the most crashes in the last 7 days:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
device.model,
COUNT(DISTINCT event_id) AS number_of_crashes
FROM
`yourProjectID.firebase_crashlytics.yourTableName`
WHERE
event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 168 HOUR)
AND event_timestamp < CURRENT_TIMESTAMP()
GROUP BY
device.model
ORDER BY
number_of_crashes DESC
LIMIT 10;
For more examples, please refer to the official documentation.
If your SQL returns no data, please first ensure that the specified conditions have Crashlytics data imported into Big Query (for example, the default SQL example queries for the day’s crash records, but the data may not have been synchronized yet); if you confirm there is data, then check if the filtering conditions are correct.
Top 10 Crashlytics Issue Big Query SQL
Here, we modify the official example from 2. to achieve results that match the crash issues and sorting data we see on the first page of Crashlytics.
Top 10 crash issues in the last 7 days:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
DISTINCT issue_id,
issue_title,
issue_subtitle,
COUNT(DISTINCT event_id) AS number_of_crashes,
COUNT(DISTINCT installation_uuid) AS number_of_impacted_user
FROM
`yourProjectID.firebase_crashlytics.yourTableName`
WHERE
is_fatal = true
AND event_timestamp >= TIMESTAMP_SUB(
CURRENT_TIMESTAMP(),
INTERVAL 7 DAY
)
GROUP BY
issue_id,
issue_title,
issue_subtitle
ORDER BY
number_of_crashes DESC
LIMIT
10;
Comparing the results of the Top 10 crash issues in Crashlytics, they match ✅.
Using Google Apps Script to Regularly Query & Forward to Slack
Go to the Google Apps Script homepage -> log in with the same account as Big Query -> click “New Project” in the upper left corner, and after opening the new project, you can rename it in the upper left corner.
First, let’s complete the integration with Big Query to obtain query data:
Refer to the official documentation example and insert the above Query SQL.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
function queryiOSTop10Crashes() {
var request = {
query: 'SELECT DISTINCT issue_id, issue_title, issue_subtitle, COUNT(DISTINCT event_id) AS number_of_crashes, COUNT(DISTINCT installation_uuid) AS number_of_impacted_user FROM `firebase_crashlytics.yourTableName` WHERE is_fatal = true AND event_timestamp >= TIMESTAMP_SUB( CURRENT_TIMESTAMP(), INTERVAL 7 DAY ) GROUP BY issue_id, issue_title, issue_subtitle ORDER BY number_of_crashes DESC LIMIT 10;',
useLegacySql: false
};
var queryResults = BigQuery.Jobs.query(request, 'yourProjectID');
var jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
Logger.log(queryResults.rows);
rows = rows.concat(queryResults.rows);
}
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
return data
}
query: The query can be replaced with any SQL you have written.
The structure of the returned object is as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
[
[
"67583e77da3b9b9d3bd8feffeb13c8d0",
"<compiler-generated> line 2147483647",
"specialized @nonobjc NSAttributedString.init(data:options:documentAttributes:)",
"417",
"355"
],
[
"a590d76bc71fd2f88132845af5455c12",
"libnetwork.dylib",
"nw_endpoint_flow_copy_path",
"259",
"207"
],
[
"d7c3b750c3e5587c91119c72f9f6514d",
"libnetwork.dylib",
"nw_endpoint_flow_copy_path",
"138",
"118"
],
[
"5bab14b8f8b88c296354cd2e",
"CoreFoundation",
"-[NSCache init]",
"131",
"117"
],
[
"c6ce52f4771294f9abaefe5c596b3433",
"XXX.m line 975",
"-[XXXX scrollToMessageBottom]",
"85",
"57"
],
[
"712765cb58d97d253ec9cc3f4b579fe1",
"<compiler-generated> line 2147483647",
"XXXXX.heightForRow(at:tableViewWidth:)",
"67",
"66"
],
[
"3ccd93daaefe80f024cc8a7d0dc20f76",
"<compiler-generated> line 2147483647",
"XXXX.tableView(_:cellForRowAt:)",
"59",
"59"
],
[
"f31a6d464301980a41367b8d14f880a3",
"XXXX.m line 46",
"-[XXXX XXX:XXXX:]",
"50",
"41"
],
[
"c149e1dfccecff848d551b501caf41cc",
"XXXX.m line 554",
"-[XXXX tableView:didSelectRowAtIndexPath:]",
"48",
"47"
],
[
"609e79f399b1e6727222a8dc75474788",
"Pinkoi",
"specialized JSONDecoder.decode<A>(_:from:)",
"47",
"38"
]
]
You can see it is a two-dimensional array.
Adding the Function to Forward to Slack:
Continue to add a new function below the above code.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
function sendTop10CrashToSlack() {
var iOSTop10Crashes = queryiOSTop10Crashes();
var top10Tasks = new Array();
for (var i = 0; i < iOSTop10Crashes.length ; i++) {
var issue_id = iOSTop10Crashes[i][0];
var issue_title = iOSTop10Crashes[i][1];
var issue_subtitle = iOSTop10Crashes[i][2];
var number_of_crashes = iOSTop10Crashes[i][3];
var number_of_impacted_user = iOSTop10Crashes[i][4];
var strip_title = issue_title.replace(/[\<|\>]/g, '');
var strip_subtitle = issue_subtitle.replace(/[\<|\>]/g, '');
top10Tasks.push("<https://console.firebase.google.com/u/1/project/yourProjectID/crashlytics/app/yourProjectID/issues/"+issue_id+"|"+(i+1)+". Crash: "+number_of_crashes+" times ("+number_of_impacted_user+" users) - "+strip_title+" "+strip_subtitle+">");
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
var messages = top10Tasks.join("\n");
var payload = {
"blocks": [
{
"type": "header",
"text": {
"type": "plain_text",
"text": ":bug::bug::bug: iOS Last 7 Days Crash Issue Ranking :bug::bug::bug:",
"emoji": true
}
},
{
"type": "divider"
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": messages
}
},
{
"type": "divider"
},
{
"type": "actions",
"elements": [
{
"type": "button",
"text": {
"type": "plain_text",
"text": "Go to Crashlytics to view records from the last 7 days",
"emoji": true
},
"url": "https://console.firebase.google.com/u/1/project/你的ProjectID/crashlytics/app/你的專案ID/issues?time=last-seven-days&state=open&type=crash&tag=all"
},
{
"type": "button",
"text": {
"type": "plain_text",
"text": "Go to Crashlytics to view records from the last 30 days",
"emoji": true
},
"url": "https://console.firebase.google.com/u/1/project/你的ProjectID/crashlytics/app/你的專案ID/issues?time=last-thirty-days&state=open&type=crash&tag=all"
}
]
},
{
"type": "context",
"elements": [
{
"type": "plain_text",
"text": "The number of crashes and the versions in which they occurred are only based on data from the last 7 days, not all data.",
"emoji": true
}
]
}
]
};
var slackWebHookURL = "https://hooks.slack.com/services/XXXXX"; // Replace with your incoming webhook URL
UrlFetchApp.fetch(slackWebHookURL,{
method : 'post',
contentType : 'application/json',
payload : JSON.stringify(payload)
})
}
If you don’t know how to obtain the incoming WebHook URL, you can refer to the section “How to Obtain Incoming WebHooks App URL” in this article.
Testing & Scheduling
At this point, your Google Apps Script project should have the two functions mentioned above.
Next, please select the “sendTop10CrashToSlack” function from the dropdown above, and then click Debug or Run to execute the test once; since the first execution requires authentication, please run it at least once before proceeding to the next step.
Once the test runs successfully, you can start setting up a schedule for automatic execution:
On the left, select the clock icon, then choose the “+ Add Trigger” option in the bottom right.
For the first option “Choose which function to run” (the function entry that needs to be executed), change it to sendTop10CrashToSlack, and set the time interval according to your preference.
⚠️⚠️⚠️ Please be especially careful as each query will accumulate and incur charges, so do not set it randomly; otherwise, you might end up bankrupt due to automatic scheduling.
Completion
Example Result Image
From now on, you can quickly track current app crash issues directly on Slack; you can even discuss them right there.
App Crash-Free Users Rate?
If you want to track the App Crash-Free Users Rate, refer to the next article “Automatically Query App Crash-Free Users Rate with Crashlytics + Google Analytics”.
Further Reading
- Automatically Query App Crash-Free Users Rate with Crashlytics + Google Analytics
- Using Python + Google Cloud Platform + Line Bot to Automate Routine Tasks
- Creating a Fully Automated WFH Employee Health Reporting System with Slack
- Using Google Apps Script to Forward Gmail Messages to Slack
If you have any questions or suggestions, feel free to contact me.
```
This article was first published on Medium ➡️ Click Here
Automatically converted and synchronized using ZMediumToMarkdown and Medium-to-jekyll-starter.
