05 January 2015

Simple Script for Loading Event Monitoring Data into the Salesforce Wave

In the past few weeks, I've had a number of customer calls asking how we can make it easier to get Event Monitoring data into the Salesforce Wave. In a previous blog post:  Event Monitoring + Salesforce Wave = BFF, I talked about the best practices to ETL (extract, transform, and load) the data into Wave. This is helpful if you are an integration specialist who is used to building enterprise integrations with Salesforce. However, if you're just trying out Event Monitoring and Wave for the first time, you need something simple that gets you up and running as fast as possible.

To get customers up and running faster, I created a script that incorporated all of these best practices. You can download this script from my Github repository: elfWave.sh to give it a try. Alternatively, you can use this script to understand the data flow in case you create scripts using other languages or middleware integration tools.

There are a couple of gotchas that you should be aware of that are specific to this script and process:
  1. jq is a pre-requisite - I recommend downloading it into your /bin directory.
  2. datasetutils.jar is a pre-requisite for making it easy to upload your data into Wave. You can download the latest version from the Analytics cloud Github repo, or download version 32.0.0 that I used from my Github repo.
  3. Event Monitoring and Wave needs to be enabled in your org. The user downloading the Event Monitoring files needs 'View Event Log Files' user permission on their profile or permission set. And the user uploading files to Analytics Cloud needs 'Upload External Data to Analytics Cloud' user permission on their profile or permission set.
  4. When running the script, be sure you have the rights (e.g. chmod +x elfWave.sh) and if you don't save the script in your /bin directory, you start it properly by navigating to the directory where you saved the script (e.g. cd ~/Downloads) and invoke it (e.g. sh elfWave.sh or ./elfWave.sh).
  5. The script has been tested on Mac OSX and Ubuntu Linux but may need some additional configuration if you run it on Windows (e.g. install cygwin and curl).
Below is a breakdown of the more important parts of the script:

If you are not scheduling the script to run automatically, you should prompt the user for logins to both their Event Monitoring and Wave orgs. It's likely that these are the same orgs; however, I'm making the assumption that these may be two different orgs.
#prompt the user to enter their username for the target (Wave) org
read -p "Please enter the Wave target org username (and press ENTER): " tUsername

#prompt the user to enter their password for the target (Wave) org
read -s -p "Please enter the Wave target org password (and press ENTER): " tPassword

#prompt the user to enter their username for the source (Event Monitoring) org
read -p "Please enter username for Event Monitoring source org (and press ENTER): " username

#prompt the user to enter their password for the source (Event Monitoring) org
read -s -p "Please enter password for Event Monitoring source org (and press ENTER): " password

#prompt the user to enter their instance end-point for the source (Event Monitoring) org
echo 
read -p "Please enter instance (e.g. na1) for the for Event Monitoring source org loginURL (and press ENTER): " instance

#prompt the user to enter the date for the logs they want to download for the source (Event Monitoring) org
read -p "Please enter logdate (e.g. Yesterday, Last_Week, Last_n_Days:5) (and press ENTER): " day

You can choose whether to upload any single event type or all 28 types to Wave. You might want to try out just one event type (e.g. API or URI) to test it. However, if you don't choose anything, all 28 will be selected.
#prompt the user to enter the eventType they want to download for the source (Event Monitoring) orgs
printf 'What EventType do you want to download?\n'
printf '1. All 28 event types (Default)\n'
printf '2. API\n'
printf '3. ApexCallout\n'
printf '4. ApexExecution\n'
printf '5. ApexSoap\n'
printf '6. ApexTrigger\n'
printf '7. AsyncReportRun\n'
printf '8. BulkApi\n'
printf '9. ChangeSetOperation\n'
printf '10. ContentDistribution\n'
#….

read eventMenu

case $eventMenu in
     1)
          eventType=${eventType:-All}
          ;;
     2)
          eventType=${eventType:-API}
          ;;
     3)
          eventType=${eventType:-ApexCallout}
          ;; 
     4)
          eventType=${eventType:-ApexExecution}
          ;; 
     5)
          eventType=${eventType:-ApexSoap}
          ;;      
     6)
          eventType=${eventType:-ApexTrigger}
          ;; 
     7)
          eventType=${eventType:-AsyncReportRun}
          ;; 
     8)
          eventType=${eventType:-BulkApi}
          ;; 
     9)
          eventType=${eventType:-ChangeSetOperation}
          ;; 
     10)
          eventType=${eventType:-ContentDistribution}
          ;; 
     #…. 
     *)
          eventType=${eventType:-All}
          ;;
esac

echo ${eventType}

