How to write an eval search to fetch the value of field2 corresponding to field1?

[muthvin]

Hi all,

How do I write an eval statement to fetch the value of field2 corresponding to field1?
For example, consider the table below:

field1  field2
Orange  10
apple    12
potato  13

If field1 value is orange, I want to assign a value of abc as corresponding field2 ..here it is 10
..|eval abc= value of field2 for orange..

Kindly help me in writing the search.

Thanks in advance
Muthu

[fdi01]

try:
yoursearch| eval abc= if(match(field1,"Orange")), field2, field2) | eval field3 = if(match(field1,"Orange")), field2, abc)

[muthvin]

hi all,
With your previous suggestion i can able to create a parameter like in field3,but i want field pass and fail should get calculated like below.

Need your help!

field1 field2 field3 pass fail
Orange 10 10 10 0
apple 9 10 9 1
potato 7 10 7 3

[renjith_nair]

Pass or fail based on what and how?

[muthvin]

Pass = field3-field2
fail = field2-field3

[renjith_nair]

Did you try |eval Pass=field3-field2|eval Fail=Field2-Field2 ?

[muthvin]

yes i did Ranjith, but unfortunately im getting result for only one row not for all rows..Actually i'm struck here..

field1 field2 field3 pass fail
Orange 10 10 10 0
apple 9 10
potato 7 10

How to make this calculations happen to all rows?

[muthvin]

what i'm trying to do here is:
step1: i will check for field1 value..if it is orange
step2: fetch the corresponding value in field2..here the value is 10
step3: Assign this value to abc..such that abc=10
step4: eval new field pass =field2-abc..if it is equal to zero then value =abc else
step5: eval new field fail - gives the difference and provide me the difference.

[renjith_nair]

Try this

your search
|eval field3=if(field1=="Orange",field2,"")|eventstats values(field3) as field3
|eval field3=mvindex(field3,1)|eval status=if(field2==field3,field2,(field2-field3))
|table sourcetype field2 field3 status

Or

   your search
    |eval field3=if(field1=="Orange",field2,"")|eventstats values(field3) as field3
    |eval field3=mvindex(field3,1)|eval status=if(field2==field3,"Pass (".field2.")","Fail (".(field2-field3).")")
    |table sourcetype field2 field3 status

OR

Your search
|eval field3=if(field1=="Orange",field2,"")|eventstats values(field3) as field3
|eval field3=mvindex(field3,1)|eval pass=if(field2==field3,field2,"")|eval fail=if(field2==field3,"",(field2-field3))
|table sourcetype field2 field3 pass fail

[muthvin]

will you be able to explain me why we need to use mvindex here after assigning values(field3) as field3 ?

[renjith_nair]

Because in eventstats we are taking values(field3) which is a list includes "" and value. To get the value from a multivalue field we use mvindex
http://docs.splunk.com/Documentation/Splunk/6.2.0/Search/Parsemultivaluefields

[muthvin]

Hi,
also here if i want to save the value of field2 corresponding to filed1 value orange say here is 10 as another variable abc..how can i do that?

[renjith_nair]

Have you tried the search posted by me?

[muthvin]

yes it is working for me..thx

[fdi01]

thx Mr renjith.nair you are right

[renjith_nair · Accepted solution]

<your search> |eval abc=if(field1=="Orange",field2,"")|eventstats values(abc) as abc

[javiergn]

Try this:

yoursearch | eval field2 = if(match(field1,"Orange")), field1, field2) 

If field1 matches Orange, then assign field1 to field2, else assign field2

[muthvin]

thanks for your reply:
Here im trying to create another field3
as per your suggestion im getting output like
field1 field2 field3
Orange 10 10
apple 12 12
potato 13 13

but i would like to have result as below:
field1 field2 field3
Orange 10 10
apple 12 10
potato 13 10

[javiergn]

Hi, so assuming you want to set the value of field3 in ALL your events to be field2 when field1 matches Orange, this is what I would do:

| inputcsv mycsv.csv
| join type=left [
   | inputcsv mycsv.csv
   | search field1 = "Orange"
   | eval fieldNEW = field2
   | fields fieldNEW
]
| eval field3 = fieldNEW
| fields - fieldNEW

mycsv.csv is just a csv matching the content of your table:

field1  field2  field3
Orange  10  11
apple   12  12
potato  13  13

And the query returns the following:

field1  field2  field3
Orange  10  10
apple   12  10
potato  13  10