Help with custom report

theG · ‎01-25-2014

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

hzhao_FTNT · ‎12-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

View solution in original post

Fullmoon · ‎02-22-2014

ORIGINAL: mnantel We can do a graph for blocked apps, but I won' t include the timestamp or you will have an excruciatingly long list. What I propose for the blocked is to sort it by largest count of attempts blocked, and then to list you the sourceip/user, app, app category. This way you have your top offenders in a relatively concise manner. I can give you a listing of 10, 100 or 500 records for this - its really up to you. Does this makes sense?

approved on this part :)

Fortigate Newbie

mnantel_FTNT · ‎02-22-2014

Give the below a try: https://www.dropbox.com/s/30olsvpbvh2ien1/Applications%20-%20Allowed%20and%20Blocked.dat

-- Mathieu Nantel Systems Engineer / Conseiller Technique - Fortinet Montreal, QC

mnantel_FTNT · ‎02-22-2014

The report I made has 500 records returned per graph. You can filter it for specific users if you need to - thats probably the best usage of a Top 500 graph report...

-- Mathieu Nantel Systems Engineer / Conseiller Technique - Fortinet Montreal, QC

Fullmoon · ‎02-23-2014

hi mnantel, a millions thanks for the effort. appreciate it. till I meet new request :) cheers! :)

Fortigate Newbie

mnantel_FTNT · ‎02-25-2014

You are missing the chart itself :) Just create a new " raw" chart, add all the columns, select top 500 (instead of default top 3) and select the proper column types for all the columns. Voila!

-- Mathieu Nantel Systems Engineer / Conseiller Technique - Fortinet Montreal, QC

Rafael_Rosseto · ‎03-20-2014

Hello AtiT, I' m trying to use the dataset posted by you: 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 But the result is " No Data" , I double check and I have those logs in there. PS: I' m using version 5.0.5, what version did you use?

AtiT · ‎03-20-2014

Hi, I have just copy+pasted this dataset and it' s working on my side. Delete the goup by, having and order by lines and let to display all the colums: SELECT * FROM $log WHERE $filter AND `status` IN (' accept' ,' close' ) AND `utmaction`=' passthrough' Do you have any output? If still no data try to delete the AND `utmaction`=' passthrough' . If still no data then add back the AND `utmaction`=' passthrough' but delete the AND `status` IN (' accept' ,' close' ). If still no data then try: SELECT * FROM $log note: the dataset is for the Traffic log. I have the same 5.0.5 version on FAZ VM but it is also working on the 5.0.6 tested on FAZ 100C.

AtiT

Rafael_Rosseto · ‎03-21-2014

Cheers AtiT it works now, After I rebuild de Database it generate fine. The only point now is the bandwidth, it is showing like this: # Time dur User/Source website web_cat bandwidth 197 2014-03-21 07:24:10 00:02:18 XXX xbox.com (65.55.42.83) Games 1209 198 2014-03-21 08:30:16 00:02:18 XXX windowsupdate.com (65.172.31.18) Information Technology 2242 199 2014-03-21 08:07:25 00:02:16 XXX solocpm.com (84.33.37.157) Advertising 25340 I' m using the following query: 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 dur desc

Rafael_Rosseto · ‎03-21-2014

I fond how!! (Change on Chart settings) Cheers Man, you help me a lot. Rafael Rosseto.

trubble · ‎02-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?