Hit count detail

[XBensemhoun]

Hi,

I've written a script to deal with data presented in hit count SQLite database. I've posted in 'Logging, Monitoring, & Event Analysis > Discussions Actions' thread some details, I propose here the updated script.

#!/bin/sh
echo "###############"
echo "##"
echo "# SQLite3 script"
echo "# To show : TotalHits, First Match and LastMatch ; by firewall ; of a specified firewall rule_uid"
echo "#"
echo "# Usage :"
echo "#    [Expert@mySMS:0]# ./sqlite3_TotalHits_FirstMatch-and-LastMatch_byFirewall_of-a-rule_uid.sh {ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}"
echo "#"

#
# Changelog:
# AAAA-MM-JJ      AUTEUR                       DESCRIPTION
# ----------      ---------------------------  ---------------------------------------------
# 2017-12-29      Xavier Bensemhoun (Victrix)  Initial version
# 2017-01-14      Xavier Bensemhoun (Victrix)  Display results ordered by TotalHits
# 2018-02-09      Xavier Bensemhoun (Victrix)  Adding interaction
#
#
echo "#"

# A recent copy of the Production database (located in $FWDIR/conf/hit_count_rules_table.sqlite) should be available in /var directory
# And as the database could be huge (more than 2GB), we should have deleted all the previous ones
#

# Find an unique and recent copy of the Production database
# In some steps:
#  1) do we have more than one copy of the Production database ?
#   > if no copy : creation of a new one
#   > if multiple copies : deletion of the oldest copy(ies)
#  2) do we have recent (no more than 5 days) copy ? If not : deletion of old one and creation of a new one

# 1) How many copy ?
numberOfCopy=$(ls -t /var/hit_count_rules_table*sqlite | wc -l)
if [ $numberOfCopy = 0 ]
 then
  echo "There is no Database copy on /var ; creating a new one ..."
  cp $FWDIR/conf/hit_count_rules_table.sqlite /var/hit_count_rules_table.`/bin/date +%Y-%m-%d_%H%M`.sqlite
  latestCopy=$(ls -t /var/hit_count_rules_table*sqlite)
  echo "The new copy is : "$latestCopy
 else
  if [ $numberOfCopy \> 1 ]
   then
    echo "There is more than one copy ($numberOfCopy) of the database !"
    echo "... deletion of old ones ..."
    ls -t /var/hit_count_rules_table*sqlite | sed -e '1,1d' | xargs -d '\n' rm
    echo " ...done !"
#  else
#   echo "... only one copy exists"
  fi;
fi;
#  2) do we have recent (no more than 5 days) copy ?
oldestCopy=$(find /var/hit_count_rules_table*sqlite -mtime +5)
if [ $oldestCopy > 0 ]
then
 echo "The database copy is older than 5 days ! deletion of the old one ..."
 find /var/hit_count_rules_table*sqlite -mtime +5 -delete
 echo " done ! ... and creation of a new one ..."
 cp $FWDIR/conf/hit_count_rules_table.sqlite /var/hit_count_rules_table.`/bin/date +%Y-%m-%d_%H%M`.sqlite
 latestCopy=$(ls -t /var/hit_count_rules_table*sqlite)
 echo "The new copy is : "$latestCopy
else
 latestCopy=$(ls -t /var/hit_count_rules_table*sqlite)
# echo "... and the database is recent : "$latestCopy
fi;

####
# Check existence of input argument in a Bash shell script
if [ $# -eq 0 ]
  then
    echo -e "Thanks to paste the rule UID like : \e[1;34m{ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}\e[0m"
    read -n 38 -p 'Rule UID ? ' ruleUID
    echo -e "\nThanks, continuing..."
  else
   ruleUID=$1
fi

# Beginning of the SQlite request
echo "# for the rule_uid $ruleUID"
echo "#"
echo "TotalHits,Firewall,FirstMatch,LastMatch"
sqlite3 -csv $latestCopy "SELECT SUM(hits) as TotalHits, netobj_name, strftime('%Y-%m-%d', datetime(MIN(start_date), 'unixepoch')) as FirstMatch, strftime('%Y-%m-%d', datetime(MAX(end_date), 'unixepoch')) as LastMatch FROM HitCountRules WHERE rule_uid='$ruleUID' GROUP BY netobj_name ORDER BY TotalHits DESC;"
echo "#"
echo "# Based on the Database copy : "$latestCopy
echo "# End of the script"
echo "########"‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You'll be able to launch this script using the rule UID as unique argument.

Something like:

[Expert@mySMS:0]# ./sqlite3_TotalHits_FirstMatch-and-LastMatch_byFirewall_of-a-rule_uid.sh {ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}

The copy of the hit count database will be stored on /var (change if you need).

A new copy will be created if old one(s) are older than 5 days.

Be aware about the size of your actual database:

[Expert@mySMS:0]# ls -lah $FWDIR/conf/hit_count_rules_table.sqlite

Enjoy

[Tomer_Sole]

Looks cool, please note it won't work in R80 and above as the platform storage was changed.

However, the R80 (or R80.10) API returns all hit count data: first hit, last hit, level, percentage, and value. Check Point - Management API reference