01 December 2014

Working with Timestamps in Event Log Files

An event in a log file represents that something happened in our application along a timeline of events.

As a result, every Event Log File contains a TIMESTAMP field which represents the time each event happened in GMT. This is useful for understanding when the event happened, for correlating user behavior during a time period, and for trending similar events over various periods of time.

The TIMESTAMP field in Event Log Files is stored as a number. This simplifies storage costs since date formatting a string comes with a storage cost and can be more difficult to transform latter. This can become a challenge when importing Event Log File data into an analytics system that requires a different date time format. And there are a lot of different kinds of date time formats that are possible.

For instance, Salesforce Analytics Cloud's Wave platform accepts a variety of different date time formats:

This means that you will have to convert the TIMESTAMP field for each row within an Event Log File into something that Wave or any other analytics platform can interpret.

I usually convert the TIMESTAMP when I download the file, that way it makes it easier to do it in one step.

To convert it, I use a simple AWK script that Aakash Pradeep wrote, in my download script or in my Mac terminal. It takes the input from a downloaded file like Login.csv and creates a new file, substituting each TIMESTAMP field value with the right format:
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")}}}' Login.csv  > new_Login1.csv

Date time formats can be a challenge in any system and this utility provides me a quick and easy way of converting date time formats into something I can use with my analytics platform of choice.

No comments:

Post a Comment