I am trying to filter based on the top 10 users for below search. I want to end up with all events with tagged with any user in top 10 users...I tried Top limit=10 User
, but it didn't really give me what I was looking for. I also tried using sort
but was unable to get desired result.
search index="ces-monitor" Interface="HTML (UI)" User=* | fields - punct _bkt _cd _indextime _kv _raw _serial _si _sourcetype _subsecond | fields _time Action User request_remote_address Duration
Try like this (the subsearch will act as filter to keep result pertaining to top 10 users only)
search index="ces-monitor" Interface="HTML (UI)" [search index="ces-monitor" Interface="HTML (UI)" User=*| top 10 User | table User] | fields - punct _bkt _cd _indextime _kv _raw _serial _si _sourcetype _subsecond | fields _time Action User request_remote_address Duration
Try like this (the subsearch will act as filter to keep result pertaining to top 10 users only)
search index="ces-monitor" Interface="HTML (UI)" [search index="ces-monitor" Interface="HTML (UI)" User=*| top 10 User | table User] | fields - punct _bkt _cd _indextime _kv _raw _serial _si _sourcetype _subsecond | fields _time Action User request_remote_address Duration
@somesoni2, Thanks! That's exactly what I was trying to do. I'll have to read the subsearch documentation. Out of curiosity - would it be possible then to do something similar to get a statistical sample of the data? I am on splunk version 6.0.1 so event sampling doesnt seem to be an option.
Well, event sampling is not available in 6.0.1 so you have write a query to return just the samples. May be something like this work for you.(added dedup command with parameter on how many events from each User to show).
search index="ces-monitor" Interface="HTML (UI)" [search index="ces-monitor" Interface="HTML (UI)" User=*| top 10 User | table User] | dedup 100 User | fields - punct _bkt _cd _indextime _kv _raw _serial _si _sourcetype _subsecond | fields _time Action User request_remote_address Duration
the top command returns the top 10 users by count in the statistics tab - I am trying to come up with a way to search using something like this:
search index="ces-monitor" Interface="HTML (UI)" User=user1 or User=user2 or User=user3...User=user10
And return event data for top X number of users
without having to specify the users as I have in above search.
ok, got it...
can you please clarify - "the top X number of users" meaning.. is it like - the user names are in an order?
(the top command finds out the users with maximum events)
without having to specify the users as I have in above search.
maybe, you can use wildcards.. like User=john*
..
Hi Inventsekar,
the top X number of users
- I want to search events only for the users returned by top limit=10 User
.
Ideally in your case top command should work, however you can also try the stats command in conjunction with sort and head command to validate the results:
Option 1
search index="ces-monitor" Interface="HTML (UI)" User=* | top limit=10 User
Option 2
search index="ces-monitor" Interface="HTML (UI)" User=* | stats count(User) as Count by User | sort - Count | head 10
If the above two are not working then you might have to provide your query and data.
Edited and added the following query, since you need top 10 Users to be used as search criteria in your subsequent search, join on User is an ideal match. Please try the following. There are other better perfroming ways as well. However, they would be specific to use case.
search index="ces-monitor" Interface="HTML (UI)" User=* | table _time Action User request_remote_address Duration | join User [search search index="ces-monitor" Interface="HTML (UI)" User=* | top limit=10 User showperc=f showcount=f ]
Hi @niketnilay,
the top command returns the top 10 users by count in the statistics tab - I am trying to come up with a way to search using something like this:
search index="ces-monitor" Interface="HTML (UI)" User=user1 or User=user2 or User=user3...User=user10
And return event data for top X number of users without having to specify the users as I have in above search.
Two ways you could do that:
1) Use lookup table to define the list of users (if it is static list/ or changes less often) and add |lookup <YourLookupTable> User |
Provided the user field in Lookup table is also called User same as indexed data.
Please let me know if this is what you are looking for or not.
Hi @niketnilay. I am not familiar with lookup tables - but I just read the documentation. If I understand, would I create a table based on top limit=10 User, and then point to users in the lookup table?
I have edited my answer and added the join query which I feel is what you need.
Gather all the details only for top 10 Users.
may i know if this query returns top 10 users -
index="ces-monitor" Interface="HTML (UI)" User=* | top limit=10 User
Yes, that returns the top 10 users by count in the stats tab. What I want thouhg, is those users returned with all the events they are stamped as if I were to search search index="ces-monitor" Interface="HTML (UI)" User=user1 or User=user2 or User=user3 etc