Splunk Search

Changing rows to columns and maintaining association

_Mauro_Costa_
Explorer

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

_Mauro_Costa__1-1621247350392.png

I want this:

_Mauro_Costa__2-1621247622863.png

Can someone help me?

Labels (1)
0 Karma
1 Solution

ITWhisperer
Legend
| 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 *

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@_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:

 

 

| makeresults 
| eval d= "id=3131202 ,pa_nome=\"MAC Address\",ipa_valor=\"D0765\"|id=3131266 ,pa_nome=\"ID De Projecto,Serial Number\",ipa_valor=\"PCW,CNJ\"|id=3131780 ,pa_nome=\"Serial Number,ID De Projecto\",ipa_valor=\"CNJ,PCW\"
    " | eval d=split(d,"|") 
| mvexpand d | eval _raw=d | kv | fields - d, _raw
| eval ipa_valor=split(ipa_valor,","),pa_nome=split(pa_nome,",")
| rename comment as "Upto Now is sample data only" 
| 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

 

 

 

Thanks
KV
▄︻̷̿┻̿═━一

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

0 Karma

ITWhisperer
Legend
| 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 *

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Try

|eval {pa_nome}=ipa_valor
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.