Using IP geolocation to detect suspicious logins to GSuite

Today I will describe how to detect suspicious login activities to Google Workspace.

I am using AdminReports.Activities.list API (https://developers.google.com/admin-sdk/reports/reference/rest/v1/activities/list) from my Apps Script script (https://script.google.com/home). To limit results I will filter only login applications (https://developers.google.com/admin-sdk/reports/reference/rest/v1/activities/list#ApplicationName). According to documentation the Login application’s activity reports return information about different types of Login activity events. The most important events are: login_failure and login_success.

Below function returns 4 fields (email of user, event name, event time and source IP address). we have to provide two parameters to our function - start date and end date. It is important to store end date in order to provide this value during next call.

function Logins(starts, ends) {

    var pageToken, responseX;

    var userKey = "all";

    var applicationName = "login";

    var table = [];

    do {

        var optionalArgs = {

            maxResults: 20,

            startTime: starts,

            endTime: ends,

            pageToken: pageToken,

        };

        responseX = AdminReports.Activities.list(

            userKey,

            applicationName,

            optionalArgs

        );

        var activities = responseX.items;

        if (activities && activities.length > 0) {

            for (i = 0; i < activities.length; i++) {

                var activity = activities[i];

                if (

                    activity.events[0].name == "login_failure" ||

                    activity.events[0].name == "login_success"

                )

                    table.push([

                        activity.actor.email,

                        activity.events[0].name,

                        activity.id.time,

                        activity.ipAddress,

                    ]);

            }

        } else {

            Logger.log("No logins found.");

        }

        pageToken = responseX.nextPageToken;

    } while (pageToken);

    return table;

}

I also had to create function responsible for retrieving GEO IP values (City, Country and continent). I decided to us ipbase.com database via API but you can choose any other geo ip database.

function example_function(){

var result_from_logins = Logins(“2022-10-06T19:23:13.280Z”, “2022-10-07T19:23:13.280Z”);

var geo_ip_data = getIpGeolocationData(result_from_logins[counter][3],'CITY, COUNTRY');

Logger.log(geo_ip_data);

}

A part of function getIpGeolocationData is presented below:

ipData = isBlank(ip)? getResponseJsonData('https://api.ipbase.com/v2/info') : getResponseJsonData('https://api.ipbase.com/v2/info?ip=' + ip);

Result is returned as JSON. In Apps Script fetch method is used from UrlFetchApp class to call ipbase API.

function getResponseJsonData(url) {

    if (isBlank(url)) return;

    let response = UrlFetchApp.fetch(url, {

        headers: {

            apikey: APIKEY,

        },

    });

    let json = response.getContentText();

    return JSON.parse(json);

}

Results can be written to spreadsheet or send directly to the SOC team. I am writing data to spreadsheet where each tab contain suspicious activities from one day. It is also possible to filter countries and email address to limit false alarms then SOC team can focus only on admin accounts and logon events from some countries.

References:

[1] https://github.com/Prevenity/Cloud-Security/tree/master/Apps%20Script

[2] https://developers.google.com/admin-sdk