topic Re: Splitting one row into multiple rows. in Splunk Search

Splitting one row into multiple rows.

robertlynch2020 — Fri, 11 Apr 2025 16:46:39 GMT

How do I split the below data into 2 lines? I need to run stats on the tables, but when they are together the answers are not correct.

I have the following SPL form some complex data and I have discovered that in some small cases there can be 2 lines coming out like this.

host="MARKET_RISK_PDT_V2" index="murex_logs" sourcetype="Market_Risk_DT" "**mr_strategy**" "typo_Collar" | search "resourceSpans{}.resource.attributes{}.value.stringValue"="*" | spath resourceSpans{}.scopeSpans{}.spans{}.attributes{} output=attributes | dedup attributes | stats count by attributes _time | spath input=attributes | eval X_{key}=coalesce('value.doubleValue', 'value.stringValue') | stats list(X_*) as * by _time | table mr_batch_compute_cpu_time mr_batch_compute_time mr_batch_load_cpu_time mr_batch_load_time mr_strategy

The data is very large. I will put it up, but I think I have done 99% of the SPL. It's just this last case that is catching me.

Re: Splitting one row into multiple rows.

ITWhisperer — Fri, 11 Apr 2025 18:17:13 GMT

| eval row=mvrange(0,2) | mvexpand row | foreach * [| eval <<FIELD>>=mvindex(<<FIELD>>,row)]

Re: Splitting one row into multiple rows.

PickleRick — Sat, 12 Apr 2025 07:54:37 GMT

While @ITWhisperer 's solution is a neat trick, I'd rethink the search.

1. You're searching for "**mrstrategy**". That's gonna be slow.

2. First you're using automatic json extraction, then you call spath.

3. Always be vigilant around "dedup" command. You're deduping on attributes then statsing on attributes and _time. You will only ever have one _time per attributes. And dedup will move processing to SH tier.

4. I have a hunch that you have some duplicate data which you want to get rid of in search time. Maybe it's worth reworking your ingestion process so you're not wasting license?

