Creating Custem Datasets

[Matty28]

Hi all I busy setting reports for our Analyzer and for the reports i have had to create our own datasets. I am no Expert with PostgreSQL and i have had to do alot of reading but i have got 90% of the stuff working. So my hold up is creating fillers for multiple fields (I think) this is the query i have so far select dst, sum(sent+rcvd)/1048576 as volume from $log where (status=' accept' ) and (dst like ' 196.46.231%' ) or (dst like ' 41.66.157%' ) group by dst having sum(sent+rcvd)/1048576 >0 order by dst and is working but I need to filter by src_int as well but as soon as i add src_int=' 201_EOHNS' to it like select dst, sum(sent+rcvd)/1048576 as volume from $log where (status=' accept' ) and (src_int=' 201_EOHNS' ) and (dst like ' 196.46.231%' ) or (dst like ' 41.66.157%' ) group by dst having sum(sent+rcvd)/1048576 >0 order by dst stuff dose not work as i expect it to. So if anyone knows this stuff please help.

[AtiT]

Hi, I tried your dataset and it' s working. I just changed the IP addresses you use. What do you mean about " stuff dose not work as i expect it to. " ? What is the output? What should be the output? I recommend to write the fields between these characters: `` (ASCII code ALT+96). Something like: SELECT `dstip`, SUM(`sentbyte`+`rcvdbyte`) AS volume FROM $log WHERE `status`=' accept' AND `srcintf`=' port1' AND (`dstip` LIKE ' 8.8.8.%' OR `dstip` LIKE ' 193.86.13.%' ) GROUP BY `dstip` HAVING SUM(`sentbyte`+`rcvdbyte`)>0 ORDER BY `dstip` I' m running on version 5.0.2 so the filed names are a little bit different like V5 `dstip` is V4.3 `dst` etc. Differences between V5 and V4 can be found on the first page in the PDF document: http://docs.fortinet.com/fgt50.html - section Log Message Reference I see you are using sum(sent+rcvd)/1048576 - where you receive traffic in MB. If you will add this output in chart you don' t have to do it. You can leave only the sum(sent+rcvd) the chart will recalculate it into kB, MB and GB. Regards,

[Matty28]

Hi AtiT Thanks for the reply. When I say stuff dose not work as expected what was happening was that when i added the src_int=' 201_EOHNS' 1: The next like filter work not work. So on my above example the (dst like ' 196.46.231%' ) would not make in to the report and if swaped the order then the (dst like ' 41.66.157%' ) would not make it to to the report. 2: the numbers did not show that the src_int=' 201_EOHNS' filter was been applied. What does the ` (ASCII code ALT+96) do in the Query? I will have to test this but it going to be tricky as our local fortinet vender has got us to turn the SQL logging off so we can uses the old report layouts. Regards

[AtiT]

Hi Matty28, I always use ` (ASCII ALT+96) in datasets since version 4.X when I experienced problems - differences bettween datasets and reports. I had a problem when I received some data in dataset but it was not in report, only " NO DATA" . When I used the ` I saw the results in the report too. There was some article about it on the forum I think. So I always use it. About your dataset - it seems to me that there are problems with the brackets. For example: 1) expression_A AND expression_B OR expression_C is not the same as 2) expression_A AND (expression_B OR expression_C) The first means: (expression_A AND expression_B) OR expression_C I think that is the reason why you can' t get the right data. Could you try the same as I wrote in my dataset: AND (`dstip` LIKE ' 8.8.8.%' OR `dstip` LIKE ' 193.86.13.%' )

[Matty28]

Thank you I will give this a try.