query rule hit counter db via command line - Revision history

Nighthawk: /* example queries R80+ */

2018-04-13T18:35:52Z

‎example queries R80+

2018-04-13T18:35:40Z

‎example queries R80+

2018-03-27T14:14:53Z

2018-03-27T14:14:24Z

‎example queries R80+

2018-03-27T14:05:40Z

2017-09-14T16:21:05Z

2017-09-14T15:52:15Z

2017-09-14T15:52:08Z

2017-09-14T04:00:48Z

2017-08-23T16:31:14Z

@@ Line 4: / Line 4: @@
 the database was moved from the sqlite file to postgres
- *** note **** case matters for the UID! all characters must be upper case it seems...
+*** note **** case matters for the UID! all characters must be upper case it seems...
 show  all hit count data for a specific rule uid

@@ Line 3: / Line 3: @@
 the database was moved from the sqlite file to postgres
 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"'''

@@ Line 4: / Line 4: @@
 the database was moved from the sqlite file to postgres
 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"
+  # '''psql_client monitoring postgres -c "select hits,end_date from hitcount where rule_uid = '{0C8C26F9-7A52-4160-BB96-73AECEF13758}' limit 5"'''
   hits |      end_date
-------+---------------------
+ ------+---------------------
 | 2017-06-30 22:01:08
 | 2017-07-01 22:01:06
@@ Line 14: / Line 14: @@
 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}'
+  # '''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,

@@ Line 3: / Line 3: @@
 the database was moved from the sqlite file to postgres
- # psql_client monitoring postgres -c "select * from hitcount where rule_uid = '' limit 5"
+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}'

← Older revision		Revision as of 14:05, 27 March 2018
Line 1:		Line 1:

−	==example ~~query~~==	+	==example queries R80+==
		+
		+	the database was moved from the sqlite file to postgres
		+
		+	# psql_client monitoring postgres -c "select * from hitcount where rule_uid = '' limit 5"
		+
		+	# 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 rule_uid="{609C7EC8-82CA-4A58-BEB8-226626DBD3E3}"'

← Older revision		Revision as of 16:21, 14 September 2017
Line 4:		Line 4:

	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"'		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		keywords: hit count, hitcount

@@ Line 3: / Line 3: @@
   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"'
+ 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"'
 keywords: hit count, hitcount

@@ Line 1: / Line 1: @@
 ==example query==
-  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" and 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 rule_uid="{609C7EC8-82CA-4A58-BEB8-226626DBD3E3}"'
 keywords: hit count, hitcount