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

How to join two logs on SQL?

Hi, so I need to do a query that shows the app, user and the kind of threat. But the app and the user are on the "Traffic" kind log and the things related to threats are in another kind of log.

So is it possible to join fields from one Log of certain kind to another of different kind?

And if it's possible, how could it be done?

 

Regards.

1 Solution
ck_FTNT
Staff
Staff

NON-JOIN combination:

 

SELECT     t.srcip, t.dstip, t.dstcountry, v.eventtype, v.ref FROM     $log-virus v, $log-traffic t WHERE     t.srcip=v.srcip     AND t.srcport=v.srcport     AND t.dstip=v.dstip     AND t.dstport=v.dstport

 

 

INNER JOIN:

 

SELECT     t.srcip, t.dstip, t.dstcountry, v.eventtype, v.ref FROM     $log-traffic t INNER JOIN $log-virus v ON     t.srcip=v.srcip WHERE     t.srcport=v.srcport AND t.dstip=v.dstip AND t.dstport=v.dstport

 

======

Log Types for reference (not all are used above)

 

Attack Log: $log-attack

Application Control: $log-app-ctrl

Content: $log-content

DLP: $log-dlp

Antispam: $log-emailfilter

Event log: $event

Fortimail History: $log-history

Traffic: $log-traffic

AntiVirus: $log-virus

VOIP: $log-voip

Web Filter: $log-filter

Vuln. Scan: $log-netscan

FCT Event: $fct-event

FCT Traffic: $fct-traffic

FCT Vulnerability: $fct-netscan

View solution in original post

1 REPLY 1
ck_FTNT
Staff
Staff

NON-JOIN combination:

 

SELECT     t.srcip, t.dstip, t.dstcountry, v.eventtype, v.ref FROM     $log-virus v, $log-traffic t WHERE     t.srcip=v.srcip     AND t.srcport=v.srcport     AND t.dstip=v.dstip     AND t.dstport=v.dstport

 

 

INNER JOIN:

 

SELECT     t.srcip, t.dstip, t.dstcountry, v.eventtype, v.ref FROM     $log-traffic t INNER JOIN $log-virus v ON     t.srcip=v.srcip WHERE     t.srcport=v.srcport AND t.dstip=v.dstip AND t.dstport=v.dstport

 

======

Log Types for reference (not all are used above)

 

Attack Log: $log-attack

Application Control: $log-app-ctrl

Content: $log-content

DLP: $log-dlp

Antispam: $log-emailfilter

Event log: $event

Fortimail History: $log-history

Traffic: $log-traffic

AntiVirus: $log-virus

VOIP: $log-voip

Web Filter: $log-filter

Vuln. Scan: $log-netscan

FCT Event: $fct-event

FCT Traffic: $fct-traffic

FCT Vulnerability: $fct-netscan