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

Adding Extra Column from dataset query,

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

0 REPLIES 0