FAZ Dataset query tables, columns and values available

[Jambo]

Hi 

Can anyone tell me where I may find a document that describes the various tables, columns and values that are present in the various 'Log Type' tables underlying the datasets. For instance I am currently attempting to run a query looking at 'Traffic' for hosts potentially spamming on TCP25. 

I have attempted 

select *
from $log
where $filter and dstport=25

to try and show all columns but the wildcard is not accepted on 7.4.2

if I try 

select `srcip`, COUNT (DISTINCT `dstip`) AS unique_dst_ip_count
from $log
where $filter and dstport =25 and service=SMTP and dstintf=INTERNET and srcip!=10.1.1.1
GROUP BY
srcip
ORDER BY
unique_dst_ip_count DESC

I receive the error that "service=SMTP" does not exist.

Is there somewhere I can go to find out the structure of the underlying tables for the various Log Types which in the case is Traffic? 

Grateful for any advice

Thanks

[ozkanaltas]

Hello @Jambo ,

You can find the FortiAnalyzer database scheme from the support portal. Also, can you try like that? 

SELECT
  srcip,
  COUNT(DISTINCT dstip) AS unique_dst_ip_count
FROM
  $log
WHERE
  $filter
  AND dstport = 25
  AND service = 'SMTP'
  AND dstintf = 'INTERNET'
  AND srcip != '10.1.1.1'
GROUP BY
  srcip
ORDER BY
  unique_dst_ip_count DESC

Also, you can review these documents about dataset reference and SQL query.

https://docs.fortinet.com/document/fortianalyzer/7.4.3/dataset-reference/42/dataset-reference-list

https://docs.fortinet.com/document/fortianalyzer/7.4.0/sql-query-documentation/861740/introduction