How to get same values from mutlivalue field?

[dhirendra761]

Hi, 

 We have below problem data in lookup: 

pan	assestId	item_deviceId	phoneNumber	imeID
11023	ass#ABC1#man6558962f asst#ABC1#man827631e ite#0#man76451627ahdgs ite#0#man76451627ahd75	ite#0#man76451627ahdgs	8763173699	123456789
11023	ass#ABC1#man6558962f asst#ABC1#man827631e ite#0#man76451627ahdgs ite#0#man76451627ahd75	ite#0#man76451627ahd75	8736628187	987654321

Now we require new field "Mobile_DeviceId" from "assestId"  for identical row.

As per below splunk table:

pan	assestId	item_deviceId	phoneNumber	imeID	Mobile_DeviceId
11023	ass#ABC1#man6558962f asst#ABC1#man827631e ite#0#man76451627ahdgs ite#0#man76451627ahd75	ite#0#man76451627ahdgs	8763173699	123456789	ass#ABC1#man6558962f
11023	ass#ABC1#man6558962f asst#ABC1#man827631e ite#0#man76451627ahdgs ite#0#man76451627ahd75	ite#0#man76451627ahd75	8736628187	987654321	asst#ABC1#man827631e

Is it possible from SPL?? Please help me to create SPL.

My query is:

| inputlookp abc.csv
| table pan assestId item_deviceId phoneNumber imeID
| eval Mobile_DeviceId=split(assestId," ")|mvexpand Mobile_DeviceId| search Mobile_DeviceId=ass#* 

[dhirendra761]

Thank you so much @ITWhisperer

You are awesome.

[ITWhisperer · Accepted solution]

How do you identify which assestId is required as the Mobile_DevideId - assuming that there are an even number of assestIds and the device id in the first part of the list corresponds to the item in the second part of the list e.g. 1st matches with 3rd and 2nd matches with 4th (as in your example), then you could try something like this

| inputlookp abc.csv
| table pan assestId item_deviceId phoneNumber imeID
| eval Mobile_DeviceId=split(assestId," ")
| eval Mobile_DeviceId=mvindex(Mobile_DeviceId,mvfind(Mobile_DeviceId, item_deviceId)%(mvcount(Mobile_DeviceId)/2))

[dhirendra761]

Hi @ITWhisperer ,

What change required  in query if we want to swap the value of Mobile_DeviceId?? without applying SORT command

[ITWhisperer]

What do you mean by swap? What result are you trying to achieve?

[dhirendra761]

Swap means switch the value in last column.

[dhirendra761]

As per below splunk table:

pan	assestId	item_deviceId	phoneNumber	imeID	Mobile_DeviceId
11023	ass#ABC1#man6558962f asst#ABC1#man827631e ite#0#man76451627ahdgs ite#0#man76451627ahd75	ite#0#man76451627ahdgs	8763173699	123456789	asst#ABC1#man827631e
11023	ass#ABC1#man6558962f asst#ABC1#man827631e ite#0#man76451627ahdgs ite#0#man76451627ahd75	ite#0#man76451627ahd75	8736628187	987654321	ass#ABC1#man6558962f

[ITWhisperer]

| inputlookp abc.csv
| table pan assestId item_deviceId phoneNumber imeID
| eval Mobile_DeviceId=split(assestId," ")
| eval Mobile_DeviceId=mvindex(Mobile_DeviceId,(mvcount(Mobile_DeviceId)/2)-(mvfind(Mobile_DeviceId, item_deviceId)%(mvcount(Mobile_DeviceId)/2)))

[dhirendra761]

Thank You @ITWhisperer  for your prompt support. 

🙂