View Single Post
Old 02-22-2016, 06:23 PM   #1199
teejcee
Enthusiast
teejcee began at the beginning.
 
Posts: 48
Karma: 10
Join Date: Mar 2012
Location: Sydney, Australia
Device: Kindle 3G & Paperwhite
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
teejcee is offline   Reply With Quote