Report for Social networking web activity

[fiberopt1986]

Hello all.

I am trying to figure out how to create a specific report by user (source ip) which shows how many hits of social networking websites each user has accessed.

The report would also need to expand the social networking category (instead of 40 hits on Social Networking, it would need to have 5 hits facebook, 2 hits twitter, etc), and combine subdomains (so that it doesn't show 2 hits XXX.facebook.com, 3 hits YYY.facebook.com, instead it just shows 5 hits Facebook). Also possibly include a percentage showing how many percent that specific site was in terms of their overall web activity.

Not sure if this is do-able. I tried to play with datasets myself and Just could not figure it out.

Much help would be appreciated.

[hzhao_FTNT]

Please try below dataset

Log Type: traffic

select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, coalesce(nullifna(root_domain(hostname)), ipstr(dstip)) as domain, count(*) as sessions from $log where $filter and catdesc='Social Networking' and logid_to_int(logid) not in (4, 7, 14) group by user_src, domain order by sessions desc

Then create a drilldown chart based on above dataset, see attached screenshot for chart setting, you can customize "show top" "drilldown top".

Regards,

hz

[fiberopt1986]

That looks great. Exactly what i was looking for.

Quick question:

Currently I do not have FSSO installed. If i installed the FSSO agent to log user's names (opposed to IP addresses), will this dataset need to be changed? Or will it work as given.

Thank you a lot.

[hzhao_FTNT]

Your are welcome. No need to change dataset, it is trying to fetch username first, when username is not available, it will log ip address.

hz 

[Abu_Hashim]

than you for the query it solved my problem but I have a question:

what (not in (4, 7, 14) group) means ?