create custom report for youtube

sreeram · ‎03-19-2018

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

RobertReynolds · ‎03-19-2018

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

sreeram · ‎03-20-2018

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

sreeram · ‎03-20-2018

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

hzhao_FTNT · ‎03-20-2018

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 · ‎03-20-2018

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

create custom report for youtube

Nominate a Forum Post for Knowledge Article Creation