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

Custom "threat-Top-Virus-Source"-dataset with added srccountry

Hello,

We have run into a problem with a custom dataset, and we don't know how to solve it. It would be great if someone with good query-skills could have a look at it.

 

A customer wants a more detailed version of the "threat-Top-Virus-Source"-dataset, where srccountry also should be listed. We suspect that the problem is that "srccountry" only exists in the DB "log-traffic", not in "log-virus", but we are pretty clueless about what to do right now...

 

The original dataset "threat-Top-Virus-Source":

[code lang=sql]select srcip, hostname, sum(totalnum) as totalnum from (###(select srcip, hostname, count(*) as totalnum from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent is not null and virus is not null group by srcip, hostname order by totalnum desc)### union all ###(select srcip , ipstr(`dstip`) as hostname, count(*) as totalnum from $log-virus where $filter and (eventtype is null or logver>=52) and nullifna(virus) is not null group by srcip, hostname order by totalnum desc)###) t group by srcip, hostname order by totalnum desc

 

Our modified dataset that fail to list srccountry:

[code lang=sql] select srcip, land, hostname, sum(totalnum) as totalnum from (###(select srcip, srccountry as land, hostname, count(*) as totalnum from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent is not null and virus is not null group by srcip, land, hostname order by totalnum desc)### union all ###(select srcip
, filename, ipstr(`dstip`) as hostname, count(*) as totalnum from $log-virus where $filter and (eventtype is null or logver>=52) and nullifna(virus) is not null group by srcip, filename, hostname order by totalnum desc)###) t group by srcip, land, hostname order by totalnum desc

Richie

NSE7

Richie NSE7
1 Solution
hzhao_FTNT
Staff
Staff

Hi there,

 

For 5.0+ FOS, a simple query could do it:

Log type: traffic

select srcip, srccountry, coalesce(hostname, ipstr(dstip)) as domain, count(*) as total_num from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and virus is not null group by srcip, srccountry, domain order by total_num desc

 

Regards,

hz

View solution in original post

2 REPLIES 2
hzhao_FTNT
Staff
Staff

Hi there,

 

For 5.0+ FOS, a simple query could do it:

Log type: traffic

select srcip, srccountry, coalesce(hostname, ipstr(dstip)) as domain, count(*) as total_num from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and virus is not null group by srcip, srccountry, domain order by total_num desc

 

Regards,

hz

kallbrandt

Cool, that works!

 

Thank you very much!

Richie

NSE7

Richie NSE7
Labels
Top Kudoed Authors