Send Log Files as Email Attachments on Data Virtuality using SQL
In this Data Virtuality tutorial, I want to share with data virtualization platform administrators and developers how they can use Data Virtuality functions and procedures from UTILS library like sendMail to share the server logs via email. If you have already completed SMTP configuration on your Data Virtuality instance, you can extend your solutions developed on the server by adding the functionality to share files via email.
To enable sending files via email with SQL code on Data Virtuality, following steps should be completed:
SMTP configuration on DV server,
Adding the folder where the files are defined as a new data source
SMTP configuration can be done on Window > Preferences menu option on Server Preferences > SMTP Configuration screen.
In this sample, my data virtualization platform is configured to use Amazon Simple Email Service aka Amazon SES service from AWS cloud platform provider.
As the following step, you can add the file folder where the files that you want to send via email should be added as a new File data source to Data Virtuality application.
File data source enables developers to add a directory path as the data source including its content.
So a single File data source can include multiple files.
On a Linux installation the log files are stored in the directory /opt/datavirtuality/dvserver/standalone/log/ so I added them as a new File data source as seen in following screenshot.
Data Virtuality SQL developers can execute following sample code to send email with the server log and boot log files in the attachments to a specific email address.
Let's look at the SQL code in detail.
Whenever the data virtualization platform administrator or developer adds a new data source of type File, it is possible to display the list of files using either getTextFiles or getFiles procedures.
Calling the native procedure getTextFiles() enables the SQL developers to access the files and its content to manipulate like applying string functions.
For example, if you look at the below SQL code, you will realize there are two attachments to this sample email.
One of the attachments is server.log file. On the other hand, since the server.log file is a big file it is not possible to send it via email.
So I had to take only the last 40000 characters from that file and attach it to the email for SMTP email.
Since the output of getTextFiles procedure is text, I could apply RIGHT() string function.
It is important to note that the attachments to the email should be binary. For this reason the content from server.log which I selected only the last n characters should be converted to binary format. This is managed by calling the TO_BYTES() function
For the second attached file boot.log, I fetched the file by calling getFiles() function which returns binary output. Since I have already the binary content and because I don't need to read and change its context by applying string SQL function.
exec "UTILS.sendMail"(
"Recipients" => 'datavirtuality@kodyaz.com',
"Subject" => 'Mail Subject',
"Body" => 'Mail Body',
"Attachments" => array(
(select to_bytes(right(file, 40000), 'UTF-8')
from (call "DV_Server_Log.getTextFiles"() ) a
where filePath like '%server.log')
,(select file
from (call "DV_Server_Log.getFiles"() ) a
where filePath like '%boot.log')
),
"AttachmentNames" => array(
'server.log'
,'boot.log'
),
"AttachmentMimeTypes" => array(
'application/text'
,'application/text'
)
) ;;
I hope this Data Virtuality tutorial which includes sample SQL code is useful for data virtualization platform users. To summarize the tutorial is showing how to attach server log files and send via email based on the SMTP configuration of the Data Virtuality server.