topic Re: Hit count detail in Firewall and Security Management

Hit count detail

George_Ellis — Mon, 20 Nov 2017 20:41:34 GMT

Hit count data seems to suggest there is more to it than rule id and count. Has anyone found or built a method to do time series hit count report by rule?

Re: Hit count detail

PhoneBoy — Tue, 21 Nov 2017 00:11:37 GMT

Not sure I follow what you mean, can you clarify?

Do you mean hit count over time or something else?

Re: Hit count detail

XavierBens — Wed, 06 Dec 2017 20:55:37 GMT

Did you tried to look at the hit_count_rules_table.sqlite database ? It's located on $FWDIR/conf and you can search on it using SQLite client:

First of all, I suggest you to backup up it and to search on the copy :

cp $FWDIR/conf/hit_count_rules_table.sqlite /var/hit_count_rules_table.`/bin/date +%Y-%m-%d_%H%M`.sqlite

... be aware of the size ... depends but could be between 1 and 2 Go easily.

Then, open the database using SQLite utility:

[Expert@sli1017:0]# sqlite3 -line /var/hit_count_rules_table.2017-12-05_1657.sqlite
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

An entry is something like:

       NAME = A-ABC12345-ABC1-ABC1-ABC1-ABC123ABC12
    RawBlob = (A-ABC12345-ABC1-ABC1-ABC1-ABC123ABC12
        :AdminInfo (
                :chkpf_uid ("{A-ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}")
                :ClassName (HitCountRules)
                :table (HitCountRules)
                :Wiznum (-1)
                :LastModified (
                        :Time ("Wed Aug 16 21:27:22 2017")
                        :last_modified_utc (1502918842)
                        :By ("Security Management Server")
                        :From (localhost)
                )
                :icon (Unknown)
        )
        :end_date (1502908003)
        :hits (2)
        :netobj_name (<NAME OF YOUR SECURITY GATEWAY>)
        :netobj_uid ("{A-ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}")
        :policy_type (fw1)
        :rule_uid ("{ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}")
        :start_date (1502907970)
        :time_stamp (1502918832)
)

SessionId =
       UUID = {ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}
   end_date = 1502908003
       hits = 2
netobj_name = <NAME OF YOUR SECURITY GATEWAY>
netobj_uid = {ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}
policy_type = fw1
   rule_uid = {ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}
start_date = 1502907970
time_stamp = 1502918832

Important values are:

rule_uid : rule Unique ID
netobj_name : name of the firewall
start_date : first time of a hit for the rule_uid on the netobj_name
end_date : last hit for the rule_uid on the netobj_name
hits : how many hits for this rule_uid on the netobj_name

Then you can use any SQLite command type such as:

In order to search all matched value of an UID:

sqlite> select * FROM HitCountRules WHERE rule_uid='{ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}';

Global first and last hit of a specific rule:

sqlite> select MAX(end_date),MIN(start_date) FROM HitCountRules WHERE rule_uid='{ABC12345-ABC1-ABC1-ABC1-ABC123ABC12}';
MAX(end_date) = 1511200242
MIN(start_date) = 1492006088

Re: Hit count detail

XavierBens — Fri, 21 Jun 2019 09:03:31 GMT

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.

Re: Hit count detail

XavierBens — Fri, 21 Jun 2019 09:03:34 GMT

I've published the updated script and on the dedicated Code Hub thread : https://community.checkpoint.com/docs/DOC-2623

It will be maintain on this thread. Thanks

Re: Hit count detail

Tomer_Sole — Sun, 11 Feb 2018 08:22:30 GMT

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

Re: Hit count detail

JozkoMrkvicka — Fri, 31 Jan 2020 15:22:18 GMT

How can I get hit of last-date from specific rule ? This is what I get from rule and I would like to get posix or iso-8601 formatted date of last hit.

"hits" : {
"percentage" : "22%",
"level" : "medium",
"value" : 3809576,
"first-date" : {
"posix" : 1568109939000,
"iso-8601" : "2019-09-10T12:05+0200"
},
"last-date" : {
"posix" : 1580473988000,
"iso-8601" : "2020-01-31T13:33+0100"
}
},

I am using following command to get total value of hits, but I am unable to get the rest

mgmt_cli -r true -d "MY_Domain" show access-rulebase name "Network" details-level "standard" use-object-dictionary false show-hits true --format json |jq -r '.rulebase[]|select(.uid=="'"<UID>"'") | .hits.value'

Re: Hit count detail

JozkoMrkvicka — Mon, 03 Feb 2020 17:06:32 GMT

Got it:
mgmt_cli -r true -d "MY_Domain" show access-rulebase name "Network" details-level "standard" use-object-dictionary false show-hits true --format json |jq -r '.rulebase[]|select(.uid=="'"<UID>"'") | .hits."first-date".posix'