topic Re: I would like to make custom_fields a table column. in Splunk Search

I would like to make custom_fields a table column.

TAmemiya — Tue, 29 Sep 2020 17:16:15 GMT

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.

Re: I would like to make custom_fields a table column.

gcusello — Sun, 17 Dec 2017 06:58:37 GMT

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

Re: I would like to make custom_fields a table column.

niketn — Sun, 17 Dec 2017 07:21:14 GMT

@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

Re: I would like to make custom_fields a table column.

TAmemiya — Wed, 27 Dec 2017 10:02:36 GMT

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