Splunk Search

Split multivalue cell and duplicate values

dcubaz91
New Member

 

 

 

 

| eval logMsgTimestampInit = logMsgTimestamp
| eval ID_SERVICE= mvappend(ID_SERVICE_1,ID_SERVICE_2) , TYPE= mvappend(TYPE1,TYPE2)
| table ID_SERVICE TYPE

 

 

 

 

ID_SERVICETYPETIME
asd232mechanic_2342023-12-01 08:45:00
afg567hydraulic_433 
   
cvf455hydraulic_7872023-12-01 08:41:00
   
bjf347mechanic_3432023-12-01 08:40:00

 

Hi Dears, I have the following issue, exists some cells (like in red) that is appearing with 02 values  per cell, like the column ID_SERVICE, this is why the payload is containing 02 service id in the same message.  

What I need? I need split this cells everytime it occurs , I tried to use mvexpand but unfortunately it causes a mess in the table.

When I try to use mvexpand it duplicates the rows and for each value in the first colum creates  another row

 

 

 

 

...
| query_search
| mvexpand ID_SERVICE
| mvexpand TYPE
| table ID_SERVICE TYPE TIME

 

 

 

 

ID_SERVICETYPETIME
asd232mechanic_2342023-12-01 08:45:00
asd232hydraulic_4332023-12-01 08:45:00 
   
afg567mechanic_2342023-12-01 08:45:00
afg567hydraulic_4332023-12-01 08:45:00 
   
cvf455hydraulic_7872023-12-01 08:41:00
   
bjf347mechanic_3432023-12-01 08:40:00

 

Since the 01 row (in red) shares the same timestamp (TIME colum) I would like to split every value in a row and copy the same timestamp for both values and the desired output is like follows below:

ID_SERVICETYPETIME
asd232mechanic_2342023-12-01 08:45:00
   
afg567hydraulic_4332023-12-01 08:45:00
   
cvf455hydraulic_7872023-12-01 08:41:00
   
bjf347mechanic_3432023-12-01 08:40:00

 

Please, help me.

Labels (2)
0 Karma

dcubaz91
New Member

@PickleRick  it didnt work too 😞

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Using the mvexpand command twice breaks any association between the values.  Instead, combine the fields, use mvexpand, then break them apart again.

| eval logMsgTimestampInit = logMsgTimestamp
| eval ID_SERVICE= mvappend(ID_SERVICE_1,ID_SERVICE_2) , TYPE= mvappend(TYPE1,TYPE2)
| eval pair = mvzip(ID_SERVICE, TYPE)
| mvexpand pair
| eval ID_SERVICE = mvindex(pair,0), TYPE = mvindex(pair, 1)
| table ID_SERVICE TYPE

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

dcubaz91
New Member

Hi , unfortunately it didnt work 😞 . Appeared as follows:

ID_SERVICE                                                               SERVICE_TYPE            TIMESTAMP

id_service_value1,servicetype_value1    <blank_value>                   <timestamp_ok>

id_service_value2,servicetype_value2    <blank_value>                   <timestamp_ok>

 

The timestamp was duplicated successfully, the values were broken as expected but they added a 'comma' , colon. and left the next column blank. 

Additionally, the mvexpand added considerably time for execution, it was performing really fast, and the performance decreased :(.

Even that I appreciate your time and response for helping me @richgalloway !

Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

I left out an important step.  Please try my revised answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma

PickleRick
SplunkTrust
SplunkTrust

To add to @richgalloway 's answer - if you do

| stats values(field1) as f1 values(field2) by f3

(Or list() instead of values()) you have absolutely no guarantee that your first value in f1 and first value in f2 are from the same event!

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...