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

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?

5 Replies
Admin
Admin

Re: Hit count detail

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

Do you mean hit count over time or something else?

0 Kudos
Highlighted
XBensemhoun
Silver

Re: Hit count detail

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

XBensemhoun
Silver

Re: Hit count detail

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.

XBensemhoun
Silver

Re: Hit count detail

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

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