Splunk Search

How to convert row value result into a column?

svm157
Loves-to-Learn

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
Communicator

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
Communicator

Does this meet your requirements?

0 Karma

fredclown
Communicator

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

 

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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...