09 November 2015

Using the Setup Audit Trail API - the best kept secret of Winter '16

The Setup Audit Trail has been around for a long time. It's part of the Salesforce trust platform and is built into every edition. It tracks changes in setup by administrators such as adding new users, activation of users, changes in permissions including escalations of privilege, and changes to metadata like the creation of fields or deletion of objects. 

Until very recently, Setup Audit Trail was only available to manually download to a CSV (comma separated values) file using a link on a page in setup. I kept hearing how customers hated putting manual processes in place to click the link once a month as well as merge newly downloaded CSV files into previous exports. It made audit and compliance use cases much harder than what anyone wanted. Everyone was asking to integrate the Setup Audit Trail using the API so they could schedule regular, automated downloads of audit trail data to analytics and SIEM tools.

Starting with Winter '16, we added the Setup Audit Trail to the API as the SetupAuditTrail sObject. There are a couple key use cases that you might want to try out using a tool like Workbench.

1. I want to know everyone who logged in as a particular end-user:
SELECT Action, CreatedById, CreatedDate, DelegateUser, Display, Id, Section 
FROM SetupAuditTrail 
WHERE CreatedBy.Name = 'Jim Rivera' 

2. I want to know everyone an admin user logged in as:
SELECT Action, CreatedBy.Name, CreatedDate, DelegateUser, Display, Id, Section 
FROM SetupAuditTrail 
WHERE DelegateUser = 'at@xx.com' 

3. I want to know everything that any users with a specific profile (or role) did in setup:
SELECT Action, CreatedBy.Profile.Name, CreatedDate, DelegateUser, Display, Id, Section 
FROM SetupAuditTrail 
WHERE CreatedBy.Profile.Name = 'EMEA VP' 

4. I want to know every user who was 'frozen' in the last week
SELECT Action, CreatedById, CreatedDate, DelegateUser, Display, Id, Section 
FROM SetupAuditTrail 
WHERE Action = 'frozeuser' AND CreatedDate = Last_n_Days:7 

5. I want to know everything a specific user did last week
SELECT Action, CreatedById, CreatedDate, DelegateUser, Display, Id, Section 
FROM SetupAuditTrail 
WHERE CreatedBy.Name = 'Adrian Kunzle' AND CreatedDate = Last_n_Days:7 

I use 'Limit 10' to just test the queries and keep them from taking a long time to return which is a good idea when experimenting with new queries.

Once you know what kind of queries you can write, you can create incredible apps that combine the SetupAuditTrail API with an app building platform like Node.js on Heroku:
The application above was created by the incredible Alessandro. You can download the source code from his Github repository or try it out with this free Heroku app.

You can also explore Setup Audit Trail data using an analytics platform like Wave:

The Setup Audit Trail is a powerful way of tracking important administrative audit events and now it's even more accessible through the API.

02 November 2015

These are a few of my favorite things

Event Monitoring provides easily downloadable CSV(Comma Separated Value) log files. The value of this data is really in the insights that you gain from rolling up and aggregating the data based on questions like:
  1. how many accounts were accessed in the last week?
  2. how many reports were downloaded by a specific user in the last 6 months?
  3. how many total executions did I have in the past year?
  4. how big are the files that I need to store on an ongoing basis for regulatory reasons?
Normally you would spend time to integrate, enrich, and analyze log data using an analytics platform. We have a great app ecosystem of ISV (Independent Software Vendors) that provide pre-built insights into this data.

But sometimes you just want a quick and easy way to answer this data without first creating an integration or enriching the data. In particular, you may want to analyze the data when it ranges over extremely long periods of time and significant volumes of data that you may not already have integrated into your analytics platform of choice. This actually came up in conversation with a customer administrator who told me that he was downloading Event Log Files and using a command line tool called grep to ask simple questions about the data; in effect he was trying to find a needle in the log file haystack without using an analytics platform.

