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.

1 comment:

  1. I love this site and have absolutely bookmarked it. I’m going to return to learn to read in additional details on my trip It’s just not generally you find Video equipment rental in Orlando

    ReplyDelete