Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
ihsan
New Contributor II

High Risk Application Report by User !!

Hi 

 

i want to add user column on High Risk Application report. I tried to change related dataset but i get an error. Can you help me for this dataset? You can see the error in the appendix.

 

Orginal dataset 

 

 

My dataset and error

 

select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, appcat, count(distinct app) as total_num from ###(select appcat, app from $log where $filter and app is not null and appcat is not null and logid_to_int(logid) not in (4, 7, 14) and apprisk in ('critical', 'high') group by appcat, app)### t group by user_src, appcat order by total_num desc

 

Error 

ERROR:  column "user" does not exist
LINE 1: select * from (select coalesce(nullifna("user"), nullifna("u...

 

 

 

 

1 Solution
jb_kalm
Contributor

Hi Ihsan,

 

Here is my attempt :)

 

select user_src, appcat, app from ###(select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, appcat, app from $log where $filter and app is not null and appcat is not null and logid_to_int(logid) not in (4, 7, 14) and apprisk in ('critical', 'high') group by user_src,  appcat, app)### t group by user_src, app, appcat order by appcat

 

Thanks,

 

jb

View solution in original post

4 REPLIES 4
jb_kalm
Contributor

Hi Ihsan,

 

Please try this query and see if it gives you what you need.

 

select user_src, appcat, count(distinct app) as total_num from ###(select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, appcat, app from $log where $filter and app is not null and appcat is not null and logid_to_int(logid) not in (4, 7, 14) and apprisk in ('critical', 'high') group by user_src,  appcat, app)### t group by user_src, appcat order by total_num desc

 

Thanks,

 

Joel

ihsan
New Contributor II

Hi jb

 

Thanks for reply. I need one more information. I want to learn app name on this query.

jb_kalm
Contributor

Hi Ihsan,

 

Here is my attempt :)

 

select user_src, appcat, app from ###(select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, appcat, app from $log where $filter and app is not null and appcat is not null and logid_to_int(logid) not in (4, 7, 14) and apprisk in ('critical', 'high') group by user_src,  appcat, app)### t group by user_src, app, appcat order by appcat

 

Thanks,

 

jb

ihsan
New Contributor II

thanks jb. This query worked.

Labels
Top Kudoed Authors