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
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
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
That is exactly what mine does. Did you even try it?
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
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?
Hi,
Values of each Y should be compared with all X values or just one? or its like table, one to one ?
Values of each Y should be compared to all X.
That is what my solution does.
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"
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
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!