Project import generated by Copybara.
GitOrigin-RevId: d9e9e3fb4e31372ec1fb43b178994ca78fa8fe70
diff --git a/tools/datalab/ratelimiting.ipynb b/tools/datalab/ratelimiting.ipynb
new file mode 100644
index 0000000..9f29fc7
--- /dev/null
+++ b/tools/datalab/ratelimiting.ipynb
@@ -0,0 +1,298 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# Analyzing Rate Limit Exceeded events\n",
+ "\n",
+ "Use this notebook to dig into Rate Limit Exceeded events on Monorail."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "import gcp\n",
+ "import gcp.bigquery as bq\n",
+ "\n",
+ "context = gcp.Context.default()\n",
+ "print 'The current project is %s' % context.project_id\n",
+ "\n",
+ "# Set the date to analyze here:\n",
+ "date = 20160514"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "%%sql --module by_ip\n",
+ "SELECT\n",
+ " protoPayload.ip as ip,\n",
+ " COUNT(protoPayload.requestId) AS num\n",
+ "FROM\n",
+ " [logs.appengine_googleapis_com_request_log_$date]\n",
+ "WHERE\n",
+ " protoPayload.moduleId is null # == \"default\", otherwise you get backend queries too.\n",
+ " AND\n",
+ " protoPayload.line.logMessage LIKE \"Rate Limit Exceeded%\"\n",
+ "GROUP BY\n",
+ " ip\n",
+ "ORDER BY\n",
+ " num DESC\n",
+ "LIMIT\n",
+ " 100;"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "%%sql --module by_ip_class\n",
+ "SELECT\n",
+ " REGEXP_EXTRACT(protoPayload.ip,r'^(?:[^\\.]*\\.){0}([^\\.]*)\\.?') AS a,\n",
+ " REGEXP_EXTRACT(protoPayload.ip,r'^(?:[^\\.]*\\.){1}([^\\.]*)\\.?') AS b,\n",
+ " REGEXP_EXTRACT(protoPayload.ip,r'^(?:[^\\.]*\\.){2}([^\\.]*)\\.?') AS c,\n",
+ " REGEXP_EXTRACT(protoPayload.ip,r'^(?:[^\\.]*\\.){3}([^\\.]*)\\.?') AS d,\n",
+ " COUNT(protoPayload.requestId) AS num\n",
+ "FROM\n",
+ " [logs.appengine_googleapis_com_request_log_$date]\n",
+ "WHERE\n",
+ " protoPayload.moduleId is null # == \"default\", otherwise you get backend queries too.\n",
+ " AND\n",
+ " protoPayload.line.logMessage LIKE \"Rate Limit Exceeded%\"\n",
+ "GROUP BY\n",
+ " a,\n",
+ " b,\n",
+ " c,\n",
+ " d\n",
+ "ORDER BY\n",
+ " num DESC\n",
+ "LIMIT\n",
+ " 100;"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "%%sql --module by_country\n",
+ "SELECT\n",
+ " protoPayload.line.logMessage as line,\n",
+ " COUNT(DISTINCT protoPayload.ip) as ip_count,\n",
+ " COUNT(protoPayload.requestId) AS req_count\n",
+ "FROM\n",
+ " FLATTEN ([logs.appengine_googleapis_com_request_log_$date], protoPayload.line)\n",
+ "WHERE\n",
+ " protoPayload.moduleId is null # == \"default\", otherwise you get backend queries too.\n",
+ " AND\n",
+ " protoPayload.line.logMessage LIKE \"Rate Limit Exceeded%\"\n",
+ " AND\n",
+ " REGEXP_MATCH(protoPayload.line.logMessage, 'X-AppEngine-Country')\n",
+ "GROUP BY\n",
+ " line\n",
+ "ORDER BY\n",
+ " req_count DESC\n",
+ "LIMIT\n",
+ " 100;"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "%%sql --module by_resource\n",
+ "SELECT\n",
+ " protoPayload.resource as resource,\n",
+ " COUNT(protoPayload.requestId) AS req_count\n",
+ "FROM\n",
+ " [logs.appengine_googleapis_com_request_log_$date]\n",
+ "WHERE\n",
+ " protoPayload.moduleId is null # == \"default\", otherwise you get backend queries too.\n",
+ " AND\n",
+ " protoPayload.line.logMessage LIKE \"Rate Limit Exceeded%\"\n",
+ "GROUP BY\n",
+ " resource\n",
+ "ORDER BY\n",
+ " req_count DESC\n",
+ "LIMIT\n",
+ " 100;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Requests by IP"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "df = bq.Query(by_ip, date=date).to_dataframe()\n",
+ "df.head(20)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "if len(df):\n",
+ " df.plot()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Requests by IP Class"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "df = bq.Query(by_ip_class, date=date).to_dataframe()\n",
+ "df.head(20)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "if len(df):\n",
+ " df.plot()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Requests by Country Code"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "df = bq.Query(by_country, date=date).to_dataframe()\n",
+ "df.head(20)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "if len(df):\n",
+ " df.plot()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Requests by Requested Resource"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "df = bq.Query(by_resource, date=date).to_dataframe()\n",
+ "df.head(20)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "if len(df):\n",
+ " df.plot()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": []
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 2",
+ "language": "python",
+ "name": "python2"
+ },
+ "language_info": {
+ "codemirror_mode": {
+ "name": "ipython",
+ "version": 2
+ },
+ "file_extension": ".py",
+ "mimetype": "text/x-python",
+ "name": "python",
+ "nbconvert_exporter": "python",
+ "pygments_lexer": "ipython2",
+ "version": "2.7.9"
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 0
+}