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!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

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

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...