AnsweredHot!Browsetime column creation ?

Author
fran1942
Bronze Member
  • Total Posts : 28
  • Scores: 0
  • Reward points: 0
  • Joined: 2016/12/21 21:56:19
  • Status: offline
2017/08/10 15:06:02 (permalink)
0

Browsetime column creation ?

Hello, I have the following drilldown dataset. I cannot work out how to convert the 'bandwidth' column in to a 'browse time' column. Is anyone able to assist ? (I have attached a screenshot which shows how I would like it to be with a browse time column).
Thank you kindly if possible.
 
select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, app, sum(coalesce(`sentbyte`, 0)+coalesce(`rcvdbyte`, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(app) is not null and
(appcat = 'Social.Media') group by user_src, app order by bandwidth desc

Attached Image(s)

#1
hzhao_FTNT
Expert Member
  • Total Posts : 325
  • Scores: 50
  • Reward points: 0
  • Joined: 2014/09/12 10:03:54
  • Status: offline
Re: Browsetime column creation ? 2017/08/10 15:27:07 (permalink)
0
Hi there, please use predefined dataset "top-user-by-website-browsetime".
 
Regards,
hz
#2
fran1942
Bronze Member
  • Total Posts : 28
  • Scores: 0
  • Reward points: 0
  • Joined: 2016/12/21 21:56:19
  • Status: offline
Re: Browsetime column creation ? 2017/08/10 19:17:55 (permalink)
0
thank you kindly but how do I change out 'domain' for 'app' within that dataset ? Simply swapping the word 'domain' for 'app' does not work.
 
top-user-by-website
select user_src, domain, ebtr_value(ebtr_agg_flat(browsetime), null, $timespan) as browsetime from ###(select user_src, domain, ebtr_agg_flat(browsetime) as browsetime from (select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, ebtr_agg_flat($browse_time) as browsetime from $log where $filter and $browse_time is not null and (appcat = 'Social.Media') group by user_src, domain) t group by user_src, domain order by ebtr_value(ebtr_agg_flat(browsetime), null, null) desc)### t group by user_src, domain order by browsetime desc
#3
hzhao_FTNT
Expert Member
  • Total Posts : 325
  • Scores: 50
  • Reward points: 0
  • Joined: 2014/09/12 10:03:54
  • Status: offline
Re: Browsetime column creation ? 2017/08/11 11:48:44 (permalink) ☼ Best Answerby fran1942 2017/08/12 01:01:35
0
Try:
select user_src, app, ebtr_value(ebtr_agg_flat(browsetime), null, $timespan) as browsetime from ###(select user_src, app, ebtr_agg_flat(browsetime) as browsetime from (select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, app, ebtr_agg_flat($browse_time) as browsetime from $log where $filter and $browse_time is not null and (appcat = 'Social.Media')  group by user_src, app) t group by user_src, app order by ebtr_value(ebtr_agg_flat(browsetime), null, null) desc)### t group by user_src, app order by browsetime desc
#4
Jump to:
© 2017 APG vNext Commercial Version 5.5