replacing $filter with custom date or how to use $filter with more than one log source

[Daniel_Borgmann]

I created a new dataset to show the country for each logged in vpn-user. As the country is not stored in the vpn log I had to use the traffic log additionally. the problem is that I cannot use the $filter macro because I am getting an error:

ERROR:  column reference "itime" is ambiguous LINE 1: ...om "FGTADOM3-elog-0" a, "FGTADOM3-tlog-0" b where itime >= 1...

The dataset:

select b.srccountry, count (distinct coalesce(nullifna(`xauthuser`), `a`.`user`) ) as f_user from $log-event a, $log-traffic b where $filter and a.subtype='vpn' and (a.tunneltype='ipsec' or left(a.tunneltype, 3)='ssl') and coalesce(nullifna(`xauthuser`), nullifna(`a`.`user`)) is not null and a.action = 'tunnel-up' and a.remip = b.srcip group by b.srccountry

With a.$filter I am getting this error:

ERROR:  column reference "itime" is ambiguous LINE 1: ...GTADOM3-tlog-0" b where a.itime >= 1435906800 and itime <= 1...

Did anybody know how to solve this problem?

[hzhao_FTNT · Accepted solution]

Hi, there are some sql errors in your query. you have to join 2 tables on a.column1=b.column2 instead of directly selecting data. besides, you can not join between $log-*.

Please try below dataset:

drop table if exists t1; drop table if exists t2; create temporary table t1 as select distinct srcip, srccountry from $log-traffic where $filter and srcip is not null and srccountry is not null; create temporary table t2 as select remip, count(distinct coalesce(nullifna(`xauthuser`), `user`) ) as user_count from $log-event where $filter and subtype='vpn' and (tunneltype like 'ipsec%' or left(tunneltype, 3)='ssl') and coalesce(nullifna(`xauthuser`), nullifna(`user`)) is not null and action = 'tunnel-up' group by remip; select t1.srccountry, sum(t2.user_count) as user_count from t1 inner join t2 on t2.remip=t1.srcip group by srccountry order by user_count desc

regards,

hz

[Daniel_Borgmann]

hzhao_FTNT wrote:

Hi, there are some sql errors in your query. you have to join 2 tables on a.column1=b.column2 instead of directly selecting data. besides, you can not join between $log-*.

Please try below dataset:

drop table if exists t1; drop table if exists t2; create temporary table t1 as select distinct srcip, srccountry from $log-traffic where $filter and srcip is not null and srccountry is not null; create temporary table t2 as select remip, count(distinct coalesce(nullifna(`xauthuser`), `user`) ) as user_count from $log-event where $filter and subtype='vpn' and (tunneltype like 'ipsec%' or left(tunneltype, 3)='ssl') and coalesce(nullifna(`xauthuser`), nullifna(`user`)) is not null and action = 'tunnel-up' group by remip; select t1.srccountry, sum(t2.user_count) as user_count from t1 inner join t2 on t2.remip=t1.srcip group by srccountry order by user_count desc

regards,

hz

Thanks, that works perfect.

bye

Daniel