Splunk Search

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

kotig
Path Finder

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Path Finder

Thanks that query worked for me.

0 Karma

cmerriman
Super Champion

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
Path Finder

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

0 Karma

cmerriman
Super Champion

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

0 Karma

kotig
Path Finder

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())

0 Karma

kotig
Path Finder

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

0 Karma

cmerriman
Super Champion
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.

0 Karma

kotig
Path Finder

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

0 Karma

kotig
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...