Fortinet black logo

Administration Guide

SQL query functions

SQL query functions

In addition to standard SQL queries, the following are some SQL functions specific to FortiAnalyzer. These are based on standard SQL functions.

root_domain(hostname)

The root domain of the FQDN. An example of using this function is:

select devid, root_domain(hostname) as website FROM $log WHERE'user'='USER01' GROUP BY devid, hostname ORDER BY hostname LIMIT 7

nullifna(expression)

This is the inverse operation of coalesce that you can use to filter out n/a values. This function takes an expression as an argument. The actual SQL syntax this is base on is select nullif(nullif(expression, 'N/A'), 'n/a').

In the following example, if the user is n/a, the source IP is returned, otherwise the username is returned.

select coalesce(nullifna('user'), nullifna('srcip')) as user_src, coalesce(nullifna(root_domain(hostname)),'unknown') as domain FROM $log WHERE dstport='80' GROUP BY user_src, domain ORDER BY user_src LIMIT 7

email_domain

email_user

email_domain returns the text after the @ symbol in an email address. email_user returns the text before the @ symbol in an email address. An example of using this function is:

select 'from' as source, email_user('from') as e_user, email_domain('from') as e_domain FROM $log LIMIT 5 OFFSET 10

from_dtime

from_itime

from_dtime(bigint) returns the device timestamp without time zone. from_itime(bigint) returns FortiAnalyzer’s timestamp without time zone. An example of using this function is:

select itime, from_itime(itime) as faz_local_time, dtime, from_dtime(dtime) as dev_local_time FROM $log LIMIT 3

SQL query functions

In addition to standard SQL queries, the following are some SQL functions specific to FortiAnalyzer. These are based on standard SQL functions.

root_domain(hostname)

The root domain of the FQDN. An example of using this function is:

select devid, root_domain(hostname) as website FROM $log WHERE'user'='USER01' GROUP BY devid, hostname ORDER BY hostname LIMIT 7

nullifna(expression)

This is the inverse operation of coalesce that you can use to filter out n/a values. This function takes an expression as an argument. The actual SQL syntax this is base on is select nullif(nullif(expression, 'N/A'), 'n/a').

In the following example, if the user is n/a, the source IP is returned, otherwise the username is returned.

select coalesce(nullifna('user'), nullifna('srcip')) as user_src, coalesce(nullifna(root_domain(hostname)),'unknown') as domain FROM $log WHERE dstport='80' GROUP BY user_src, domain ORDER BY user_src LIMIT 7

email_domain

email_user

email_domain returns the text after the @ symbol in an email address. email_user returns the text before the @ symbol in an email address. An example of using this function is:

select 'from' as source, email_user('from') as e_user, email_domain('from') as e_domain FROM $log LIMIT 5 OFFSET 10

from_dtime

from_itime

from_dtime(bigint) returns the device timestamp without time zone. from_itime(bigint) returns FortiAnalyzer’s timestamp without time zone. An example of using this function is:

select itime, from_itime(itime) as faz_local_time, dtime, from_dtime(dtime) as dev_local_time FROM $log LIMIT 3