Ok....I've now had time to make progress with this little project. I've added
my code ( a mixture of yours & mine ) and inserted it in your original
post below. You'll need to make some changes given we are addressing
different apache2 logs. That's an area where I got caught out. I forgot
that you were accessing a different log file & it all kept going
pear-shaped until the little light globe went on
Plus I've created a minor problem by naming my main library
"Calibre Library" ; note the space. I could not get this working at
all until I realised this space was probably the culprit. It was just
a matter of creating a link to it and referencing the link in the
code. sqlite3 worked fine then. A lesson learned.
So just substitute your original code for mine if you're so inclined.
I've commented the areas.
Code:
#!/bin/bash
# email log entries of COPS users for previous 24 hours
DATE=$(date +%d-%b-%Y)
#LOGFILE=/var/log/apache2/other_vhosts_access.log
LOGFILE=/var/log/apache2/access.log
DATABASE[0]='/path/one/to/metadata.db'
DATABASE[1]='/path/two/to/metadata.db'
DATABASE[2]="/path/three/to/metadata.db"
DATABASE[3]='/path/four/to/metadata.db'
DATABASE[4]='/path/five/to/metadata.db'
grep -e "azw3"-e "epub" -e "mobi" $LOGFILE | grep -e "200" > book-log.txt
LOGFILE=book-log.txt
TMPFILE=tmp.$LOGFILE
rm -f $TMPFILE 2> /dev/null
###########################################
Your original code starts here....code above is pretty much common
###########################################
cat $LOGFILE 2> /dev/null | while read LINE
do
IP=$(echo $LINE | cut -f 1 -d' ')
USER=$(echo $LINE | cut -d ' ' -f 3)
TIME=$(echo $LINE | cut -f2 -d'[' | cut -f1 -d' ')
QSTR=$(echo $LINE | cut -f7 -d' ' | cut -f2 -d'?')
DATA=$(echo $QSTR | cut -f1 -d'&' | cut -f2 -d'=')
TYPE=$(echo $QSTR | cut -f2 -d'&' | cut -f2 -d'=')
ID=$(echo $QSTR | cut -f3 -d'&' | cut -f2 -d'=')
DB=$(echo $QSTR | cut -f4 -d'&' | cut -f2 -d'=')
DBASE=${DATABASE["$DB"]}
SQL="select title, author_sort from books where id="$ID" ;"
TITLE=$(sqlite3 $DBASE "$SQL")
# echo "$TIME $USER $IP $BOOK $DATA $TYPE $ID $DB $TITLE" >> $TMPFILE
echo "$TIME $USER $IP $TITLE" >> $TMPFILE
done
if [[ ! -s $TMPFILE ]]
then
echo "There is no data to report." >> $TMPFILE
fi
cat $TMPFILE | mail -s "Today's COPS users $DATE" -r "yourfrommailaddress@mail.com>" yourmail@mail.com
# END
####################################
Your original code ends here and mine starts below
####################################
cat $LOGFILE 2> /dev/null | while read LINE
do
IP=$(echo $LINE | cut -f2 -d' ')
USER=$(echo $LINE | cut -f4 -d' ')
TIME=$(echo $LINE | cut -f2 -d'[' | cut -f1 -d' ')
QSTR=$(echo $LINE | cut -f8 -d' ' | cut -f2 -d'?')
DATA=$(echo $QSTR | cut -f1 -d'&' | cut -f2 -d'=')
TYPE=$(echo $QSTR | cut -f2 -d'&' | cut -f2 -d'=')
ID=$(echo $QSTR | cut -f3 -d'&' | cut -f2 -d'=')
DB=$(echo $QSTR | cut -f4 -d'&' | cut -f2 -d'=')
# Look to get some more interesting information about the IP address.
unset CITY REGION COUNTRY POSTAL LOC
if [[ ${IP:0:7} == "192.168" ]]
then
CITY="---Internal IP address.----"
else
IPINFO=$(curl ipinfo.io/$IP 2> /dev/null)
CITY=$(echo ${IPINFO##*city\":} | cut -f2 -d'"')
REGION=$(echo ${IPINFO##*region\":} | cut -f2 -d'"')
COUNTRY=$(echo ${IPINFO##*country\":} | cut -f2 -d'"')
POSTAL=$(echo ${IPINFO##*postal\":} | cut -f2 -d'"')
LOC=$(echo ${IPINFO##*loc\":} | cut -f2 -d'"')
fi
echo "$DATE $USER $IP $CITY $REGION $COUNTRY $POSTAL $LOC" >> $TMPFILE
DBASE=${DATABASE["$DB"]}
SQL="select title, author_sort from books where id="$ID" ;"
TITLE=$(sqlite3 $DBASE "$SQL")
AUTHOR=$(echo $TITLE | cut -f2 -d"|")
TITLE=$(echo $TITLE | cut -f1 -d"|")
GIVEN=$(echo $AUTHOR | cut -f2 -d",")
SURNAME=$(echo $AUTHOR | cut -f1 -d",")
echo "$TITLE " " $GIVEN $SURNAME" >> $TMPFILE
echo " " >> $TMPFILE
done
if [[ ! -s $TMPFILE ]]
then
echo "There is no data to report." >> $TMPFILE
fi
cat $TMPFILE | mail -s "Today's COPS users $DATE" -r "yourfrommailaddress@mail.com>" yourmail@mail.com
# END
As you can see it's pretty much your code modified & added to
Quote:
helped a lot!
|
...... Yes...me too
Sample output...
23-Feb-2016 userA 192.168.1.1 ---Internal IP address.----
Title Author Given-name Surname
23-Feb-2016 userB 58.nnn.nn.nn Concord New South Wales AU 2137 -33.8429,151.1107
Title Author Given-name Surname
Yes...I know....GPS coordinates are a little over the top but it's
interesting that you can do this kind of stuff
Any questions, just yell
Cheers

TC