Event Log Files generate CSV log file content. As a result, it's easy to report on it using piping in a Unix based CLI (Command Line Interface or Terminal).  This is definitely not for the button click crowd but it's incredibly fast and efficient for processing large amounts of data quickly. And since it's a good idea to keep a source system of files for a long period of time, you can always write quick piping commands to answer ad-hoc questions that may come up where you don't want to first stage or munge the data into an analytics platform.

If you haven't ever worked with a CLI (Command Line Interface or Terminal), I recommend first reading this great blog post from Lifehacker or this DataVu blog post. Also, if you're a windows only user, I wouldn't really continue with this post unless your files are on a unix based system like Mac or Linux that you can SSH into.

Here a few of my favorite things when it comes to commands to CLI prompts:

Use case: login to the file server where you're keeping the source log files
Example Prompt: ssh username@boxname
Notes: you'll need a password which means if you don't own that server, you'll need to request access

Use case: navigate to the directory where the log files are for a specific date (assuming you're storing them by date rather than by type or something else)
Example Prompt: cd Documents/logs/2015-09-30
Notes: if you don't manage this server, you'll need to find out where the files are first

Use case: list the files based on last modified date
Example Prompt: ls -tr
Example Output:
Notes: I love this command. It's super simple and assumes you're dealing with the latest change first.

Use case: list the files based on size sorted from largest to smallest
Example Prompt: ls -less | sort -n -r
Example Output:
4116672 -rw-r--r--  1 auser  SFDC\Domain Users  2107733241 Oct  1 21:43 ApexTrigger-2015-09-30.csv
3512792 -rw-r--r--  1 auser  SFDC\Domain Users  1798546132 Oct  1 21:53 UITracking-2015-09-30.csv
3437816 -rw-r--r--  1 auser  SFDC\Domain Users  1760159289 Oct  1 21:58 URI-2015-09-30.csv
Notes: This is really helpful for finding the size of files, for instance if you just want to view the smallest file as a sample data set.

Use case: view the entire file in the terminal
Example Prompt: cat PackageInstall-2015-09-30.csv
Example Output:
"PackageInstall","20150930175506.083","4-YHvN7eC7w3t6H5TippD-","00D000000000062","0053000000BqwWz","175","","","","INSTALL","0","0","0","0","SOS Quick Setup & Dashboard","OTHER","2015-09-30T17:55:06.083Z","0053000000BqwWzAAJ"
"PackageInstall","20150930175953.848","4-YIEBVUUMqV2bH5Tiluk-","00D000000000062","0053000000BqwWz","96","","","","INSTALL","0","0","0","0","SOS Quick Setup
Notes: This is really helpful for small files but can be overwhelming when you have a larger one unless you first reduce the output using a command like grep.

Use case: only view the first 10 lines of a file
Example Prompt: head -10 ApexTrigger-2015-09-30.csv
Example Output:
Notes: This is really helpful for really large files when you want to quickly validate the file headers with some sample data without having to load all of the data to the terminal or first reduce the data using a command like grep.

Use case: only view the last 10 lines of a file
Example Prompt: tail -10 ApexTrigger-2015-09-30.csv
Example Output:
Notes: This is really helpful for really large files to find when and where the file ends without having to load all of the data to there terminal. Several times, I had questions where the file ended and when the last events were processed for the day. Tail helps me to find out where the file and day ends.

Use case: find out how many total transactions you had for a day, sorted from highest to lowest
Example Prompt: wc -cl /Users/atorman/Documents/logs/2015-09-30/*.csv | sort -n -r 
Example Output:
31129864 9577494169 total
8904594 2107733241 /Users/auser/Documents/logs/2015-09-30/ApexTrigger-2015-09-30.csv
5655267 1760159289 /Users/auser/Documents/logs/2015-09-30/URI-2015-09-30.csv
Notes: This is really helpful for capacity planning or for quick questions around total transactions by file type or date. I'm often asked how many log lines were generated or how big the files are that we're collecting. This is also really easy to output to a CSV so you can provide the information to another person. Just change the prompt to wc -cl /Users/atorman/Documents/logs/ | sort -n -r >> lineCount.csv.

Use case: simple report on number of lines by file type (*.csv)
Example Prompt: du -a *.csv | sort -n -r 
Example Output:
1676752 UITracking-2015-11-01.csv
1094160 ApexExecution-2015-11-01.csv
554040 ApexSoap-2015-11-01.csv
443408 RestApi-2015-11-01.csv
276112 URI-2015-11-01.csv
Notes: Similar to wc -l, du helps with capacity planning and enables you to answer quick questions around total transactions for a file or event type.

Use case: simple report on size by file type (*.csv)
Example Prompt: du -hs *.csv | sort -n
Example Output:
1.4M ApexCallout-2015-11-01.csv
1.7M TimeBasedWorkflow-2015-11-01.csv
3.2M QueuedExecution-2015-11-01.csv
3.6M Logout-2015-11-01.csv
5.7M Report-2015-11-01.csv
Notes: Similar to wc -c, helps with capacity planning and to answer quick questions around total transactions for a file or event type.

Use case: merge multiple CSV files together into a new CSV
Example Prompt: Merge cat *.csv > new.csv
Notes: This is really helpful when merging multiple files of the same type (e.g. API) that span multiple days into a single CSV prior to loading to an analytics platform.

Use case: get all of the report export log lines for a specific user (e.g. '00530000000h51Z')
Example Prompt: grep -r '00530000000h51Z' 2015*/ReportExport*.csv
Example Output:
2015-10-13/ReportExport-2015-10-13.csv:"ReportExport","20151013205624.313","4-nPScfLnMsalbH5Tipnt-","00D000000000062","00530000000h51Z","","","","/00O30000008ZXB4","Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.101 Safari/537.36"
Notes: I <3 grep! Grep is basically a simple search, and as a result, is a good tool for finding a needle in the haystack of log lines. Grep is really helpful for providing a quick audit report for your auditors on everything a specific user did. It's also really powerful to combine this with other commands by using a pipe (i.e. '|'). For instance, by adding '| wc -l' to the end of the above command, you can get the total number of reports the user exported instead of the specific report log lines. This is similar to performing a count() in SOQL and filtering by a specific user Id. Now you're using the command line for reporting purposes!

Use case: get all of the events where an account was accessed
Example Prompt: grep 'EVENT_TYPE\|/001' URI*.csv | head -10
Example Output:
Notes: This is really helpful for providing a quick audit report for your auditors on account access. This is also another good prompt to add '| wc -l' to in order to find out how many times a specific account was accessed.

Use case: convert timestamp from a number to a string prior to importing to a reporting application
Example Prompt: awk -F ','  '{ if(NR==1) printf("%s\n",$0); else{ for(i=1;i<=NF;i++) { if(i>1&& i<=NF) printf("%s",","); if(i == 2) printf "\"%s-%s-%sT%s:%s:%sZ\"", substr($2,2,4),substr($2,6,2),substr($2,8,2),substr($2,10,2),substr($2,12,2),substr($2,14,2); else printf ("%s",$i);  if(i==NF) printf("\n")}}}' "${eventTypes[$i]}-raw/${eventTypes[$i]}-${logDates[$i]}.csv" > "${eventTypes[$i]}/${eventTypes[$i]}-${logDates[$i]}.csv"
Notes: this one takes a lot more work but is really helpful for transforming data before loading it into a system that has specific formatting requirements.

There are many more great utilities available on the command line, which when coupled with pipes (|) and shell scripts makes for an easy way to automate many simple tasks or perform ad-hoc queries against the raw log files.

And those are a few of my favorite things!

29 October 2015

LogDate vs CreatedDate - when to use one vs the other in an integration

Why are there two date-time fields for each Event Log File: LogDate and CreatedDate? Shouldn't one be good enough?

Seems like it should be a straightforward question, but it comes up frequently and the answer can effect how you integrate with Event Log Files.

Lets start with the definition of each:
  • LogDate tracks usage activity of a log file for a 24-hour period, from 12:00 a.m. to 11:59 p.m.
  • CreatedDate tracks when the log file was generated.
Why is this important? Why have these two different timestamps? Because having both ensure the reliability and eventual consistency of log delivery within a massively distributed system.

Each customer is co-located on a logical collection of servers we call a 'pod'. You can read more about pods and the multi-tenant architecture on the developer force blog.

There can be anywhere from ten to one hundred thousand customer organizations on a single pod. Each pod has numerous app servers which, at any given time, handle requests from any of those customer organizations. In such a large, distributed system, it's possible, though not frequently, for an app server to go down.

As a result, while a customer's transactions are being captured, if an app server does goes down, their transactions can be routed to another server seamlessly without affecting the end-user's experience or the integrity of the data. For a variety of reasons, app servers can go up or down throughout the day but what's important is that this activity doesn't affect the end-user's experience of the app or the integrity of the customer's data.

Each app server captures it's own log files throughout the day regardless of which customer's transactions are being handled by the server. Each log file therefore represents log entries for all customers who had transactions on that app server throughout the day. At the end of the day (~2am local server time), Salesforce ships the log files from active app servers to an HDFS where Hadoop jobs run. The Hadoop server generates the Event Log File content (~3am local server time) for each customer based on the app logs that were shipped earlier. This job is what generates Event Log File content which is accessible to the customer via the API.

It's possible that some log files will have to get shipped at a later date. This could have been from an app server that was offline during some part of the day that comes back on line after the log files are shipped for that day.  Therefore log files may be considered eventually consistent based on the log shipper or Hadoop job picking up a past file in a future job run. As a result, it's possible that Salesforce will catch up at a later point. We have built look back functionality to address this scenario. Every night when we run the Hadoop job, we check to see if new files exist for previous days and then re-generate new Event Log File content, overwriting the existing log files that were previously generated.

This is why we have both CreatedDate and LogDate fields - LogDate reflects the actual 24-hour period when the user activity occurred and CreatedDate reflects when the actual Event Log File was generated. So it's possible, due to look back functionality, that we will re-generate a previous LogDate's file and in the process, write more lines than we did on the previous day with the newly available app server files co-mingled with the original app server log files that were originally received.

This eventual consistency of log files may impact your integration with Event Log Files.

The easy way to integrate with Event Log Files is to use LogDate and write a daily query that simply asks for the last 'n' days of log files based on the LogDate:

Select LogFile from EventLogFile where LogDate=Last_n_Days:7

However, if you query on LogDate, it is possible to miss data that you might get from downloading it later. For instance, if you downloaded yesterday's log files and then re-download them tomorrow, you may actually have more log lines in the newer download. This is because some app log files may have caught up, overwriting the original log content with more log lines.

To ensure a more accurate query that also captures look back updates of the previous day's log files, you should use CreatedDate:

Select LogFile from EventLogFile where CreatedDate=Last_n_Days:7

This is a more complicated integration because you will have to keep track of the CreatedDate for each LogDate and EventType that was previously downloaded in the case that a CreatedDate is newer than a previously downloaded file. You may also need to handle event row de-duplication where you've already downloaded log lines from a previous download into an analytics tool like Splunk only to find additional log lines added in a subsequent download.

There's one option that simplifies this a little bit. You can overwrite past data every time you run your integration. This is what we do with some analytics apps that work with Event Log Files; the job automatically overwrites the last seven days worth of log data with each job rather than appending new data and de-duplicating older downloads.

This may seem antithetical but believe it or not, having look back is a really good thing because it increases the reliability and eventual consistency when working with logs to ensure you get all of the data you expect to be there.