Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
sreeram
New Contributor

create custom report for youtube

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

1 Solution
RobertReynolds
Contributor

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.

View solution in original post

13 REPLIES 13
sreeram

v5.2.11,build754 (GA) / v5.4.3-build1187 170518 (GA)

sreeram

v5.2.11,build754 (GA) / v5.4.3-build1187 170518 (GA)

hzhao_FTNT

In order to check correct url info, we have to query webfilter log

1. enable webfilter profile on FGT,  change "Allow" to "Monitor"

2. log type: webfilter

select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src,  group,  (lower(service) || '://' || hostname || url) as website, count(*) as total_num, from_dtime(max(dtime)) as last_seen from $log where $filter and lower(hostname) like '%youtube%' group by user_src, group, website order by total_num desc

 

regards,

hz

abelio
Valued Contributor

Hello Huai,

 

Thanks for the dataset,

let me fix a small typo to avoid error message:

 

hzhao_FTNT wrote:

select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src,  group,  (lower(service) || '://' || hostname || url) as website, count(*) as total_num, from_dtime(max(dtime)) as last_seen from $log where $filter and lower(hostname) like '%youtube%' group by user_src, group, website order by total_num desc

 

As group does exist in the table, I'm replacing 

 

select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, nullifna(`group`) as usergroup,  (lower(service) || '://' || hostname || url) as website, count(*) as total_num, from_dtime(max(dtime)) as last_seen from $log where $filter and lower(hostname) like '%youtube%' group by user_src, usergroup, website order by total_num desc

 

Thanks for your help

regards




/ Abel

regards / Abel
Labels
Top Kudoed Authors