Splunk Search

How to convert row value result into a column?

svm157
Loves-to-Learn Lots

Hi, I am very new to splunk and need help for the below situation. I am having two columns as below

Row        Column 1                                  Column2

1.        Value:dataclass                      Internal
              Value:url                                     http://****.com/****
              Value:application-name      ABC
              Value:daily-backup                Y
2.        Value:dataclass                      Internal
              Value:url                                     http://*n**.com/****
              Value:application-name      XPZ
              Value:daily-backup                N
Now I need to convert these row value of Column1 as Column and Column 2 value as their row value like below.
Dataclass                    URL                            Application-Name                                   Daily-Backup
Internal               http://****.com/****                ABC                                                           Y
Internal               http://*n**.com/****                XPZ                                                          N
 
Thanks in advance. 
 
SVM
Labels (3)
Tags (2)
0 Karma

fredclown
Builder

I think something like this should work. Let's start by creating some sample data.

| makeresults  count=5
| eval column1 = split("Value:dataclass,Value:url,Value:application-name,Value:daily-backup", ",")
| eval column2 = split("Internal,http://www.google.com/,ABC,Y", ",")
| fields - _time

 

This next section is what does the magic.

| streamstats count as row ``` assign row numbers for later```
| eval columns = mvzip(column1, column2) ```combine the two cols into one```
| mvexpand columns ```create events for each item in the mv field called columns```
| eval field = replace(mvindex(split(columns, ","), 0), "Value:", "") ```split the columns field into field```
| eval value = mvindex(split(columns, ","), 1)```split the columns field into value```
| xyseries row field value ```pivot the table```
| fields - row column1 column2 ```clean up extranious columns```

 

Put together it looks like this.

| makeresults  count=5
| eval column1 = split("Value:dataclass,Value:url,Value:application-name,Value:daily-backup", ",")
| eval column2 = split("Internal,http://www.google.com/,ABC,Y", ",")
| fields - _time
```The stuff above is just to create some sample data ... the stuff below should get you what you want```
| streamstats count as row
| eval columns = mvzip(column1, column2)
| mvexpand columns
| eval field = replace(mvindex(split(columns, ","), 0), "Value:", "")
| eval value = mvindex(split(columns, ","), 1)
| xyseries row field value
| fields - row column1 column2
0 Karma

fredclown
Builder

Does this meet your requirements?

0 Karma

fredclown
Builder

Does this work for what you are asking?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@svm157 

If Column 1 and Column 2 are multivalue fields then you can try the below search.

YOUR_SEARCH
| eval Dataclass=mvindex(Column_2,0),URL=mvindex(Column_2,1),"Application-Name"=mvindex(Column_2,2),"Daily-Backup"=mvindex(Column_2,3)
| table Column_1 Column_2 Dataclass URL "Application-Name" "Daily-Backup"

 

My Sample Search :

 

| makeresults 
| eval Column_1="Value:dataclass|Value:url|Value:application-name|Value:daily-backup", Column_2="Internal|http://****.com/****|ABC|Y", Column_1=split(Column_1,"|"), Column_2=split(Column_2,"|")
| rename comment as "upto this is sample data" 
| eval Dataclass=mvindex(Column_2,0),URL=mvindex(Column_2,1),"Application-Name"=mvindex(Column_2,2),"Daily-Backup"=mvindex(Column_2,3)
| table Column_1 Column_2 Dataclass URL "Application-Name" "Daily-Backup"

 

I hope this will help you.

If you have events with another pattern then share _raw with us.

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

 

0 Karma

svm157
Loves-to-Learn Lots

 

Hi @kamlesh_vaghela ,

Appreciate your quick response. 

There are several similar rows in column1 and column 2. So I need something which can populate the value dynamically. When I am using your syntax, it is giving me result like below. So no value coming in those 4 columns.

Row        Column 1                                  Column2                    Dataclass    URL            Application-Name Daily-Backup

1.        Value:dataclass                      Internal
              Value:url                                     http://****.com/****
              Value:application-name      ABC
              Value:daily-backup                Y
2.        Value:dataclass                      Internal
              Value:url                                     http://*n**.com/****
              Value:application-name      XPZ
              Value:daily-backup                N
 
 
0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...