Help with custom report

[theG]

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

[mnantel_FTNT]

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]

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]

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]

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]

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?

[AtiT]

FOR ALL, I had a little time so I created a website most traffic by user dataset based on the dataset from mnantel (thanks). SELECT TO_TIMESTAMP(`itime`)::timestamp AS time, ((`duration`)/60) AS dur, COALESCE(NULLIFNA(`user`), `srcip`) AS user_src, COALESCE(ROOT_DOMAIN(`hostname`),' Unknown Website' )||' (' ||`dstip`||' )' AS website, COALESCE(`catdesc`,' Unknown Category' ) AS web_cat, SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0)) AS bandwidth FROM $log WHERE $filter AND `status` IN (' accept' ,' close' ) AND `utmaction`=' passthrough' GROUP BY time, dur, user_src, srcip, website, web_cat HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC - this is for the Traffic log. The results are: This is based on the higher BW to the site. The duration is for minutes. For instance the first row: somebody was watching video on googlevideo.com for 23 minutes which ended at 10:08. So he started to watching at 10:08 - 23 minutes: 09:45.

[AtiT]

some improovment: for duration you can use: TO_CHAR((`duration` || ' second' )::interval, ' HH24:MI:SS' ) AS dur, instead of: ((`duration`)/60) AS dur, It will give the duration in the format HOUR:MINUTES:SECONDS like 00:23:17

[Fullmoon]

ORIGINAL: AtiT some improovment: for duration you can use: TO_CHAR((`duration` || ' second' )::interval, ' HH24:MI:SS' ) AS dur, instead of: ((`duration`)/60) AS dur, It will give the duration in the format HOUR:MINUTES:SECONDS like 00:23:17

Hi AtiT, Im playing/tesing around your dataset. Pls rectify If this is wrong. Thanks SELECT TO_TIMESTAMP(`itime`)::timestamp AS time, TO_CHAR((`duration` || ' second' )::interval, ' HH24:MI:SS' ) AS dur, COALESCE(NULLIFNA(`user`), `srcip`) AS user_src, COALESCE(ROOT_DOMAIN(`hostname`),' Unknown Website' )||' (' ||`dstip`||' )' AS website, COALESCE(`catdesc`,' Unknown Category' ) AS web_cat, SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0)) AS bandwidth FROM $log WHERE $filter AND `status` IN (' accept' ,' close' ) AND `utmaction`=' passthrough' GROUP BY time, dur, user_src, srcip, website, web_cat HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC

[Fullmoon]

Hi AtIT, how about for this request :) template or dataset like Top Allowed Applications including the user/ip and timestamps and Top Blocked Applications including the user/ip and timestamps. again, many thanks!