cancel
Showing results for 
Search instead for 
Did you mean: 
Create a Post
XBensemhoun
Silver

Hit count detail

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

1 Reply

Re: Hit count detail

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