Hot!Problems with youtube report

Author
Edemar
New Member
  • Total Posts : 5
  • Scores: 0
  • Reward points: 0
  • Joined: 2018/02/16 15:07:34
  • Status: offline
2018/05/07 09:42:46 (permalink)
0

Problems with youtube report

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
 
 
 
 

 
 
#1

7 Replies Related Threads

    chall_FTNT
    skyhigh
    • Total Posts : 275
    • Scores: 24
    • Reward points: 0
    • Joined: 2003/11/28 16:19:30
    • Status: offline
    Re: Problems with youtube report 2018/05/07 11:35:25 (permalink)
    4 (2)
    $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.
     
    #2
    Edemar
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/02/16 15:07:34
    • Status: offline
    Re: Problems with youtube report 2018/05/08 11:46:18 (permalink)
    0
    Perfetc...tanks
    #3
    Edemar
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/02/16 15:07:34
    • Status: offline
    Re: Problems with youtube report 2018/05/09 08:14:46 (permalink)
    0

    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?
    #4
    AtiT
    Gold Member
    • Total Posts : 440
    • Scores: 32
    • Reward points: 0
    • Joined: 2012/04/18 12:13:27
    • Location: Prague / Czech Republic
    • Status: offline
    Re: Problems with youtube report 2018/05/09 23:38:22 (permalink)
    0
    Hello Edemar,
    What is your FAZ version and which type of log type is the dataset for?

    AtiT
    --------------------
    NSE 8, CCNP R+S
    #5
    Edemar
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/02/16 15:07:34
    • Status: offline
    Re: Problems with youtube report 2018/05/11 05:20:50 (permalink)
    0
    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
    #6
    AtiT
    Gold Member
    • Total Posts : 440
    • Scores: 32
    • Reward points: 0
    • Joined: 2012/04/18 12:13:27
    • Location: Prague / Czech Republic
    • Status: offline
    Re: Problems with youtube report 2018/05/16 07:17:21 (permalink)
    3 (1)
    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:
     

    Attached Image(s)


    AtiT
    --------------------
    NSE 8, CCNP R+S
    #7
    Edemar
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/02/16 15:07:34
    • Status: offline
    Re: Problems with youtube report 2018/09/14 09:33:50 (permalink)
    0
    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?
    #8
    Jump to:
    © 2018 APG vNext Commercial Version 5.5