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

*** Fortianalyzer Combining in one chart two Datasets ***

Hello,

 

I need some help in order to create a custom report. I have an IDS profile and I want to repport the attacks in order to optimize the IPS profile that I'm configuring. The problem is that the data I need is in differents Log Type Database, so I create two datasets: Dataset with log type traffic (where I get srcip,srccountry, dstip,natIP) and Dataset with log type attack (where I get attack type). But i didn't find a way to relate both datasets and I don't have the attack type for a database log type traffic.

 

Does anyone have an idea how can I do that? Is there an SQL sequence I can do in order to have all this information into one Dataset?

 

I'm using the version 5.2.4 ( I'm planning an upgrade, but not for this week)

 

Thanks in advance :)

2 Solutions
AtiT
Valued Contributor

Hi,

I looked at your dataset and you are using 3 SELECTs but in this case only 1 SELECT is enough as all the informaction is in the Traffic log:

 

Create a Traffic dataset:

 

SELECT DISTINCT `srcip`, `srccountry`, `dstip`, `dstname`, `tranip`, `attack` FROM $log WHERE $filter AND NULLIFNA(`attack`) IS NOT NULL ORDER BY `srccountry`

 

You will get the same results and in my case more then 5 times faster.

AtiT

View solution in original post

AtiT
AtiT
Valued Contributor

Does the dataset I wrote return some data?

What more information you would like to see in the table?

AtiT

View solution in original post

AtiT
8 REPLIES 8
chall_FTNT
Staff
Staff

Creating a datasets which pulls data from 2 log types is quite complex and should generally only be considered for those quite comfortable with SQL.  It requires a UNION of 2 select statements. 

 

Also, in some cases, some queries involving a UNION can be quite computationally demanding on the FortiAnalyzer. 

Chris Hall
Fortinet Technical Support
laupin
New Contributor III

Thanks for your comments. I had created this request:

 

select distinct srcip, srccountry, dstip, dstname, tranip, attack from $log-traffic where srcip in (select srcip from $log-attack) and attack in (select  attack from $log-attack) and (policyid=10174 or policyid=116) order by srccountry

 

I almost have waht I want, but I also have a chart into the repport that shows me the Top 10 srcip attacks and there's some Ips into this list that aren't into my detailed one.

chall_FTNT

It sounds like you might want to change the value of "Only Show First"  (FAZ 5.2) in your chart.  A value of 0 is "unlimited" (to the maximum global setting which is 10,000).

Chris Hall
Fortinet Technical Support
laupin
New Contributor III

Yes I changed that, but In my repport I have both charts, the one who gave me the detailed information and the other that only shows me the top 10. The thing is that some times there's a mistmatch with the information in both. Let says, I have the ip x.x.x.x into the top ten and when I look into the detailed table I can't find that IP. That's why I think that it's missing data into my sql request.

AtiT
Valued Contributor

Hi,

I looked at your dataset and you are using 3 SELECTs but in this case only 1 SELECT is enough as all the informaction is in the Traffic log:

 

Create a Traffic dataset:

 

SELECT DISTINCT `srcip`, `srccountry`, `dstip`, `dstname`, `tranip`, `attack` FROM $log WHERE $filter AND NULLIFNA(`attack`) IS NOT NULL ORDER BY `srccountry`

 

You will get the same results and in my case more then 5 times faster.

AtiT

AtiT
laupin
New Contributor III

Firstly thanks for your reply. 

I had tried that at first, it was my first option since I have an attack column into the traffic log, but I get nothing. There's no attack information, and then when looked into the attack log, I founded it (same session ID) but this time there was data into this champ.

and then, I started to looked for a way to correlate both tables... :(

 

Until now, at least I get some information but it isn't all the information that is at FAZ

AtiT
Valued Contributor

Does the dataset I wrote return some data?

What more information you would like to see in the table?

AtiT

AtiT
laupin
New Contributor III

Yes thanks, it works perfectly! ;)

 

 

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors