topic Re: comparing multivalue fields in Splunk Search

comparing multivalue fields

stevesmith08 — Sun, 07 Apr 2019 19:23:17 GMT

Good day!

I need to compare the results of a search query that contains multivalued fields.

My search query looks like this:

sourcetype = MySourceType earliest = 0 latest = now()
| eval category = if(_time>relative_time(now(), "-2h@h"), "DeviceIDnew", "DeviceIDlater")
| chart values(deviceID) by IP, category 
| eval compare = if(DeviceIDlater=DeviceIDnew, 0, 1)
| table IP, DeviceIDlater, DeviceIDnew, compare

Despite the fact that, in most cases, the comparison is correct, I noticed that in some cases there are errors.

For example:

In the example above, I mean that compare = 0 because the values DeviceIDnew contained in the field DeviceIDlater.

Could you help me, please? How it is correct to compare multi-value field?

Thank you!

Re: comparing multivalue fields

MuS — Sun, 07 Apr 2019 20:59:59 GMT

Hi stevesmith08,

If you use the field with less multi values, expand it and do the compare operation it should work just fine. Try something like this:

sourcetype = MySourceType earliest = 0 latest = now()
 | eval category = if(_time>relative_time(now(), "-2h@h"), "DeviceIDnew", "DeviceIDlater")
 | chart values(deviceID) by IP, category 
 | mvexpand DeviceIDnew
 | eval compare = if(DeviceIDlater=DeviceIDnew, 0, 1)
 | table IP, DeviceIDlater, DeviceIDnew, compare

This will work with a small set of events, if you have millions of events try this:

sourcetype = MySourceType earliest = 0 latest = now()
 | eval category = if(_time>relative_time(now(), "-2h@h"), "DeviceIDnew", "DeviceIDlater")
 | chart values(deviceID) by IP, category 
 | stats values(*) AS * by IP DeviceIDnew
 | eval compare = if(DeviceIDlater=DeviceIDnew, 0, 1)
 | table IP, DeviceIDlater, DeviceIDnew, compare

Hope this helps ...

cheers, MuS

Re: comparing multivalue fields

harishalipaka — Mon, 08 Apr 2019 10:57:07 GMT

@stevesmith08

try like this | eval compare = if(match(DeviceIDlater,DeviceIDnew), 0, 1)