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
