Refine your search:

I got a challenging request from a customer regarding their access logs. They want to monitor access patterns across all the the access points in their network by user. Particularly, they are interested in stats for the top 10% of users for each access point. (10%- the top ten out of 100 users). I've managed to get most of the info that they want using pretty simple searches, but I'm still stumped on this one:

total number of logins for the top 10% of users by access point

I've tried some things using subsearches and the perc() function, but the search string gets too complicated or I end up doing something that Splunk doesn't like. Maybe I'm overthinking it.

Here's my latest failure(the appendcols and where commands cause the problems):

sourcetype="access_log" |stats count as EVNT by APNUM USERID 
|appendcols [search sourcetype="access_log" | stats count AS EVNTCNT by APNUM USERID
             | stats p90(EVNTCNT) as LIM by APIP| fields APIP LIM ]
| eval USE=if(EVNT<LIM, "NO", "YES")| table APIP, EVNT, LIM, USE| stats sum(EVNT) by APIP| where USE=YES

asked 15 Feb '11, 04:37

gpburgett's gravatar image

gpburgett
2132218
accept rate: 33%

edited 16 Feb '11, 19:06

jrodman's gravatar image

jrodman ♦
7.6k31030

It looks like the "where" command can only be used after a search string and not after a function. Correct?

(15 Feb '11, 06:21) gpburgett

The search string got cut off. Here's the complete search:

sourcetype="wims_auth" |stats count as EVNT by APIP MACID |appendcols [search sourcetype="wims_auth" | stats count AS EVNTCNT by APIP MACID| stats p90(EVNTCNT) as LIM by APIP| fields APIP LIM ]| eval USE=if(EVNT<LIM, "NO", "YES")| table APIP, EVNT, LIM, USE| stats sum(EVNT) by APIP| where USE=YES

(15 Feb '11, 06:24) gpburgett

Just tried to hack up the searchstring to be a bit more readable in answers

(16 Feb '11, 19:06) jrodman ♦

Are logins and accesses the same? Are we starting with the set of logins, and wanting to find, for each access point, the top 10% of users and their count of logins?

(16 Feb '11, 19:10) jrodman ♦

One note is that "| where USE=YES" is going to look for rows where the value of the USE field is equal to the value of the YES field. If you mean the literal 3-character value YES, you have to put the YES in quotes. Where is a little different from search and that's one of the ways.

(16 Feb '11, 19:45) sideview ♦

Thanks for the formatting help!

Yes, I mean the same thing when I say "access" and "login". I have a set of access logs and I want to find the total count of accesses for the top 10% of users per access point.

(17 Feb '11, 00:20) gpburgett

One Answer:
sourcetype=wims_auth | stats count as EVNT by APIP MACID | eventstats perc90(EVNT) as cutoff by APIP | where EVNT>=cutoff | stats sum(EVNT) by APIP
link

answered 17 Feb '11, 06:03

steveyz's gravatar image

steveyz ♦
1.2k24
accept rate: 52%

Post your answer
toggle preview

Follow this question

Log In to enable email subscriptions

RSS:

Answers

Answers + Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×425

Asked: 15 Feb '11, 04:37

Seen: 1,854 times

Last updated: 01 Apr '11, 23:22

Copyright © 2005-2014 Splunk Inc. All rights reserved.