Fortinet white logo
Fortinet docs logo DOCUMENT LIBRARY
Fortinet white logo
Fortinet docs logo DOCUMENT LIBRARY
Products Best Practices Hardware Guides Products A-Z
Summary
By Solution
By 4D Pillars
By Cloud
Secure Networking
Unified SASE
Security Operations
Secure SD-WAN
Secure Access Service Edge (SASE)
ZTNA
LAN Edge
Identity and Access Management
Next Generation Firewall
Public Cloud
Private Cloud
FortiCloud
Secure Networking
  • Hybrid Mesh Firewall
  • NOC Management
  • LAN
  • WAN
    • More >>
    Unified SASE
  • Single Vendor SASE
  • Cloud Network Security
  • Secure Endpoint Connectivity
  • Web Application / API Protection
    • More >>
    Security Operations
  • Security Operations Automation
  • Identity
  • Early Detection & Prevention
    • More >>
    Secure Networking
  • Hybrid Mesh Firewall
  • NOC Management
  • LAN
  • WAN
  • Communication & Surveillance
    • Unified SASE
  • Single Vendor SASE
  • Secure Endpoint Connectivity
  • Cloud Network Security
  • Cloud-Native Security
  • Web Application / API Protection
    • Security Operations
  • Security Operations Automation
  • Endpoint
  • Data Protection
  • Identity
  • Email
  • Early Detection & Prevention
  • Expert Services
  • Edge Firewall
  • Orchestration & management
  • SD Branch
  • Application Delivery
  • Single Vendor SASE
  • Secure Endpoint Connectivity
  • Secure Private Access
  • Thin Edge
  • Identity
  • Application Gateway
  • Enterprise Asset Management
  • Endpoint Agent
  • Agentless Security Posture
  • Identity
  • Wireless
  • Switching
  • Identity
  • Privilege Acccess Management
  • Next Generation Firewall
  • Orchestration & management
  • Expert Services
  • All
  • All
  • Account Management
  • SAAS Management
  • SAAS Application Security
  • Managed Services
  • Platform as a service (PAAS)
  • Other SAAS Services
    • 4D Resources
    Solution Hubs
  • 4D Pillars
  • Cloud
  • Popular Solutions

    • Administration Guide

    Home FortiAnalyzer 7.0.12 Administration Guide
    7.0.12
    7.6.1
    7.6.0
    7.4.5
    7.4.4
    7.4.3
    7.4.2
    7.4.1
    7.4.0
    7.2.8
    7.2.7
    7.2.6
    7.2.5
    7.2.4
    7.2.3
    7.2.2
    7.2.1
    7.2.0
    7.0.13
    7.0.12
    7.0.11
    7.0.10
    7.0.9
    7.0.8
    7.0.7
    7.0.6
    7.0.5
    7.0.4
    7.0.3
    7.0.2
    7.0.1
    7.0.0
    6.4.15
    6.4.14
    6.4.13
    6.4.12
    6.4.11
    6.4.10
    6.4.9
    6.4.8
    6.4.7
    6.4.6
    6.4.5
    6.4.4
    6.4.3
    6.4.2
    6.4.1
    6.4.0
    6.2.13
    6.2.12
    6.2.11
    6.2.10
    6.2.9
    6.2.8
    6.2.7
    6.2.6
    6.2.5
    6.2.3
    6.2.2
    6.2.1
    6.2.0
    6.0.12
    6.0.11
    6.0.10
    6.0.9
    6.0.8
    6.0.7
    6.0.6
    6.0.5
    6.0.4
    6.0.3
    6.0.2
    6.0.1
    6.0.0
    5.6.11
    5.6.10
    5.6.10
    5.6.9
    5.6.8
    5.6.7
    5.6.6
    5.6.5
    5.6.4
    5.6.3
    5.6.2
    5.6.1
    5.6.0
    5.4.7
    5.4.6
    5.4.5
    5.4.4
    5.4.3
    5.4.2
    5.4.1
    5.4.0
    5.2.10
    5.2.9
    5.2.7
    5.2.6
    5.2.5
    5.2.4
    5.2.3
    5.2.2
    5.2.1
    5.2.0
    5.0.13
    5.0.11
    5.0.10
    5.0.9
    5.0.8
    5.0.7
    5.0.6
    5.0.5
    5.0.4
    5.0.3
    5.0.2
    4.3.0
    4.2.0
    4.1.0
    4.0.0

    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

    get_devtype()

    Returns the source device type. An example of using this function is:

    select get_devtype(srcswversion, osname, devtype) as devtype_new, coalesce(nullifna(`srcname`),nullifna(`srcmac`), ipstr(`srcip`)) as dev_src, sum(crscore%65536) as scores from $log where $filter and (logflag&1>0) and crscore is not null group by devtype_new, dev_src having sum(crscore%65536)>0 order by scores desc

    This function may return null values. To replace null values with "Unknown", you can add the following outer query:

    select coalesce(nullifna(`devtype_new`), 'Unknown') as devtype_new1,dev_src, scores

    from ###(select get_devtype(srcswversion, osname, devtype) as devtype_new, coalesce(nullifna(`srcname`),nullifna(`srcmac`), ipstr(`srcip`)) as dev_src, sum(crscore%65536) as scores from $log where $filter and (logflag&1>0) and crscore is not null group by devtype_new, dev_src having sum(crscore%65536)>0 order by scores desc )### t
    Previous
    Next

    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

    get_devtype()

    Returns the source device type. An example of using this function is:

    select get_devtype(srcswversion, osname, devtype) as devtype_new, coalesce(nullifna(`srcname`),nullifna(`srcmac`), ipstr(`srcip`)) as dev_src, sum(crscore%65536) as scores from $log where $filter and (logflag&1>0) and crscore is not null group by devtype_new, dev_src having sum(crscore%65536)>0 order by scores desc

    This function may return null values. To replace null values with "Unknown", you can add the following outer query:

    select coalesce(nullifna(`devtype_new`), 'Unknown') as devtype_new1,dev_src, scores

    from ###(select get_devtype(srcswversion, osname, devtype) as devtype_new, coalesce(nullifna(`srcname`),nullifna(`srcmac`), ipstr(`srcip`)) as dev_src, sum(crscore%65536) as scores from $log where $filter and (logflag&1>0) and crscore is not null group by devtype_new, dev_src having sum(crscore%65536)>0 order by scores desc )### t
    Previous
    Next