Query log traffic via CLI

[rvogt]

Hi all

I've implemented various user defined FortiAnalyzer reports via the FortiAnalyzer webinterface. In some cases it would be a lot easier if it would be possible to directly query the database via CLI.

How can I do this? I tried it via the "execute sql-query-generic" command, however it seems the variable $log is not defined in the CLI:

  sv1 $ execute  sql-query-generic "SELECT count(*) from $log"   ERROR: syntax error at or near "$"   LINE 1: SELECT count(*) from $log

Which table (view) do I have to query for traffic log via CLI?

Thanks

Rene

[L_FTNT]

There is no plan to add query support to the CLI. However we will look into the API support for the query.

Could you please elaborate on what cases that you might need this kind of support?

Thanks,

L

[hzhao_FTNT]

Hi Rene,

You can not query logs directly from CLI, but you can run dataset under CLI.

For example, create a dataset "test" on GUI, then run sql-query-dataset under cli

 execute sql-query-dataset  <adom> <dataset-name> <device/group name> <faz/dev> <start-time> <end-time>

sample query:

FAZVM64 # exe sql-query-dataset root test All_FortiGates faz "2015-05-01 00:00:00" "2015-06-01 00:00:00"

Regards,

Huai Zhao

[rvogt]

hzhao_FTNT wrote:

You can not query logs directly from CLI, but you can run dataset under CLI.

For example, create a dataset "test" on GUI, then run sql-query-dataset under cli

 execute sql-query-dataset  <adom> <dataset-name> <device/group name> <faz/dev> <start-time> <end-time>

sample query:

FAZVM64 # exe sql-query-dataset root test All_FortiGates faz "2015-05-01 00:00:00" "2015-06-01 00:00:00"

Ok, thank you. I will try this next Monday.

[rvogt]

Hi

It is already possible to execute SQL commands via CLI ("execute sql-query-generic sql"). However the variable $log is not available in CLI. 

What do you mean by API support?

The problem is some of my user defined reports do not work on the FortiAnalyzer (Bugfix is pending). So I have to manually query some data from the database. This is a lot easier if it could be done via CLI...