Forum
Bildirimler
Hepsini Temizle
Fortinet
2
Yazılar
2
Üyeler
0
Likes
529
Görüntüleme
Konu başlatıcı
Aşağıdaki sql query i ben sectigim kullanıcıya gore uyarlamak istiyorum, nasıl yapabilirim, aşağıdaki query kullanıcılar hangi sitelere girmiş ve nakadar kalmış bilgisini gösteriyor, ben a kullanıcısı ne kadar kalmış hangi sitelere girmiş bilgisini istiyorum
select user_src, web_site, totalhit, bw_download, bw_upload, bw_volumebyte, browse_time from ( with tx as (select coalesce(nullifna(`user`), `srcip`) as user_src, coalesce(`hostname`, `dstip`, 'unknown') as web_site, rcvdbyte, sentbyte, dtime, $hour_of_day as hourstamp, $day_of_month as daystamp from $log where $filter and utmevent in ('webfilter', 'banned-word', 'web-content', 'script-filter', 'command-block', 'script-filter') and status!='blocked'), tt as (select user_src, web_site, sum(browse_time) as t_bt from (select user_src, web_site, daystamp, hourstamp, (l_time - f_time) as browse_time from (select tmptb1.user_src, tmptb1.web_site, tmptb1.hourstamp, tmptb1.daystamp, min(tmptb1.dtime) as f_time, max(tmptb2.dtime) as l_time from tx as tmptb1, tx as tmptb2 where tmptb1.user_src = tmptb2.user_src and tmptb1.web_site = tmptb2.web_site and tmptb1.hourstamp = tmptb2.hourstamp and tmptb1.daystamp = tmptb2.daystamp group by tmptb1.user_src, tmptb1.web_site, tmptb1.daystamp, tmptb1.hourstamp ) as t3 ) as t4 group by user_src, web_site ) select t1.user_src, t1.web_site, t1.totalhit, t1.bw_dl as bw_download, t1.bw_ul as bw_upload, t1.bw_tb as bw_volumebyte, tt.t_bt as browse_time from (select user_src, web_site, count(*) as totalhit, round(((sum(rcvdbyte)/1024)/1024), 2) || ' MB' as bw_dl, round(((sum(sentbyte)/1024)/1024), 2) || ' MB' as bw_ul, sum(sentbyte+rcvdbyte) || ' B' as bw_tb from tx group by user_src, web_site ) as t1, tt where t1.user_src = tt.user_src and t1.web_site = tt.web_site order by 1, 3 desc ) tz group by user_src, web_site, totalhit, bw_download, bw_upload, bw_volumebyte, browse_time order by user_src, totalhit desc
Gönderildi : 21/02/2013 15:45
Merhaba
çok emin olmamakla birlikte 🙂 aşağıdaki gibi yapabilirsin olmadı excel'e alıp filter uygulanabilir.
select user_src, web_site, totalhit, bw_download, bw_upload, bw_volumebyte, browse_time from ( with tx as (select coalesce(nullifna(`user`), `srcip`) as user_src, coalesce(`hostname`, `dstip`, 'unknown') as web_site, rcvdbyte, sentbyte, dtime, $hour_of_day as hourstamp, $day_of_month as daystamp from $log where $filter and utmevent in ('webfilter', 'banned-word', 'web-content', 'script-filter', 'command-block', 'script-filter') and status!='blocked'), tt as (select user_src, web_site, sum(browse_time) as t_bt from (select user_src, web_site, daystamp, hourstamp, (l_time - f_time) as browse_time from (select tmptb1.user_src, tmptb1.web_site, tmptb1.hourstamp, tmptb1.daystamp, min(tmptb1.dtime) as f_time, max(tmptb2.dtime) as l_time from tx as tmptb1, tx as tmptb2 where tmptb1.user_src = tmptb2.user_src and tmptb1.web_site = tmptb2.web_site and tmptb1.hourstamp = tmptb2.hourstamp and tmptb1.daystamp = tmptb2.daystamp group by tmptb1.user_src, tmptb1.web_site, tmptb1.daystamp, tmptb1.hourstamp ) as t3 ) as t4 group by user_src, web_site ) select t1.user_src, t1.web_site, t1.totalhit, t1.bw_dl as bw_download, t1.bw_ul as bw_upload, t1.bw_tb as bw_volumebyte, tt.t_bt as browse_time from (select user_src, web_site, count(*) as totalhit, round(((sum(rcvdbyte)/1024)/1024), 2) || ' MB' as bw_dl, round(((sum(sentbyte)/1024)/1024), 2) || ' MB' as bw_ul, sum(sentbyte+rcvdbyte) || ' B' as bw_tb from tx group by user_src, web_site ) as t1, tt where t1.user_src = tt.user_src and t1.web_site = tt.web_site order by 1, 3 desc ) tz
where user_src='GOKHAN'
group by user_src, web_site, totalhit, bw_download, bw_upload, bw_volumebyte, browse_time order by user_src, totalhit desc
Gönderildi : 01/03/2013 02:18
