Salesforce login statistics aggregation

A recent requirement from a customer was "I'd like to analyze logins by users in Excel", despite a dashboard approach would be sufficient. With a few million records aggregating in Excel wasn't particularly appealing

Download the log

Salesforce setup allows to download the log as csv or csv.gz file. In any case you should use the later. I learned the hard way: the chunked transfer encoding might leave you with less data be processed than you expect.

The Scanner simply stopped after a few thousand entries, while the csv parser barfed with an error.

Processing

After downloading and extracting the csv I used a small Java routine (yep, I'm that old) to aggregate logins per user, capturing the count and the first/last login date as well as the country of login (with the disclaimer caveats) and the eventual community.

For reliably and robustly reading csv in Java, usually I would use a robust library, however in this case having no dependencies and using the scanner did just nicely

Two little details: to get the results I simply overwrote the toString() method, so the output generation is a one liner using an arrow function. Enjoy!

Class: Report.java

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.Map;
import java.util.Scanner;
import java.util.TreeMap;

/**
 * @author swissel
 *
 */
public class Report {

    /**
     * @param args
     * @throws IOException
     */
    public static void main(final String[] args) throws IOException {
        if (args.length < 2) {
           System.err.println("Use: java report CSVInputFile CSVOutoutFile");
           System.exit(1);
         }
        // From args[0]/[1]
        final String inFileName = args[0];
        final String outFileName = args[1];

        final File inFile = new File(inFileName);
        final File outFile = new File(outFileName);

        if (outFile.exists()) {
            outFile.delete();
        }
        final OutputStream out = new FileOutputStream(outFile);
        final Report r = new Report();
        r.run(inFile, out);
        out.close();
    }

    private void run(final File inFile, final OutputStream outStream) throws IOException {

        final Map<String, UserStat> allUsers = new TreeMap<>();
        int total = 0;
        final PrintWriter out = new PrintWriter(outStream);
        final Scanner s = new Scanner(inFile);
        s.nextLine(); // Skip the header
        while (s.hasNextLine()) {
            total++;
            final String line = s.nextLine();
            final String[] items = line.split(",");
            final String user = items[0];
            final String timeString = items[1];
            final String country = (items.length > 16) ? items[16] : "N/A";
            final String community = (items.length > 12) ? items[12] : "N/A";
            final UserStat curUser = (allUsers.containsKey(user)) ? allUsers.get(user) : new UserStat(user);
            curUser.addLogin(timeString, country, community);
            allUsers.put(user, curUser);
        }
        s.close();
        out.println("User,Country,Community,firstLogin,LastLogin,LoginCount");
        allUsers.entrySet().forEach(u -> out.println(u.getValue()));
        System.out.println(total);
    }
}

Class: UserStat.java

public class UserStat {
    
    private final String userName;
    private String country = "N/A";
    private String community = "N/A";
    private int loginCount;
    private String firstLogin = "";
    private String lastLogin = "";
    
    public UserStat(final String userName) {
        this.userName = userName;
    }
    
    public void addLogin(final String dateString, final String country, final String community) {
        String loginDateString = this.yearMonthDate(dateString);
        if (country != null && !country.equals("") && !country.equals("N/A")) {
            this.country = country;
        }
        if (community != null && !community.equals("") && !community.equals("N/A")) {
            this.community = community;
        }
        this.loginCount++;
        if (loginDateString.compareTo(this.lastLogin) > 0) {
            this.lastLogin = loginDateString;
        }
        
        if (this.firstLogin.equals("") || this.firstLogin.compareTo(loginDateString) > 0) {
            this.firstLogin = loginDateString;
        }
    }
    
    @Override
    public String toString() {
        return this.userName+","+this.country+","+this.community+","+this.firstLogin+","+this.lastLogin+","+String.valueOf(this.loginCount);
    }
    
    // Reformats date string Singapore format DD/MM/YYYY into YYYY-MM-DD
    private String yearMonthDate(final String incoming) {
        return incoming.substring(7, 11)+"-"+incoming.substring(4, 6)+"-"+incoming.substring(1, 3);
    }

}

Take the result, open it in the spreadsheet of your choice and make shiny graphs!

As usual YMMV!