topic Re: How to get rows into columns from rows values? in Splunk Search

How to get rows into columns from rows values?

siriosus — Wed, 21 Sep 2022 16:44:19 GMT

Hello dear Splunk experts!

I've stuck with one search and can't figure how to do this.

Did a lot of searching here on this friendly community but couldn't find the answer despite that saw a lot of similar tasks with solutions.

I have a dataset like this:

sg1	sg1_approval	sg2	sg2_approval	sg3	sg3_approval
value1	approved	value2	not approved	value3	delayed

And I want to get something like this:

sg	sg_value	sg_approval
sg1	value1	approved
sg2	value2	not approved
sg3	value3	delayed

What is the best way to achieve this?

I've found some examples around transpose, eval or stats functions but these didn't solve my task completely.

And to add a bit of complexity to this task - how to calculate sum of particular cells in rows with particular values of columns. I have this dataset:

sg1	sg1_approval	sg1_now	sg2	sg2_approval	sg2_now	sg3	sg2_now	sg3_approval
value1	approved	4	value2	not approved	2	value3	5	delayed
value2	not approved	3	value3	approved	5	value4	1	approved

And want to extract it as:

sg_approval	sg_now
approved	10
not approved	5
delayed	5

So that sg_now column contains sum of all values in sg1_now, sg2_now and sg3_now sorted by sg1_approval, sg2_approval and sg3_approval.

Thank you very much in advance!

Regards

Re: How to get rows into columns from rows values?

kamlesh_vaghela — Wed, 21 Sep 2022 18:01:41 GMT

@siriosus

I have designed some sample searches for you.

for your first scenario please try this.

YOUR_BASE_SEARCH | foreach *_* [ eval sg= mvappend(sg,"<<MATCHSEG1>>"), sgvalue=mvappend(sgvalue,'<<MATCHSEG1>>'), sgapproval= mvappend(sgapproval,<<FIELD>>) ] | eval t = mvzip(mvzip(sg,sgvalue),sgapproval) | stats count by t | eval sg=mvindex(split(t,","),0),sg_value=mvindex(split(t,","),1),sg_approval=mvindex(split(t,","),2) | fields - t,count

My Sample Search :

| makeresults | eval _raw="sg1 sg1_approval sg2 sg2_approval sg3 sg3_approval value1 approved value2 not approved value3 delayed value11 approved1 value21 not approved1 value31 delayed1" | multikv forceheader=1 | table sg1 sg1_approval sg2 sg2_approval sg3 sg3_approval | rename comment as "upto this is sample data" | foreach *_* [ eval sg= mvappend(sg,"<<MATCHSEG1>>"), sgvalue=mvappend(sgvalue,'<<MATCHSEG1>>'), sgapproval= mvappend(sgapproval,<<FIELD>>) ] | eval t = mvzip(mvzip(sg,sgvalue),sgapproval) | stats count by t | eval sg=mvindex(split(t,","),0),sg_value=mvindex(split(t,","),1),sg_approval=mvindex(split(t,","),2) | fields - t,count

Screen

For your second scenario

YOUR_BASE_SEARCH | foreach *_a* [ eval sgapproval= mvappend(sgapproval,<<FIELD>>), sgnow= mvappend(sgnow,'<<MATCHSEG1>>_now') ] | eval t = mvzip(sgapproval,sgnow) | stats count by t | eval sg_approval=mvindex(split(t,","),0),sg_now=mvindex(split(t,","),1) |stats sum(sg_now) as sg_now by sg_approval

My Sample Search :

| makeresults | eval _raw="sg1 sg1_approval sg1_now sg2 sg2_approval sg2_now sg3 sg3_now sg3_approval value1 approved 4 value2 not approved 2 value3 5 delayed value2 not approved 3 value3 approved 5 value4 1 approved" | multikv forceheader=1 | table sg1 sg1_approval sg1_now sg2 sg2_approval sg2_now sg3 sg3_approval sg3_now | rename comment as "upto this is sample data" | foreach *_a* [ eval sgapproval= mvappend(sgapproval,<<FIELD>>), sgnow= mvappend(sgnow,'<<MATCHSEG1>>_now') ] | eval t = mvzip(sgapproval,sgnow) | stats count by t | eval sg_approval=mvindex(split(t,","),0),sg_now=mvindex(split(t,","),1) |stats sum(sg_now) as sg_now by sg_approval

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: How to get rows into columns from rows values?

siriosus — Thu, 22 Sep 2022 06:10:22 GMT

@kamlesh_vaghela

Thank you very much for your efforts in helping with this!

Your examples do exactly what I was trying to achieve.

I'm new to Splunk and see that sky is the limit to SPL searching capabilities and transformations of data it is able to perform, but it requires some practice to master.

Your solution is great instance how exact examples (which are not always presented and obvious in the documentation) can help to understand SPL for newbies.

Thanks again!

Regards

Re: How to get rows into columns from rows values?

kamlesh_vaghela — Thu, 22 Sep 2022 06:43:01 GMT

Glad to help you @siriosus

Happy Splunking

🙂