Skip to main content
theG
theG
New Member
January 25, 2014
Solved

Help with custom report

  • January 25, 2014
  • 44 replies
  • 33042 views
Hi Guys... SoI' ve gone through the standard charts / datasets and I can' t seem to find one that will give me browsing history. I' m running Fortianalyzer 100c with v5.0.5 I need to be able to check on browsing history for specific users. What websites and when...can anybody help me with this please?? thanks
    Best answer by hzhao_FTNT

    Hi there,

     

    `status` field was changed to `action` after FAZ 5.0.7.

    For FOS 5.0 log, please query traffic log use utmaction!='blocked' for allowed traffic

    For FOS 5.2&4.3 log, please query webfilter log and use action!='blocked' for allowed traffic

    To generate a report based on the blocked URL's and source user, please try:

     

    select hostname, user_src, sum(requests) as requests from (###(select hostname, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter') and hostname is not null and utmaction='blocked' group by hostname, user_src order by requests desc)### union all ###(select hostname, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log-webfilter where $filter and (eventtype is null or logver = 52) and action='blocked' group by hostname, user_src order by requests desc)###) t group by hostname, user_src order by requests desc

     

    This dataset should work for 4.3,5.0&5.2 log

     

    Regards,

    hz

    44 replies

    mnantel_FTNT
    Staff
    mnantel_FTNT
    Staff
    February 20, 2014
    Hi theG, First step, upgrade to 5.0.6! Would you be able to clarify what you mean by browsing history? I can give you a dataset that will extract every single consulted website but this will prove to be a rather long report. We have to impose a limit to the number of records returned, lest you end up with a 1GB PDF file report. Just clarify what you would like the report to look like and I' ll be happy to help out. Specifically, let me know which columns you need and what data do you want aggregated. An example would be to get columns=username,srcip,hostname,category with an aggregate bandwidth listed, and sorted by bandwidth in reverse order. Or I can give you a dataset with the timestamp, in which case you will get one line for each visit. Cheers, Mat
    Fullmoon
    Fullmoon
    New Member
    February 20, 2014
    ORIGINAL: An example would be to get columns=username,srcip,hostname,category with an aggregate bandwidth listed, and sorted by bandwidth in reverse order. ch visit.
    hi mnantel, sorry to hijack this post,but i have similar problem with this. Im using FAZ200D using ver 5.0.6. can you help me to have a template something like this, in single drill down I can have these reports in pdf format " columns=username,columns=srcip,columns=hostname of computer,columns=category/websites columns=corresponding bandwidth consumed"
    AtiT
    AtiT
    New Member
    February 20, 2014
    Hi, For the beginning you can try the dataset (found in the earlier post): SELECT COALESCE(NULLIFNA(`user`), `srcip`) AS user, TO_TIMESTAMP(`itime`)::timestamp AS time, `hostname`||`url` AS url, `catdesc` FROM $log Use it for Webfilter logs and start with the Last N Hours = 1 to see what will be the results. Be prepare for the huge amount of data (rows). That is the reason why do not use the 5.0.6 as in the release notes is written: Table 18: Known reporting issues Bug ID Description 0228960 The table/chart will not output more than 1500 rows when generating a report. It can happen that you will have more than 1500 rows...
    Fullmoon
    Fullmoon
    New Member
    February 20, 2014
    ORIGINAL: AtiT SELECT COALESCE(NULLIFNA(`user`), `srcip`) AS user, TO_TIMESTAMP(`itime`)::timestamp AS time, `hostname`||`url` AS url, `catdesc` FROM $log
    hi AtiT, thanks for the help appreciate it also. can you share some helpful links for us to educate so that by our own can we create our own desired dataset? :)
    AtiT
    AtiT
    New Member
    February 21, 2014
    Hi Fullmoon, I wrote it in some earlier post woh I started: 1) a good starting point how to write datasets (the basics) is: http://docs.fortinet.com/uploaded/files/1177/fortianalyzer-fortigate-sql-technote-40-mr2.pdf See the Appendix D: Querying FortiAnalyzer SQL log databases - this is an old version (4.2) but quering the database is the same. 2) Read the document on http://docs.fortinet.com/d/log-message-reference There are the tables and columns you can use and you can compare the diferences between the version 4.3 and 5. You can check the available colums. Let the analyer to show everything like: SELECT * FROM $log LIMIT 10 The first row (header) is the field names you can use. Maybe someone can help to find out what macros and functions are awailable for the SQL query? For instance macro $day_of_month or the function root_domain() used by mnantel. Does anyone has a collection of these or some list where I can find it?
    mnantel_FTNT
    Staff
    mnantel_FTNT
    Staff
    February 20, 2014
    I' ve created something quick for you. Do note that I am limiting the number of records to 500. This reports isn' t an actual audit trail with timestamps - it is aggregating data common to each combination of user+ip+website and presenting the bandwidth consumed in decrementing order. Let me know what tweaks you need! (And please, run 5.0.6 with this report - there are sometimes incompatibility in between versions) Go to the report section, and simply import the file and run it. https://www.dropbox.com/s/1j8sym6nooy1wmh/Websites%20-%20Top%20500%20by%20Bandwidth.dat I believe the OP wanted a report that has the timestamp, so I will wait for him to respond back...
    Fullmoon
    Fullmoon
    New Member
    February 20, 2014
    Hi mnantel, thanks for that file appreciate it and its working. Pls can you add more template like top allowed applications including the user/ip and timestamps and top blocked applications including the user/ip and timestamps. your help is highly appreciated
    mnantel_FTNT
    Staff
    mnantel_FTNT
    Staff
    February 20, 2014
    I can definitely provide that however if I provide you with a timestamp, aggregation is no longer possible because each row with a different timestamp is a new entry. What I can produce is a report which will basically end up listing the largest flows over the specific time interval - is that what you want? For instance: 
        #######################################################################################  # Timestamp                 #  User     # App            # Hostname     #  Bandwidth  #  #######################################################################################  | 2014/02/20 11:55:00       |  MAT      | Youtube.HD     | youtube.com  | 56 MB       |  ———————————————————————————————————————————————————————————————————————————————————————  | 2014/02/20 09:55:00       |  MAT      | BitTorrent     | N/A          | 45 MB       |  ———————————————————————————————————————————————————————————————————————————————————————  | 2014/02/20 10:55:00       |  MAT      | Youtube.HD     | youtube.com  | 35 MB       |  ———————————————————————————————————————————————————————————————————————————————————————  | 2014/02/20 08:55:00       |  MAT      | BitTorrent     | N/A          | 24 MB       |  ———————————————————————————————————————————————————————————————————————————————————————
    This is sorted reversed by the bandwidth transferred. Is that what you want? If so, how many records do you need?
    mnantel_FTNT
    Staff
    mnantel_FTNT
    Staff
    February 21, 2014
    Great dataset AtiT - i' m afraid your Postgres-fu is much more advanced than mine! To your question, there are not that many behind the scene macros - I' ve revealed one of them, root_domain(hostname), the other one you can find in some dataset is the $browse_time macro which calculates a browsing time column for the aggregated row' s criteria. You can find examples in the existing datasets.
    ede_pfau
    SuperUser
    ede_pfau
    SuperUser
    February 22, 2014
    @fullmoon: Simple syntax error: you' ve used ' double quotes' instead of the correct ' back ticks' when you quoted the ' duration' keyword.
    Fullmoon
    Fullmoon
    New Member
    February 22, 2014
    hi ede thanks for your attention. I guess it was rectified already. I should use Traffic Logs Under Log Type rather than selecting App Control
    mnantel_FTNT
    Staff
    mnantel_FTNT
    Staff
    February 22, 2014
    Fullmoon, do let me know if you still require assistance producing those reports. I will check with the forum admins if we can have a sticky capability in here for those posts that may contain good reports.
    trubble
    trubble
    New Member
    February 25, 2014
    I' ve created the data set as previously mentioned in this post. The test query runs great. However, I am unable to create a report based on this data set - it simply does not show up in the list of " charts" . What am I missing here?
    Rfornell
    Rfornell
    New Member
    March 11, 2014
    Any ideas when trying to create the dataset I would get this? ERROR: each UNION query must have the same number of columns LINE 1: ...12608918-wlog-1394098200-d3a548" union all select * from " FG... Using this with web filter selected SELECT COALESCE(NULLIFNA(`user`), `srcip`) AS user, TO_TIMESTAMP(`itime`)::timestamp AS time, `hostname`||`url` AS url, `catdesc` FROM $log ^
    AtiT
    AtiT
    New Member
    March 12, 2014
    Hi, I copy-paste your dataset and its working on my side. The error is writing something about " union" . Do you use the UNION in your dataset? If yes then it seems that you don not have the same number of columns in the SELECTs.
    fsheriff
    fsheriff
    New Member
    December 28, 2014

    Hello,

     

    Guys this thread has helped me a lot, so thanks a lot for your posts.

    I have question regarding the data sheets for the blocked and allowed apps, i encountered two error when i imported your file.

    1) ipstr keyword needed to be added before srcip

    2) Status column is missing, i removed this line and then i worked fine.

     

    But the generated reported is showing only the application blocked is there a way i can generate a report based on the blocked URL's and source user.

     

    Any help will be highly appreciated.

     

    Regards,

    hzhao_FTNT
    Staff
    hzhao_FTNTAnswer
    Staff
    December 29, 2014

    Hi there,

     

    `status` field was changed to `action` after FAZ 5.0.7.

    For FOS 5.0 log, please query traffic log use utmaction!='blocked' for allowed traffic

    For FOS 5.2&4.3 log, please query webfilter log and use action!='blocked' for allowed traffic

    To generate a report based on the blocked URL's and source user, please try:

     

    select hostname, user_src, sum(requests) as requests from (###(select hostname, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter') and hostname is not null and utmaction='blocked' group by hostname, user_src order by requests desc)### union all ###(select hostname, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log-webfilter where $filter and (eventtype is null or logver = 52) and action='blocked' group by hostname, user_src order by requests desc)###) t group by hostname, user_src order by requests desc

     

    This dataset should work for 4.3,5.0&5.2 log

     

    Regards,

    hz

    fsheriff
    fsheriff
    New Member
    December 30, 2014

    Helooo,

     

    Thanks a lot for the quey, it works like a charm.

    I was not expecting a response since the post was so old, really appreciate your time and concern.

    Checking out your other posts to see more sulotions and under stand the query rules

     

    you have good one mate!!

    Terms & ConditionsAccessibility statement
    Thought Leadership Security Summit. Outpace New Threats with AI - enhanced defense. Tuesday, Septmeber 15, 8:30 AM - 2:30 PM PT. The Golf Club at Newcastle, WA.
    Fortinet Flag the Hack. Wednesday, August 26, 9:00 AM - 5:00 PM ET, COSM, Atlanta, GA.