and....
For the date value, you can interpret them using this transformation:
strftime('%Y-%m-%d', datetime(MIN(start_date), 'unixepoch'))
You would maybe understand the part of every firewalls that matched that rule : select
netobj_name
column and add:
GROUP BY netobj_name
... at the end of the request.
The SQL request should be :
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='{ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}' GROUP BY netobj_name;
Or in CLI:
sqlite3 -csv <Name of your copy> "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='{ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}' GROUP BY netobj_name;"
Note that I've chosen to show result in csv mode.
Note : I've published the following script into Code Hub : https://community.checkpoint.com/docs/DOC-2623
... and for those who are interested, I've creating a script which verify if exists already only one recent copy of the database and then execute this SQL request:
#!/bin/sh
echo "###############"
echo "##"
echo "# SQLite3 script"
echo "# To show : TotalHits, First Match and LastMatch ; by firewall ; of a specified firewall rule_uid"
#
# HISTORIQUE:
# AAAA-MM-JJ AUTEUR DESCRIPTION
# ---------- --------------------------- ---------------------------------------------
# 2017-12-29 Xavier Bensemhoun (Victrix) Initial version
#
#
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 (approx 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;
# Begining of the SQlite request
echo " for the rule_uid $1"
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='$1' GROUP BY netobj_name;"
echo "#"
echo "# Based on the Database copy : "$latestCopy
echo "# End of the script"
echo "########"
Then, you just have to call this script such as:
./sqlite3_TotalHits_FirstMatch-and-LastMatch_byFirewall_of-a-rule_uid.sh {ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}
adding the rule Unique ID as the only one parameter
Do not hesitate to comment/share/improve.
Information Security enthusiast, CISSP, CCSP
