topic Re: How can I count only users who exceeded a specific number of visits to web pages? in Splunk Search

How can I count only users who exceeded a specific number of visits to web pages?

rickettw — Thu, 25 Jan 2018 15:36:21 GMT

I want to find users who visited more than 1,000 urls in a month and the field name is cs_uri.
I tried this:

sourcetype= my sourcetype, index=my index, user=******| table date_month date time  user category cs_uri  | where cs_uri > 1,000.

I want a list of users only when the count is GT 1,000 for my date range. I do not get any results when I use the where command but I do when I take it off.

Re: How can I count only users who exceeded a specific number of visits to web pages?

rmsit — Thu, 25 Jan 2018 15:42:37 GMT

Did you try removing the comma from 1,000?

Re: How can I count only users who exceeded a specific number of visits to web pages?

FrankVl — Thu, 25 Jan 2018 15:44:12 GMT

You'll first need to count the URLs visited by a user before you can filter for count greater than 1000.

For example:

index=my index, user=**
| stats count by user
| search count>1000

Re: How can I count only users who exceeded a specific number of visits to web pages?

rmsit — Tue, 29 Sep 2020 17:50:43 GMT

Try this

sourcetype= my sourcetype index=my index user=* | stats count | where cs_uri>1000 | table date_month date time user category cs_uri

Re: How can I count only users who exceeded a specific number of visits to web pages?

rickettw — Thu, 25 Jan 2018 16:24:26 GMT

thanks, i did eliminate the , and am running the report now.

Re: How can I count only users who exceeded a specific number of visits to web pages?

rickettw — Thu, 25 Jan 2018 16:26:20 GMT

thank you for your help, i am running this new query now, but am queued up so it is taking a bit longer than usual.

Re: How can I count only users who exceeded a specific number of visits to web pages?

niketn — Tue, 29 Sep 2020 17:50:49 GMT

If you want to get the total number or URLs visited by each user for current month and then display details only for users who exceeded 1000 visits to URLs in total you can use eventstats command. (PS: If you need unique URLs visited to be > 1000, you would need to use dc() instead of count()

<YourBaseSearch> earliest=@mon latest=now
| eventstats count(cs_uri) as TotalURLsVisited by user
| search TotalURLsVisited > 1000
| table date_month _time user category cs_uri

Following is a run anywhere example from Splunk's _internal index for sourcetype=splunkd_access

index=_internal sourcetype="splunkd_access" earliest=@mon latest=now
| eventstats count(uri) as TotalURLsVisited by user
| search TotalURLsVisited>1000
| table date_month _time user method status uri

Re: How can I count only users who exceeded a specific number of visits to web pages?

niketn — Thu, 25 Jan 2018 16:52:51 GMT

@rickettw, @FrankVl, use of stats will retain only two fields after second pipe i.e. count and user. Which implies final table will not display required table for other fields.

Refer to my answer below as this is a usecase for eventstats if you want to display the entire table after stats. Also either count(cs_uri) as TotalURLsVisited or dc(cs_uri) as UniqueURLsVisited should be used as per the use case.

Re: How can I count only users who exceeded a specific number of visits to web pages?

FrankVl — Thu, 25 Jan 2018 16:56:49 GMT

Fair point, I had them the other way around originally, then thought to optimize things a bit, but clearly didn't fully think that through 🙂

Let me adjust that.

Question spoke about "a list of users" which my solution would give. If you indeed want to just filter the original data for those entries that relate to users that have over 1000 entries, then eventstats is indeed the way to go.

Re: How can I count only users who exceeded a specific number of visits to web pages?

rickettw — Thu, 25 Jan 2018 18:14:00 GMT

One issue I am having is my search is with an enterprise that has over 400000 users so I am only running it for Last 7 Days, to gain an idea who is spending time on the internet rather than their normal duties. The results i am getting just like the events and are not downloadable, in other words I am just getting events not statistics even with the table command. I will try the eventstats command, but am not sure where it goes: does this look correct? sourcetype="access:file", index="proxytraffic" earliest=@mon latest=now | eventstats count(uri) as TotalURLsVisited by user | search TotalURLsVisited>10000| table date_month _time user method status uri