How are dataset variables used?

[xinger]

As far as I can tell, there is no documentation about variables in datasets other than, "Select the add variable icon, , to add a variable, expression, and description information."  What happens after that?  Where is the variable used?  How is it used? 

My ultimate question is, can I create a custom query such as "select … from $log where $filter and sentbyte >= @MinimumBytes"?  Can I have a MinimumBytes variable which is threshold value configurable within a Chart or Report?  Sometimes I want to report on large file transfers (e.g., >= 1 MB) and sometimes I want to include smaller file transfers (e.g., >= 5 KB).  Do I need two different datasets or can I have one dataset which is filtered by a variable?

[hzhao_FTNT · Accepted solution]

Hi there, unfortunately we are not able to see user-defined variables on GUI. In addition, our report or chart filter only support "Equal To" and "Not Equal To". So you will need 2 different datasets in your case.

Regards,

hz

[hzhao_FTNT · Accepted solution]

Hi there, unfortunately we are not able to see user-defined variables on GUI. In addition, our report or chart filter only support "Equal To" and "Not Equal To". So you will need 2 different datasets in your case.

Regards,

hz

[diegodeosti · Accepted solution]

Hello

After several hours testing the Fortianalyzer, I discovered how the dataset variables works: It's used inside the variable $filter to replace the standard "where" clause generate by the fortianalyzer Example: Let's assume that I have the following dataset select coalesce(nullifna(`user`),ipstr('srcip')) as user_src, root_domain(hostname) as website_domain, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-webfilter where $filter group by user_src, website_domain In the dataset, I created the following variable: Variable: Group Expression: lower(coalesce(nullifna(`user`),'empty''))) Description: Users in Group In the report that I use this dataset, we want to filter users from a specific Active Directory group. So we need to go to the "Advanced Settings" tab in the report and add a filter "Group (group)" Equal to "Group from AD". In this tab, we also need to check the option "LDAP Query" and set the LDAP Server. The group "Group from AD" has the following users: john, david, peter Now when I run the report, the Fortianalyzer replaces the $filter for: select coalesce(nullifna(`user`),ipstr('srcip')) as user_src, root_domain(hostname) as website_domain, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-webfilter where lower(coalesce(nullifna(`user`), 'empty')) in ('john','david','peter')  group by user_src, website_domain When we remove this variable, the query change to: select coalesce(nullifna(`user`),ipstr('srcip')) as user_src, root_domain(hostname) as website_domain, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-webfilter where (`user`) in ('john','david','peter')  group by user_src, website_domain In this example, my Active Directory store the username in lower case, but the fortianalyzer record the username in uppercase. Since the Postgres is case sensitive, I used the funcion "lower()" (from Postgres), to change the case from the value stored in the "user" field. I am learning english, so I sorry about the grammar and other errors. Thanks Diego

[pcraponi]

Diego,

Fortianalyzer itself has a function to solve the upper/lower case issue using CLI:

http://kb.fortinet.com/kb/viewContent.do?externalId=FD31177

Regards,

Paulo Raponi