Joining data from same index on single field value and multifield field

[MarekKrzak]

Hi I'm trying to join data from same index but with different marker field and multiple values in second index. Example of rows:

TS=06/22/2021 08:50:39:390|Type=A|Ids=550
TS=06/22/2021 08:51:39:390|Type=B|Ids=495,550,698

What I want is merge record Type=A with Record Type=B and get how much time passed (so TS from TypeB - TS from TypeA). Basically records with Type=A will always have one Ids, and with Type=B can have 1 or more Ids.

Any ideas what could be the best? selfjoin in this case is not possible as the Type=B is multivalve field .

[kamlesh_vaghela]

@MarekKrzak 

Can you please try this?

YOUR_SEARCH | extract pairdelim="|" kvdelim="=" 
| search Type="A"
| join Type Ids [YOUR_SEARCH | extract pairdelim="|" kvdelim="=" | search Type="B"
| eval Ids=split(Ids,",") | eval Type="A" | rename TS as TS1 
| mvexpand Ids] 
| eval "Time Passed (In Sec)"= round(strptime(TS1,"%m/%d/%Y %H:%M:%S.%3N") - strptime(TS,"%m/%d/%Y %H:%M:%S.%3N")),"Time Passed"=tostring('Run Time (In Sec)',"duration")

My Sample Search :

| makeresults | eval raw="TS=06/22/2021 08:50:39:390|Type=A|Ids=550&&TS=06/22/2021 08:51:39:390|Type=B|Ids=495,550,698" | eval raw=split(raw,"&&") 
| mvexpand raw | rename raw as _raw | extract pairdelim="|" kvdelim="=" 
| search Type="A"
| join Type Ids [| makeresults | eval raw="TS=06/22/2021 08:50:39:390|Type=A|Ids=550&&TS=06/22/2021 08:51:39:390|Type=B|Ids=495,550,698" | eval raw=split(raw,"&&") 
| mvexpand raw | rename raw as _raw | extract pairdelim="|" kvdelim="=" | search Type="B"
| eval Ids=split(Ids,",") | eval Type="A" | rename TS as TS1 
| mvexpand Ids] 
| eval "Time Passed (In Sec)"= round(strptime(TS1,"%m/%d/%Y %H:%M:%S.%3N") - strptime(TS,"%m/%d/%Y %H:%M:%S.%3N")),"Time Passed"=tostring('Run Time (In Sec)',"duration")

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.