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
SplunkTrust
SplunkTrust
| 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
SplunkTrust
SplunkTrust
| 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 *
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Try

|eval {pa_nome}=ipa_valor
Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...