Report Web Filter Overrides

[Beysel]

Hello everyone.

Our customer want to see all the web filter overrides, made last month.

Is there a way to get these in the Fortigate Reporting with username, date and url ?

I cant found any charts on the Fortianalyzer. In a Fortigate Guide i found out, that all the override events are logged under "Forward Traffic". Is there a chart to show the "Forward Traffic", filtered  by override events  ?

Thanks for any reply.

Beysel

[CrisP]

Hello,

I think you could check in FortiView, in the WebFilter logs, to see if the values of the log fields ovrdtbl and ovrdid are meaningful. Try searching something like

-ovrdtbl=""

ovrdid>0

I really don't know the actual content of these log fields, but they sound like having a link with the override events. After finding some values here, the other log fields will show you the user, date&time etc. In the end you should build a dataset based on your FortiView findings.

Good luck (but please keep us posted with your findings!)

[CrisP]

Sorry, the correct search to find non-null values is "-ovrdtbl=NULL"

[CrisP]

Actually, the search format "-<log_field>=NULL" may not work, it depends on the data type of the field. For instance, such a query does not work in $log-traffic for the array-type fields (like threats, threatcnts, threattyps). These fields are described in the database schema as text or integer arrays:

    "threats"            text[],     "threatlvls"         smallint[],     "threattyps"         text[],     "threatcnts"         smallint[],     "threatwgts"         int[] I'm not aware of any other way to explore such fields but by using SQL queries in custom datasets, where you can use the 'is null' or 'is not null' logical test.

https://dl.dropboxusercontent.com/u/33044717/threats.jpg

So, in case the ovrdtbl field would be an array, the FortiView couldn't help you to quickly explore the overrides logging issue. But the override fields are actually described in the database schema as

    "ovrdtbl"            varchar(128),     "ovrdid"             bigint

so -ovrdtbl=NULL should work in FortiView.

[CrisP]

In case you need to know the database schema (log tables field definitions), here is a method:

Replace "tlog" in the command "postgres=# \d+ "FGTADOMxxx-tlog-yyy" with the following

*alog* for IPS attack logs

*elog* for event logs

*rlog* for app-ctrl logs

*vlog* for virus logs

*wlog* for web filtering logs

Connect to the FAZ and

