Adding Extra Column from dataset query,

[thomsoni2]

Hello!

I'm starting to get my head aorund various aspects of FortiAnalyzer - What a great tool!

However, by my own admission, my SQL knowledge isn't great.

The below Query is working great for me across our estate where an "IP-CONN" error has been reported. What I can't quite figure out is how to expand that query so the report on the end (After being inserted in a chart) can include either the VDOM name, or the device name

    vd    VDOM               devname HOSTNAME

select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, service || '(' || ipstr(srcip) || ')' as interface, dstip, count(*) as total_num from $log where $filter and (logflag&1>0) and action = 'ip-conn' group by user_src, interface, dstip order by total_num desc

I've tried the below, but i'm making an assumption it will have to be referenced twice in the string. - Can anyone point me in the right direction ?

select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, service || '(' || ipstr(srcip) || ')' as interface, dstip, count(*) as total_num from $log where $filter and (logflag&1>0) and action = 'ip-conn' group by vd, user_src, interface, dstip order by total_num desc