Match Substring in Multivalue field and return only the matched substring values

[vn_g]

ReconnectedTime	ReconnectedDetails
2021-02-02T16:46:19.000	2021-02-02T08:54:48.000|viceusr|0xA310B|BEK-329999910922|11.188.92.6 2021-02-02T09:29:59.000|shuani|0xF2C223|NTIC4|1.273.6.189 2021-02-02T16:46:19.000|scrmp_install|0x4216DA|GLB163|21.1.218.15
2021-02-02T08:54:48.000 2021-02-02T09:29:59.000	2021-02-02T08:54:48.000|viceusr|0xA310B|BEK-329999910922|11.188.92.6 2021-02-02T09:29:59.000|shuani|0xF2C223|NTIC4|1.273.6.189 2021-02-02T16:46:19.000|scrmp_install|0x4216DA|GLB163|21.1.218.15

Both ReconnectedTime and ReconnectedDetails are multivalue fields. In each event the "ReconnectedTime" value [ substring which needs to be valuated ] exists in "ReconnectedDetails" then only the matched substring values of ReconnectedDetails should be the final Ouput.

ReconnectedTime	ReconnectedDetails
2021-02-02T16:46:19.000	2021-02-02T16:46:19.000|scrmp_install|0x4216DA|GLB163|21.1.218.15
2021-02-02T08:54:48.000 2021-02-02T09:29:59.000	2021-02-02T08:54:48.000|viceusr|0xA310B|BEK-329999910922|11.188.92.6 2021-02-02T09:29:59.000|shuani|0xF2C223|NTIC4|1.273.6.189

[manjunathmeti]

hi @vn_g ,

Try this:

<your_base_search>
| eval ReconnectedTime_orig=ReconnectedTime 
| nomv ReconnectedTime_orig
| mvexpand ReconnectedTime 
| mvexpand ReconnectedDetails 
| where match(ReconnectedDetails, ReconnectedTime) 
| stats values(ReconnectedTime) as ReconnectedTime values(ReconnectedDetails) as ReconnectedDetails by ReconnectedTime_orig 
| fields ReconnectedTime, ReconnectedDetails

[vn_g]

No the output is not as expected. All 3 values of "Reconnected Details" are being displayed for each "ReconnectedTime". Only the sub string value of ReconnectedTime in Reconnected Details should be displayed for Reconnected Details.

[manjunathmeti · Accepted solution]

ok, try this if you don't want to group: 

<your_base_search>
| mvexpand ReconnectedTime 
| mvexpand ReconnectedDetails 
| where match(ReconnectedDetails, ReconnectedTime) 
| fields ReconnectedTime, ReconnectedDetails