FAZ-3000E_1 # FAZ-3000E_1 # exec shell sh-4.3# su - postgres [FAZ-3000E_1/]$ [FAZ-3000E_1/]$ psql psql (9.3.4) Type "help" for help. postgres=# \d                                 List of relations  Schema |                       Name                        |   Type   |  Owner    --------+---------------------------------------------------+----------+----------  public | FAZADOM3-ALLELSE-elog-1458291600-0                | table    | postgres ...........  public | FGT-ELOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-NLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-TLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-aLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-cLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-dLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-eLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-nLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-pLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-rLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-sLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-tLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-vLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGT-wLOG-TABLE-TEMPLATE                           | table    | postgres  public | FGTADOM387-ALLELSE-alog-1463494020-0              | table    | postgres  public | FGTADOM387-ALLELSE-vlog-1463487630-0              | table    | postgres  public | FGTADOM387-FGHA000683465410_CID-rlog-1463503710-0 | table    | postgres  public | FGTADOM387-FGHA000683465410_CID-tlog-1463488500-0 | table    | postgres  public | FGTADOM387-FGHA000683465410_CID-wlog-1463476530-0 | table    | postgres  public | FGTADOM387-alog-1463494020                        | table    | postgres  public | FGTADOM387-elog-1463864760                        | table    | postgres  public | FGTADOM387-rlog-1463503710                        | table    | postgres  public | FGTADOM387-tlog-1463746140                        | table    | postgres  public | FGTADOM387-vlog-1463565240                        | table    | postgres  public | FGTADOM387-wlog-1463562930                        | table    | postgres ...  public | alert_logs                                        | table    | postgres  public | alert_logs_seq_num_seq                            | sequence | postgres  public | alerts                                            | table    | postgres  public | app_mdata                                         | table    | postgres  public | ips_mdata                                         | table    | postgres  public | log_tablst                                        | table    | postgres  public | maltarg                                           | table    | postgres  public | table_ref                                         | table    | postgres  public | table_ref_tbl_id_seq                              | sequence | postgres  public | vacuum_tablst                                     | table    | postgres (18401 rows) postgres=#                   postgres=# postgres=# \d+ "FGTADOM478-tlog-1465660950"                              Table "public.FGTADOM478-tlog-1465660950"        Column        |          Type           | Modifiers | Storage  | Stats target | Description ---------------------+-------------------------+-----------+----------+--------------+-------------  id                  | bigint                  | not null  | plain    |              |  itime               | integer                 | not null  | plain    |              |  dtime               | integer                 | not null  | plain    |              |  cluster_id          | character varying(24)   |           | extended |              |  ebtime              | smallint                |           | plain    |              |  threat              | character varying(512)  |           | extended |              |  threatlevel         | smallint                |           | plain    |              |  threattype          | character varying(256)  |           | extended |              |  utmref              | character varying(4096) |           | extended |              |  logver              | smallint                |           | plain    |              |  logid               | character varying(10)   |           | main     |              |  type                | character varying(16)   |           | plain    |              |  subtype             | character varying(20)   |           | plain    |              |  level               | character varying(11)   |           | plain    |              |  vd                  | character varying(32)   |           | main     |              |  devid               | character varying(16)   |           | plain    |              |  action              | character varying(16)   |           | plain    |              |  trandisp            | character varying(16)   |           | extended |              |  srcip               | inet                    |           | main     |              |  srcname             | character varying(66)   |           | extended |              |  srcport             | integer                 |           | plain    |              |  dstip               | inet                    |           | main     |              |  dstname             | character varying(66)   |           | extended |              |  dstport             | integer                 |           | plain    |              |  tranip              | inet                    |           | main     |              |  tranport            | integer                 |           | plain    |              |  service             | character varying(36)   |           | main     |              |  proto               | smallint                |           | plain    |              |  duration            | bigint                  |           | plain    |              |  policyid            | bigint                  |           | plain    |              |  sentbyte            | bigint                  |           | plain    |              |  rcvdbyte            | bigint                  |           | plain    |              |  sentpkt             | bigint                  |           | plain    |              |  rcvdpkt             | bigint                  |           | plain    |              |  vpn                 | character varying(32)   |           | extended |              |  srcintf             | character varying(32)   |           | extended |              |  dstintf             | character varying(32)   |           | extended |              |  sessionid           | bigint                  |           | plain    |              |  user                | character varying(256)  |           | main     |              |  group               | character varying(64)   |           | extended |              |  custom_field1       | character varying(64)   |           | extended |              |  wanoptapptype       | character varying(9)    |           | extended |              |  wanin               | bigint                  |           | plain    |              |  wanout              | bigint                  |           | plain    |              |  lanin               | bigint                  |           | plain    |              |  lanout              | bigint                  |           | plain    |              |  app                 | character varying(96)   |           | extended |              |  appcat              | character varying(64)   |           | extended |              |  shaperdropsentbyte  | bigint                  |           | plain    |              |  shaperdroprcvdbyte  | bigint                  |           | plain    |              |  shaperperipdropbyte | bigint                  |           | plain    |              |  shapersentname      | character varying(36)   |           | extended |              |  shaperrcvdname      | character varying(36)   |           | extended |              |  shaperperipname     | character varying(36)   |           | extended |              |  transip             | inet                    |           | main     |              |  transport           | integer                 |           | plain    |              |  dstcountry          | character varying(64)   |           | extended |              |  vpntype             | character varying(14)   |           | extended |              |  applist             | character varying(64)   |           | extended |              |  appact              | character varying(16)   |           | extended |              |  devtype             | character varying(32)   |           | extended |              |  osname              | character varying(66)   |           | extended |              |  osversion           | character varying(66)   |           | extended |              |  unauthuser          | character varying(66)   |           | extended |              |  unauthusersource    | character varying(66)   |           | extended |              |  mastersrcmac        | character varying(17)   |           | extended |              |  srcmac              | character varying(17)   |           | extended |              |  collectedemail      | character varying(66)   |           | extended |              |  appid               | bigint                  |           | plain    |              |  srccountry          | character varying(64)   |           | extended |              |  msg                 | character varying(64)   |           | extended |              |  utmaction           | character varying(32)   |           | main     |              |  crscore             | bigint                  |           | plain    |              |  craction            | bigint                  |           | plain    |              |  srcssid             | character varying(33)   |           | extended |              |  dstssid             | character varying(33)   |           | extended |              |  srcuuid             | uuid                    |           | plain    |              |  dstuuid             | uuid                    |           | plain    |              |  poluuid             | uuid                    |           | plain    |              |  apprisk             | character varying(16)   |           | extended |              |  countapp            | integer                 |           | plain    |              |  countav             | integer                 |           | plain    |              |  countdlp            | integer                 |           | plain    |              |  countemail          | integer                 |           | plain    |              |  countips            | integer                 |           | plain    |              |  countweb            | integer                 |           | plain    |              |  utmevent            | character varying(32)   |           | extended |              |  utmsubtype          | character varying(32)   |           | extended |              |  sender              | character varying(128)  |           | extended |              |  recipient           | character varying(512)  |           | extended |              |  virus               | character varying(512)  |           | extended |              |  attack              | character varying(512)  |           | extended |              |  hostname            | character varying(256)  |           | extended |              |  catdesc             | character varying(128)  |           | extended |              |  dlpsensor           | character varying(64)   |           | extended |              |  threats             | text[]                  |           | extended |              |  threatlvls          | smallint[]              |           | extended |              |  threattyps          | text[]                  |           | extended |              |  threatcnts          | smallint[]              |           | extended |              |  threatwgts          | integer[]               |           | extended |              |  ebtime2             | smallint                |           | plain    |              |  crlevel             | character varying(10)   |           | extended |              | Has OIDs: no Options: autovacuum_enabled=false, toast.autovacuum_enabled=false

[CrisP]

Last but not least:

It seems that we can kiss goodbye "exec shell" from 5.4 on...

Let's hope hzhao_FTNT shall keep close to us users!