How can I build a nested query with the following attributes: class, user, id, value?

[yifatcy]

Hi,
I have data with the following attributes: class, user, id, value.
I want to execute for value larger than <number> and for the top 5 classes with the maximal quantity of records (ids), the user with maximum records for each of those classes.

I have this for now (which gives me the first part) : 
index="x"  value>1036800 | fields class id value | chart count(id) as 12+_days by class 
| sort -12+_days limit=5

example for my expected final results:

user	12+_days
user_a	215
user_b	35
user_c	65
user_d	656
user_e	695

where each of those users is from different class from the top 5 classes.

I tried nested loop with square brackets but nothing gave my the expected result.
Thanks.

[richgalloway]

Please provide some sample data.

[yifatcy]

Here is an example, the value is only a condition so I'll ignore it for the sake of the example.

expected result (I'll show for top 2 here).

Top 2 classes (those with the maximum records):

A 

C

Top user in each class:

James (in A)

Jenny (in C)

data:

user	id	class	value
James	12245	A	1225488
James	24545	A	2156321
Gianna	12457	B	4558923
Jenny	12462	C	1214578
James	41512	A	1324486
Jenny	41277	C	2151778
John	54588	C	4584897
Erik	55785	A	2165156

[richgalloway · Accepted solution]

This was a challenge.  I think you can do it with a subsearch that selects the top classes.

| makeresults | eval _raw="user   id    class value
James  12245 A     1225488
James  24545 A     2156321
Gianna 12457 B     4558923
Jenny  12462 C     1214578
James  41512 A     1324486
Jenny  41277 C     2151778
John   54588 C     4584897
Erik   55785 A     2165156" | multikv forceheader=1
```Above just defines test data```
```Use a subsearch to find the top classes```
| search [| makeresults | eval _raw="user   id    class value
James  12245 A     1225488
James  24545 A     2156321
Gianna 12457 B     4558923
Jenny  12462 C     1214578
James  41512 A     1324486
Jenny  41277 C     2151778
John   54588 C     4584897
Erik   55785 A     2165156" | multikv forceheader=1
```Above just defines test data```
  | streamstats sum(value) as total by class
  | stats max(total) as maxtotal by class 
  ```Change "2" to the desired number of classes```
  | sort 2 - maxtotal | fields class | format]
| stats sum(value) as sum by class,user
| sort - sum
| dedup class

[yifatcy]

Thank you very much!

I'm struggling to replace the definition of data test with index (I'm fairly new to Splunk).

this is the way I extracted the data in my previous queries, and I tried to use it here but it seems like I'm not supposed to use index after the search command. could you tell me how to replace the data definition with this:

index=jobs  value>1036800 | fields user id class

thank you again.

[richgalloway]

What you have should work, with the addition of the value field.

index=jobs  value>1036800 | fields user id class value