Download the files into the 'eventType-raw' directory, convert the TIMESTAMP from integer to date/time format, and store them in the eventType directory.
#loop through the array of results and download each file with the following naming convention: EventType.csv
for i in "${!ids[@]}"; do
    
    #make directory to store the files by date and separate out raw data from 
    #converted timezone data
    mkdir "${eventTypes[$i]}-raw"
    mkdir "${eventTypes[$i]}"

    #download files into the ${eventTypes[$i]}-raw directory
    curl "https://${instance}.salesforce.com/services/data/v32.0/sobjects/EventLogFile/${ids[$i]}/LogFile" -H "Authorization: Bearer ${access_token}" -H "X-PrettyPrint:1" -o "${eventTypes[$i]}-raw/${eventTypes[$i]}-${logDates[$i]}.csv" 

    #convert files into the ${eventTypes[$i]} directory for Salesforce Analytics
    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"

done

Because there may be 'n' number of days resulting in 'n' number of files, we merge the files into a single eventType file that will be uploaded into a single dataset with the eventType name. This will simplify the development of lenses and dashboards that trend events over a period of days.
#variable to count the number of unique event types
uEventTypes=( $(echo ${elfs} | jq -r ".records[].EventType" | uniq) )

#merge data into single CSV file
for j in "${uEventTypes[@]}"
do
    output_file="$j.csv"
    count=0

    for f in `ls $j/*.csv`
    do
        echo "still merging [$f]"
            
            echo "merging file: $f to $output_file."
            if [ $count -eq 0 ]; then

                    awk -F ',' '{print $0}' $f 1>$output_file
            else
                    awk -F ',' 'FNR>1 {print $0}' $f 1>>$output_file
            fi
            count=`expr $count + 1`
            echo "number of input files: $count merged to output file: $output_file"
    done
done

Using the datasetutils java app, upload the transformed, merged CSV files into Wave.
#load CSV files to datasets in Wave
for i in `ls *.csv`; do
    #variables to specify file and dataset name
    eventFile=`echo $i`
    eventName=`echo $i | sed 's/\.csv//g'`
    
    #comment next line to test before uploading to Wave
    java -jar datasetutils-32.0.0.jar --action load --u ${tUsername} --p ${tPassword} --inputFile ${eventFile} --dataset ${eventName}
done

Finally, because we may want to repeat this process on a regular basis (e.g. daily, weekly, monthly, etc…), choose whether to remove the files locally or keep them around for long-term storage and audit purposes.
#prompt user to clean up data and directories
read -p "Do you want to delete data directories and files? (Y/N)" del 

if [ $del == Y ] || [ $del == y ] || [ $del == Yes ] || [ $del == yes ]; then
    #clean up data directories
    for i in "${!uEventTypes[@]}"; do
        rm -r "${uEventTypes[$i]}-raw"
        rm -r "${uEventTypes[$i]}"
        rm "${uEventTypes[$i]}.csv"
    done
    rm -r "archive"
    #leave data and directories for audit reasons
    echo "The files were removed."
elif [ $del == N ] || [ $del == n ] || [ $del == No ] || [ $del == no ]; then
    echo "The files will remain."
fi

This script doesn't replace the need for an integration specialist to automate the ETL process; however, it should hopefully make it easier to trial both Event Monitoring and Wave.

