High Risk Application Report by User !!

[ihsan]

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...

[jb_kalm]

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]

Hi jb

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

[jb_kalm · Accepted solution]

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]

thanks jb. This query worked.