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)
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.
| 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
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.
1. \22.214.171.124\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 could be in the middle or end of X as well and X can include spaces, hyphens etc.
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"