5. Unfortunately, as you must have noticed already - this is a very ugly data format (from Splunk's point of view). This whiole "keyname=key1,value=something" schema is very inconvenient for searching and processing since you firstly have to read, parse, and interpret all events to get to the "contents". So now you're bending over backwards to do something which should be easy as just writing a simple filter condition. Are you sure you don't have someone in your org to sit with and have a chat about the data format? Or about the ingestion process - maybe it's worth setting up something that wil, transform your data into something more reasonable?

Re: Splitting one row into multiple rows.

yuanliu — Sun, 13 Apr 2025 09:24:19 GMT

In addition to the problems @PickleRick points out, the SPL ignored a fundamental design in the dataset. The use of stats without group by mr_batchId begs the question: What is the logic to say which values should form ONE line as opposed to another? In fact, whenever you find yourself "needing" to use stats by _time in Splunk, you ought to tell yourself that some logic is probably wrong.

The first troublesome command is | spath resourceSpans{}.scopeSpans{}.spans{}.attributes{} output=attributes . Here, you bypass several arrays of arrays to only focus on resourceSpans{}.scopeSpans{}.spans{}.attributes{}. Unless you are absolutely certain about the uniqueness of this path, the prudent strategy is to fully process handle each array. In your case, the next command, | dedup attributes, indicates that there is no such certainty. But uniqueness is not the big problem here. The real problem is: the path resourceSpans{}.scopeSpans{}.spans{} is the key to your developer/vendor's data design. Each value of resourceSpans{}.scopeSpans{}.spans{} contains a unique mr_batchId that is key to distinguish dataset. If you want to perform stats, perform stats against resourceSpans{}.scopeSpans{}.spans{}.

So, step one is to fully mvexpand into this path:

host="MARKET_RISK_PDT_V2" index="murex_logs" sourcetype="Market_Risk_DT" "**mr_strategy**" "typo_Collar" "resourceSpans{}.resource.attributes{}.value.stringValue"="*" | fields - resourceSpans{}.* | spath path=resourceSpans{} | mvexpand resourceSpans{} | spath input=resourceSpans{} path=scopeSpans{} | fields - resourceSpans{} | mvexpand scopeSpans{} | spath input=scopeSpans{} path=spans{} | fields - scopeSpans{} | mvexpand spans{}

The above does not address the efficiency problem with **mr_strategy**, but collapses search of "resourceSpans{}.resource.attributes{}.value.stringValue"="*" into index search, which also improves efficiency.

Using your sample data, the above will give 96 spans{} values for a single event. Among the 96, only two are relevant to your final results. So, I would recommend adding

| where match('spans{}', "mr_batchId")

This would give two rows like

spans{}

{"traceId":"e0d25217dd28e57d2db07e06d690428f","spanId":"d6c133764c7891c3","parentSpanId":"dbd5a3ed4854e73f","name":"fullreval_task","kind":1,"startTimeUnixNano":"1744296121513194653","endTimeUnixNano":"1744296126583212823","attributes":[{"key":"market_risk_span","value":{"stringValue":"true"}},{"key":"mr_batchId","value":{"stringValue":"37"}},{"key":"mr_batchType","value":{"stringValue":"Full Revaluation"}},{"key":"mr_bucketName","value":{"stringValue":""}},{"key":"mr_jobDomain","value":{"stringValue":"Market Risk"}},{"key":"mr_jobId","value":{"stringValue":"CONSO_ABAQ | 31/03/2016 | 12"}},{"key":"mr_strategy","value":{"stringValue":"typo_Collar Cap"}},{"key":"mr_uuid","value":{"stringValue":"4405ed87-fbc0-4751-b5b2-41836f1181cc"}},{"key":"mrb_batch_affinity","value":{"stringValue":"CONSO_ABAQ_run_Batch|CONSO_ABAQ|2016/03/31|12_FullReval0_00037"}},{"key":"mr_batch_compute_cpu_time","value":{"doubleValue":2.042433}},{"key":"mr_batch_compute_time","value":{"doubleValue":2.138}},{"key":"mr_batch_load_cpu_time","value":{"doubleValue":2.154398}},{"key":"mr_batch_load_time","value":{"doubleValue":2.852}},{"key":"mr_batch_status","value":{"stringValue":"WARNING"}},{"key":"mr_batch_total_cpu_time","value":{"doubleValue":4.265003}},{"key":"mr_batch_total_time","value":{"doubleValue":5.069}}],"status":{}}

{"traceId":"e0d25217dd28e57d2db07e06d690428f","spanId":"4c8da45757b1ea2a","parentSpanId":"dbd5a3ed4854e73f","name":"fullreval_task","kind":1,"startTimeUnixNano":"1744296126596384480","endTimeUnixNano":"1744296130515095708","attributes":[{"key":"market_risk_span","value":{"stringValue":"true"}},{"key":"mr_batchId","value":{"stringValue":"58"}},{"key":"mr_batchType","value":{"stringValue":"Full Revaluation"}},{"key":"mr_bucketName","value":{"stringValue":""}},{"key":"mr_jobDomain","value":{"stringValue":"Market Risk"}},{"key":"mr_jobId","value":{"stringValue":"CONSO_ABAQ | 31/03/2016 | 12"}},{"key":"mr_strategy","value":{"stringValue":"typo_Non Deliv. Xccy Swap"}},{"key":"mr_uuid","value":{"stringValue":"f6035cef-e661-49bd-8b4c-d8d09da06822"}},{"key":"mrb_batch_affinity","value":{"stringValue":"CONSO_ABAQ_run_Batch|CONSO_ABAQ|2016/03/31|12_FullReval0_00058"}},{"key":"mr_batch_compute_cpu_time","value":{"doubleValue":0.8687239999999999}},{"key":"mr_batch_compute_time","value":{"doubleValue":0.907}},{"key":"mr_batch_load_cpu_time","value":{"doubleValue":2.257638}},{"key":"mr_batch_load_time","value":{"doubleValue":2.955}},{"key":"mr_batch_status","value":{"stringValue":"OK"}},{"key":"mr_batch_total_cpu_time","value":{"doubleValue":3.1801}},{"key":"mr_batch_total_time","value":{"doubleValue":3.917}}],"status":{}}

But for flexibility, I consider this optional.

From here, there are many ways to get to your desired output. Given that you only need mr_batch_compute_cpu_time, mr_batch_compute_time, mr_batch_load_cpu_time, mr_batch_load_time, and mr_strategy, I recommend to directly extract them; however, I strongly recommend adding mr_batchId to the list because that's a critical piece of information for you to corroborate data and validate your calculations.

``` the following line is optional - improves efficiency if these are the only attributes of interest | where match('spans{}', "mr_batchId") ``` | spath input=spans{} path=attributes{} output=attributes | foreach mr_batchId mr_batch_compute_cpu_time mr_batch_compute_time mr_batch_load_cpu_time mr_batch_load_time mr_strategy [eval <<FIELD>> = mvappend(<<FIELD>>, mvmap(attributes, if(spath(attributes, "key") != "<<FIELD>>", null(), spath(attributes, "value")))), <<FIELD>> = coalesce(spath(<<FIELD>>, "doubleValue"), spath(<<FIELD>>, "stringValue"))] | dedup _time mr_batchId ``` the above is key logic. If there is any doubt, you can also use | dedup _time mr_batchId mr_batch_compute_cpu_time mr_batch_compute_time ``` | table _time mr_batchId mr_batch_compute_cpu_time mr_batch_compute_time mr_batch_load_cpu_time mr_batch_load_time mr_strategy

With this, the output will be

_time	mr_batchId	mr_batch_compute_cpu_time	mr_batch_compute_time	mr_batch_load_cpu_time	mr_batch_load_time	mr_strategy
2025-04-12 23:55:21	37	2.042433	2.138	2.154398	2.852	typo_Collar Cap
2025-04-12 23:55:21	58	0.8687239999999999	0.907	2.257638	2.955	typo_Non Deliv. Xccy Swap

There is no need to perform stats against _time.

Re: Splitting one row into multiple rows.

PickleRick — Sun, 13 Apr 2025 11:27:54 GMT

Just for the sake of completness - stats by _time is fairly useful if you manipulate your timestamps (usually by means of bin/bucket).

With raw untouched _time it can be useful if you have several events emmited at the same time (and you can be 100% sure about that) and you have no other unique identifier to mark them by. But this is rather unlikely since separate events, even regarding the same "physical event" usually come from separate sources and are slightly offset in _time.

Re: Splitting one row into multiple rows.

robertlynch2020 — Tue, 15 Apr 2025 09:54:39 GMT

Wow, what a great answer to my issues! I am using this, and I want to thank you very much again. Thanks.

I did not spot the mr_batchID as unique - so i am using it now.

Re: Splitting one row into multiple rows.

robertlynch2020 — Tue, 15 Apr 2025 09:55:57 GMT

That is a wonderful answer, and thanks very much. I did find another issue where I have multiple lines on the same line, so I have accepted another answer - but that very much

Re: Splitting one row into multiple rows.

robertlynch2020 — Tue, 15 Apr 2025 10:03:51 GMT

Thanks for the answers on this. yes you have some good points that i will look into.

The data is distributed Trace from OTel. I am not sure how much I can change, but I will talk to the Developers.

Cheers

Robert

Re: Splitting one row into multiple rows.

PickleRick — Tue, 15 Apr 2025 11:30:56 GMT

Well, sometimes you have to work what you have. Nothing shameful about it 😉

Just be aware that this format might require you to craft your searches much more thoughtfully if you want them to be relatively quick.

For example, since you have this whole keyname=something,value=somethingelse setup, you can't do a simple something=somethingelse search because that field isn't extracted and isn't know until you plow through your data with the foreach command.

But you can limit your initial search results in that case by simply searching for "somethingelse" as search term regardless of where in the event it is. This can hugely improve your search times.