Monitoring of operating system versions in Google Workspace

Let’s continue the presentation of examples of use event logs from Google Workspace. Today I’ll describe how to use event logs to check and notify users about outdated versions of the operating system. The idea is to inform users via email that newer versions of the operating systems are available to install. Described methods work for all popular operating systems - e.g. MacOS, Windows, Linux, iOS or Android. 

Keep in mind that this method only notify users and/or administrators. The next step is to enforce installation of the new version. MDM systems can enforce such installation or we can use Google Workspace Context-Aware Access to prohibit older versions. 

Described use case: User just after login to Google Workspace receives notification via email that newer version of the operating system is available.

Source code described here is available at github repository: https://github.com/Prevenity/Cloud-Security

The main function is main_version_monitor(). The first function is read_dates(). The function is using a spreadsheet to store the date of the last execution of the script. The main function should be executed at least every 5-10 minutes in order to notify users as soon as possible. We use the last execution date because in the next function read_events() is calling AdminReports.Activities.list() GSuite API which retrieves a list of activities. The read_events() collect 6 types of data from Google Workspace events: DEVICE_TYPE, DEVICE_MODEL, OS_VERSION, SERIAL_NUMBER, date and email of user. 

try {
 response = AdminReports.Activities.list(
 userKeyA, //”all”
 applicationNameA, //”mobile”
 optionalArgsA
);

} 

catch (error) {
Logger.log(error);
}

...

if (params_event[zm4].name == "DEVICE_TYPE")
 device_type_vod = params_event[zm4].value;
if (params_event[zm4].name == "DEVICE_MODEL")
 device_model_vod = params_event[zm4].value;
if (params_event[zm4].name == "OS_VERSION")
 os_version_vod = params_event[zm4].value;
if (params_event[zm4].name == "SERIAL_NUMBER”
 serial_id_vod = params_event[zm4].value;

…

table_mobile.push([
 event_temp.actor.email,
 event_temp.actor.callerType,
 event_temp.id.time,
 serial_id_vod,
 device_type_vod,
 device_model_vod,
 os_version_vod]);

The next important function is compare_version_v2(). As you can see we are able to collect from the Device Action event information about the version of the operating system. Now, we have to inform the function which operating systems are approved. We are using a spreadsheet as a database of approved versions. An example is below:

Function is quite simple:

function compare_version_v2(version_to_check){

var handler_to_file = open_spreadsheet_file("", "supported versions");

for(var zmx = 1; zmx<handler_to_file[10].length;zmx++ )

{

 if(handler_to_file[10][zmx] == version_to_check)

   return true

}

 return false;

}

Finally, we have to prepare a message which will be sent via email.

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