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

[laupin]

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 :)

[AtiT · Accepted solution]

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.

[chall_FTNT]

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. 

[laupin]

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).

[laupin]

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 · Accepted solution]

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.

[laupin]

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 · Accepted solution]

Does the dataset I wrote return some data?

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

[laupin]

Yes thanks, it works perfectly! ;)