Difference between revisions of "query rule hit counter db via command line"

From cpwiki.net
Jump to: navigation, search
Check Point Profressional Services
(Created page with " <syntaxhighlight lang="bash"> sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select datetime(start_date, "unixepoch") as time, datetime(end_date, "unixepoch") as time, net...")
 
(example queries R80+)
 
(16 intermediate revisions by one user not shown)
Line 1: Line 1:
  
<syntaxhighlight lang="bash">
+
==example queries R80+==
sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select datetime(start_date, "unixepoch") as time, datetime(end_date, "unixepoch") as time, netobj_name, rule_uid, hits from HitCountRules where netobj_name="PBROxxRCHVCP1" and rule_uid="{609C7EC8-82CA-4A58-BEB8-226626DBD3E3}"'
+
 
</syntaxhighlight>
+
the database was moved from the sqlite file to postgres
 +
 
 +
*** note **** case matters for the UID! all characters must be upper case it seems...
 +
 
 +
show  all hit count data for a specific rule uid
 +
# '''psql_client monitoring postgres -c "select hits,end_date from hitcount where rule_uid = '{0C8C26F9-7A52-4160-BB96-73AECEF13758}' limit 5"'''
 +
hits |      end_date     
 +
------+---------------------
 +
    4 | 2017-06-30 22:01:08
 +
  16 | 2017-07-01 22:01:06
 +
  16 | 2017-07-02 22:01:05
 +
  16 | 2017-07-03 22:01:03
 +
  16 | 2017-07-04 22:01:02
 +
 
 +
show
 +
# '''mgmt_cli show access-rulebase name "Network" -s id.txt package "Standard" show-hits true --format json | jq '.rulebase[] | {rule_number: ."rule-number",uid: .uid,hits: .hits.value}''''
 +
 
 +
  "rule_number": 1,
 +
  "uid": "1de8fab0-4858-4067-977d-1cbb5cd2e55d",
 +
  "hits": 0
 +
  <br>
 +
  "rule_number": 2,
 +
  "uid": "bbbfd8e6-72b6-4ff0-82ca-a9b0a6151d07",
 +
  "hits": 19
 +
  <br>
 +
  "rule_number": 3,
 +
  "uid": "bc69f34d-7ee8-47fe-a225-11b8e27e9a44",
 +
  "hits": 16617
 +
  <br>
 +
  "rule_number": 4,
 +
  "uid": "26373728-50df-49c3-b8d0-8895e350bc9f",
 +
  "hits": 1187628
 +
 
 +
==example queries R75.30==
 +
sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select datetime(start_date, "unixepoch") as time, datetime(end_date, "unixepoch") as time, netobj_name, rule_uid, hits from HitCountRules where rule_uid="{609C7EC8-82CA-4A58-BEB8-226626DBD3E3}"'
 +
 
 +
sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select datetime(start_date, "unixepoch") as time, datetime(end_date, "unixepoch") as time, netobj_name, rule_uid, hits from HitCountRules where netobj_name="myfirewall"'
 +
 
 +
hits per day for a firewall, within day range
 +
 
 +
  sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select date(start_date, "unixepoch") as day, SUM(hits) AS hits_total from HitCountRules where netobj_name="<my_firewall>" AND day between "2017-07-25" and "2017-09-14" GROUP by day'
 +
 
 +
 
 +
keywords: hit count, hitcount

Latest revision as of 18:35, 13 April 2018

example queries R80+

the database was moved from the sqlite file to postgres

      • note **** case matters for the UID! all characters must be upper case it seems...

show all hit count data for a specific rule uid

# psql_client monitoring postgres -c "select hits,end_date from hitcount where rule_uid = '{0C8C26F9-7A52-4160-BB96-73AECEF13758}' limit 5"
hits |      end_date       
------+---------------------
   4 | 2017-06-30 22:01:08
  16 | 2017-07-01 22:01:06
  16 | 2017-07-02 22:01:05
  16 | 2017-07-03 22:01:03
  16 | 2017-07-04 22:01:02

show

# mgmt_cli show access-rulebase name "Network" -s id.txt package "Standard" show-hits true --format json | jq '.rulebase[] | {rule_number: ."rule-number",uid: .uid,hits: .hits.value}'
 "rule_number": 1,
 "uid": "1de8fab0-4858-4067-977d-1cbb5cd2e55d",
 "hits": 0
 
"rule_number": 2, "uid": "bbbfd8e6-72b6-4ff0-82ca-a9b0a6151d07", "hits": 19
"rule_number": 3, "uid": "bc69f34d-7ee8-47fe-a225-11b8e27e9a44", "hits": 16617
"rule_number": 4, "uid": "26373728-50df-49c3-b8d0-8895e350bc9f", "hits": 1187628

example queries R75.30

sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select datetime(start_date, "unixepoch") as time, datetime(end_date, "unixepoch") as time, netobj_name, rule_uid, hits from HitCountRules where rule_uid="{609C7EC8-82CA-4A58-BEB8-226626DBD3E3}"'
sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select datetime(start_date, "unixepoch") as time, datetime(end_date, "unixepoch") as time, netobj_name, rule_uid, hits from HitCountRules where netobj_name="myfirewall"'

hits per day for a firewall, within day range

 sqlite3 $FWDIR/conf/hit_count_rules_table.sqlite 'select date(start_date, "unixepoch") as day, SUM(hits) AS hits_total from HitCountRules where netobj_name="<my_firewall>" AND day between "2017-07-25" and "2017-09-14" GROUP by day'


keywords: hit count, hitcount