Problems with youtube report

[Edemar]

Hi 

I created a Youtube usage report, but I'm having problems when the report goes beyond the current month.

Ex. is today 07 and I want a 14 days report, I visualize the date without the time, but the same report visualizing the last 7 days shows date and time normally. If you shoot a report only from last month, the time is not shown either. Could it be some version bug or something similar?   as I said, I use the same report, only changing the time period, and in the filter I add the user I want.

Sorry for google translate

  Firmware VersionCentral VPNAllocated StorageDevices FortiGates (1) root FortiGate 5.4

Dataset

select $flex_timescale(dtime) as date, srcip, appid, filename, `user`, sum(filesize) as filesize

from $log

where $filter

and appid = '38569'

and filename is not null

and filesize is not null

group by date, srcip, appid, filename, `user`, filesize

order by date asc

 

[chall_FTNT]

$flex_timescale converts the time scale depending on the length of the report period. 

For more information on this & other macros see: http://kb.fortinet.com/kb/microsites/search.do?cmd=displayKC&docType=kc&externalId=FD36191

It sounds like you might want to use $HOUR_OF_DAY instead if you to see references to hours (time) in your report.

[Edemar]

Perfetc...tanks

[Edemar]

I managed to solve my problem with the schedule, but now I came up with another that I had not previously, which is the union of the records that has the same filename adding the filesize

dataset

select $calendar_time(dtime) as date, srcip, appid, filename, `user`, sum(filesize) as filesize from $log where $filter and (app like '%YouTube%' or hostname like '%youtube%') and filename is not null and filesize is not null group by date, filename, srcip, filesize, `user`, appid order by date asc

return

45 2018-04-02 08:52:47 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 130.10 KB 46 2018-04-02 08:52:55 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 488.34 KB 47 2018-04-02 08:53:00 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 82.18 KB 48 2018-04-02 08:53:04 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 316.01 KB 49 2018-04-02 08:53:07 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 131.75 KB 50 2018-04-02 08:53:13 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 468.99 KB 51 2018-04-02 08:53:21 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 155.22 KB 52 2018-04-02 08:53:29 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 485.94 KB 53 2018-04-02 08:53:36 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 162.92 KB 54 2018-04-02 08:53:43 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 464.98 KB 55 2018-04-02 08:53:52 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 154.83 KB 56 2018-04-02 08:53:59 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 467.25 KB 57 2018-04-02 08:54:08 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 162.33 KB 58 2018-04-02 08:54:14 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 479.75 KB 59 2018-04-02 08:54:25 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 156.96 KB 60 2018-04-02 08:54:28 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 492.28 KB 61 2018-04-02 08:54:40 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 159.86 KB 62 2018-04-02 08:54:46 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 470.13 KB 63 2018-04-02 08:55:13 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 160.46 KB 64 2018-04-02 08:55:16 10.20.218.128 Nando Reis - Por Onde Andei (ao Vivo) 17518 469.83 KB

in the old reports, it appeared in a line with only the sum (filesize) Is there something I'm forgetting?

[AtiT]

Hello Edemar,

What is your FAZ version and which type of log type is the dataset for?

[Edemar]

Firmware VersionCentral VPNAllocated StorageDevices FortiGates (1) root FortiGate 5.4

dataset application control

I saw that if I only get the time, it does sum (filesize) correctly. would I have to do this sum and show only the first access time? Because the same video is presented several times, with difference of few seconds if I bring the complete hour. thank you

[AtiT]

Hello,

Sorry I had a lot of another work...

You get the results for every few seconds because it is something like a stream, after a few seconds you download the next portion of the video file.

You need to do a compromise let's say you will report these data for every hour.

Could you try this dataset:

SELECT time||':00' AS htime, user_src, t.filename, SUM(filesize) AS filesize FROM ###( SELECT TO_CHAR(FROM_ITIME(`itime`), 'YYYY-MM-DD HH24') AS time, COALESCE(NULLIFNA(`user`), NULLIFNA(`unauthuser`), IPSTR(`srcip`)) AS user_src, `filename`, SUM(`filesize`) AS filesize FROM $log WHERE $filter AND `appid`=38569 AND NULLIFNA(`filename`) IS NOT NULL GROUP BY time, filename, user_src HAVING SUM(filesize)>1000000 ORDER BY time ASC )### t GROUP BY htime, user_src, t.filename ORDER BY htime ASC

It was created on FAZ version 5.6.3, the FortiGate logging to FAZ is on version 5.6.4.

You will get results like:

[Edemar]

Hi people, 

I am using this report as indicated, and so far it is all right. However I have to not receive what was accessed between 12:00 and 14:00 (at this time the Internet is released). The little that I know would be something like ... AND DATE BEETWEN '* 12: 00' AND '* 13: 00' ... I've been trying a lot of combinations for a long time, but I did not get the expected result. Can someone help me?