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

[AtiT]

Hi, I' m actually testing a dataset for application usage, it looks like this: 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(`app`,' Unknown Application' ) AS apl, COALESCE(`appcat`,' Unknown Category' ) AS apc, 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, apl, apc HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC

[Fullmoon]

ORIGINAL: AtiT 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(`app`,' Unknown Application' ) AS apl, COALESCE(`appcat`,' Unknown Category' ) AS apc, 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, apl, apc HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC

I copied your syntax line per line but when I tried to test it I got this result.See attached file

[AtiT]

Hi, The dataset is for the Traffic log.

[Fullmoon]

hi, Is this normal unknown application and unknown category? How to fix this? I enbaled Log in my App Control profile including my web profile as well as my egress policy

[mnantel_FTNT]

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]

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]

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?

[Fullmoon]

Give me atleast 100 records/entries if possible. Thanks

[mnantel_FTNT]

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]

@fullmoon: Simple syntax error: you' ve used ' double quotes' instead of the correct ' back ticks' when you quoted the ' duration' keyword.