Helpful ReplyHot!Gathering info about users connected per hour/day/week

Author
Marek
New Member
  • Total Posts : 8
  • Scores: 0
  • Reward points: 0
  • Joined: 2017/06/12 02:16:22
  • Status: offline
2017/06/13 03:20:24 (permalink) 5.2
0

Gathering info about users connected per hour/day/week

Hi
I was wandering how can i get raport about end-systems like smartphones/hosts etc. that were connected to my device last day/ last week/last mont
In my Analyzer i can only generate report about "TOP" users TOP sites etc. but i need to know how many end-systems were use my FG to connect to the internet.
I have about 500 fortigates to report about "how many end systems were using my FG to connect to the internet", my devices are like FG80C and FW80CM
 
Can anybody help me?
Regards Marek
#1
Marek
New Member
  • Total Posts : 8
  • Scores: 0
  • Reward points: 0
  • Joined: 2017/06/12 02:16:22
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2017/06/14 05:44:05 (permalink)
0
Anybody?
#2
hzhao_FTNT
Expert Member
  • Total Posts : 338
  • Scores: 54
  • Reward points: 0
  • Joined: 2014/09/12 10:03:54
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2017/06/14 13:26:29 (permalink) ☄ Helpfulby Marek 2017/09/04 05:51:30
5 (1)
Hi there,
 
Please create a dataset as below, then create a drill-down table chart base on it, see attached screenshot for chart setting..
Log type: traffic
select hourstamp, devid, count(distinct f_user) as user_count from ###(select devid, $HOUR_OF_DAY as hourstamp, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as f_user from $log where $filter and logid_to_int(logid) not in (4, 7, 14)  group by devid, hourstamp, f_user)### t group by hourstamp, devid order by hourstamp desc
 
regards,
hz

Attached Image(s)

#3
hzhao_FTNT
Expert Member
  • Total Posts : 338
  • Scores: 54
  • Reward points: 0
  • Joined: 2014/09/12 10:03:54
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2017/06/14 13:32:43 (permalink) ☄ Helpfulby Marek 2017/09/04 05:51:34
5 (1)
BTW, you can change $HOUR_OF_DAY to $DAY_OF_MONTH for per day report. We don't have built-in function for per week, you can check standard postgres data/time function for it.
#4
Marek
New Member
  • Total Posts : 8
  • Scores: 0
  • Reward points: 0
  • Joined: 2017/06/12 02:16:22
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2017/06/20 00:17:33 (permalink)
0
After this


select daystamp, devid, count(distinct f_user) as user_count from ###(select devid, $DAY_OF_MONTH as daystamp, coalesce(nullifna(`user`),
nullifna(`unauthuser`), ipstr(`srcip`)) as f_user from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by devid, daystamp, f_user)### t group by daystamp, devid order by daystamp desc


Im reciving that
invalid request-id:600 for result fetch.

time peroid is set on 7 days

post edited by Marek - 2017/06/20 00:19:16
#5
hzhao_FTNT
Expert Member
  • Total Posts : 338
  • Scores: 54
  • Reward points: 0
  • Joined: 2014/09/12 10:03:54
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2017/06/20 09:52:59 (permalink) ☄ Helpfulby Marek 2017/09/04 05:51:46
0
You got this error message from dataset test console? How about report running?
Because dataset test will directly query all available tables, if you have high log rate, it could cause DB server out of memory. This dataset is good, you can just create a report based on it.
#6
singhaam
New Member
  • Total Posts : 1
  • Scores: 0
  • Reward points: 0
  • Joined: 2018/10/10 14:39:48
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2018/10/10 14:45:28 (permalink)
0
Hi hzhao_FTNT thanks so much for this Dataset and i was looking for something similar.
one last question please how do i remove Subtotal Tabs and % of subtotal ?
 
#7
hzhao_FTNT
Expert Member
  • Total Posts : 338
  • Scores: 54
  • Reward points: 0
  • Joined: 2014/09/12 10:03:54
  • Status: offline
Re: Gathering info about users connected per hour/day/week 2018/10/11 09:54:34 (permalink)
0
Hi there,  Subtotal Tabs and % of subtotal are hard coded for drilldown table chart and can not be removed.
#8
Jump to:
© 2018 APG vNext Commercial Version 5.5