Skip to main content
Ian_Harrison
Ian_Harrison
New Member
May 11, 2016
Question

How to create report on blocked URLs for a category

  • May 11, 2016
  • 1 reply
  • 9063 views

Hi

 

I want to create a report that allows me to select a category and then to list all the activity for users where the URLS match that category. For example a report that lists user name, source IP, date time, url for the "Explicit Violence" category.

 

I have had a look at the datasets but my SQL coding is not to good :(

 

Has anyone tried this or any tips on what I need to do.

 

Regards

 

Ian

1 reply

CrisP
CrisP
New Member
June 6, 2016

Hello Ian,

I hate unanswered questions on a forum like this...

So here you have my two pennies on this.

Create a new dataset using

==============================

select from_dtime(dtime) as timestamp, user_src, catdesc, hostname as website,

action as status, sum(bandwidth) as bandwidth from ###( select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, dtime, catdesc, hostname, utmaction as action, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-traffic where $filter and hostname is not null and logid_to_int(logid) not in (4, 7, 14) and ((logver>=52 and countweb>0) or ((logver is null) and utmevent in ('webfilter', 'banned-word', 'web-content',

'command-block', 'script-filter'))) group by user_src, dtime, catdesc, hostname, utmaction order by dtime desc )### t group by user_src, dtime, catdesc, website, status order by dtime desc

===================================

(slightly modified from default dataset called "web-Detailed-Website-Browsing-Log")

then build a new chart using the dataset, and a report using that chart.

Use a filter specifying the categories you want to detail in the Advanced settings of the report.

 

Now, if you really need the URLs, the story becomes quite ugly. You could see the post called "URL field" in this forum.

Best regards and good luck

 

 

 

 

Ronildo
Ronildo
New Member
June 28, 2016

It works very well, thank you alot man.

 

You are awesome. ;)

0xkieron
0xkieron
New Member
October 17, 2017

Many thanks CrisP - I used your query as the basis for a dataset for a school and added specific webfilter categories - just in case anyone would find that useful..

 

select from_dtime(dtime) as timestamp, user_src, catdesc, hostname as website, action as status, sum(bandwidth) as bandwidth from ###( select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, dtime, catdesc, hostname, utmaction as action, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-traffic where $filter and hostname is not null and logid_to_int(logid) not in (4, 7, 14) and ((logver>=52 and countweb>0) or ((logver is null) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter'))) and (catdesc='Child Abuse' or catdesc='Extremist Groups' or catdesc='Explicit Violence' or catdesc='Drug Abuse' or catdesc='Discrimination' or catdesc='Hacking' or catdesc='Illegal or Unethical' or catdesc='Abortion' or catdesc='Marijuana' or catdesc='Pornography' or catdesc='Sports Hunting and War Games' or catdesc='Plagiarism' or catdesc='Proxy Avoidance' or catdesc='Weapons (sales)') group by user_src, dtime, catdesc, hostname, utmaction order by dtime desc )### t group by user_src, dtime, catdesc, website, status order by dtime desc

 

Terms & ConditionsAccessibility statement