comparing multivalue fields

[stevesmith08]

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!

[harishalipaka]

@stevesmith08

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

[MuS]

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