Crashlytics｜BigQuery Integration for Real-Time Crash Tracking and Alerts
Developers struggling with delayed crash reports can automate crash log forwarding from Crashlytics to Slack via BigQuery, enabling instant monitoring and faster issue resolution.
点击这里查看本文章简体中文版本。
點擊這裡查看本文章正體中文版本。
This post was translated with AI assistance — let me know if anything sounds off!
Crashlytics + Big Query Build a More Real-Time and Convenient Crash Tracking Tool
Integrate Crashlytics and BigQuery to Automatically Forward Crash Logs to a Slack Channel
Results
Pinkoi iOS Team Real Photos
First, show the result image. Weekly, regularly check the Crashlytics crash reports; filter out the top 10 most frequent crash issues; send the information to the Slack channel, allowing all iOS team members to quickly understand the current stability.
Question
For app developers, the Crash-Free Rate is arguably the most important metric; it represents the percentage of users who did not experience a crash. I believe every app aims for a Crash-Free Rate close to 99.9%. However, this is unrealistic because any software can have bugs, especially since some crashes are caused by the underlying system (Apple) or third-party SDKs. Additionally, the size of the DAU (Daily Active Users) affects the Crash-Free Rate — the higher the DAU, the more likely rare crash issues will occur.
Since a 100% crash-free app does not exist, tracking and handling crashes is very important. Besides the most common Google Firebase Crashlytics (formerly Fabric), there are other options like Bugsnag and Bugfender. I haven’t personally compared these tools, so interested readers can explore them on their own. If you use other tools, the content introduced in this article may not apply.
Crashlytics
Benefits of Choosing Crashlytics:
Stable, Backed by Google
Free, easy, and quick to install
Besides crashes, you can also log error events (e.g., decode error).
A complete Firebase suite can handle everything: other services include Google Analytics, Realtime Database, Remote Config, Authentication, Cloud Messaging, Cloud Storage…
Note: It is not recommended to build a full production service entirely on Firebase, as the costs can become very high once traffic increases… it’s a kind of bait-and-switch model.
Crashlytics also has many drawbacks:
Crashlytics does not provide an API to query crash data
Crashlytics only stores crash records for the past 90 days.
Crashlytics has very poor support and flexibility for integrations.
The biggest pain is the extremely poor support and flexibility of Integrations, combined with no API to write scripts for crash data syncing; you can only occasionally rely on manual checks in Crashlytics to view crash logs and track crash issues.
Crashlytics Supported Integrations Only:
[Email Notification] — Trending Stability Issues (Increasing Crash Problems Encountered by More Users)
[Slack, Email Notification] — New Fatal Issue (Crash Problem)
[Slack, Email Notification] — New Non-Fatal Issue (Non-Crash Problem)
[Slack, Email Notification] — Velocity Alert (Sudden Increase in Crash Issues)
[Slack, Email Notifications] — Regression Alert (Issues Previously Solved but Reappeared)
Crashlytics to Jira issue
The content and rules of the above Integrations cannot be customized.
At the very beginning, we directly used 2.New Fatal Issue to Slack or Email. For Email, it would trigger subsequent scripts via Google Apps Script; however, this notification would bombard the channel nonstop because crashes caused by any issue—big or small, or even sporadic problems from user devices or iOS itself—would all trigger notifications. As DAU grew, the channel was flooded with these alerts daily, but only about 10% of them were truly valuable, related to errors in our code that many users encountered.
As a result, it does not solve the issue of Crashlytics being hard to track automatically, and still requires a lot of time reviewing whether the problem is important.
Crashlytics + Big Query
After searching around, I only found this method, and the official source only provides this one; this is the trap behind the free offering. I guess neither Crashlytics nor Analytics Event has or plans to release an API for users to query data via API. This is because the official recommendation is to import data into BigQuery, and exceeding the free storage and query limits in BigQuery incurs charges.
Storage: The first 10 GB each month is free.
Query: The first 1 TB per month is free. (Query quota means how much data volume is processed when running a Select)
For detailed information, please refer to the Big Query pricing guide
For Crashlytics to BigQuery setup details, please refer to the official documentation. You need to enable GCP services, link a credit card, and more.
Getting Started with Big Query to Query Crashlytics Logs
After setting the Crashlytics Log to Big Query import cycle and completing the first data import, we can start querying the data.
First, go to your Firebase project -> Crashlytics -> click the “•••” at the top right of the list -> select “Go to BigQuery dataset”.
Go to GCP -> Big Query, then in the left “Explorer” select “firebase_crashlytics” -> choose your Table name -> “Detail” -> on the right, you can view Table information, including last modified time, used storage, retention period, and more.
Confirm that the imported data can be queried.
The top tab allows you to switch to “SCHEMA” to view the table’s column information or refer to the official documentation.
Click the “Query” button at the top right to open the interface with the assisted SQL Builder (recommended if you are not familiar with SQL):
Or directly click “COMPOSE NEW QUERY” to open a blank Query Editor:
No matter which method is used, it’s the same text editor; after entering the SQL, you can preview automatic SQL syntax checking and the estimated query cost in the upper right corner (This query will process XXX when run.):
After confirming the query, click “RUN” at the top left to execute it. The results will appear in the Query results section below.
⚠️ Pressing “RUN” to execute the query will count toward your query quota and incur charges; please be careful not to run random queries.
If you are less familiar with SQL, you can first learn the basics, then refer to Crashlytics’ official example for customization:
1. Count the number of crashes per day in 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
`你的ProjectID.firebase_crashlytics.你的TableName`
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
`你的ProjectID.firebase_crashlytics.你的TableName`
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 official example’s query results are sorted differently from what Crashlytics shows, likely because it groups by blame_frame.file (nullable) and blame_frame.line (nullable).
3. Query the top 10 devices with the most crashes in the past 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
`你的ProjectID.firebase_crashlytics.你的TableName`
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, first confirm that the specified Crashlytics data has been imported into BigQuery (for example, the default SQL example queries crash records of the current day, but the data may not have synced yet, so no results will be found); if data is confirmed, then check whether the filter conditions are correct.
Top 10 Crashlytics Issue Big Query SQL
Here, we refer to the official example in section 2 for modification. We want the result to match the crash issues and sorted data as seen on the first page of Crashlytics.
Top 10 Crash Issues in the Past 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
`你的ProjectID.firebase_crashlytics.你的TableName`
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;
Compare the Top 10 crash issues from Crashlytics, matches ✅.
Use Google Apps Script to Periodically Query & Forward to Slack
Go to the Google Apps Script homepage -> Sign in with the same account as Big Query -> Click “New Project” at the top left, then rename the project by clicking the top left again after opening the new project.
First, let’s connect to Big Query to fetch the 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.你的TableName` 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, '你的ProjectID');
var jobId = queryResults.jobReference.jobId;
// Check the status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Retrieve all 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 number of meals can be freely changed to a pre-written Query SQL.
The returned object structure 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.
Add Slack Forwarding Function:
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
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
73
74
75
76
77
78
79
80
81
82
83
84
85
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/你的ProjectID/crashlytics/app/你的專案ID/issues/"+issue_id+"\|"+(i+1)+". Crash: "+number_of_crashes+" times ("+number_of_impacted_user+" users) - "+strip_title+" "+strip_subtitle+">");
}
var messages = top10Tasks.join("\n");
var payload = {
"blocks": [
{
"type": "header",
"text": {
"type": "plain_text",
"text": ":bug::bug::bug: iOS Top Crash Issues in the Last 7 Days :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": "View Last 7 Days in Crashlytics",
"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": "View Last 30 Days in Crashlytics",
"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": "Crash counts and affected versions only include data from the last 7 days, not all data.",
"emoji": true
}
]
}
]
};
var slackWebHookURL = "https://hooks.slack.com/services/XXXXX"; // Replace with your in-coming webhook url
UrlFetchApp.fetch(slackWebHookURL,{
method : 'post',
contentType : 'application/json',
payload : JSON.stringify(payload)
})
}
If you don’t know how to get the incoming WebHook URL, refer to the “Getting Incoming WebHooks App URL” section in this article.
Testing & Scheduling Setup
At this point, your Google Apps Script project should have the two Functions mentioned above.
Next, select the “sendTop10CrashToSlack” function above, then click Debug or Run to test it once; since the first run requires authentication, please run it at least once before proceeding to the next step.
After successfully running a test once, you can start scheduling automatic execution:
Select the alarm icon on the left, then choose “+ Add Trigger” at the bottom right.
The first “Choose which function to run” (the entry point for the function to execute) should be changed to sendTop10CrashToSlack. The schedule can be set according to personal preference.
⚠️⚠️⚠️ Please note that each query accumulates and incurs charges, so do not set it carelessly; otherwise, automated scheduling may run and cause bankruptcy.
Completed
Example Result Image
From now on, you can quickly track current app crash issues directly on Slack; you can even discuss them there.
App Crash-Free Users Rate?
If you want to track the App Crash-Free Users Rate, refer to the next article “Crashlytics + Google Analytics Automated Query for App Crash-Free Users Rate”
Further Reading
Crashlytics + Google Analytics Automatic Query for App Crash-Free Users Rate
Automate Routine Tasks with Python + Google Cloud Platform + Line Bot
Slack Builds Fully Automated WFH Employee Health Reporting System
If you have any questions or feedback, feel free to contact me.
This post was originally published on Medium (View original post), and automatically converted and synced by ZMediumToMarkdown.