Splunk Search

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

MarekKrzak
Observer

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 .

Labels (1)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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")

 

Screenshot 2021-06-22 at 10.27.13 PM.png

 

Thanks
KV
▄︻̷̿┻̿═━一

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...