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)

*********All the Best***********
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!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...