Splunk Search

comparing multivalue fields

stevesmith08
Explorer

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:
alt text
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!

0 Karma

harishalipaka
Motivator

@stevesmith08

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

Thanks
Harish
0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...