Hit count detail

[XavierBens]

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/shecho "###############"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 : "$latestCopyelse latestCopy=$(ls -t /var/hit_count_rules_table*sqlite)# echo "... and the database is recent : "$latestCopyfi;##### Check existence of input argument in a Bash shell scriptif [ $# -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=$1fi# Beginning of the SQlite requestecho "# 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 : "$latestCopyecho "# 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