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
Adrian_James

I think this is the problem: AND `type`=' webfilter' because in this log, the type is utm and subtype is webfilter. If you have selected the right log to query against, then you don' t need this at all because $log will be the webfilter log. In any case, I think what you are looking for is the to_timestamp function: select to_timestamp(itime) from $log limit 1 This will convert the itime field to a human readable timestamp, but in UTC time. If you want it in local time, use this instead: select to_timestamp(itime)::timestamp with time zone from $log limit 1 I am working on the same problem (getting an audit report for one user to HR) so if I get it working I will post the full query.
Adrian_James

Here is the query I used in the dataset:
select coalesce(nullifna(`user`), `srcip`) as user,to_timestamp(`itime`)::timestamp with time zone as time,`hostname`||`url` as url,`catdesc` from $log where $filter
I added this dataset to a chart and set the max rows to something like 1000000 (you need a value). When you add the chart to a report, you can specify the user in the " filter" section.
RH2
New Contributor II

Great! I got data! My test button returned user data. Now I' m just testing a report. Unfortunately my fortianalyzer is having issues running reports right now. Hopefully support will figure it out soon.
RH2
New Contributor II

They do, the field is reqtype, I tried picking a filter in the dataset, but reqtype is not one of the options. If I look at the raw web log it shows: reqtype=referral or reqtype=direct
Rafael_Rosseto

Guys, I need build a report with each websites visited with time. Cheers for any help.
Rafael_Rosseto

Found answser above.
Jond
New Contributor III

Thank you for that SQL line - that' s just what I needed. One question ... how can I present it in the correct time order? It seems like it gets to 17:12 then onto 08:14 - can' t understand why.
AtiT
Valued Contributor

Hi Jond, Set the time to " ascending" like this: ORDER BY time ASC SELECT COALESCE(NULLIFNA(`user`), `srcip`) AS user_name, to_timestamp(`itime`)::timestamp AS time, `hostname`||`url` AS url_addr, `catdesc` FROM $log WHERE $filter AND `status`=' passthrough' AND NULLIFNA(`hostname`) IS NOT NULL GROUP BY user_name, time, url_addr, `catdesc` ORDER BY time ASC To export the data you can create a report - to generate a PDF document.

AtiT

AtiT
RH2
New Contributor II

It appears that this is working to only return the reqtype=direct not any referrals, which show up because of automatic links on a page. select coalesce(nullifna(`user`), `srcip`) as user,to_timestamp(`itime`)::timestamp with time zone as time,`hostname`||`url` as url, `reqtype`, `catdesc` from $log where $filter AND reqtype=' direct' ORDER BY time ASC
AtiT
Valued Contributor

Hi, It works as it should be. Put the reqtype into the dataset:

AtiT

AtiT
Labels
Top Kudoed Authors