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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...