Splunk Search

Match field values of two fields

megha0794
New Member

Hi,

If I have a table 'X' and 'Y' , I want to be able to compare if any individual comma separated value in Y is a part of the values of X (values of Y remain fixed)

X :

abc/xyz.xlsx

bcd/test/test2.xlsx

jhi/jjk.xlsx

Y:
abc,bcd,cda

Here since in the 1st entry, 'abc' in Y matches with a portion of the string in X, it should return the entry. Same goes with the 2nd entry. But as the 3rd has no common values, it needs to be rejected.

Any help would be greatly appreciated.
Regards,
Megha

0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults
| eval X = "abc/xyz.xlsx bcd/test/test2.xlsx jhi/jjk.xlsx"
| makemv X
| mvexpand X
| append [ | makeresults |  eval Y = "abc,bcd,cda" ]
| fields - _*

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eventstats first(Y) AS Y
| where isnotnull(X)
| makemv delim="," Y
| eval Xparts = X
| makemv tokenizer="([A-z,0-9]+)" Xparts

| mvexpand Y
| mvexpand Xparts
| where Xparts==Y
0 Karma

megha0794
New Member

Thankyou @woodcock
The ask is actually to compare all individual values in X with all the individual values in a field of the lookup(in this case Y). And the lookup gets appended over time.

Below is a format of X in our data.

X
1. \11.177.32.12\iris$\DBD Team\DBD_New\Megha\Capacity Utilization September'19 (1).xlsx
2. \TG2PVFS3\Crystal_AM_Shared\2 - Team Specific Docs\01 - Business Process Document Reviews\AA\OneNote Notebooks\AA's Note\links.one

Y
1. \11.177.32.12\iris$
2. \TG2PVFS3\Crystal_AM_Shared

Y could be in the middle or end of X as well and X can include spaces, hyphens etc.

Thanks,
Megha

0 Karma

woodcock
Esteemed Legend

That is exactly what mine does. Did you even try it?

0 Karma

megha0794
New Member

Yes I did. It does match the first example I posted. But doesn't seem to be working on the actual format. Im checking if I can make any changes in the query.

Thanks,
Megha

0 Karma

megha0794
New Member

The issue is that Xparts is splitting in a format which would not exactly match with Y. Would something like a search function in excel work?

0 Karma

gaurav_maniar
Builder

Hi,

Values of each Y should be compared with all X values or just one? or its like table, one to one ?

0 Karma

megha0794
New Member

Values of each Y should be compared to all X.

0 Karma

woodcock
Esteemed Legend

That is what my solution does.

0 Karma

ololdach
Builder

Hi, sure. Try: | makeresults | eval X="abc/xyz.xlsx" | eval Y="abc,bcd,cda" | eval match = ".*".replace(Y,",","|").".*" | where match(X,match) where the new field match holds the regular expression that does the matching. If you compose the Y field yourself, you could compose it with | instead of commas from the start. The reason being that in a regular expression, strings separated by | are options and any of the options matches the regular expression at that point. The "." before and after the options matches any string (even the empty). Hence `".*abc|bcd|cda."` matches any string, that has either abc, bcd or cda as a substring.

Please note that as the first occurance of either option matches and all characters after that first match are basically ignored, the regex also matches multiple occurences of the string like "abc/xxx/xxx/bcd/cda/xxx.xls"

0 Karma

ololdach
Builder

Try: | makeresults | eval X="abc/xyz.xlsx" | eval Y="abc,bcd,cda" | eval match = Y | makemv delim="," match | mvexpand match | eval match_me="%".match."%" | where X like match_me

0 Karma

megha0794
New Member

Hi ,

I do not want break down the Y field, I have merged field values to create the Y field. Is there any way this can be done?

Thanks!

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