Splunk Search

I would like to make custom_fields a table column.

TAmemiya
Explorer

We have imported Json data with the following custom_fields.

{
"id": 100,
"custom_fields":
[{
"id": 1,
"name": "Date",
"value": "2017-12-15"
}, {
"id": 2,
"name": "Apply_Time",
"value": 1.5
}, {
"id": 3,
"name": "Actual_Time",
"value": 0
}],
"created_on": "2017-12-15",
"updated_on": "2017-12-15"
}

I want to make the value of custom_fields look like the table below.

| id | Date | Apply_Time | Actual_Time | created_on | updated_on |
| 100 | 2017-12-15 | 1.5 | 0 | 2017-12-15 | 2017-12-15 |

I tried spath command etc, but I did not solve it.
Thank you for your instruction.

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi
this solution must be adapted to your real solution, especially spath command configuration.
Anyway, try something like this

index=mt_index sourcetype="json_no_timestamp" 
| spath 
| rename custom_fields{}.id AS id2 custom_fields{}.name AS name custom_fields{}.value AS value 
| rex "\{\"id\":\s+(?<id>\d+)," 
| eval 
     id=mvindex(id,0), 
     Date=mvindex(value,0), 
     Apply_Time=mvindex(value,1), 
     Actual_Time=mvindex(value,2), 
     created_on=mvindex(created_on,0), 
     updated_on=mvindex(updated_on,0)
| table id Date Apply_Time Actual_Time created_on updated_on

Bye.
Giuseppe

View solution in original post

0 Karma

niketn
Legend

@TAmemiya, Please try the following run anywhere search which mimics the sample data as per the question. The | makeresults and | eval _raw command before | spath are used to create sample data, you can replace with your base search.

| makeresults
| eval _raw="{
\"id\": 100,
\"custom_fields\":
[{
\"id\": 1,
\"name\": \"Date\",
\"value\": \"2017-12-15\"
}, {
\"id\": 2,
\"name\": \"Apply_Time\",
\"value\": 1.5
}, {
\"id\": 3,
\"name\": \"Actual_Time\",
\"value\": 0
}],
\"created_on\": \"2017-12-15\",
\"updated_on\": \"2017-12-15\"
}"
| spath
| rename custom_fields{}.id as sub_id
| rename "custom_fields{}.*" as "*"
| eval name=mvappend(name,"id","created_on","updated_on")
| eval value=mvappend(value,id,created_on,updated_on)
| eval custom_fields=mvzip(name,value)
| mvexpand custom_fields
| eval custom_fields=split(custom_fields,",")
| eval name=mvindex(custom_fields,0)
| eval value=mvindex(custom_fields,1)
| table name value
| transpose header_field=name column_name=name
| fields - name

Refer to multivalue eval functions in Splunk Documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

TAmemiya
Explorer

Thank you for your reply, Mr. Niketnilay.
I'll try it immediately.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi
this solution must be adapted to your real solution, especially spath command configuration.
Anyway, try something like this

index=mt_index sourcetype="json_no_timestamp" 
| spath 
| rename custom_fields{}.id AS id2 custom_fields{}.name AS name custom_fields{}.value AS value 
| rex "\{\"id\":\s+(?<id>\d+)," 
| eval 
     id=mvindex(id,0), 
     Date=mvindex(value,0), 
     Apply_Time=mvindex(value,1), 
     Actual_Time=mvindex(value,2), 
     created_on=mvindex(created_on,0), 
     updated_on=mvindex(updated_on,0)
| table id Date Apply_Time Actual_Time created_on updated_on

Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...