43 comments:

  1. Business building is done in various ways with some failure and success. Building a sales business utilizing events is not only fun but rewarding to you and the people the attend. Events create a stronger database for you that results in a Fan Club of followers by allowing attendees to gain value from being part of what you do for them! EVENT LOGISTIC EXPERTS

    ReplyDelete
  2. Before you endeavor to offer a content, ensure it is cleaned to flawlessness. It ought not contain typographical mistakes or some other blemishes in its development. script doctor

    ReplyDelete
    Replies
    1. Solving of data related problems is not easy when you do not have the appropriate matter person who is expert in data handling concerns, so for getting the best data handling expert go to this excellent website go to this web-site activewizards.com/ and select the best data scientist for your data handling problem-solving.

      Delete
    2. Great Article
      Cloud Computing Projects


      Networking Projects

      Final Year Projects for CSE


      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  3. For instance, Uber hauls out information of its clients from different urban areas. By isolating a city into different pockets, its information experts play with the most astute of the thoughts.data science course in pune

    ReplyDelete
  4. Thank you so much for helping me out to find the Data science course in Mumbai Organisations and introducing reputed stalwarts in the industry dealing with data analyzing & assorting it in a structured and precise manner. Keep up the good work. Looking forward to view more from you.

    ReplyDelete
  5. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. I would like to state about something which creates curiosity in knowing more about it. It is a part of our daily routine life which we usually don`t notice in all the things which turns the dreams in to real experiences. Back from the ages, we have been growing and world is evolving at a pace lying on the shoulder of technology. data science certification will be a great piece added to the term technology. Cheer for more ideas & innovation which are part of evolution.

    ReplyDelete
  6. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    machine learning and artificial intelligence courses in bangalore

    ReplyDelete
  7. Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

    ReplyDelete
  8. Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained!
    Data Analytics Course in Mumbai

    ReplyDelete
  9. cool stuff you have and you keep overhaul every one of us
    Data science course in mumbai

    ReplyDelete
  10. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data science course in mumbai

    ReplyDelete
  11. Such a very useful Blog. Very interesting to read this article. I have learn some new information.thanks for sharing. know more about

    ReplyDelete
  12. I am impressed by the information that you have on this blog. It shows how well you understand this subject.
    ExcelR Business Analytics Course

    ReplyDelete
  13. Very nice blog here and thanks for post it.. Keep blogging...
    ExcelR data science training

    ReplyDelete
  14. Attend The Data Analytics Courses Online From ExcelR. Practical Data Analytics Courses Online Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses Online.
    ExcelR Data Analytics Courses Online

    ReplyDelete
  15. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in hyderabad

    ReplyDelete
  16. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    ExcelR data science course in mumbai

    ReplyDelete
  17. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own Blog Engine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it. excelr data science

    ReplyDelete
  18. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics courses

    ReplyDelete
  19. I am a new user of this site so here i saw multiple articles and posts posted by this blog,I curious more interest in some of them Data Scientist course

    ReplyDelete
  20. Business Analytics Certification Course Overview
    The Professional Certification in Business Analytics is a foundation course for students and professionals who want to develop niche data skills for their chosen industry domain or function area. Become a Business Intelligence and Data Visualisation expert and surge ahead in your career. The nine-day Business Analytics certification course covers all the essential Analytical and Statistical techniques for effective business decision making. This programme introduces the student to the basic concepts of Python language. https://360digitmg.com/business-analytics-training-in-hyderabad

    ReplyDelete
  21. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful.

    data analytics courses

    business analytics course

    data science interview questions

    data science course in mumbai

    ReplyDelete
  22. I would like to say that this blog really convinced me to do it! Thanks, very good post.
    ExcelR Data Analytics Course in Pune

    ReplyDelete
  23. This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environmen python training in vijayawada. , data scince training in vijayawada . , java training in vijayawada. ,

    ReplyDelete
  24. The information provided on the site is informative. Looking forward more such blogs. Thanks for sharing .
    Artificial Inteligence course in Nashik
    AI Course in Nashik

    ReplyDelete
  25. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!
    data science course
    360DigiTMG

    ReplyDelete
  26. Attend The Artificial Intelligence course From ExcelR. Practical Artificial Intelligence course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Artificial Intelligence course.
    Artificial Intelligence Course

    ReplyDelete
  27. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    Data Analytics Training in Hyderabad
    Business Analytics Course in Hyderabad

    ReplyDelete
  28. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  29. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course

    ReplyDelete
  30. On the off chance that individuals that compose articles thought progressively about composing incredible material like you, more perusers would peruse their substance. It's invigorating to discover such unique substance in an in any case duplicate feline world. Much obliged to you to such an extent.


    SEO services in kolkata
    Best SEO services in kolkata
    SEO company in kolkata
    Best SEO company in kolkata
    Top SEO company in kolkata
    Top SEO services in kolkata
    SEO services in India
    SEO copmany in India

    ReplyDelete
  31. Your amazing insightful information entails much to me and especially to my peers. Thanks a ton; from all of us. ExcelR Machine Learning Course In Pune

    ReplyDelete
  32. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  33. Very nice blog. I am really very happy to visit your blog. Now I am found which I actually want. I check your blog everyday and try to learn something from your blog. Thank you and waiting for your new post. 360DigiTMG data science course in aurangabad

    ReplyDelete
  34. Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

    Data Science In Banglore With Placements
    Data Science Course In Bangalore
    Data Science Training In Bangalore
    Best Data Science Courses In Bangalore
    Data Science Institute In Bangalore

    Thank you..

    ReplyDelete
  35. This is my first time visit here. From the tons of comments ExcelR Machine Learning Courses on your articles.I guess I am not only one having all the enjoyment right here!

    ReplyDelete
  36. Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.
    event

    ReplyDelete
  37. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  38. Attend The Data Analytics Courses From ExcelR. Practical Data Analytics Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses.
    Data Analytics Courses

    ReplyDelete