Splunk Search

How to create a new field with values in existing field based on the values in other field.

allan_newton
Path Finder

Hi,
I'm new to splunk and seek your help in achieving in a functionality.

My log goes something like this,

time=12/04/2013 12:00:36, login_id=1, head_key_value=124, txn_dur=12.54, txn_status=success
time=12/04/2013 12:02:46, login_id=2, head_key_value=232, txn_dur=6.36, txn_status=success
time=12/04/2013 14:36:11, login_id=3, head_key_value=221, txn_dur=19.23, txn_status=failure

Now I want to display a table with three fields head_key_value_for_1 (values for head_key_value whose login_id=1), head_key_value_for_2 (values for head_key_value whose login_id=2), and head_key_value_for_3 (values for head_key_value whose login_id=3).

Please help me.

Tags (2)
1 Solution

Gilberto_Castil
Splunk Employee
Splunk Employee

Splunk will create fields dynamically using the eval command. You may also pair the eval command with an if condition. Take this for example:

sourcetype="answers-1372957739" | eval head_key_value_for_1=if(login_id=="1",head_key_value,"NULL")

However, that is highly inefficient because you’d have to hard-code each one of the possible values for the login_id into a giant expression.






The solution here is to create the fields dynamically, based on the data in the message. There is a bit magic to make this happen cleanly. Here is the process:

Group the desired data values in head_key_value by the login_id

sourcetype="answers-1372957739" | stats list(head_key_value) AS head_key_value by login_id 

Dynamically create the field that will identify the desired head_key_value with the corresponding login_id:

| eval header="head_key_value_for_".login_id 

Remove the unnecessary data to match the report exactly as described in this question:

| fields - login_id 

Tranform the tabular data, where the new head_key_value_for_{no} header is the column and the associated value are the values for the head_key_value

| eval no_op=" " | xyseries no_op header head_key_value | fields - no_op

All together you will get something like this:

  sourcetype="answers-1372957739"
| stats list(head_key_value) AS head_key_value by login_id 
| eval header="head_key_value_for_".login_id 
| fields - login_id 
| eval no_op=" " 
| xyseries no_op header head_key_value 
| fields - no_op

alt text



I hope this is what you were trying to accomplish.

--gc

View solution in original post

Gilberto_Castil
Splunk Employee
Splunk Employee

Splunk will create fields dynamically using the eval command. You may also pair the eval command with an if condition. Take this for example:

sourcetype="answers-1372957739" | eval head_key_value_for_1=if(login_id=="1",head_key_value,"NULL")

However, that is highly inefficient because you’d have to hard-code each one of the possible values for the login_id into a giant expression.






The solution here is to create the fields dynamically, based on the data in the message. There is a bit magic to make this happen cleanly. Here is the process:

Group the desired data values in head_key_value by the login_id

sourcetype="answers-1372957739" | stats list(head_key_value) AS head_key_value by login_id 

Dynamically create the field that will identify the desired head_key_value with the corresponding login_id:

| eval header="head_key_value_for_".login_id 

Remove the unnecessary data to match the report exactly as described in this question:

| fields - login_id 

Tranform the tabular data, where the new head_key_value_for_{no} header is the column and the associated value are the values for the head_key_value

| eval no_op=" " | xyseries no_op header head_key_value | fields - no_op

All together you will get something like this:

  sourcetype="answers-1372957739"
| stats list(head_key_value) AS head_key_value by login_id 
| eval header="head_key_value_for_".login_id 
| fields - login_id 
| eval no_op=" " 
| xyseries no_op header head_key_value 
| fields - no_op

alt text



I hope this is what you were trying to accomplish.

--gc

allan_newton
Path Finder

Can't beat this explanation!!
bow!!

Thank you.

Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Can’t Make It to Boston? Stream .conf25 and Learn with Haya Husain

Boston may be buzzing this September with Splunk University and .conf25, but you don’t have to pack a bag to ...

Splunk Lantern’s Guide to The Most Popular .conf25 Sessions

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

Unlock What’s Next: The Splunk Cloud Platform at .conf25

In just a few days, Boston will be buzzing as the Splunk team and thousands of community members come together ...