Reporting

Generate report for top 10 web category usage

dharveynswccd
Path Finder

Hey guys, I'm trying to complete a report to show the top web users in my environment that are accessing websites that fall under a certain category.

My search thus far :

index="proxi" sourcetype="prxy" src="*" |stats count by src category url 
|where count > 1
|sort - count

This produces results 1 line at a time. However, what I'd like to accomplish is a cumulative number of categories for each user (src) and all the urls associated with those categories. So my table would look something like this:
src category url
XX.XXX.XX.X Advertisements https://ib.adnxs.com
Information Technology https://btlr.sharethrough.com
Web Collaboration https://portal.engilitycorp.com

XX.XXX.XX.X Search Engines and Portals https://www.gstatic.com
News and Media https://smetrics.cnn.com
Business and Economy https://ssc.33across.com

I am not totally convinced that my method is the most efficient so I'm open to suggestions

Tags (1)
0 Karma
1 Solution

nickhills
Ultra Champion

I'm not 100% sure if have understood your requirements, but I assume you don't care about the counts per-se, but you would want every URL reported?

if so, how does this work for you?

index="proxi" sourcetype="prxy" src="*" category=* 
|stats values(category) as categories by src
If my comment helps, please give it a thumbs up!

View solution in original post

0 Karma

nickhills
Ultra Champion

I'm not 100% sure if have understood your requirements, but I assume you don't care about the counts per-se, but you would want every URL reported?

if so, how does this work for you?

index="proxi" sourcetype="prxy" src="*" category=* 
|stats values(category) as categories by src
If my comment helps, please give it a thumbs up!
0 Karma

dharveynswccd
Path Finder

When I ran that I got no results and no errors. If I remove "transaction src" i see results but simply line-by-line per src. You asked about having every url reported. That is something that I can do without and simply do drilldowns after the report is generated, as I realize that there will be a huge number of URLs.

0 Karma

dharveynswccd
Path Finder

@nickhillscpl, how would I tabulate the number of hits against each category?

0 Karma

nickhills
Ultra Champion

|stats values(category) as categories count(src) as Hits by src

If my comment helps, please give it a thumbs up!
0 Karma

dharveynswccd
Path Finder

Nice, thanks again!!

0 Karma

nickhills
Ultra Champion

Ok, thats simpler - I just revised my answer. Is that closer?

If my comment helps, please give it a thumbs up!
0 Karma

dharveynswccd
Path Finder

This is great @nickhillscpl. Thanks much

0 Karma

dharveynswccd
Path Finder

Sorry, the header should have stated "Generate report for top 10 web category usage"

0 Karma

nickhills
Ultra Champion

edited for you 🙂

If my comment helps, please give it a thumbs up!
0 Karma