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

Forum|Forum|10 years ago
July 10, 2015
1 reply
5608 views

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?

5.2

Best answer by hzhao_FTNT

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

hzhao_FTNTAnswer

Staff

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_BorgmannAuthor

New Member

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

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded