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

Hit count detail

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?

7 Replies
Highlighted
Admin
Admin

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

Do you mean hit count over time or something else?

0 Kudos
Highlighted
Silver

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

Highlighted
Silver

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.

Highlighted
Silver

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

0 Kudos
Highlighted

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 

Highlighted
Platinum

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'

 

 

Kind regards,
Jozko Mrkvicka
Highlighted
Platinum

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'
Kind regards,
Jozko Mrkvicka
0 Kudos