topic Changing rows to columns and maintaining association in Splunk Search

Changing rows to columns and maintaining association

_Mauro_Costa_ — Mon, 17 May 2021 10:39:16 GMT

Hello,
I have a table of items and I need to convert the results in the rows "pa_name" and "pa_valor" to columns and keep the relacion of each one.

An example, I have this:

index="items" id IN (3438776 3131202 3438780)
| stats latest(pa_nome) AS pa_nome
latest(ipa_valor) AS ipa_valor
BY id
| makemv delim=";" pa_nome
| makemv delim=";" ipa_valor

I want this:

Can someone help me?

Re: Changing rows to columns and maintaining association

renjith_nair — Mon, 17 May 2021 10:57:47 GMT

Try

|eval {pa_nome}=ipa_valor

Re: Changing rows to columns and maintaining association

ITWhisperer — Mon, 17 May 2021 11:00:54 GMT

| makeresults | eval _raw="id,pa_nome,ipa_valor 3131202,MAC Address,D0765812D877 3438776,ID de Projeto;Serial Number,PCW.609.20;CNJDJSS4X4 3438780,Serial Number;ID de Projeto,CNJDJSS9GS;PCW.609.20" | multikv forceheader=1 | fields - _* linecount | makemv delim=";" pa_nome | makemv delim=";" ipa_valor | streamstats count as row | mvexpand pa_nome | streamstats count as index by row | eval ipa_valor=mvindex(ipa_valor,index-1) | eval {pa_nome}=ipa_valor | fields - index ipa_valor pa_nome | stats values(*) as * by row | fields - row | table id *

Re: Changing rows to columns and maintaining association

kamlesh_vaghela — Mon, 17 May 2021 12:08:11 GMT

@_Mauro_Costa_

You ca try this also.

YOUR_SEARCH | eval t=mvzip(ipa_valor,pa_nome) |mvexpand t | eval ipa_valor=mvindex(split(t,","),0),pa_nome=mvindex(split(t,","),1) | chart values(ipa_valor) over id by pa_nome

Sample Search:

Thanks
KV
▄︻̷̿┻̿═━一

If this reply helps you, an upvote would be appreciated.