How to get rows into columns from rows values?

[siriosus]

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

[kamlesh_vaghela · Accepted solution]

@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.

[siriosus]

@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

[kamlesh_vaghela]

Glad to help you @siriosus 

Happy Splunking

🙂