Reliable form of checking last login date / or account activity?

Simon Lee

unread,

Mar 1, 2021, 2:54:43 PM3/1/21

to GAM for Google Workspace

Just wondering if this is possible via GAM or GAMADV-XTD?

The reason I'm asking is that the 'last login date' doesn't appear to be a reliable metric to use when performing analysis on accounts. If a user is logged into a mail client, and only ever use that, then their last login date would show when they logged in to set up, but not any more recently.

So is it possible to retrieve the last sent / received mail date?

And/or the last account activity? (Google Current post?)

Stumped as I need to perform an account reclamation exercise, but have hit some hiccups due to the last login date apparently not being accurate.

Any ideas or advice would be greatly appreciated!

Brian Kim

unread,

Mar 1, 2021, 7:35:20 PM3/1/21

to GAM for Google Workspace

Few other metrics to look at:

https://developers.google.com/admin-sdk/reports/v1/appendix/usage/user/gmail (timestamp_last_access)

https://developers.google.com/admin-sdk/reports/v1/appendix/usage/user/drive (timestamp_last_active_usage)

Aston Wooller

unread,

Mar 2, 2021, 3:53:44 PM3/2/21

to GAM for Google Workspace

Expanding on the metrics listed above

This page lists out the syntax used to report these metrics

https://github.com/jay0lee/GAM/wiki/GAM3CSVListings

So as an example in Powershell I would write the following:

$Date = Get-Date

$Date = $Date.ToString('yyyy-MM-dd_hh-mm')

$csvGAMReport = "Z:\outputlocationhere\$Date-GmailLastAccess.csv"

Z:\gamlocationhere\gam report users parameters accounts:timestamp_last_login,gmail:timestamp_last_access,gmail:timestamp_last_imap |

Out-File $csvGAMReport -encoding ascii

Simon Lee

unread,

Mar 4, 2021, 6:51:55 AM3/4/21

to GAM for Google Workspace

Thanks all, I ended up writing some Apps Script within a Sheet to pull the data.

For anyone who is curious, here it is:

//Set to true if developing/testing - if running a live version, set to false
let testState = false;
function generateUserReport() {
let target;
//Check if dev or prod and change values as required
if (testState == true) {
target = "Frodo....@domain.com";
} else if (testState == false) {
target = "all";
} else {
throw "State not defined";
}
Logger.log("Test State: " + testState);
const daysToCheck = 14;
const now = new Date();
const daysAgo = new Date(now.getTime() - daysToCheck * 24 * 60 * 60 * 1000);
const timezone = Session.getScriptTimeZone();
const date = Utilities.formatDate(daysAgo, timezone, "yyyy-MM-dd");
const parameters = [
"accounts:first_name",
"accounts:last_name",
"accounts:last_login_time",
"gmail:timestamp_last_access",
"gmail:timestamp_last_interaction",
"gmail:timestamp_last_imap",
"gmail:timestamp_last_pop",
"gmail:timestamp_last_webmail",
"drive:timestamp_last_active_usage"
];
const rows = [];
let pageToken;
let page;
Logger.log("Target for API call: " + target);
do {
page = AdminReports.UserUsageReport.get(target, date, {
parameters: parameters.join(","),
maxResults: 500,
pageToken
});
if (page.warnings) {
for (var i = 0; i < page.warnings.length; i++) {
const warning = page.warnings[i];
Logger.log(warning.message);
}
}
const reports = page.usageReports;
if (reports) {
for (var i = 0; i < reports.length; i++) {
const report = reports[i];
const parameterValues = getParameterValues(report.parameters);
const row = [
report.date,
report.entity.userEmail,
parameterValues["accounts:first_name"],
parameterValues["accounts:last_name"],
parameterValues["accounts:last_login_time"],
parameterValues["gmail:last_access_time"],
parameterValues["gmail:last_interaction_time"],
parameterValues["gmail:last_imap_time"],
parameterValues["gmail:last_pop_time"],
parameterValues["gmail:last_webmail_time"],
parameterValues["drive:last_active_usage_time"]
];
//If testing, log row
if (testState == true) {
Logger.log("Logging row: \n" + row);
} else {
}
rows.push(row);
//If testing, log rows
if (testState == true) {
Logger.log("Logging rows after push: \n" + rows);
} else {
}
}
}
pageToken = page.nextPageToken;
} while (pageToken);
if (rows.length > 0) {
const spreadsheet = SpreadsheetApp.getActive();
const sheet = spreadsheet.getSheetByName("User Access Report");
sheet.clear(); // Append the headers.
const headers = [
"Date",
"User",
"First Name",
"Last Name",
"Account Last Login",
"Email Last Access",
"Email Interaction",
"IMAP",
"POP",
"WEBMAIL",
"DRIVE"
];
sheet.appendRow(headers);
// Append the results.
sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
Logger.log("Report spreadsheet completed");
// Send notification email on completion
MailApp.sendEmail({
to: "my.e...@domain.com",
subject: "The account Usage Report has completed",
htmlBody: `The script to get user account usage has completed.<br><br>Please click here view view it:<br>${spreadsheet.getUrl()}`,
name: "Script Helper"
});
} else {
Logger.log("No results returned.");
}
}
/**
* Gets a map of parameter names to values from an array of parameter objects.
* @param {Array} parameters An array of parameter objects.
* @return {Object} A map from parameter names to their values.
*/
function getParameterValues(parameters) {
return parameters.reduce((result, parameter) => {
const name = parameter.name;
let value;
if (parameter.intValue !== undefined) {
value = parameter.intValue;
} else if (parameter.stringValue !== undefined) {
value = parameter.stringValue;
} else if (parameter.datetimeValue !== undefined) {
value = new Date(parameter.datetimeValue);
} else if (parameter.boolValue !== undefined) {
value = parameter.boolValue;
}
result[name] = value;
//If testing, logout the results
if (testState == true) {
Logger.log(`${name} Result: ${result[name]}`);
} else {
}
return result;
}, {});
}

Reply all

Reply to author

Forward