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

Help build report - new to SQL logging

So we are upgrading all of our fortigates to 100D and our fortianalyzer is new too. We want to move to version 5 and we have been using indexer reports. The new OS only uses SQL reporting but the analyzer only has one sample report template. We need to be able to run a report on a srcip to capture all of their webtraffic for the past 30-60 days. With an indexer report we just created a filter to match the users ip address and the traffic was not " referral" to get it. How do I set up a filter for one specific ip? How do I create a report template to capture all of that ip addresses traffic? Thanks! Rick
45 REPLIES 45
RH2
New Contributor II

Thanks, I did add the column and it' s working well now. I added the direct only as the referrals added hundreds of lines to the report and the FAZ has a bug limiting the number of rows returned in reports.
Adrian_James

It seems something has changed with postgres under FA5.2. You can' t use coalesce with different data types any more so you need to cast srcip to text. This: coalesce(nullifna(`user`), `srcip`) needs to be changed to: coalesce(nullifna(`user`), text(`srcip`))
AtiT
Valued Contributor

Hi, It' s true. In some earlier post hzhao_FTNT said that from FAZ5.0.7, ip datatype was changed to inet. https://forum.fortinet.com/FindPost/115067

AtiT

AtiT
Jond
New Contributor III

And... how would I export that data to process it elsewhere? Thanks :)
RH2
New Contributor II

It works!!!!! Now, what do I need to do to exclude referral requests in the report? I only want to show direct requests not the addresses that show up because the page requested a link to another site.
Adrian_James

The only way the SQL query can help with that is if there is a table field that you can use to limit the data. I' m not sure that the Fortigates even differentiate real clicks from page requests.
Labels
Top Kudoed Authors