Splunk Search

Match field values of two fields

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

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

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

Esteemed Legend

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

0 Karma

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

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

Hi,

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

0 Karma

New Member

Values of each Y should be compared to all X.

0 Karma

Esteemed Legend

That is what my solution does.

0 Karma

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

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

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