High Risk Application Report by User !!

Forum|Forum|11 years ago
January 15, 2015
4 replies
7416 views

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

risk.jpg

Best answer by jb_kalm

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

jb_kalm

New Member

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

ihsanAuthor

New Member

Hi jb

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

jb_kalmAnswer

New Member

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

ihsanAuthor

New Member

thanks jb. This query worked.

Sign up

Login with SSO

Login to the community

Login with SSO