Skip to main content
Edemar
Edemar
New Member
May 7, 2018
Question

Problems with youtube report

  • May 7, 2018
  • 2 replies
  • 10844 views

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

 

 

 

 

 

 

    2 replies

    chall_FTNT
    Staff
    chall_FTNT
    Staff
    May 7, 2018

    $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
    EdemarAuthor
    New Member
    May 8, 2018

    Perfetc...tanks

    Edemar
    EdemarAuthor
    New Member
    May 9, 2018

    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
    AtiT
    New Member
    May 16, 2018

    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
    EdemarAuthor
    New Member
    September 14, 2018

    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?

    Terms & ConditionsAccessibility statement