How to compare values in field1 to a list of values in field2, and return all values where field2 is greater than field1?

[kotig]

Here is my situation. I have written a search to get a list of values per user and I did an average of the values as average. Now I wanted to compare the average I received with the list of values where I got the average and display which are more than the average.

Test Data Sample:

user    Marks   Average(marks)
1         1        3
           2    
           3    
           4    
           5

Now I want to display in another column where marks>average(marks) for each user. In this example, user1 has 4 and 5 to be displayed in my search.

[somesoni2 · Accepted solution]

Try like this

your current search giving you multivalued field 'marks' and single valued fields 'user' and  'average' 
| mvexpand marks | eval above_average=if(marks>average,marks,null()) | stats list(marks) as marks list(above_average) as above_average values(avg) as avg by user

[kotig]

Thanks that query worked for me.

[cmerriman]

could you do something like. if it isn't a multivalued field, you can excluve the mvexpand.

...|stats ....avg(marks) as averageMarks by.....|mvexpand user|eval GreaterThanAverage=if(marks>averageMarks,"True",null())

[kotig]

And to add it is a multivalued filed

user field value is 1 and corresponding values of marks filed are 1,2,3,4,5

[cmerriman]

sorry, you should use the mvexpand command on the multivalued field. i updated the syntax. can you show me the syntax you're trying?

[kotig]

index=mysource earliest =-15m|rex field=_raw "(?msi)(?{.+}$)" | spath input=json_field | rename s.Path as sPath,s.pElapsedTime as ElapsedTime|eval ElapsedTimeinSecs=(ElapsedTime/1000)|where isnotnull(ElapsedTimeinSecs)| stats list(ElapsedTimeinSecs) as ElapsTime, avg(ElapsedTimeinSecs) as AVGResponse by sPath|mvexpand ElapsedTimeinSecs|eval GreaterThankAvg=if(ElapsTime>AVGResponse,ElapsTime,null())

[kotig]

that is the query i am using. My data is coming from a json and i did extract my fields out of json and did that rex and stuff and then extracted the fields i needed for my calculations

[cmerriman]

index=mysource earliest =-15m|rex field=_raw "(?msi)(?{.+}$)" | spath input=json_field | rename s.Path as sPath,s.pElapsedTime as ElapsedTime|eval ElapsedTimeinSecs=(ElapsedTime/1000)|where isnotnull(ElapsedTimeinSecs)| stats list(ElapsedTimeinSecs) as ElapsTime, avg(ElapsedTimeinSecs) as AVGResponse by sPath|mvexpand ElapsTime|eval GreaterThankAvg=if(ElapsTime>AVGResponse,ElapsTime,null())

try that. you mvexpaned a field you renamed as ElapsTime.

[kotig]

I tried the same query but still it does not give the greater values. I tried the other option suggested by other splunk user and adding the below after what you gave.

|stats list(ElapsTime) as ElapsTime, list(above_average) as above_average values(AVGResponse) as AVGResponse by sPath

Thank you for your help

Thanks
Koti

[kotig]

I tried the same way but the response i get is always it gives me null() even though there are value which are greater like 4,5