Compare two lists of values, not on the same row

[Tim00]

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':

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:

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!

[ITWhisperer]

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))

[Tim00]

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.

[ITWhisperer]

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?

[Tim00]

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

[ITWhisperer]

Which version of splunk are you using?

[Tim00]

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.

[ITWhisperer]

Version 8.0 has mvmap which would make your life easier 🙂

[Tim00]

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

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

[ITWhisperer · Accepted solution]

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]

Thank you very much, this solves the issue completely!