Gathering info about users connected per hour/day/week

[Marek]

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

[hzhao_FTNT · Accepted solution]

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

[hzhao_FTNT · Accepted solution]

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.

[Marek]

Anybody?

[hzhao_FTNT · Accepted solution]

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

[hzhao_FTNT · Accepted solution]

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.

[Marek]

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

[hzhao_FTNT · Accepted solution]

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.

[singhaam]

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 ?

[hzhao_FTNT]

Hi there,  Subtotal Tabs and % of subtotal are hard coded for drilldown table chart and can not be removed.

[singhaam]

we can see users connected per day or hour now. thanks for that.

is it possible to see if these users are new on that day or same user coming back ?

it is possible to achieve this please ?