Splunk Search

Compare two lists of values, not on the same row

Tim00
Explorer

I have a query which runs once a day and which produces a list of all countries a user has visited over the last 30 days.  Below is an example of this list which I call 'Country_hist':

Screenshot 2021-02-15 114145.jpg

I have another query which runs far more often, and which generates a list of all countries a user has visited over the past 24 hours. I want to compare the list of countries from this query with the countries on the  'Country_hist'-list. I added a screenshot below to show you what I mean. On the left side is a column with the countries a user visited over the past 24 hours, on the right side we have to history columns:

Screenshot 2021-02-15 1141452.jpg

I then do a check if items in the left column are also in the right column: has the user visited a country in the last 24 hours which he has not visited before over the past 30 days.

My problem is that both lists are not the same. In the screenshot above please look at the location of the cursor: in the left hand column Japan and South Korea are not on the same row (separated by what seems to be an enter), while they are on the same row in the right hand column.

So the left column contains:

Japan
South Korea

 

And the right column contains:

Japan South Korea
 

 

Currently I use this function to compare the two columns:

| eval check=if(isnull(Country_hist), 0, if(like(Country_hist, "%"+$Country_lst1$+"%"), 0, 1))

This returns a 1 in the example above, but I would want it to return a 0 (since the visited countries are the same). How can I fix this, so Splunk understands that values are the same even if they are on different rows?

Thanks!

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try mvexpand Country_lst1 before your comparison then when check=1 it is new - you will have multiple events but you could filter out the known countries (check = 0) and rejoin the events with a stats command

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Your historic list has concatenated the values into a single string, whereas your daily has multi-values in the field. You should probably change your historic list so that it maintains the multi-values so that South Korea is one item rather than two words.

If you don't need to distinguish different countries, and just detect if there is any difference, then you could just mvjoin the daily list before comparing to the historic list (assuming both lists are in alphabetical order)

 

| eval check=if(isnull(Country_hist), 0, if(Country_hist=mvjoin(Country_lst1," "), 0, 1))

 

0 Karma

Tim00
Explorer

Thanks for your quick reply!

The historic list also contains multi-values when I run the query without saving it to an outputlookup. Saving it as an outputlookup changes the multi-values to a single string.  I don't see an alternative to saving the historic list as an outputlookup, since my company has many employees worldwide and it would take FAR to long to run the historic query more then once a day.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

So is converting (joining) the daily list into a single string sufficient for you to detect that there are differences or do you need to identify which countries have been added?

0 Karma

Tim00
Explorer

If possible I would like to know which countries have been added.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Which version of splunk are you using?

0 Karma

Tim00
Explorer

We have version 7.3.6. 

We are planning to upgrade to 8.x, but considering the size of my company an upgrade like that is not something that's easily done.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Version 8.0 has mvmap which would make your life easier 🙂

0 Karma

Tim00
Explorer

Unfortunately it will be months before we upgrade...  🙁

Is there a way to do this in 7.3.x?

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try mvexpand Country_lst1 before your comparison then when check=1 it is new - you will have multiple events but you could filter out the known countries (check = 0) and rejoin the events with a stats command

Tim00
Explorer

Thank you very much, this solves the issue completely!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...