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

[kallbrandt]

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

[hzhao_FTNT · Accepted solution]

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!