create custom report for youtube

[sreeram]

Please help me to get youtube URL report. Which i am getting attached image. Kindly help

[RobertReynolds · Accepted solution]

Here are a couple of Youtube queries I have created

This one lists all YouTube Videos played and embeds a hyperlink to take you straight to the video:

select `user` AS "Username", filename as "Name of Video",  concat(   '<a href="'    ,  '[link]https://', [/link] hostname, url,    '">',    concat(hostname, substring(url from 1 for 20),       '</a>')) AS  "YouTube URL"

from $log

where app = 'YouTube_Video.Access'

and filename is not null

This one shows the top watched Youtube videos by play count:

select filename AS "YouTube Video", count (filename) as "Number of Times Watched"

from $log

where app = 'YouTube_Video.Access'

and filename is not null

group by filename

order by "Number of Times Watched" desc;

As Youtube is https now, you need SSL Inspection enabled to be able to log the video name.

[ShawnZA]

What versions of firmware are you running these queries on?

Also get "No Data" when running it, would love to use them

Mine is on:

Fortigate 300D 5.4.4

FAZ 5.6.2

[OzanOzen]

Hello dear, thank you for detailed explaination. Could you please update for FortiAnalyzer-400E v7.0.2 GA build0180 I tried your config but doesn't work. Regards,

[sreeram]

Thanks to reply. I tried to apply this query. But not working.It's showing no data. Could you help me to enable ssl inspection in fortigate. i am using fortigate 300c

[RobertReynolds]

These Cookbook articles should help

http://cookbook.fortinet....ld-use-ssl-inspection/

http://cookbook.fortinet.com/preventing-certificate-warnings-defaultcert-56/

[sreeram]

After enable the SSL/SSH Inspection, same page only came now (no data). 

[RobertReynolds]

what fimrware are you running on FGT / FAZ?

your screenshot didnt attach

[sreeram]

FGT300c / FAZ200D

[RobertReynolds]

what about the FortiOS versions?

5.2.x / 5.4.x / 5.6.x ?