A shell script that just emails the SQL execution result

As the title says. I wrote a little during the lunch break.

environment

Use PostgreSQL for Azure VM instance.

$ cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core)

$ psql -V
psql (PostgreSQL) 9.2.23

code

I really wanted to write it in Python, but with the current module configuration I had to play with the environment. Since it is a production system, I gave it up and created it with only shell scripts (bash) and standard commands.

bash


#!/bin/bash
YESTERDAY=`date --date '1 day ago' "+%Y%m%d"`
SQL_FILE=/tmp/dakoku.sql
MAIL_TEXT=/tmp/mail.txt

#SQL statement/Created in tmp
cat << EOL > $SQL_FILE
SELECT d.syaincd as employee number, e.syainmei as employee name, d.torokudt as stamped date and time,
CASE dakokusyubetucd
  WHEN '1' THEN 'Attendance'
  WHEN '2' THEN 'Leave work'
  ELSE 'unknown'
END as type
FROM dakoku_table d
LEFT JOIN employee_table e ON d.syaincd = e.syaincd
WHERE dakokuymd = '$YESTERDAY'
ORDER by d.torokudt, d.syaincd;
EOL

#Email header etc./Created in tmp
cat << EOL > $MAIL_TEXT
To: [email protected]
Cc: [email protected]
Subject: DAILY TIME RECORD ($YESTERDAY)

This email is automatically sent from the system.
We will inform you of the attendance time stamp log.

EOL

#Add SQL execution result to email body
su - postgres -c "psql KINTAI_DB < $SQL_FILE" >> $MAIL_TEXT

#Send email
/sbin/sendmail -t < $MAIL_TEXT

Email sent


This email is automatically sent from the system.
We will inform you of the attendance time stamp log.

employee number|Employee name|Stamping date and time|Type
------------+--------------+-------------------------+------
     539258 |Yamada Taro| 2020-07-17 07:47:05.314 |Attendance
     265167 |Suzuki Hanako| 2020-07-17 07:52:18.736 |Attendance
     329665 |Ichiro Tanaka| 2020-07-17 08:03:57.356 |Attendance

Register with root's crontab and automatically execute at 0:05 am. The Human Resources and General Affairs Department can now check for omissions in attendance on the previous day. It's a shameful code, but I hope it helps someone!

Recommended Posts

A shell script that just emails the SQL execution result
A script that just gets an RSS feed
A shell program that displays the Fibonacci sequence
How to pass the execution result of a shell command in a list in Python
A Python script that reads a SQL file, executes BigQuery and saves the csv
A shell script that puts Webmin into Alpine Linux
A shell script that numbers duplicate names when creating files
Access the file with a relative path from the execution script.
Creating a Python script that supports the e-Stat API (ver.2)
Let's try a shell script
Process the files in the folder in order with a shell script
[Note] A shell script that checks the CPU usage of a specific process in a while loop.
A program that just presses and releases the Esc key
A script that displays the running CloudFormation stack like a mannequin
How to pass the execution result of a shell command in a list in Python (non-blocking version)
A Python script that compares the contents of two directories
I wrote a script that splits the image in two
Process the contents of the file in order with a shell script
[sh] How to store the command execution result in a variable
Shell script (Linux, macOS) that outputs the date of the last week
Conditional branch due to the existence of a shell script file
Create a shell script to run the python file multiple times
Try to write a program that abuses the program and sends 100 emails
A script that returns 0, 1 attached to the first Python prime number
A shell script to make sure you don't forget the pipenv shell again
[Ubuntu] How to execute a shell script
Run the Python interpreter in a script
Manage logrotate generations with a shell script.
A script that morphologically parses a specified URL
Creating a shell script to write a diary
A code that corrects the yoon / sokuon (sokuon)
[Python] A program that rounds the score
Try to solve the traveling salesman problem with a genetic algorithm (execution result)
An article that just tries a little HTTP request with the curl command
A python script that draws a band diagram from the VASP output file EIGENVAL
In Blender, a script that just joins all objects directly under a particular group
I wrote a script to revive the gulp watch that will die soon