topic Re: How to String Compare? in Splunk Search

How to String Compare?

chengyu — Wed, 06 May 2015 07:16:22 GMT

Hi Sir:
My Raw data CurrentPrice,VendorPrice1...is string not number, so i use convert change fields attribute. I hope VendorPrice1 < CurrentPrice or VendorPrice2 < CurrentPrice or VendorPrice3 < CurrentPrice appear data and highlights red color. If use ... |search VendorPrice1 < 0.3345 | ... is fine, if use |search VendorPrice1 < CurrentPrice| data is error, It can not seem to use than the size of the string, should can i do? Thank you.

My RAW data:
CurrentPrice VendorPrice1 VendorPrice2 VendorPrice3
.3345 .3303 .3302 .3305

sourcetype=xxx Status=Approved PartNo=$PartNo$ VendorCode=$VendorCode$ | Convert num(CurrentPrice) as CurrentPrice,num(VendorPrice1) as VendorPrice1,num(VendorPrice2) as VendorPrice2,,num(VendorPrice3) as VendorPrice3 | search ((VendorPrice1 < CurrentPrice) OR (VendorPrice2 < CurrentPrice) OR (VendorPrice3 < CurrentPrice) ) |stats values(CurrentPrice) values(VendorPrice1) values(VendorPrice2) values(VendorPrice3) by RfqNo,PartNo,VendorCode

Re: How to String Compare?

vganjare — Wed, 06 May 2015 07:59:20 GMT

Is there any value of the CurrentPrice which is lesser than any of the VendorPrices? In current example, the current price value .3345 is largest among all the prices.

Thanks!!

Re: How to String Compare?

Runals — Wed, 06 May 2015 10:42:17 GMT

In your case since you are comparing values in a field what you want to use is where not search as in

... | where VendorPrice1 &lt; CurrentPrice | ...

The search command like you've used it WILL work if you put in values as you've seen

Re: How to String Compare?

stephanefotso — Wed, 06 May 2015 10:46:07 GMT

Hello! When you use Values (X), The order of the values is lexicographical. Try the list function as follows and let me know what happen:

sourcetype=xxx Status=Approved PartNo=$PartNo$ VendorCode=$VendorCode$ | Convert num(CurrentPrice) as CurrentPrice,num(VendorPrice1) as VendorPrice1,num(VendorPrice2) as VendorPrice2,,num(VendorPrice3) as VendorPrice3 | search ((VendorPrice1 &lt; CurrentPrice) OR (VendorPrice2 &lt; CurrentPrice) OR (VendorPrice3 &lt; CurrentPrice) ) |stats list(CurrentPrice) list(VendorPrice1) list(VendorPrice2) list(VendorPrice3) by RfqNo,PartNo,VendorCode

Re: How to String Compare?

chengyu — Wed, 06 May 2015 11:44:30 GMT

Hi Runals:

         I have try where command | where VendorPrice1 &lt; CurrentPrice |, result still  the same.

Re: How to String Compare?

chengyu — Fri, 08 May 2015 02:28:40 GMT

I misplaced where command. Sorry, already resolve.