Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
Jambo
New Contributor II

FAZ Dataset query tables, columns and values available

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

1 REPLY 1
ozkanaltas
Valued Contributor II

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

 

 

If you have found a solution, please like and accept it to make it easily accessible to others.
NSE 4-5-6-7 OT Sec - ENT FW
If you have found a solution, please like and accept it to make it easily accessible to others.NSE 4-5-6-7 OT Sec - ENT FW
Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors