Splunk Search

How to String Compare?

chengyu
Path Finder

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

Tags (2)
0 Karma
1 Solution

Runals
Motivator

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

View solution in original post

0 Karma

chengyu
Path Finder

Hi Runals:

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

chengyu
Path Finder

I misplaced where command. Sorry, already resolve.

0 Karma

stephanefotso
Motivator

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
SGF
0 Karma

Runals
Motivator

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

0 Karma

vganjare
Builder

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!!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...